3

I'm trying to connect a remote server by IP,port via Windows authentication.
It will end out with the following error

Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=41306; handshake=27677; (Microsoft SQL Server, Error: -2)

However, if connect to IP,port but login with sa through SQL Server authentication then I can connect to the server correctly.

Moreover, if I switch to MachineName,port and login via Windows authentication then I can successfully connect too.

Not sure what kind of setting/adjustment I need to make this use case work.

Environment

  • My laptop is Win10 and the SQL Server 2012 is on a Windows server 2012 R2
  • These two machines are in the same domain.
  • We are using another port instead of default 1433.

Case I tried

  1. remote IP,port via Windows authentication - failed
  2. remote MachineName,port via Windows authentication - successful
  3. remote IP,port via SQL Server authentication(sa account) - successful
  4. remote IP\MSSQLSERVER,port via Windows authentication - failed
  5. RDP to remote then use ssms there IP,port via Windows authentication - successful

Setting I have tried

  • Add the SQL Server exe to the inbound rule
  • Launch SQL Server browser
  • Change IP1 to the IPv4 I'm using and Active/Enabled it(restart service too)
  • Make sure TCP/IP are enabled on Protocols of MSSQLSERVER and Client Protocols(/32bit)
  • Add the port I'm using to IPAll/TCP Port and keep Dynamic ports blank
2

So it turns out the timeout is actually a timeout... After I expend the connection timeout to 90 sec it connected after about a minute.

Infrastructure guys say this might since it's trying to resolve the machine name or something? (I'm not really familiar with network and setting stuff). I will update this answer if they figure out any way to speed up the process.

1

The case number 4 (IP\MSSQLSERVER,port) is not necessary. However, the issue seems with security protocols/providers in windows level where SQL Service is running.

Hope this post would help for further troubleshooting and workaround solution, applying windows security path would be a proper fix such issues.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.