I ran across a crazy issue this am that I’ve never seen before. I was setting up SQL Server Express 2008R2 on a smaller server at home, the likes of which I have done many times before with no issues. Then I did the following, per my usual config routine:
- Opened ports 1433 and 1434 on this server for “listening” for SQL requests
- Opened a hole in the firewall for SQLBrowser.exe
- Enabled TCP/IP listening for those same ports in SQLServerManager10.msc (running on Windows 10 Professional operating system).
- Enabled remote connections to the Server process
- Set up a login account for Administrator with full rights to the SQL server processes
So then I went to test the setup from my laptop, fired up SQL Server Management Studio, and connected to the SQL Server. Connection made – check. Opened up a known scalar function to modify – check. Run the alter script – check. Run it again to double check the persistence of the network connection – ERROR 64.
I had never seen that one before, but I knew it was network or network protocol related.
I remoted into this particular server and started double-checking all settings that would seem to have any relevance to that issue.
After A LOT of Googling and matching snippets of information together, I finally found resolution with the following regedit modification for the local registry.
- Create a DWORD “Sesstimeout” key with a decimal value of 360.
It seems the session-layer of the connection was not persistent enough for remote connections. Why doesn’t Microsoft take care of this by default with a config script? We might never live long enough to know…
Problem solved, for now. If I see it again, I will update this post.