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
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" 1433You 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
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.
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 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.
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”.
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.
- Click enter to add a new entry, then type the Server IP + Tab from keyword + Host Name.
DB Server IP Hostname
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.
- 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.
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
- Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgment.
- SQL Server: How to get the current installed update level.
- The transaction log for database SharePoint_Config is full due to LOG_BACKUP
Have a Question?
If you have any related questions, please don’t hesitate to ask it at deBUG.to Community.
Pingback: Workflow Manager: Port is blocked | SPGeeks
Pingback: Cannot connect to database master at SQL server in SharePoint | ITCore
very informative, thank you