“What’s wrong with the database?" or “I can’t connect to SQL Server”  How many times have you heard phrases like that from your developers and QA people? Remember, DBA means “default blame acceptor”, or guilty until proven innocent. I am going to show you how to prove yourself (and SQL Server) innocent…

Many veteran DBAs and developers are probably familiar with using a Microsoft Data Link file to test a connection to SQL Server without having to have SSMS or Visual Studio installed on a machine, but I thought I would run through it in case you are unfamiliar with this technique.

First, you need to make sure that Windows Explorer is configured to not hide file extensions for known file types (so that you can see and change the file extension of a file). Then you can simply right-click on your Windows Desktop and choose “New Text Document”. After you have created an empty text file, you need to right click on the file, choose Rename and change the file extension to .udl. You will get a warning dialog that you should click Yes to dismiss.

Next, you want to double-click on that UDL file, and you will get a tabbed Microsoft Data Link Properties dialog, opened on the Connection tab.

You should switch to the Provider tab, and make sure it is using an OLE DB provider that is meant to connect to SQL Server. This list of providers will be different depending on what is installed on the machine.

Then, you want to switch back to the Connection tab and enter the credentials that you need to connect to SQL Server. If you choose “Use Windows NT Integrated security”, that means that it will use Windows authentication with the interactive user’s credentials (which is probably not what you want). If you choose “Use a specific user name and password”, that means that it will use SQL Authentication, which means that you must have Mixed Mode authentication enabled on your SQL Server instance.

At any rate, after you enter your credentials, you can click on “Test Connection” to verify that you can connect to a particular database on a particular server or instance with those credentials. I like to do this locally on the database server first, then on a different server (such as a web or application server) next.

After you have done these tests, you have proof that the DB server and instance is running, that the credentials work (and that you don’t have orphaned users with SQL Server authentication), and that you have network connectivity between the two machines. This gives you some pretty high caliber ammunition to defend yourself (and solve the problem) when your developer’s claim they cannot connect to SQL Server.  I like to do this test ahead of time, to increase my confidence that SQL Server is configured correctly. If your developers or applications cannot connect to SQL Server, but these tests pass, you can be pretty sure that there is something wrong with the connection string they are trying to use to connect.