The information contained in this support note is for IT administrators. MSSQL is not part of Calxa and these instructions herein are provided as a guide only and should be confirmed with the appropriate Microsoft documentation.


When using Calxa with an SQL Server database there are a number of factors which can contribute to a connection failure. Most connection issues will result in the following error:


Failed to connect to <Server Name>\<Instance Name>. A network-related or instance specific error occurred while establishing a connection to SQL Server.


This error usually means that the SQL Server computer can't be found or that the TCP port number is either not known, or is not the correct port number, or is blocked by a firewall. This support note should help you identify which of these factors is the root cause of the connection failure.

 


Initial Troubleshooting Checks


  1. Ensure you have configured your SQL Server for network access and restarted the SQL Server services. This means that the TCP/IP protocol is enabled and the Browser service is running.
    1. In the SQL Server Configuration Manager, if your instance is named MSSQLSERVER then this is the default (unnamed) instance.
  2. When connecting ensure the Server name field is correct and in the following formats:
    1. Named Instance: <ComputerName>\<InstanceName>
    2. Default (unnamed) instance: <ComputerName>
  3. Check Windows Firewall rules to ensure communication is not being blocked.
    1. You may try temporarily disabling your firewall to determine if this is the cause.
  4. Ensure the SQL Login has appropriate permissions on the SQL Server and Calxa database.
    1. If using a domain group for your SQL Login, ensure the windows user is part of the domain group.

 

Further Troubleshooting


The further troubleshooting steps outlined in this help note are indicative of the most common problems found by our support team, but may not include all possible causes of connection failure. Please refer to this TechNet article for an exhaustive list of troubleshooting steps: How to Troubleshoot Connecting to the SQL Server Database Engine.

 

Test Connections Using a Universal Data Link (UDL)

Using a Universal Data Link (UDL) can be a quick way to test connections from different machines without the need to install Calxa or other specialised tools. As you go through these troubleshooting steps you can use a UDL to test the connection.

  1. Create a notepad file (Text Document) and change the extension to .udl, then save.
    Note: A warning that changing file extensions can cause files to become unusable might appear. Disregard it.

  2. Double-click the universal data link (.udl) file that you have just created.  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. On the Provider tab, select a database provider.

    DataLinkProperties


  4. On the Connection tab, you can use the drop down menu for the server name field to browse available servers. If all is configured correctly then the server will display but presumably you are reading this support note because it does not. In that case you can type the SQL Server name directly in the format <ComputerName>\<InstanceName>.

    DataLinkPropertiesConnection


  5. If you have a database already you can populate this field but it is not required to test a connection to the server. Click Test Connection. If you receive the connection succeeded message below you are now ready to use the same connection properties in Calxa.

    TestConnectionSucceeded

 

Checking Firewall

In Calxa Support we find that the most common cause of connection failure is due to a firewall restriction. As mentioned above the easiest way to test if the firewall is blocking some or all of your SQL Server communication is to temporarily disable the firewall on the server. If you connect successfully when the firewall is disabled then you know this is the cause of the problem. Below is a list of default ports that may need to be opened in the firewall. The top two should definitely be added as exceptions but we do see the others cause problems on a limited number of setups:

  • SQLServer - TCP port 1433
  • SQL Browser Service - UDP port 1434
  • SQL Server Service Broker – TCP 4022
  • SQL Debugger/RPC – TCP 135
  • Analysis Services TCP 2382 and 2383
  • HTTP – TCP 80
  • SSL – TCP 443

You can download a batch file that contains a script that will create the appropriate firewall rules. It is attached at the bottom of this help note. This script has been taken from the Microsoft KB article titled How to open the firewall port for SQL Server on Windows Server 2008.

 

Test TCP/IP Connectivity

Connecting to the SQL Server using TCP/IP requires that Windows can establish a connection. This means the IP address must be reachable on the network. We will try and ping the machine hosting the SQL Server instance.

  1. In a command prompt try to ping the IP Address. For example, ping <IP Address> (replace <IP Address> with the target machines IP Address). You should receive a Reply from <IP Address>
    1. If you get errors at this point such as "Destination host unreachable." or "Request timed out." double check you have entered the correct serial number. Errors at this point indicate a problem with the client computer, the server or a network problem such as router issues.
    2. Now ping the server name. For example, ping <ComputerName> (replace <ComputerName> with the target machines name)
      1. Errors at this point indicate problems with DNS. Type ipconfig /flushdns in your command prompttoempty theDNS cache and try again.

If you can successfully ping the <ComputerName> then we now know the first part of the SQL Server name to use when connecting to the SQL Server.

If you cannot ping the <ComputerName>, you could just use the <IP Address> instead of the Server name when connecting from Calxa, but this does indicate that name resolution is not configured correctly.