Just about every deployment has an SQL shenanigan that slows me down. Usually I will install SQL Management Studio simply to prove that I am able to connect to SQL.
That’s a BIG hammer..
I am aware that it can be done from command line, but SQL gives me the jitters on the best of days. So just recently I came across a neat little trick.
In layman’s terms you are essentially performing a Data Link (UDL – Universal Data Link) test to check connectivity from a Windows server to a SQL server instance and database.
Here is how it works..
First..
Creating the UDL File
Create a new txt document and rename it so that the file extension is .udl
TIP You will need to clear the “Hide file extensions for known file types” check box to rename the .txt to .udl
You are now ready to test..
Testing Connectivity
Step 1
Launch the .udl file. In my case I called it SQL Connectivity Test.udl. You should see the following GUI
Provide the server name (if you click on the drop down arrow you should see a list of all the servers found)
TIP Under the provider tab you can select other database servers.The server name drop down box is populated using the SQL Browser service. When you have multiple pools its likely that you will only see the local pool server names. You should still be able to connect. If not, you may need to add a firewall rule to allow remote SQL Browser connections in the destination server in question.
Step 2
Provide the account details for accessing the SQL server. In my case I was logged in as the user with permissions. You can either use the account you have logged in as by selecting the “Use Windows NT Integrated security” radio button, or specify different credentials by selecting the “Use a specific user name and password” radio button.
Step 3
Select the database to connect to, once again you should see the databases available if you click on the drop down arrow. Of course you wont get to this step if your authentication doesn’t pass or the server name is invalid.
Step 4
Click Test Connection
Any errors you receive should be sufficient to forward to the SQL admins for resolution.
