Online Help | Desktop App

SQL connectivity issues

Symptoms

When you are connecting to the SQL Server instance hosting the Alloy Navigator Express database from your computer, these errors may occur:

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist

SQL Server Network Interfaces: Error Locating Server/Instance Specified (xFFFFFFFF)

Cause

These and other typical errors are caused by connectivity issues between your client computer and your SQL Server instance hosting the database. For example, the "SQL Server does not exist" error occurs when the client is unable to find the SQL Server. The "Error Locating Server/Instance Specified" error is caused by similar reasons.

Resolution

We recommend you to go through a sequence of steps that may resolve the majority of SQL Server connectivity issues:

Verify basic connectivity over IP address and check for any abnormalities
1. Get the SQL Server instance information as follows:
  1. Sign in to the computer hosting the instance of SQL Server.
  2. Start SQL Server Configuration Manager.
  3. In the left pane, select SQL Server Services.
  4. In the right pane, verify the name of the instance of the database engine:
    - SQL SERVER (MSSQLSERVER) denotes a default instance of SQL Server. The name of the default instance is <computer name>.
    - SQL SERVER (<instance name>) denotes a named instance of SQL Server. The name of the name instance is <computer name>\<instance name>.
2. Get the IP address of the computer hosting the instance of SQL Server:
  1. On the Start menu, click Run. In the Run window, type cmd, and then click OK.
  2. In the command prompt dialog box, type ipconfig and then press enter. Make a note of the IPv4 Address.
3. On the Start menu, click Run. In the Run window, type cmd, and then click OK.
4. In the command prompt window, type the following:
  - ping –a <SQL Server instance name>
  - ping –a <SQL Server IPv4 address>
5. If your network is properly configured, ping returns Reply from <IP address> followed by some additional information. If ping returns Destination host unreachable or Request timed out, work with your network administrator to fix the issue.
Enable TCP/IP protocols
In some installations of SQL Server, connecting to the Database Engine from another computer is not enabled unless an administrator uses Configuration Manager to enable it. To enable connections from another computer:
1. Open SQL Server Configuration Manager.
2. Using Configuration Manager, in the left pane expand SQL Server Network Configuration, and then choose the instance of SQL Server that you want to connect to. The right-pane lists the connection protocols available. Shared Memory is normally enabled. It can only be used from the same computer, so most installations leave Shared Memory enabled. To connect to SQL Server from another computer, you normally use TCP/IP. If TCP/IP is not enabled, right-click TCP/IP, and then click Enable.
3. If you changed the enabled setting for any protocol, restart the Database Engine. In the left pane select SQL Server Services. In the right-pane, right-click the instance of the Database Engine, and then click Restart.
Configure your firewall properly: ensure the SQL Server instance on specific TCP port is opened
By default, the Windows firewall is turned on and will block connections from another computer. To connect using TCP/IP from another computer, on the SQL Server computer you must configure the firewall to allow connections to the TCP port used by the Database Engine. The default instance is listening on TCP port 1433. If you have named instances or if you changed the default instance port, the SQL Server TCP port may be listening on another port.

TIP: This topic describes how to configure the Windows firewall, but the basic principles apply to other firewall programs.

If you are connecting to a named instance or a port other than TCP port 1433, you must also open the UDP port 1434 for the SQL Server Browser service. The principal steps to allow access are the following:
1. Configure the Database Engine to use a specific TCP/IP port. The default instance of the Database Engine uses port 1433, but that can be changed. The port used by the Database Engine is listed in the SQL Server error log. Instances of SQL Server Express, SQL Server Compact, and named instances of the Database Engine use dynamic ports.
  
  To assign a TCP/IP port number to the SQL Server Database Engine:
  1. In SQL Server Configuration Manager, in the console pane, expand SQL Server Network Configuration, expand Protocols for <instance name>, and then double-click TCP/IP.
  2. In the TCP/IP Properties dialog box, on the IP Addresses tab, several IP addresses appear (in the format IP1, IP2, up to IPAll). One of these is for the IP address of the loopback adapter, 127.0.0.1. Additional IP addresses appear for each IP Address on the computer. (You will probably see both IP version 4 and IP version 6 addresses.) Right-click each address, and then click Properties to identify the IP address that you want to configure.
  3. If the TCP Dynamic Ports dialog box contains 0, indicating the Database Engine is listening on dynamic ports, delete the 0.
  4. In the IPn Properties area box, in the TCP Port box, type the port number you want this IP address to listen on, and then click OK. Multiple ports may be specified by separating them with a comma. If the Listen All setting on the Protocol tab is set to "Yes", then only TCP Port and TCP Dynamic Port values under the IPAll section will be used and individual IPn sections will be ignored in their entirety. If the Listen All setting is set to "No", then the TCP Port and TCP Dynamic Port settings under the IPAll section will be ignored and the TCP Port, TCP Dynamic Port, and Enabled settings on the individual IPт sections will be used instead. Each IPn section has an Enabled setting with a default value of "No" which causes SQL Server to ignore this IP address even if it has a port defined.
  5. In the console pane, click SQL Server Services.
  6. In the details pane, right-click SQL Server (<instance name>) and then click Restart, to stop and restart SQL Server.
2. Configure the firewall to allow access to that port for authorized users or computers.
  
  To open a port in the Windows firewall for TCP access:
  1. On the Start menu, click Run, enter WF.msc, and then click OK.
  2. In the Windows Firewall with Advanced Security, in the left pane, right-click Inbound Rules, and then click New Rule in the action pane.
  3. In the Rule Type dialog box, click Port, and then click Next.
  4. In the Protocol and Ports dialog box, click TCP. Select Specific local ports, and then type the port number of the instance of the Database Engine, such as 1433 for the default instance. Click Next.
  5. In the Action dialog box, click Allow the connection, and then click Next.
  6. In the Profile dialog box, choose any profiles that describe the computer connection environment when you want to connect to the Database Engine, and then click Next.
  7. In the Name dialog box, type a name and description for this rule, and then click Finish.
  To open access to SQL Server when using dynamic ports:
  1. On the Start menu, click Run, enter WF.msc, and then click OK.
  2. In the Windows Firewall with Advanced Security, in the left pane, right-click Inbound Rules, and then click New Rule in the action pane.
  3. In the Rule Type dialog box, click Program, and then click Next.
  4. In the Program dialog box, click This program path. Click Browse, and navigate to the instance of SQL Server that you want to access through the firewall, and then click Open. By default, SQL Server is at C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\Sqlservr.exe. Click Next.
  5. In the Action dialog box, select Allow the connection, and then click Next.
  6. In the Profile dialog box, select any profiles that describe the computer connection environment when you want to connect to the Database Engine, and then click Next.
  7. In the Name dialog box, enter a name and description for this rule, and then click Finish.
Run SQL Server Browser service

To connect to a named instance, SQL Server Browser service must be running. In Configuration Manager, locate SQL Server Browser service and start it. A default instance of SQL Server does not require SQL Server Browser service.
Test your ability to connect to your SQL Server instance using TCP

For example, using your PowerShell, execute this:
Test-NetConnection -ComputerName <SQL Server name or IP> -Port <SQL Server Port>

If the connection fails, check your configuration using previous steps of the instruction.

NOTE: If your TCP/IP protocols were configured with the SQL Client Configuration Utility (cliconfig.exe). Make sure you used its 32- bit version. 64-bit version is not compatible with the Alloy Navigator Express.
Ensure the user has a login in your SQL Server instance and appropriate permissions to connect to the database that the user is trying to connect to.
Alloy Navigator Express apps use the Database Access Key to access the Alloy Navigator Express database. Each key stores login and password that enables the connection. A registered copy of this key must exist on each computer that requires access to the Alloy Navigator Express database, which means that the key must be distributed to all target computers. For details, see Database Access Key distribution, Managing Database Access Key.

To check your access key and login details, do the following:
1. Launch Alloy Navigator Express.
2. In the Log in dialog box, click Manage Connections. The Connection Manager dialog box opens (for details, see Connection Manager, Importing the Database Access Key).
3. Review details of your access key, database connection and technician account.
4. To verify the connection, click the Test Connection button.
NOTE: If the test succeeded, but the issues persists, you can check login issues within the Alloy Navigator Express. For information on troubleshooting login failures, see Troubleshooting login issues.

TIP: If the user with Windows Authentication is experiencing login failures, try to use a Database Account with SQL Server authentication. For details, see Troubleshooting login issues.
Test your ability to connect from a client application installed locally on the computer that is using SQL Server (use your SQL Server Management Studio)
1. On the client computer, using SQL Server Management Studio, attempt to connect using the IP Address and the TCP port number in the format IP address comma port number. For example, 192.168.1.101,1433. If this connection fails, then you probably have one of the following problems:
  - ping of the IP address doesn't work, indicating a general TCP configuration problem.
  - SQL Server is not listening on the TCP protocol.
  - SQL Server is listening on a port other than the port you specified or the SQL Server TCP port is being blocked by the firewall,
2. Once you can connect using the IP address and port number, attempt to connect using the IP address without a port number. For a default instance, use the IP address. For a named instance, use the IP address and the instance name in the format IP address backslash instance name, for example 192.168.1.101\<instance name> If this doesn't work, then you probably have one of the following problems:
  - If you are connecting to the default instance, it might be listening on a port other than TCP port 1433, and the client isn't attempting to connect to the correct port number.
  - If you are connecting to a named instance, the port number is not being returned to the client.
  Both of these problems are related to the SQL Server Browser service, which provides the port number to the client. The solutions are:
  - Start the SQL Server Browser service.
  - The SQL Server Browser service is being blocked by the firewall. Open UDP port 1434 in the firewall. Go back to the section Open a port in the firewall. Make sure you are opening a UDP port, not a TCP port.
  - The UDP port 1434 information is being blocked by a router. UDP communication (user datagram protocol) is not designed to pass through routers. This keeps the network from getting filled with low-priority traffic. You might be able to configure your router to forward UDP traffic, or you can decide to always provide the port number when you connect.
  - If the client computer is using Windows 7 or Windows Server 2008, (or a more recent operating system,) the UDP traffic might be dropped by the client operating system because the response from the server is returned from a different IP address than was queried. This is a security feature blocking "loose source mapping."
3. Once you can connect using the IP address (or IP address and instance name for a named instance), attempt to connect using the computer name (or computer name and instance name for a named instance). Put tcp: in front of the computer name to force a TCP/IP connection. For example, for the default instance on a computer named ACCNT27, use tcp:ACCNT27 For a named instance called PAYROLL, on that computer use tcp:ACCNT27\PAYROLL If you can connect using the IP address but not using the computer name, then you have a name resolution problem. Go back to the section Testing TCP/IP connectivity, section 4.
4. Once you can connect using the computer name forcing TCP, attempt connecting using the computer name but not forcing TCP. For example, for a default instance use just the computer name such as CCNT27 For a named instance use the computer name and instance name like ACCNT27\PAYROLL If you could connect while forcing TCP, but not without forcing TCP, then the client is probably using another protocol (such as named pipes).
  1. On the client computer, using SQL Server Configuration Manager, in the left-pane expand SQL Native Client version Configuration, and then select Client Protocols.
  2. On the right-pane, Make sure TCP/IP is enabled. If TCP/IP is disabled, right-click TCP/IP and then click Enable.
  3. Make sure that the protocol order for TCP/IP is a smaller number that the named pipes (or VIA on older versions) protocols. Generally you should leave Shared Memory as order 1 and TCP/IP as order 2. Shared memory is only used when the client and SQL Server are running on the same computer. All enabled protocols are tried in order until one succeeds, except that shared memory is skipped when the connection is not to the same computer.

NOTE: If your configuration is set properly, but the issue persists, please report it to our Support Team and we will do our best to help you. For details, see Obtaining Technical Support.

SQL connectivity issues

Symptoms

Cause

Resolution

Products

Resources

Company