snippetsqlMinor
How to prevent SQL Server service account from registering / deregistering SPN?
Viewed 0 times
preventregisteringsqlspnaccountservicederegisteringhowserverfrom
Problem
The service account is a domain account. It is not a domain admin (nor is it a member of any group that is a domain admin). It has neither "write servicePrincipalName" nor "Write public information" permission (nor a member of a group with these permissions). Yet it is still able to register / deregister it's SPN upon startup and shutdown.
What permission am I missing?
What permission am I missing?
Solution
This doesn't make sense, as you are running the service under a domain account with no "write ServicePrincipalName" permissions. If you were running under a localSystem account or a domain account with "write ServicePrincipalName" permissions, it would be able to create the spn. Is it possible that someone/something manually created the spn?
Here's an excerpt from Degremont Michel (SQL Server Core Engineer at Microsoft) explaining how this works:
If you run SQL Server under the LocalSystem account, the SPN is automatically registered as SQL registering with the machine account that has the right to create an SPN default. So Kerberos interacts successfully with the server running SQL Server.
However, if you run SQL Server under a domain account or a local account, the attempt to create the SPN may fail. When creating the service principal name fails, this means that no SPN is set for the service that is running SQL Server.
http://blogs.technet.com/b/mdegre/archive/2009/11/20/the-sql-network-interface-library-was-unable-to-register-spn.aspx
Here's another excerpt from msdn:
When the Database Engine service starts, it attempts to register the Service Principal Name (SPN). If the account starting SQL Server doesn’t have permission to register a SPN in Active Directory Domain Services, this call will fail and a warning message will be logged in the Application event log as well as the SQL Server error log. To register the SPN, the Database Engine must be running under a built-in account, such as Local System (not recommended) or NETWORK SERVICE, or an account that has permission to register an SPN, such as a domain administrator account. If SQL Server is not running under one of these accounts, the SPN is not registered at startup and the domain administrator must register the SPN manually.
http://msdn.microsoft.com/en-us/library/ms191153%28v=sql.105%29.aspx
Sounds like the account "somehow" has permissions that you don't know about. Maybe group policy or something similar...
Here's an excerpt from Degremont Michel (SQL Server Core Engineer at Microsoft) explaining how this works:
If you run SQL Server under the LocalSystem account, the SPN is automatically registered as SQL registering with the machine account that has the right to create an SPN default. So Kerberos interacts successfully with the server running SQL Server.
However, if you run SQL Server under a domain account or a local account, the attempt to create the SPN may fail. When creating the service principal name fails, this means that no SPN is set for the service that is running SQL Server.
http://blogs.technet.com/b/mdegre/archive/2009/11/20/the-sql-network-interface-library-was-unable-to-register-spn.aspx
Here's another excerpt from msdn:
When the Database Engine service starts, it attempts to register the Service Principal Name (SPN). If the account starting SQL Server doesn’t have permission to register a SPN in Active Directory Domain Services, this call will fail and a warning message will be logged in the Application event log as well as the SQL Server error log. To register the SPN, the Database Engine must be running under a built-in account, such as Local System (not recommended) or NETWORK SERVICE, or an account that has permission to register an SPN, such as a domain administrator account. If SQL Server is not running under one of these accounts, the SPN is not registered at startup and the domain administrator must register the SPN manually.
http://msdn.microsoft.com/en-us/library/ms191153%28v=sql.105%29.aspx
Sounds like the account "somehow" has permissions that you don't know about. Maybe group policy or something similar...
Context
StackExchange Database Administrators Q#30371, answer score: 3
Revisions (0)
No revisions yet.