After having a bit of a nightmare last night trying to get a SQL server connection working, I decided that it was a good time for a debugging blog post. Often you get all manner of errors when you cannot connect to SQL Server and they may not be strictly accurate in their error description. I have had the usual code 40 could not connect, which mentions Named Pipes but also invalid username and/or password which I knew was not the case. Anyway, here is a fairly complete list of things to check when you cannot connect to SQL server. You can retry the connection after each thing you change, some of these will already be correct.

  1. Sanity check. Ensure the server is connected to the network and that you can ping it from your client. You might need to open ICMP-Echo Request in the firewall to allow this but often it is enabled by default.
  2. Run up the SQL Server Configuration Manager (which is in the Start Menu under SQL Server/Configuration Tools) and do the following.
  3. First click on the SQL Server Services tree item and ensure that SQL Server service is started. If not, it might be set to manual, disabled or there may be some other problem which will usually be logged in the Windows Event Viewer if you cannot start the service. (You do not need the browser service to be running, this just allows your machine to broadcast its server to the network which makes it easier to find from various database applications.)
  4. Secondly, click on the SQL Server Network Configuration tree item and the item Protocols for (in most cases there will probably only be one item in this menu). If you are connecting from another machine, TCP/IP must be enabled. Sometimes it is not enabled by default for security reasons. Right-click it and choose "enable" if it is disabled.
  5. After this, right-click TCP/IP and choose properties and click the IP Addresses tab. The entry under IP ALL should have nothing in TCP Dynamic Ports and 1433 (or another port if you prefer) in the TCP Port box. 1433 is the standard port for SQL Server.
  6. Open SQL Server Management studio on the server and connect to the server locally using an sa account. Right-click the server in the Object Explorer and choose properties. Then select the security element and check to see that the server has windows and/or SQL Server authentication depending on what you want to use (e.g. if you are trying to connect with a username and password but the server is windows only, it will not allow you to connect, even with sa)
  7. Open the security folder at the server level in Management Studio and open logins. Ensure that this lists the account you are trying to login with and that the accounts are enabled. At this point, these logins only imply that you can connect to the server, not that the login has any database access - that is a different issue related to roles, database users and permissions. If your problem is permission related, your error will be more specific (cannot open Database x, it does not exist or you do not have permission)
  8. Ensure that the Windows firewall has TCP port 1433 open (or whatever port you have used) and that the scope is correct. For instance, it might be restricted to specific ip addresses or remote ports. If your server is behind a corporate firewall, it is often safe to reduce the scope on the rule although you can always restrict connections to the local subnet. If you are using SQL Server Browser, you will also need UDP port 1434 open.
  9. Ensure that the client machine doesn't have outgoing connections firewalled. This is not the default for Windows but it still might be a problem if someone has switched it on (outgoing connections are usually allowed unless explicitly blocked).
  10. If you are having problems with Windows logins, ensure that the SQL server has been joined to your domain so it has the ability to find Active Directory and the permission to check the login credentials.
  11. If you are having problems with a SQL server login, create a new login on the server to ensure that it is not just a case of a forgotten password or something else unusual that has been done to the login or its permissions.
  12. If you are logging in to SQL Server Express, your connection usually needs to use \SQLEXPRESS as the sever name since the express database is added as a named instance. On full SQL Server, there is usually a default instance in which case you can connect directly to . You can see what the names are when you connect to it in Management Studio and the name is in the Object Explorer.
  13. If you are logging in using a name rather than an IP address, make sure that the DNS is correctly resolving the name to the right IP address. You can use ping on the command line to see what ip address is being resolved to. If this is wrong, you might have an entry in your hosts file (windows\system32\drivers\etc\hosts) pointing to a hard-coded IP address, otherwise your DNS server needs adjusting.
  14. If your server and your clients are not on the same subnet, you will need a bridge to allow your subnet to talk to the other subnet. This needs to be configured by your IT department.