Here’s a quick one for today and is an issue that had me stumped for a while. It’s not one that I’d come across before and there isn’t really all that much out there on the internet about it.
The Issue as I Saw It
So, what was the issue?
I was setting up an AG, all pretty standard stuff using the wizard in SSMS. I went through the usual setup and when I got to the end, everything seemed to create as I’d expect it, the only issue and first hint that there was an issue was that the wizard just sat there spinning when trying to join a database to the secondary.
I eventually got bored and bailed out of the wizard.
Checking the state of the AG out, I could see that the secondary node was showing as failed. Not true, it was up and looked fine to the cluster.
Checking the logs on the secondary, it was littered with ‘Database Mirroring login attempt failed with error: ‘Connection handshake failed. An OS call failed: (80090302) 0x80090302(The function requested is not supported).’ messages. The primary server wasn’t able to authenticate with the secondary, but why? Everything looked ok as far as I could see.
But what does that mean? The wisdom of the internet gave me nothing and I was at a bit of a loss with the whole thing.
The Solution
The clue to the real issue gave itself up when I tried to connect to the AG using the listener. I received an ‘SSPI handshake failed’. That can point to a problem with Kerberos authentication.
That made me think of SPNs. Every AG listener in SQL needs to have an SPN defined, usually SQL will register the SPNs itself but in this case the service account wasn’t able to. Manually creating an SPN for the listeners sorted the problem, the error went away and the AGs suddenly came to life.
I spent far too long on this issue, so hopefully if you’ve hit the same thing this has been helpful.
SQL Undercover 