Verify Connectivity to SQL Server

By:   |   Updated: 2017-04-25   |   Comments (1)   |   Related: More > Database Configurations

Problem

I'm having trouble verifying that an application server has connectivity to the SQL Server. We're not seeing anything in the SQL Server error log. I assume the application is configured correctly and so we suspect that there is something else preventing the connection. What can I do to check to see if we can connect from the application's system to the SQL Server?

Solution

As a DBA, we get this issue more often than we'd like. If we can quickly demonstrate that there is connectivity between the application server and the SQL Server, it rules that out from our troubleshooting efforts. Likewise, though, if we can't connect, then we need to correct that first before proceeding further. So let's look at how to do this.

Auditing Failed Logins

Before we can begin troubleshooting, we need to make sure that we see failed logins on our SQL Server. We do this by ensuring that we are logging failed logins for the SQL Server configuration.

Security Configuration for SQL Server

If you have to change this setting, realize that it doesn't take effect until SQL Server is restarted. If that's not possible, then you'll need to fire up Profiler and set up the correct event to audit failed logins. Ideally, though, you want to audit failed logins because not only does this information get written to SQL Server's log, but it's also placed in Windows' Application event log, meaning it can be picked up by a log collector and processed by your security monitoring systems.

We'll verify the auditing in a later step.

Name Resolution

With auditing configured, we move on to the actual troubleshooting steps. The first thing we'll want to do is verify that the application server can resolve the name we use for SQL Server. Here we're looking for the server name or friendly name. If you're dealing with a named instance, don't worry about the instance part. Just as verifying connectivity outside of the application is the first step in troubleshooting, the first part of verifying outside of the application is ensuring name resolution is happening properly.

We can do this by using ping from the Command Prompt or the PowerShell interface. You'll want to run this test on the application server. Simply ping the server/friendly name. We're looking for the response to come back "Pinging ..." and the name we specified followed by the IP address. Don't get caught up if the ping fails. At this point we're just testing name resolution.

Successful name resolution

What we don't want to see is something like this:

Failed name resolution

If you get "could not find host," then the name you're using can be connected with an IP. If that's the case, that has to be fixed before you can proceed any further. Verify that you have the correct name. If you do, then server and network folks need to investigate why name resolution isn't happening properly.

Before moving on, let's go back to the results of the ping. If you get successful name resolution, but you're not getting the replies like above, that may not indicate an issue. Some networks are configured where you can't ping. Any attempts will meet with failure. If, however, that's not the case with your network and you are getting the failures, then that's typically indicative of a network or server firewall issue.

Verifying the Failed Login Auditing

We've determined that auditing is turned on and we can get proper name resolution. Now we attempt to make a connection to the SQL Server from a location we know has connectivity. For instance, if you used SSMS from your workstation to connect and verify the audit settings, you can start a new Database Engine Query. You'll want to specify a SQL Server login. For the login name, choose something obvious that you can find easily in the SQL Server log and which others will be able to easily identify as you testing the system. For instance:

New Database Engine Query via SSMS

Try to connect and then verify you see the failed login in the SQL Server error log.

Log entry for failed login

If you don't, fire up Profiler, set up the security event for failed logins and test again.

Testing from the Application Server

Most application servers won't have SQL Server Management Studio installed. Therefore, we'll need a different tool to perform the test. We have such a tool: Data Sources (ODBC). You can find it under Administrative Tools. You can always find Administrative Tools under Control Panel so this particular tool is always available.

Data Sources (ODBC)

Go through the process of adding a DSN. We're not saving it, so starting the process to create a User DSN is fine. Choose the SQL Server driver (or the SQL Native Client appropriate for that version of SQL Server if it's installed on the application server). Specify the name you're using to connect to the SQL Server. In this case, if you're using a named instance, make sure to specify the instance name, too. Then you'll get to the screen where it prompts you if you want to connect to SQL Server to gather more information. You do. And you want to do so using SQL Server authentication. Specify a login you'll recognize immediately in the log.

Using Data Sources (ODBC)

When you specify a password and click Next, a login will be attempted to the SQL Server. Now you should be able to go back to SQL Server, look in the log and see if you have a failed login. If you do, you've verified connectivity between the application server and the SQL Server and the issue likely lies in the application configuration.

If you don't see the failed login attempt, then you may be facing a network or server firewall issue. It's entirely possible to be able to resolve the name, to even ping the name, but not be able to connect to the SQL Server. A networking or firewall configuration could be blocking the traffic. For that, you'll need to get the appropriate people involved.

Testing Using the Telnet Client

If you have the luxury of having the Telnet client (not the server) installed, you can use it to attempt a connection. You won't see the failed login in the SQL Server error log, but the behavior difference with a successful versus unsuccessful connection is clear. If it's not installed, there's no need to request for it to be installed, as the Data Sources (ODBC) tool should be sufficient. However, for those who are familiar with telnet, this is often a quicker method of verification.

Start a command prompt / PowerShell prompt and then enter telnet <name> <port>. This means you will need to know the port SQL Server is listening on. For a default instance, the default port is 1433, but it's trivial to change so verify what the port is by checking out the TCP network library in SQL Server Configuration Manager. You'll definitely need to do this if you're using a named instance. Here's an example of the command to enter. Note the name and port after telnet. The port is important. If you leave it off, the telnet client will attempt to make a connection to port 23, the default port for a telnet server and likely not the port your SQL Server is listening on.

Entering telnet command

Once you hit enter, if the screen goes blank, you've verified connectivity:

Successful test via telnet

If, on the other hand, you get the following, you can't connect. If you've specified the right name and the right port, you have a connectivity issue.

Failed test via telnet

Final Thoughts

Obviously, if these connectivity tests fail, then it's time to look elsewhere, as I mentioned multiple times in the article. These methods are quick ways that connectivity can be checked without pouring over network configurations and firewall settings. However, if you don't get a successful connection, those other places will have to be looked at.

Next Steps


Last Updated: 2017-04-25


get scripts

next tip button



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Thursday, April 27, 2017 - 7:52:33 AM - FAUSTO FIORESE BRANCO Back To Top

 Hi, I use two others ways to identify SQL Server connection:

 

1) Using PortQry from microsoft ( https://www.microsoft.com/en-us/download/details.aspx?id=17148 ).

Portqry helps to identify if there is a SQLServer service listening on a port.

 

2) Create an empty text file on the source server and save it with any name, but MUST have the .UDL extension.

As an example create an empty text file named SQL.udl:

-Double click on the file, it will open a screen with some connection parameters:

- Enter the database server in the "Select or enter a server name" box

- Enter the SQLServer user and password in "Use a specific user name and password" for SQLAuthentication or "Use Windows NT Integrated security" for Windows Authentication.

- Click on "Test Connection"

 

http://www.dbinternals.com.br/?p=1370 (in portuguese)



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools