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:
- At any place on the application server (any server in your farm) desktop > Drop Right Click.
- Select New > New Text Document.
- Rename the file to any appropriate name > Change the extension from .TXT to .UDL file.
- Double click on the created file > The Datalink Properties should be shown.
- Type or select the SQL Server name.
- Select the authentication mode.
- Select the database name.
- Click on Test Connection.
- If the connection has been established successfully you should get the following message.
- If the provided user doesn’t have an elevated privilege to connect to SQL Server Instance you will get the following message.
- If the provided user does not have an elevated privilege to connect to a specific database you will get the following message.
- If the provided user has particularly elevated privilege for specific databases you will get only his related databases.