re: Connecting Checklist
That would be a much useful tool.
« December 2005 | Main | February 2006 »
January 20, 2006 • Vol.28 Issue 3
Page(s) 24 in print issue
As a developer and author, I find myself constantly researching new technology. Ostensibly, my readers (mostly other developers) are doing the same. The problem is that the information available is all too often overwhelming—even for the experts. Incidentally, all of us are experts on something. Some know every aspect of getting an aging lawnmower to start; others (supposedly) know how to build an effective antiballistic missile defense system (although IMHO there are too many “experts” in this field). But I digress.
With Microsoft churning out new technology at breakneck speed, it seems the epoch ages of technical evolution last a few years (months or weeks) with or without “intelligent design.” As a result, Microsoft, third-party authors, trainers, and content providers (myself included) churn out material based on current functionality; we discuss what works now. Because of this, developers (and authors) can be easily overwhelmed. When we search, we find too much content that’s not helpful, assuming we can find it in the first place. The concepts these topics describe might be correct, but the implementation and code is often unusable or out of date. This is especially true of articles written against alpha or beta versions.
Mining The Documentation
When working with these new and improved Microsoft .NET technologies, I spend far too much time mining the Visual Studio 2005 documentation to find a few nuggets of useful information. This process can be very frustrating as the doc is so poorly organized and cross-referenced. I suspect that there is a lot of useful content there that simply can’t be located without more dynamite. As a side note, I just heard that a portion of the documentation topics are machine-generated. I kinda thought so given the lack of meaningful content in some topics.
Generally, I think there are several problems here. First, the documentation is far from complete. Consider that the doc team probably froze the documentation in May or June (anticipating a September or October RTM). This means the DVD (local) doc is badly out of date. The Visual Studio 2005 online doc is not much better, and it also assumes you have connectivity when you’re researching a problem. This is not usually the case when I fly or sit in a hotel in Justshootmenowandputmeoutofmymisery, Texas. I have also found that many newer topics are very sparse, lacking essential detail on how to use the object. Too often the details on where this concept fits with others and how it works in practice are missing. It’s like buying a model airplane kit only to discover a block of wood and a picture of the finished plane.
Another problem is that the Microsoft-provided help search engine sometimes gives you information about other (often irrelevant) architectures. For example, when I’m working with a Smart Client application, the programming paradigm and the objects I’m working with are very different than those used with ASP.NET. Yes, these two architectures have objects and concepts in common, but even then they are often managed differently.
Is Google The Best Source?
Many of us depend on Google to find out how to best use and apply these new tools. We have to. Yes, Google does an adequate job of finding stuff, but it can’t (or doesn’t) keep track of when the article was published or when it should have expired. It can’t validate the topic either; you have no way of knowing the credentials of the person who wrote it. So, no, Google does not always find relevant, suitable content in the sense that content written one, two, or more years ago has any bearing on what one is trying to do today. Similarly, if one is still working on Visual Basic 6.0, an article on “Visual Basic” might detail Visual Basic .NET that is less than no help at all because it can cause more harm than good.
So what is a developer to do? Get a book? Well, until the product has been on the shelf for four to six months, you won’t find any books that describe how the product actually works based on the RTM version. It takes that long to get a book finalized, edited, printed, shipped, and distributed. Magazine articles are not much better.
Blogs are probably the most current source of information on technical topics. Not only that, but blogs are interactive. This means if someone reads the content and finds a flaw or a nuance not mentioned in the article, the reader can contribute a comment (or an ad for online poker). However, just because something is posted to a blog does not make it correct, relevant, or current. Make sure to check the posting date and the author’s credentials to make sure. We should also encourage bloggers to purge dated material or at least mark it to show the applicable version. For a taste, try betav.com/blog/billva.
Updated: March 7, 2006: Added ASP.NET connecting info.
In the newsgroups and in my conference sessions, there still seem to be a lot of questions on getting connected. Perhaps this checklist can help. Yes, connecting can be a ... well, problematic. ASP apps also pose additional issues. In order to connect to SQL Server several factors must be in place. Go through this checklist to help get (and stay connected) to an instance of SQL Server.
1) Your application must identify the machine name and the instance of the target SQL Server. This means you’ll need to reference the machine name and the instance name unless you have installed SQL Server as the “default” SQL Server (the only option pre-SQL Server 2000). The syntax is “<machine name>\<instance name>”.
2) To identify the local machine name, you can use “.” or “(local)” or in VS 2005 (2.0 Framework) you can use My.Computer.Name. You can also define an alias to address a specific machine and instance and use this alias in the Server= element in the connection string. The term “localhost” refers to the local TCP/IP address and is used to access the local IIS server, not the local SQL Server.
3) When connecting to an instance of SQL Server directly via an IP address (which raises a number of scary security issues), see the addendum at the bottom of this checklist.
4) The DNS must be able to identify the named machine (it must resolve to a valid IP address). If the DNS is not working, the server can't be found. It might be necessary to alter the “hosts” file (Windows\system32\drivers\etc) to map a specific machine name to an IP address to circumvent this issue. DNS accessibility can be a function of how the NIC is configured or the availability (state) of the domain controller.
5) The targeted instance service must be running on the specified machine. SQL Server can be configured in single-user or paused mode or the service might be stopped for some reason. These states don't permit external connections. You can check the status of the service using the services.msc MMC snapin or via code (2.0 Framework).
6) If you're using SQL Server authentication, the server instance must be configured for mixed mode security. By, default SQL Server is not configured to support SQL Server authentication (what’s called “mixed-mode” security.
7) If you’re using SSPI authentication (Trusted Connection=True or Integrated Security=True (or SSPI)), the Windows account you’re using (or impersonating) must have an account on the server one way or another. This means there must be a SQL Server login that grants sufficient rights to the user’s group. By default, Windows users in the Administrators group are granted access to all objects but the DBA might (and usually does) restrict those rights.
8) In the ASP case, IIS opens the connection as ASPNET (pre-Windows 2003) so this login must be granted rights. Yes, you can try to impersonate a Windows user using an ASP application, but this is complex to setup and keep working. Other complicating factors here include running the SQL Server on a different system—other than the IIS system.
9) ASP applications might have to run under a trusted domain account.
10) The login account must exist and be granted rights to the initial catalog.
11) Depending on the protocol you choose (or is chosen for you), you must punch a hole or two in the client firewall if the server is on another machine. This means getting into the Windows firewall program. If the server is on the local system, it's best to bypass the network providers and use the shared memory provider. You must also make sure the protocols you need are enabled on the server--they are off by default. The best way to manage the services is using the new SQL Server Surface Area Configuration tool and the SQL Server Configuration Manager.
12) The server itself must have available connections. This is not a given. Check the server configuration to ensure that the license restriction or the DBA-imposed restriction on accounts has not been exceeded.
13) The connection pool must also have available space. The new ADO.NET 2.0 exception handlers are not that helpful. They often return a generic message that doesn't really help. The ConnectionString parser in ADO.NET 2.0 is better than ever. It’s more tolerant of additional spaces and such and will simply report an exception if the syntax is incorrect. That said, remember that the ConnectionString is used to test for existing connections in the connection pool. It can’t vary from connection-to-connection without creating a new pool. Connection pooling issues are not a concern for Windows forms applications (smart clients), but are a vital issue for ASP and Web Service applications.
14) For Windows 2000 and XP IIS logs on using “ASPNET” account credentials. For Windows 2003, IIS uses “INetworkService” by default. Some folks think that impersonating another account for XP and Windows 2000 is not that hard. They say there are two ways to do it:
a) In web.config: .
b) Change the anonymous account on the IIS server and in web.config set:
For Windows 2003 you must setup a new application pool with the new account as its identity. (Thanks to Patrik Löwendahl [C# MVP] for this contribution.)
15) When connecting to a SQL Server instance connected via IP address:
a. While 1433 is the official Internet Assigned Number Authority (IANA) socket number for SQL Server, you should still determine the port being exposed by the targeted server instance—each instance can be addressed by its own TCP port. The SQL Server Configuration Manager exposes the IP address of the TCP port if it has been assigned. If Dynamic Ports are enabled, then you need to determine that port. See KBs 269882, 823938 and 287932 for more details.
b. Using the same tool, make sure the TCP/IP protocol is enabled.
c. Using your Firewall administration tool on the client and the server, make sure this port is not blocked. It will be blocked by default.
d. Make sure your connection string uses “Network=dbmssocn;” as the protocol.
e. It might be easier to simply add an alias to the Host file located at “C:\WINDOWS\system32\drivers\etc”.
I think that covers most of the known issues. If you think I need to add more to the list, let me know.
Other useful links:
http://support.microsoft.com/Default.aspx?id=317012
January 6, 2006 • Vol.28 Issue 1
Page(s) 23 in print issue
Starting the New Year right means making (and hopefully keeping) resolutions that will make us better people (or perhaps just thinner). I think about how I can be a better person all the time but I (like others, I suspect) don’t have the willpower to alter my self-destructive behavior enough to stop eating burgers.
Actually, it seems my addiction is to catsup—something I discovered on my last trip to New Zealand and Australia. They only serve “tomato sauce” in tiny (and expensive) little packets down under, so my fast-food experiences were not particularly satisfying. No, it just ain’t the same.
Developers and their managers might want to make a few new resolutions that can help them (and their companies) survive another year. Here are a few ideas—perhaps you might want to pick a few to adopt.
Resolve to limit use of trusted connections. When building applications, consider that SSPI security (used to connect to databases using Windows authentication) can lead to Trojan horse attacks. I suggest using security configurations that limit the surface area of potential attacks. This means creating (and using) SSPI or DBMS accounts that have focused rights so, if they are compromised, the damage they can do is limited. It also means that managers and DBAs have to be very (very) careful when running any software—even from people they think they can trust. When they log on with super rights, they take on a big responsibility to ensure what they run only does what it’s supposed to run.
Don’t log on as administrator (or SA). See above. Sure, this is hard, but if you routinely log on as an ordinary user, the chances of you running destructive software is far more remote. Learn how to use the existing “runas” utility and set up your work so you can perform specific (critical) administrative tasks using appropriate (just enough) security rights.
Back up your data regularly and restore it at least once. I can’t count the number of times the people and staff I support have lost important work—from a year’s worth of work to homework done at the last minute. Sure, many of us back up our work and systems, but how many of us have tested the recovery process? Remember, when you lose an entire system you probably won’t want to replace it with the three-year-old hardware; you’ll want to upgrade it to a more recent configuration. However, if you don’t use the same motherboard, processor, and disk configuration, you might find that your Windows system image won’t run once restored. Yes, there is a way to (eventually) work around this, but until you actually go through the process of restoring your systems (and not just the critical ones), you won’t know what pieces you need to bring it back to life.
Fix the broken stuff but leave what’s working alone. Every day we’re assaulted with marketing sessions, email, and staff recommendations to “upgrade” your systems, applications, development platforms, and training. I know, I often recommend people move from systems and approaches that are crippling or hobbling their applications. However, I also encourage folks to follow my dad’s (sage) advice: “Son, if it ain’t broke, don’t fix it.” He said this as he stood over me and my three-speed bike disassembled on the lawn.
Get off JET. At almost every turn I hear about another troubled soul tearing her hair out as she tries to get a serious application to work after having depended on the free JET database management engine. I won’t go into the litany of issues you expose yourself to when using JET, but consider that it’s not (IMHO) suitable for use in any business where security and data stability are important.
Don’t format anything after 8 p.m. Microsoft and the dozen or so other companies I worked for before then often “encouraged” their people to work long hours. However, by the time I started at Microsoft, I had learned to work “smart” instead of “long” hours. While at work, I tried (sometimes in vain) to stay focused and meet my commitments. I also learned that the number of mistakes I (and my staff) made after hours was very costly; it often took many more (long) hours to fix the damage caused by a heavy-eyed engineer.
True, kids (and the sub-30 kids who code nowadays) don’t need as much sleep as us old folks seem to need. But they too have their limits. Encourage your staff to go home, unwind, and recharge their lives. This means going to your daughter’s soccer game on Saturday and not spending the day at the office trying to meet another deadline. For managers it means building reality into the schedule and encouraging your staff to want to stay working for you long after the latest crunch.