(Updated May 2007)

In the newsgroups and in my conference sessions, there still seem to be a lot of questions on getting and staying connected to SQL Server--especially with SQL Server 2005 Express Edition. Perhaps the checklist shown below can help. This content is extracted from Chapter 9 in my book "Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)" available in bookstores and online all over the world.   This whitepaper assumes that you’ve already begun to implement an application and can’t seem to get connected (or stay connected).

If you still find that this information is not enough to get you connected to your SQL Server database engine or you need one-on-one support, feel free to contact me for a personal mentoring session or attend one of my conference sessions that focuses on your specific needs. I have also updated my whitepaper “Managing and Monitoring .NET Connection Pools” which discusses a number of pool-related issues as well as architectural design issues that can help eliminate a lot of needless overhead while connecting.

Understanding the Issues

Yes, connecting can be a ... well, problematic. ASP apps also pose additional issues but anyone working with IIS and remote server instances can tell you the same. Consider that SQL Server's default behavior often makes it invisible to the outside world—even your own system in some cases. SQL Server can be accessed via DBLibrary, ODBC, OLE DB or native .NET data providers but I recommend that you use the .NET SqlClient namespace for best performance and most reliable connections—at least from .NET Framework applications. For COM-based ADO (what I call ADO Classic) I recommend using the SNAC OLE DB (COM) provider which is designed specifically to enable SQL Server 2005 functionality. Each SQL Server data access interface requires a specially formatted ConnectString to identify the system hosting the SQL Server service and usually the name of the SQL Server instance. It also specifies the security protocol to use and usually the default database.

Configuring the Server and Choosing a Protocol

Anytime you want to access SQL Server you’ll do it over the network (typically using TCP/IP protocol) if your SQL Server instance is not hosted locally. If you are accessing a local instance (as is typically done with SQL Server Express Edition), it’s best to use ConnectionString keywords that reference the (local) or “.” system. This tells ADO.NET to use the Shared Memory provider which is fastest (and simplest) of all providers.

  When traversing the network to get to your chosen SQL Server host and instance, keep in mind the protocols, ports and the firewalls that protect them must all be correctly activated and configured. It’s best to use the SQL Server Surface Area Configuration tool and SQL Server Configuration Manager to inspect and configure the appropriate protocols, ports and services—before you try to connect. Remember that by default SQL Server does not always expose itself to the network. If you’re having trouble connecting, make sure you visit the aforementioned tools to enable visibility.

Unlike SQL Server 2000, you’ll also need to start the SQL Server Browser service for all SQL Server 2005 editions if you want the SQL Server service exposed on the LAN. This additional layer of security ensures that unless you specifically ask for it, the existence of your SQL Server instance(s) will not be broadcast on the network for all to see.

Before you connect, SQL Server must be pre-programmed to accept the connection with the given security credentials and must have granted the specified login ID rights to the chosen database. In the case of SQL Express installed using the User Instance=True keyword, the current Windows User is assumed to have system administrator (SA) rights but you must specify “Integrated Security=SSPI” for this to work. This is a complex subject. If you plan to take this route, I suggest reading Chapter 9 of the aforementioned book.

In a Nutshell

Let’s summarize: In order to connect to SQL Server several factors must be in place. If something goes wrong, you might find that the exception handlers used by the SQL Server providers lack a great deal of granularity. All too often they return a message:

This generic one-size-fits-all error message is returned whether or not you are attempting a “remote” connection. No, don’t bother clicking on the help icon—there is no additional help provided by Microsoft. However, in this case note that the hint at the bottom tells us that the server/instance could not be found. Remember that when you use a name to refer to a server on the LAN the DNS server must resolve this name and the server itself must be configured to expose it. If the DNS service is not programmed correctly, it might not be able to see your SQL Server Instance. Unfortunately, the reason you get this message might have nothing to do with remote connections configuration.

Other impediments to connecting include XP and other firewalls, security configurations, improperly enabled ports, incorrectly installed server instances, missing databases or user accounts and in the case of IIS/ASP applications incorrectly configured accounts on the server to map the IIS anonymous accounts.

The Connection Checklist

The following checklist should help your code get (and stay connected) to any instance of SQL Server.

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 “SERVER=<machine name>\<instance name>”. If you didn’t specify an instance name, the default SQL Server instance is referenced by the server name as in “SERVER=<machine name>”.

1)      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.

2)      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.

3)      The Directory Name Service (DNS) in your domain 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. In this case 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. Check with your system admin about DNS issues.

4)      SQL Server 2005 no longer broadcasts availably of its instances on the LAN—that’s now done by the SQL Server Browser service. If you need to access an instance of SQL Server on another system, the SQL Browser service must be running on the host machine.

5)      The targeted SQL Server instance service must be running on the specified machine (not stopped or paused or in recovery). Remember that 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). It might be a good idea to check with the SQL Server DBA before arbitrarily restarting the service.

6)      If (and only 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. In any case, the login account specified in the ConnectionString or inferred (with Integrated Security=SSPI) must exist and be granted rights to the initial catalog (the default database).

8)      In the ASP case, IIS opens the connection as ASPNET (pre-Windows 2003) or IUSR_<machine> 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. ASP applications might have to run under a trusted domain account.

9)      If the SQL Server instance is hosted on another machine, depending on the protocol you choose (or is chosen for you), you must punch a hole or two in the client firewall. This means getting into the Windows firewall program if it’s enabled. If the server is on the local system, it's best to bypass the network providers and use the shared memory provider.

10)   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 SQL Server Surface Area Configuration tool and the SQL Server Configuration Manager. Generally, you only need the TCP/IP protocol.

11)   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.

12)   The connection pool must also have available space. In this case, your connections will be accepted at first but once the pool is full additional connections will fail with a timeout exception. No, increasing the size of the connection pool won’t help any more than putting a band-aid on a leaking boat’s hull. Managing and monitoring the connection pool is discussed in great detail in the book. It’s summarized in this whitepaper.

13)   If you’re getting timeout exceptions on the first attempt to connect, it’s unlikely that the connection pool is an issue but available resources, LAN latency and other hardware configuration/performance issues could be playing a role. If timeout exceptions occur after that, check the connection pool (as described in the aforementioned whitepaper. A full pool is an indication that you’re not releasing connections in time. Typically, this means you aren’t closing connections or you’re pushing the server too hard.

14)   While the new ADO.NET 2.0 exception handlers are better than the old handlers, they often return a generic message that doesn't really help (as discussed above). They will tell you, however if the provider has found the server or not. Once the server is accessed, the next layer of protection is rights. That is, does the set of credentials specified have access rights to the initial catalog (default database) in question?

15)   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.

16)   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 at least three ways to do it:

a)      In web.config

b)      Change the anonymous account on the IIS server and in web.config set:

c)       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.)

17)   When connecting to a SQL Server instance connected via IP address: 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.

a)      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 hope this helps.