Restored a TFS Database in CUS region in Azure SQL VM. Not able to connect to database from Client machine which is an on-prem machine. Getting the following error when using SQLCMD utility: Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Timeout error [258]. . Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired. Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Unable to complete login process due to delay in prelogin response. Firewall is working as expected, port 1433 is open to the Client connections. Tried sqlcmd with -C also to accept the Certificate from server, still getting the same error. Able to connect DB using SQLCMD / SSMS on the local server, not working from the client server. telnet is working from client machine to server on port 1433 Ping is also working from client machine. SQL Server version 2019 Resource Type - SQL virtual machine "Allow remote connections to this server" is enabled Information related to Azure SQL VM: Resource type - SQL Virtual Machine SQL Connectivity - Private (within Virtual Network) Port - 1433 SQL Authentication: Enabled AZURE AD AUTHENTICATION - Not available on Developer Edition Public IP Address - No Private IP Address - Yes Any help is appreciated. Thanks in advance.

What type of login are you trying to use? This error about "delay in prelogin response" often indicates that there is a problem in the connection to the AD if you are on on-prem and you are using Windows Authentication. Two things to test: Test logging in with SQL authentication. (Which does not require connection to other resources). Increase the login timeout with the -l option. (Default is 15 seconds).

Getting Timeout Error[258] Unable to complete login process due to delay in prelogin response.

@umesh 5

Restored a TFS Database in CUS region in Azure SQL VM. Not able to connect to database from Client machine which is an on-prem machine.

Getting the following error when using SQLCMD utility:

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Timeout error [258]. .

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Unable to complete login process due to delay in prelogin response.

- Firewall is working as expected, port 1433 is open to the Client connections.
- Tried sqlcmd with -C also to accept the Certificate from server, still getting the same error.
- Able to connect DB using SQLCMD / SSMS on the local server, not working from the client server.
telnet is working from client machine to server on port 1433
Ping is also working from client machine.
- SQL Server version 2019
- Resource Type - SQL virtual machine
"Allow remote connections to this server" is enabled

Information related to Azure SQL VM:

Resource type - SQL Virtual Machine
SQL Connectivity - Private (within Virtual Network)
Port - 1433
SQL Authentication: Enabled
AZURE AD AUTHENTICATION - Not available on Developer Edition
Public IP Address - No
Private IP Address - Yes

Any help is appreciated.

Thanks in advance.

1 answer

Erland Sommarskog 112.7K Reputation points MVP

2023-04-02T19:46:04.0166667+00:00
What type of login are you trying to use? This error about "delay in prelogin response" often indicates that there is a problem in the connection to the AD if you are on on-prem and you are using Windows Authentication.

Two things to test:

Test logging in with SQL authentication. (Which does not require connection to other resources).

Increase the login timeout with the -l option. (Default is 15 seconds).
Please sign in to rate this answer.
@umesh 5 Reputation points

2023-04-02T20:11:42.41+00:00

From Client, I am using only SQL authentication. Server is still not part of the AD.

On the server, I can login using both Integrated and SQL authentication.

Remote Query Timeout is 0

Erland Sommarskog 112.7K Reputation points MVP

2023-04-02T20:23:15.3866667+00:00

Hm, with SQL authentication I would not expect any delay, but when I think of it, I think I have sometimes seen this at home when connecting to instances running on my VMs. (VMs running on my desktop at home, not Azure VMs.)

Remote Query Timeout is 0

That setting has nothing to do with it. That setting controls how long to wait to response when running queries over linked servers. So try the -l option to SQLCMD. I would not really expect it to help, but at least it's part of the troubleshooting process.

@umesh 5 Reputation points

2023-04-02T20:30:43.56+00:00

Thanks for looking into this Erland, any suggestion is helpful.

I tried SQLCMD with -l 0 option too and it didn't help either.

Erland Sommarskog 112.7K Reputation points MVP

2023-04-02T20:39:27.52+00:00

Did you still get the same error message? Or did it just hang (which is what we could expect with -l 0. If you got the same error message, how long time did it take? (I'm not asking because I think I do something useful with the response, but it is just general troubleshooting.)

@umesh 5 Reputation points

2023-04-02T20:48:46.5833333+00:00

Here is the result of netstat on SQL Server after running the SQLCMD command: netstat -ano -p tcp | find "client_ip" TCP server_ip:1433 client_ip:59762 SYN_RECEIVED 3848

@umesh 5 Reputation points

2023-04-02T20:49:43.7733333+00:00

@Erland Sommarskog I am getting the different error after using the -l switch. I get this error after approx 20 seconds

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Client unable to establish connection because an error was encountered during handshakes before login. Common causes include client attempting to connect to an unsupported version of SQL Server, server too busy to accept new connections or a resource limitation (memory or maximum allowed connections) on the server..

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.

.

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Client unable to establish connection.

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Client unable to establish connection due to prelogin failure.

Erland Sommarskog 112.7K Reputation points MVP

2023-04-02T20:55:46.2533333+00:00

More troubleshooting ideas: is this account a member of sysadmin? What happens if you try with an account that is member of sysadmin? (And please bear with me, I am only guess wildly. I don't really what the problem might be.)

@umesh 5 Reputation points

2023-04-02T20:58:02.09+00:00

Yes, account is a member of sysadmin

@umesh 5 Reputation points

2023-04-06T15:24:55.0766667+00:00

Closing this issue, looks like packets are not reaching to server correctly. The error message should be more relevant.

Oury Ba-MSFT 19,581 Reputation points Microsoft Employee

2023-04-10T17:19:06.9466667+00:00

@umesh Thank you for reaching out and sorry about this issue. Could you please share your connection string. Regards, Oury

Oury Ba-MSFT 19,581 Reputation points Microsoft Employee

2023-04-10T17:23:59.56+00:00

@umesh I am assuming that you are following the steps in this documentation. https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/ways-to-connect-to-sql?view=azuresql Regards, Oury

Oury Ba-MSFT 19,581 Reputation points Microsoft Employee

2023-04-12T23:26:26.1466667+00:00

@umesh We haven’t heard from you on the last response and was just checking back to see if you are still facing the same issue. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help. Regards, Oury
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Getting Timeout Error[258] Unable to complete login process due to delay in prelogin response.

1 answer

Your answer