The SQL Network Interface library could not deregister the Service Principal Name (SPN) for the SQL Server service

Question

The SQL Network Interface library could not deregister the Service Principal Name (SPN) for the SQL Server service

Maxer

SSCrazy Eights

Points: 8964
More actions
December 18, 2008 at 7:31 am

#125107

I saw this error in my SQL Server 05 logs after a restart (this was during the shutdown process, but I checked the logs after it restarted which is why I saw this):
"The SQL Network Interface library could not deregister the Service Principal Name (SPN) for the SQL Server service. Error: 0x45b, state: 4. Administrator should deregister this SPN manually to avoid client authentication errors."
I tried your basic Google search but didn't get anything. I only saw two results and those were both people who couldn't get their server to either start properly.
Anyway, my server works fine (so far, knock on wood, thank diety of choice!) but seeing errors is never a good warm fuzzy experience.
Does anyone have any thoughts/suggestions on this?
I take it there are some issues with my service principal name that the SQL Server account is running with?
Thanks.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

Are you running multiple instances of SQL Server, and what account is the SQL Service using for the Service Account? If you have multiple instances, it may be possible that dynamic port allocation changed the port of the named instances around, and the registered SPN's are no longer accurate. You can use the setspn tool to change the SPN out:

http://support.microsoft.com/kb/909801

Maxer SSCrazy Eights Points: 8964 More actions · Answer 2

Each server only has a single SQL instance.

HOWEVER, I do have several servers all running SQL under the same domain account.

Is that causing problems?

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

No it shouldn't be. In a multi-instance server, by default the named instances don't have a set port to run on. The port is dynamically determined at run time and assigned to the instance. If the order of instance start had changed either through timing or just random luck the ports could have swapped between two of the named instances which would have caused a SPN issue. That wouldn't be a problem on a single instance server though unless you changed the default port from 1433 to something else recently on the servers.

Maxer SSCrazy Eights Points: 8964 More actions · Answer 4

Nope, no port changes, running on default.

I'd wager something is screwed up with Kerberos then, I'll have to start looking around there and make sure everything is working OK.

(Just saw an article on the front page about setting up kerberos, so that's great timing for me it seems)

Thanks for the help, it's good to know that running multiple servers under the same account isn't what is causing me the pain as it started happening around the time I brought another server online.

But I could have also screwed up the SPN at the same time 🙂

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 5

Take a look at this thread and see if the information marked as the answer helps you solve this problem:

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/772834e7-9b96-4e88-bdc5-aebff246bfb4/

It is also dealing with SPN's, but in a different context so I don't know if it will help or not.

Jeffrey Williams SSC Guru Points: 90257 More actions · Answer 6

Have you recently changed the service account that is running SQL Server? For example, did you change it from LocalSystem to your domain account?

When SQL Server is running as LocalSystem - the machine has the rights necessary to create (and remove) the SPN records. If you changed the service account to a domain account, the domain account no longer has that right and can't add/remove the SPN records.

You can setup the domain account so it has the right privileges to set the base SPN records. But be aware, just doing that alone will not guarantee that you are going to use Kerberos authentication. The default that is created is MSSQLSvc/{FCQN}:{port} - example: MSSQLSvc/myserver.site.domain.com:1433.

With that, if you connect to SQL Server using just 'myserver' - you will not use Kerberos. To use Kerberos authentication, you would have to connect using the FCQN of 'myserver.site.domain.com'.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Maxer SSCrazy Eights Points: 8964 More actions · Answer 7

I didn't realize that about the FQDN for Kerberos.

So in my application's connection string:

myserver is NOT sufficient to get "the job done".

I need to specify: myserver.mydomain.blah.blah the entire path of the computer's name or the "full computer name", is that correct?

Jeffrey Williams SSC Guru Points: 90257 More actions · Answer 8

To get Kerberos to work, you need to specify one of the SPN entries that was created.

If you just leave it to SQL Server to create it using either the LocalSystem account or a domain account that has the right privileges - the only entry you will have is: MSSQLSvc/{FCQN}:{port}.

If you manually create the SPN's that you need, then you can use whatever you have created. For example:

SETSPN -A MSSQLSvc/myserver

SETSPN -A MSSQLSvc/myserver:1433

Then you would be able to connect to 'myserver' and it should use Kerberos.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Maxer SSCrazy Eights Points: 8964 More actions · Answer 9

Jeffrey Williams (12/19/2008)
To get Kerberos to work, you need to specify one of the SPN entries that was created.
If you just leave it to SQL Server to create it using either the LocalSystem account or a domain account that has the right privileges - the only entry you will have is: MSSQLSvc/{FCQN}:{port}.
If you manually create the SPN's that you need, then you can use whatever you have created. For example:
SETSPN -A MSSQLSvc/myserver
SETSPN -A MSSQLSvc/myserver:1433
Then you would be able to connect to 'myserver' and it should use Kerberos.

using MySQLServersName would be preferable in my situation.

That being the case then I would set the SPNs (using the various technet documents available or following the article published at this site a short while back) and I can specify them to be the server's name.

Next question:

Even though the same service account (domain account) is being used for all of my servers that is NOT an issue.

I just specify

SETSPN -A MSSQLSvc/myserver1

SETSPN -A MSSQLSvc/myserver2

SETSPN -A MSSQLSvc/myserver3

and so on, once for each server that is running MS SQL 2005 and that's taken care of then.

I set my applications to connect to myserver1 or 2 or 3 (whatever they are supposed to be connecting to) and I no longer need to worry about the FQDN as I set it manually.

Next question:

WHY would I set it manually if I could elevate the domain user account to create it automatically?

What's the benefit of having it happen automatically vs manually (other than manually I can set whatever I want instead of FQDN, is that the only real benefit?)

Thank you again for your help.

Jeffrey Williams SSC Guru Points: 90257 More actions · Answer 10

Yes, I think you understand what needs to be done. Just one clarification:

SETSPN -A MSSQLSvc/myserver1 ServiceAccount

SETSPN -A MSSQLSvc/myserver2 ServiceAccount

SETSPN -A MSSQLSvc/myserver3 ServiceAccount

I forgot to add in the service account to the above. I am sure you would have figured that out.

Next question:
WHY would I set it manually if I could elevate the domain user account to create it automatically?
What's the benefit of having it happen automatically vs manually (other than manually I can set whatever I want instead of FQDN, is that the only real benefit?)

You would set it manually so you don't have to use the FQDN for all connections. The only benefet to allowing the service account to manage this automatically is just for the FQDN entry. I really don't think it matters much either way. I like having the account set it up automatically because I personally don't have that access. If I need Kerberos authentication I can get it by forcing the connection through the FQDN.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Maxer SSCrazy Eights Points: 8964 More actions · Answer 11

Hmm... can I have it both ways?

Can I give the service account privs to do that AND setup my own serverName so you can go to FQDN or servername?

What I'm worried about would be Reporting Services as users just type in the URL and they might type

server-name/reports or they might type the full proper address.

I'm not sure how it would work in that case.

Other ASP.NET apps they just connect to IIS, but IIS uses an application account to connect to the server and I specify the server name (FQDN if needed) in web.config.... reporting services Report Manager site... I don't know how that would work there?

(Again this has been most helpful Kerberos confuses the HECK out of me!)

Jeffrey Williams SSC Guru Points: 90257 More actions · Answer 12

Yes, you can have it both ways. And don't worry - most of us are confused about it also. I only know a few pieces of it myself.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs