Isn’t it fun to investigate SSPI errors? Really? There’s something magical in the fact that someone is trying to connect to the SQL Server, but can’t (especially if this is the app account)
Last night, I and a colleague of mine faced continuously generating SSPI handshake errors on a server with whole a lot mirrored databases (and yes, it was filling up the error log like you won’t believe). The IP address, where the issue was coming from was, oh do you believe it, the server used to “hold” the mirrored DBs. So, at the moment I saw the errors flying into the error log I was saying to myself: “After some seconds the mirroring will fail”. However, it didn’t and when I checked the Database Mirroring monitor, everything was looking just fine. After some investigation, we didn’t find a proper reason why this error was continuing to log in to the Error log. We found in the Application log the user who was trying to connect and the reason was “Unknown user name or bad password”. We checked in the Active Directory, but everything was looking fine for his account. Sometime later, my colleague suggested: “Let’s check who is logged on the server where the SSPI’s are coming from”. So, guess how surprised we were to see the exact same.
user, logged on to the server where the SSPIs were coming from, but staying in Disconnected state in the Task Manager. We immediately logged him off and, heaven, no more SSPIs were generated.
Now, that’s all OK, but later that night, I found the user who was on the server, obviously causing us headache and start to chat with him. I was curious about what he was doing at the time the issues arouse and so, started to ask questions. It turned out that he was trying to run a VBA script, which was moving data from the server where the mirrored databases were residing to the one where the principals were and the script was using an account user name and password as parameters as part of the script(probably domain one). However, he confessed that he was missing to enter the password parameter and so the script was trying to execute and login to the destination server with blank password (empty string). Oh, did I forget to tell you that he was continuously hitting F5 because at first he was not able to understand why the script was not working (that explains the massive amount of SSPI records)?
So, let’s not blame the DEV guy, shall we…
… changed my mind – we need to blame him.