Problem
When running PeopleSync Frontend 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
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.
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.
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"
}
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 .
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.Related articles