Old DBA Trick To Confirm SQL Server Connectivity

“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.

This entry was posted in SQL Server 2008. Bookmark the permalink.

12 Responses to Old DBA Trick To Confirm SQL Server Connectivity

  1. Unknown says:

    http://www.batteryfast.com/asus/f3.htm Asus f3 Battery http://www.batteryfast.com/toshiba/pa3594u-1brs.htm Toshiba pa3594u-1brs Battery http://www.batteryfast.com/toshiba/pa3593u-1bas.htm Toshiba pa3593u-1bas Battery http://www.batteryfast.com/toshiba/pabas111.htm Toshiba pabas111 Battery http://www.batteryfast.com/dell/xps-m1530.htm Dell xps m1530 Battery http://www.batteryfast.com/hp/tx2000.htm Hp tx2000 Battery http://www.batteryfast.com/hp/pavilion-tx1000.htm Hp pavilion tx1000 Battery http://www.batteryfast.com/dell/xps-m1730.htm Dell xps m1730 Battery http://www.batteryfast.com/hp/pavilion-dv6.htm Hp pavilion dv6 Battery http://www.batteryfast.com/toshiba/pa3395u-1brs.htm Toshiba pa3395u-1brs Battery http://www.batteryfast.com/toshiba/pa3421u-1brs.htm Toshiba pa3421u-1brs Battery http://www.batteryfast.com/toshiba/pa3451u-1brs.htm Toshiba pa3451u-1brs Battery http://www.batteryfast.com/toshiba/pa3399u-1brs.htm Toshiba pa3399u-1brs Battery http://www.batteryfast.com/toshiba/pa3399u-2bas.htm Toshiba pa3399u-2bas Battery http://www.batteryfast.com/toshiba/satellite-a100.htm Toshiba satellite a100 Battery http://www.batteryfast.com/toshiba/satellite-m40.htm Toshiba satellite m40 Battery http://www.batteryfast.com/toshiba/satellite-m45.htm Toshiba satellite m45 Battery http://www.batteryfast.com/toshiba/satellite-m55.htm Toshiba satellite m55 Battery http://www.batteryfast.com/toshiba/pa3356u.htm Toshiba pa3356u Battery http://www.batteryfast.com/toshiba/pa3356u-1bas.htm Toshiba pa3356u-1bas Battery http://www.batteryfast.com/toshiba/pa3356u-1brs.htm Toshiba pa3356u-1brs Battery http://www.batteryfast.com/toshiba/pa3356u-2brs.htm Toshiba pa3356u-2brs Battery http://www.batteryfast.com/toshiba/pa3456u-1brs.htm Toshiba pa3456u-1brs Battery http://www.batteryfast.com/toshiba/portege-m500.htm Toshiba portege m500 Battery http://www.batteryfast.com/toshiba/pa3451u.htm Toshiba pa3451u Battery http://www.batteryfast.com/toshiba/pabas067.htm Toshiba pabas067 Battery http://www.batteryfast.com/toshiba/pa3465u-1brs.htm Toshiba pa3465u-1brs Battery http://www.batteryfast.com/toshiba/pa3534u-1brs.htm Toshiba pa3534u-1brs Battery http://www.batteryfast.com/toshiba/pa3533u-1bas.htm Toshiba pa3533u-1bas Battery http://www.batteryfast.com/toshiba/satellite-a205.htm Toshiba satellite a205 Battery http://www.batteryfast.com/toshiba/pa3534u-1bas.htm Toshiba pa3534u-1bas Battery http://www.batteryfast.com/uniwill/255-3s4400-g1l1.htm Uniwill 255-3s4400-g1l1 Battery http://www.batteryfast.com/uniwill/un255.htm Uniwill un255 Battery

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s