ODBC Timeout During Setup

Problem

When running PeopleSync setup, you encounter the following error message:

ODBC Connect: Timeout exceeded

This can happen if there is a firewall between PeopleSync Frontend and SQL Server, the SQL Browser service is stopped or there is a TLS problem.

Solution

With the following advice you can find out which ports your SQL Server instance uses and consequently determine if they can be reached through the firewall. You can also check for other causes with the script provided.

Determine SQL Server Ports used

Open SQL Server Management Studio and connect to the SQL Server instance where the PeopleSync database is located.
Open a new query window
Run the following command:
```
EXEC xp_ReadErrorLog 0, 1, N'Server is listening on', N'any', NULL, NULL, 'DESC'
```
On our server, for example, this gives the following output "Server is listening on [ 'any' <ipv4> 1433]."

Verify Connection

Now that you know which port SQL Server uses, you can check if you can connect from the server where you are installing PeopleSync Frontend by running the following command from a PowerShell window:

test-netconnection -ComputerName <SQL Server Name> -Port <Port>

Where <SQL Server Name> is the hostname of the SQL Server and <Port> is the port shown in the output of the SQL query.

For example, on our server, we are calling "test-netconnection -ComputerName srv-2 -Port 1433". This gives the following result:

ComputerName           : srv-2
RemoteAddress          : 10.0.18.96
RemotePort             : 1433
InterfaceAlias         : Ethernet
SourceAddress          : 10.0.18.54
PingSucceeded          : True
PingReplyDetails (RTT) : 1 ms
TcpTestSucceeded       : False

If "PingSucceeded" or "TcpTestSucceeded" is false, then probably the firewall hasn't been opened.

Multiple Instances, Dynamic Ports and Special Configurations

Please note that if you are running more than one instance of SQL Server on the same machine, or you are using named instances, dynamic ports will be used. If only one instance is present on the server, it usually can be contacted through port 1433.

If the SQL query above returns a port in the range of 49152–65535, dynamic ports are being used. This is not suitable for a scenario where the connection passes a firewall. In this case you need to specify a fixed port for SQL server, as described in
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-a-server-to-listen-on-a-specific-tcp-port?view=sql-server-2017 .

Please see https://docs.microsoft.com/en-us/sql/sql-server/install/configure-the-windows-firewall-to-allow-sql-server-access?view=sql-server-2017#BKMK_dynamic_ports for SQL Server firewall configuration.

If the SQL Server port is not 1433, you also have to specify the port in the database connection dialog of PeopleSync server setup. The syntax is <servername>,<port>. For example, in the setup, the connection to instance "srv-2\instance1" running on port 1456 would be specified as "srv-2,1456". In this case, there is no need to specify the instance name.

Advanced Troubleshooting

For a better error message, run the following script in PowerShell and fill in the information as requested:

ODBC Connection Diagnostic

$servername = Read-Host -Prompt "Enter SQL Server and instance name"
$database = Read-Host -Prompt "Enter database name"

$installtype = Read-Host -Prompt "Enter 'f' if you are testing the database connection for the frontend or 'b' for the backend"

$Error.Clear()
$connection = New-Object System.Data.Odbc.OdbcConnection

switch ($installtype.ToLower()) {
    'f' {
        $login = Get-Credential
        $connection.ConnectionString = "Driver={ODBC Driver 17 for SQL Server};Server=$servername; Uid=$($login.UserName);Pwd=$($login.GetNetworkCredential().Password); Database=$database"
    }
    'b' {
        $connection.ConnectionString = "Driver={ODBC Driver 17 for SQL Server};Server=$servername; Trusted_Connection=yes; Database=$database"
    }
    Default { Write-Error -Message "Invalid choice. Exiting." -ErrorAction Stop }
}

Write-Host "Connecting..."

$connection.Open()
$connection.Close()

if ($Error.Count -eq 0) {
    Write-Host "Connected"
}

The error message will provide more information regarding the cause.