SQL Server Connectivity Issues–Troubleshooting Tips

  • Article

1. Test the connectivity using UDL file:

To create a Universal Data Link (.udl) file

    1. Open Windows Explorer.
    2. Select the folder in which you want to store the .udl file.
    3. Create a new Text Document under this folder. A new file named New Text Document.txt appears in the directory. Rename this file, removing all spaces and changing its file extension to .udl.
    4. A new file named New Microsoft Data Link.udl appears in the directory. You can rename this file.

To configure a universal data link (.udl) file

    1. Double-click the universal data link (.udl) file.

    2. The Data Link Properties dialog box opens, displaying the following tabs: Provider, Connection, Advanced, and All. Choose Next to navigate from tab to tab.

    3. Provider

    4. On the Provider tab, select a database provider.

    5. On the Connection tab, either select the data source name (DSN) of an available Provider, or enter a custom connection string. Valid DSNs for providers that are pre-defined on your system are displayed in the Use Data Source drop-down list.

    6. Connection

    7. Here are the different fields you need to enter:

    1. Select or enter a server name: This is the name of the SQL Server along with the SQL Instance. If the name of the SQL Server is SERVER1, and the name of the SQL Server instance is INST1, then the value you need to enter is : SERVER1\INST1. Check the table below to get more idea on the way we need to force either TCP\IP or Named Pipes protocol.

    2. Server name

      Instance name

      Port

      Select or enter a server name

      Select or enter a server name (Forcing TCP\IP protocol)

      Select or enter a server name (Forcing Named Pipe protocol)

      SERVER1

      <default instance>

      1433 (default port)

      SERVER1

      TCP:SERVER1,1433

      NP:SERVER1

      SERVER1

      <default instance>

      1500 (non-default port)

      SERVER1

      TCP:SERVER1,1500

      NP:SERVER1

      SERVER1

      INST1

      1433

      SERVER1\INST1

      TCP:SERVER1\INST1,1433

      NP:SERVER1\INST1

      SERVER1

      INST1

      1500 (non-default port)

      SERVER1\INST1

      TCP:SERVER1\INST1,1500

      NP:SERVER1\INST1

    3. Enter information to log on to the server: You can either choose “Use Windows NT Integrated security”. Once you choose this option, the currently logged-in users credentials would be used to connect to the SQL Server Instance mentioned. The other option “Use a specific user name and password”, would let you enter ONLY the SQL Server user.

    4. You can choose to test the connectivity by clicking on Test Connection, without choosing the other options available.

Following the above approaches you can test the connectivity to an Instance of SQL Server both locally, and remotely.

If you are trying to use the UDL file on a 64 bit machine refer: https://blogs.msdn.com/b/chaitanya_medikonduri/archive/2008/04/09/how-to-run-32-bit-udl-file-on-a-64-bit-operating-system.aspx

For a local connection you don’t need to be really on any network. For remote connection, a stable network is required. The first thing to trouble shoot SQL connectivity issues is to make sure the network we rely on is workable and stable. Please run the following commands:

ping -a <your_target_machine> (use -4 and -6 for IPv4 and IPv6 specifically)

ping -a <Your_remote_IPAddress>

If you are not able to ping your target machine, it has high chance that either the network is broken or the target machine is not running.

You are now able to ping the remote machine. Now you need to make sure the port on which SQL Server is listening on is really available. Try the following command:

TELNET <your_target_machine> <Port_Of_SQLServer>

If the TELNET fails, there is a possible the target machine is behind a firewall and the specified port might be blocked on the Firewall.

If works fine, then your remote SQL Server is reachable, and there might not be any network related issues here.

A firewall on the SQL Server machine (or anywhere between client and server) could block SQL connection request. To isolate this, try to turn off firewall for a short time if you can. Long term solution is to put exception for SQL Server and SQL Browser.

  • For Named Pipe protocol, please make sure file sharing is in firewall exception list. Both file sharing and NP use SMB protocol.
  • For TCP protocol, you need put the TCP port on which the SQL Server listens to the exceptions.
  • For SQL Browser,  UDP port 1434 should be added to the exception list.

Refer: https://msdn.microsoft.com/en-us/library/cc646023.aspx [Configuring the Windows Firewall to Allow SQL Server Access]

 

3. SQL Server Configuration Manager

Please refer: