Symptom

Trying to connect to a remote BarTender System Database fails with the below error message:

#1047: A connection to SQL-Server Instance [Servername\Instance] could not be made.

Applicable to

BarTender 2016 and later

Diagnosis

1. Install SQL Server Management Studio also at the client side.
2. Connect from SQL Management Studio to the centrally based SQL Server instance using the same user credentials that are being used via the System Database wizard.
3. If this fails, it is an indication that this problem is on the SQL Server side (lack of permissions or configuration).

Solution

Configure the Necessary Permissions

1. Enable mixed-mode Authentication on the Server side if you plan to use SQL Server credentials:
   1. In SQL Server Management Studio > Object Explorer, right-click the server, and click Properties.
   2. On the Security page, under Server authentication, select the new server authentication mode, and then click OK
2. Provide permissions for the account used to connect to the BarTender System Database. See the linked article for more information on how to set the account permissions for Configuring Permissions to a System Database (Video - 5:45)
3. Determine if a connection will be made using Windows or SQL Server credentials (SQL Server credentials are recommended).
4. If issues continue, try the following configuration recommendations:

If the System Database has been configured on Computer A, and there are issues connecting to the remote system database from Computer B (in EA Edition, using SQL Express), try the tips below to remedy the issue. 

• Rather than using the named instance, try using the IP address and port (usually 1433) when connecting to the remote SQL Server instance.
• If using the named instance instead, SQL Server requires some previous configuration (see below).

Enable Remote Connections on the SQL Server Instance

1. Open SQL Management Studio.

2. Right-click the server name in the left pane and select Properties, then Connections and make sure that the checkbox Allow remote connections to this server is selected as shown below.

Configure SQL Express Server to Listen on Static Port.

1. Open SQL Server Configuration Manager and click on SQL Server Services  in the left pane.

2. Use the Process ID column (center) to reference the list of Process IDs for each running service. Identify the PID in the row for SQL Server and the port that that PID is listening on by typing the following into a command prompt:

netstat -ano | find /i “[PID-Number-Of-SQL-Server]"

Based on the details shown in the image above, the syntax is as follows:

netstat -ano | find /i “668” 

The results are shown below.

3. There are no results from the command executed in step 3 because the TCP/IP protocol is disabled and must be enabled. In SQL Server Configuration Manager click on SQL Server Network Configuration in the left pane and right-click TCP/IP protocol. Select Enable.

4. Restart SQL Server service and identify the process ID assigned to the SQL service.

5. In the command prompt execute the command:

netstat -ano | find /i “23444”

The results are shown below.

6.  In SQL Server Configuration Manager, click on SQL Server Network Configuration in the left pane and right-click TCP/IP protocol. Select the option for Properties. Go to IP Address tab and scroll down to the IPAll section. Remove the value for TCP Dynamic Ports (do not enter Zero 0 !!!). Enter port 1433 for TCP Port. Click Apply and then OK. 

7. Restart the SQL Server service, identify the new process ID assigned to SQL service, and in the command prompt execute command:

 netstat -ano | find /i “5088”

The results are shown below

At this stage, SQL Express is configured to listen on standard port 1433.

Turn On the SQL Server Browser Service.

1. Open the SQL Server Configuration Manager and click on SQL Server Services in the left pane. Right-click SQL Server Browser service and select Properties.

2. Go to the Service tab, and for the Start Mode option, change the start type to Automatic. Click Apply and Ok. 

3. Click the Start button to start the SQL Browser service.

4. Confirm that SQL Server Browser service is up and running, as shown below.

Configure the Firewall to Allow Network Traffic Related to SQL Server

Four exceptions must be configured in Windows Firewall to allow access to SQL Server:

1. A port exception for TCP Port 1433. In the New Inbound Rule Wizard dialog, use the following information to create a port exception:
   1. Select Port.
   2. Select TCP and specify port 1433.
   3. Allow the connection.
   4. Choose all three profiles (Domain, Private & Public).
   5. Name the rule SQL – TCP 1433.
2. A port exception for UDP Port 1434. Click New Rule again and use the following information to create another port exception:
   1. Select Port.
   2. Select UDP and specify port 1434.
   3. Allow the connection.
   4. Choose all three profiles (Domain, Private & Public).
   5. Name the rule SQL – UDP 1434.
3. A program exception for sqlservr.exe. Click New Rule again and use the following information to create a program exception:
   1. Select Program.
   2. Click Browse and select sqlservr.exe at this location: C:\Program Files\Microsoft SQL Server\MSSQL11.[INSTANCE_NAME]\MSSQL\Binn\sqlservr.exe where [INSTANCE_NAME] is the name of your SQL instance.
   3. Allow the connection.
   4. Choose all three profiles (Domain, Private & Public).
   5. Name the rule SQL – sqlservr.exe.
4. A program exception for sqlbrowser.exe Click New Rule again and use the following information to create another program exception:
   1. Select Program.
   2. Click Browse to select sqlbrowser.exe at this location: C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe. 
   3. Allow the connection.
   4. Choose all three profiles (Domain, Private & Public).
   5. Name the rule SQL – sqlbrowser.exe.

As you can see, a lot of configuration might be required in order to make remote connections work via the Instance Name of SQL Server (besides this being a security concern). So using IP address and port is recommended.