2017-02-17

Cannot Generate SSPI Context

I ran into a SQL Server connectivity issue this week that was a puzzler, but (thankfully) only for a short time. Maybe my pain can be your gain. Here's a quick rundown.

I installed and configured a new instance of SQL Server. After getting appropriate firewall ports opened by the network team, everything seemed A-OK. Until I tried connecting from a remote machine. Here is what I saw from SSMS:

SQL Server - Dave Mason - Cannot Generate SSPI Context


Ah! An "SSPI" error. I was certain I knew what the problem was. It had to be a failure to properly register the SPN. I was prepared to run a few SetSPN.exe commands to fix the problem and be on my way. To my surprise, the SPN *was* registered correctly, as I discovered in the Log File Viewer:

SQL Server - Dave Mason - Log File Viewer-Service Principal Name


Out of curiosity, I tried connecting to the instance via Windows Authenticatin by IP address instead of DNS name. VoilĂ , that worked. Connecting via SQL Authentication also worked (using either IP address or DNS name). Pinging the DNS name checked out (it returned the correct IP address). Pinging the IP address looked OK, too. Pinging the IP address with the -a parameter didn't return the DNS name, though. Hmmm. Maybe the SQL host is having a problem talking to the domain controller.

I took to Twitter and the #SQLHelp hash tag. Peter Shore jumped in to assist. (Thanks for bending my ear, Peter!). We made fun of AD replication slowness and generally pondered if AD was the problem. I'm a little fuzzy on what happened next, but for some reason, I happened to notice the date setting on the SQL host was wrong. The time was correct, but the date was ahead by one day. So I manually corrected that and tried the remote SQL connection with Windows Authentication and DNS name again. And...success!

Oddly, the Windows Time local service was running:

SQL Server - Dave Mason - Windows Time Service

I'm not sure at all what to think of that. I guess the moral of the story is to check the host server date and time settings.


SHARE