How to Check SQL Server Instance Connectivity from the application server to database server for a specific user without using SQL Server Management Studio

In some situation, you may need to check the SQL Server Instance connectivity from the application server to database server for a specific user especially when you don’t have access to SQL Servers on your farm based on your company policy.

You may try to perform Ping cmdlet for the SQL Server IP and Telnet to SQL TCP port.but that means There’s a network connectivity between two servers and you can reach the database server from the application server.

But what about if you need to test connectivity to SQL Server Instance for a specific user without using SQL Server Management Studio?

In this case, you can test connectivity to SQL Server Instance by creating a data link UDL file as I have mentioned below:

Steps:

  • At any place on the application server (any server in your farm) desktop > Drop Right Click.

New Menu - New Text Document.png

  • Select New > New Text Document.

New Text Document

  • Rename the file to any appropriate name > Change the extension from .TXT to .UDL file.

UDL file.png

  • Double click on the created file > The Datalink Properties should be shown.

Data Link Properties.png

  • Type or select the SQL Server name.

Data Link Properties - Select or enter your database server name.png

  • Select the authentication mode.

Data Link Properties - SQL Authentication.png

  • Select the database name.

Data Link Properties - Select Database.png

  • Click on Test Connection.
  • If the connection has been established successfully you should get the following message.

Data Link Properties - Test Connection.png

  • If the provided user doesn’t have an elevated privilege to connect to SQL Server Instance you will get the following message.

Data Link Properties - Login failed.png

  • If the provided user does not have an elevated privilege to connect to a specific database you will get the following message.

Data Link Properties - Test connection failed.png

  • If the provided user has particularly elevated privilege for specific databases you will get only his related databases.

Data Link Properties - Limited to specific database.png

Enjoy 🙂

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s