15 Oct 2010 - Digital Strategy // By Productive Edge Team

How to Troubleshoot SQL Server Connection Issues

The easiest way to test connection issues with Universal Data Link (.udl) files. No code required. Simply create an empty text file on a Windows machine with the extension .udl and double click it.


A wizard pops up and you can test your configuration pretty quickly.  If you did a default install of anything other than SQL Express, your instance name (the part to the right of the '' below) will default to MSSQLSERVER and shouldn’t be shown in the server name.  If you installed SQLExpress, your instance name will be SQLEXPRESS.


And, as a bonus, once you get it working you can open it in a text editor and extract your connection string. If it doesn't work on your local box, try it on the server itself. If that works, skip on down to the end of this for the culprit.

You can also attempt to telnet directly to port 1433 (the default TCP/IP port for SQL Server) to test connectivity.  You won’t see anything once you get there, but if the connection succeeds, you are in business.

If you are not running SQL Server locally, you could also get into trouble if remote connections are not allowed. To check/change this, Open up Management Studio and right click on the instance you are connected to and open the properties window:


and go to the connections tab and insure remote connections are allowed:


If none of that works, the problem you are probably having is related to the setup of SQL Server itself. Open up the SQL Server Configuration Manager:

alt text

then go to the SQL Server Network Configuration:

alt text

then double click on the Protocols for MSSQLSERVER:

alt text

and make sure that TCP/IP is enabled:


If TCP/IP is enabled, you may be running into firewall issues (it requires port 1433 by default, but it can be configured to a different port).   To see which port your SQL instance is running on, double click on the TCP/IP icon to open the properties page and on the IP Address tab you will find the TCP port you are running on:


How to Troubleshoot SQL Server Connection Issues