How to Check SQL Server Connectivity between Application Server and Database Server

How to Check Network Connectivity between Application Server and Database Server

In this article, we will explain How to check SQL Server Connectivity between application server and database server for a specific user. by exploring the folloiwng:

You might also like to read SQL Server Best Practices for SharePoint 2019.


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

To check SQL Server Connectivity between application server and database server, you would 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.

So that, In the next section, we’ll explore multiple ways to make sure that the Application server can reach the database Server properly.


How to Check Connectivity between 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.

ping Server IP
How to Check SQL Server Connectivity between 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 SQL Server Connectivity for Specific Port

To check if the SQL Server Instance port is opened or not, you should run the telnet command from the application server.

telnet "the SQL Server IP" 1433

You might also like to read Enable the telnet client feature on Windows Server.

If the SQL Server port is not opened on the database server, when you run the TelNet cmdlet from the application server, you will get the below error:

Connecting To 127.0.0.1…Could not open connection to the host, on port xxxx: Connect failed

telnet Connection failed

Note: Although the SQL Server port is opened, you may get connect failed error when you run TelNet cmdlet, which means this port might have been blocked by firewall or antivirus settings or any other policy settings.

If the SQL Server port is opened and it’s not blocked by any other settings like firewall, antivirus ..etc, you will get the below screen which means your application server can reach the database server on the specified port.

telnet port is opened and Connection success

Note:

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

You might also like to read Exit Telnet Session.

Create SQL Server Alias CLICong.EXE

It’s recommend to configure SQL Alias on application server to avoid any additional work if you decide to change the server name in the future.

To configure SQL Alias using cliconfg.exe, you should do the following:

  • Run Command Prompt as Administrator.
Run Command Prompt As Administrator
  • Run the below command to open SQL Server Client Network Utility.
CLICONFG.EXE
  • Go to the “Alias” Tab.
  • Check “TCP / IP”.
  • Provide the Alias Name.
  • Provide the Server name and the SQL Server port.
Configure SQL Server Alias CLICong.EXE

Note: “Dynamically determine port” requires the SQL Server Browser to be running.

Get SQL Server Port

To get the SQL Server Port, you should do the following:

  • Open SQL Server Configuration Manager.
  • From the left side, click o “SQL Server Network Configuration” > Protocols for your instance.
  • Double Click on “TCP / IP” to open its properties.
  • At the “IP Addresses” tab, scroll down until you find “TCP Port”.
Get SQL Server Port

You might also like to read SQL Server: Get the Detailed Information Via SERVERPROPERTY

Configure Hosts File

Alternatively, you may need to map and resolve host-name to a specific IP address. this is can be done by configuring the Hosts file.

To edit hosts file and add a new entry, you should do the following:

  • Open Notepad as Administrator.
  • From the “File” menu, click on “Open”.
  • Navigate to “C:\Windows\System32\drivers\etc“.
  • Click on the Hosts file to edit it.
Hosts file path in SharePoint
  • Click enter to add a new entry, then type the Server IP + Tab from keyword + Host Name.
DB Server IP  Hostname
Configure Hosts file in SharePoint

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

Consider, you don’t have SQL Server management Studio on the application server and you would like to check if a specific user has access to a specific SQL Server database.

In this case, you could check the SQL Server Instance accessibility by creating a new data link UDL file.

It’s strongly recommended to don’t install any DEV tools like SSMS, VS ..etc on the Production environment,

So this way is an optimum solution to check SQL Server instance connectivity in a production environment without using SQL Server Studio Management.

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 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 SQL Server Instance 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

Conclusion

In conclusion, we have learned how to check connectivity between two servers by using ping,telnet and UDL file.

We’ve also learned how to

  • Create SQL Server Alias CLICong.EXE.
  • Get SQL Server Port.
  • Map host name with IP address by configuring Hosts file.
Applies To
  • SQL Server, Database Server.
  • Application Server.
  • SharePoint Farm.
You might also like to read
Have a Question?

If you have any related questions, please don’t hesitate to ask it at deBUG.to Community.

3 thoughts on “How to Check SQL Server Connectivity between Application Server and Database Server”

  1. Pingback: Workflow Manager: Port is blocked | SPGeeks

  2. Pingback: Cannot connect to database master at SQL server in SharePoint | ITCore

Leave a Reply