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
Circle Hsiao
  • 455
  • 1
  • 6
  • 19

2 Answers2

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.

Circle Hsiao
  • 455
  • 1
  • 6
  • 19
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.

Shekar Kola
  • 2,477
  • 2
  • 10
  • 24