How to test whether your SQL server is available or not without installing anything?

Technorati Tags: Data Connection

Scenario: Recently, I was asked what if I want to test connectivity to my SQL server from a web server or from my local desktop machine ?

Approach: Well, this seems a much obvious question that can be answered on the spur as use PING <server IP> from the command prompt.

But PING just does the basic test and tells that whether your server is available or not. What if you want to see that your databases that your SQL server is hosting are available or not ?

There is a more smatter and easy way of doing this. The idea is to use UDL (Universal Data Link) files (i.e  .udl files).

Overview of UDL files:

UDL files are an easy, efficient and fast way to test connection to a server from your work station or from web server eliminating the need to install any application (e.g dtcping) or SSMS (SQL Server Management Studio). Also, its a good way to quickly test that the credentials you specified will connect to a server and to build a connection string.

How do I do this ?

1. Create an empty .txt file. Name it for example MyConnection.txt .
2. Now rename the extension and save it as MyConnection.udl .
3. Below is the image of the MyConnection.udl file.

4. Now double click the MyConnection.udl file to see the below screen.

5. Click the Provider tab and select SQL Native Client and then click Next.

6. Enter the data source: localhost or IP address of the server. Here I have used Windows authentication, but choosing the second option allows you to use SQL server authentication wherein you have to provide username and password. (Note: Here there is an option to save the password as blank or Allow saving password option and choosing this option will save the password as clear text on the disk which is a security risk !). Next you can choose to connect to the specific database by clicking the drop box and selecting the database name. Click test connection button to verify that the connection is successful. Below is what you will see:         

7. Press OK when you are done.

8. Now we can examine the contents of our MyConnection.udl file by renaming it back to MyConnection.txt

9. Below are the contents of the file:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=AdventureWorks;Data Source=localhost

10. You can use the above connection string in your application code directly !

Hope this helps !

Thanks,

\\K