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

In this article, I will explain

Check the connectivity between Application Server and SQL Server Instance in Database Server.


Scenario

If you are working in an implementation team, you will walk through the following:

  • Check the connectivity between the application server and database server.
  • Make sure that the SQL Server port (default is 1433) is opened and accessible through the application server.
  • Make sure that the web site application pool account can access a specific database.
  • Make sure that the SQL Server account can access a specific database.

Check the connectivity between the application server and database server.

Simply, you can use Ping cmdlet to verify that the Application Server can communicate and reach properly with the Database Server over the network.

Check the connectivity between the application server and database server.

In this case, you just checked the network connectivity, but what about if you need to check the port accessibility of the SQL Server instance.


Check the Port Accessibility of the SQL Server Instance

To check the port accessibility of the SQL Server instance from the application server, you should run the telnet command from the application server.


telnet "the SQL Server IP" 1433

For more details check Enable the telnet client feature on Windows Server.

Note:

  • If you are using High Availability SQL Server, you should use the SQL Server Virtual IP.
  • The default port of SQL Server is 1433.

Test SQL Server Instance connectivity/accessibility using UDL data link file

Consider, you need to check if a specific user has access to a specific SQL Server database.

In this case, you can achieve that by creating a data link UDL file by doing the following:

Steps:

  • At any place on the application server desktop > Drop Right Click.

Create a data link UDL file

  • Select New > New Text Document.

Test SQL Server Instance connectivity/accessibility using UDL data link file

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

Create data link UDL file

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

Check the port accessibility of the SQL Server instance

  • Type or select the SQL Server name.

Check the connectivity between the application server and database server

  • Select the authentication mode.

Test SQL Server Instance connectivity

  • 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

  • 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

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

Check the SQL Server Instance Port status

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

text SQL Server Instance accessibility


Applies To
  • SharePoint.
  • SQL Server.
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 )

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