Service Principal Name (SPN) getting this error and then sqlserver restarting.
-
HI All,
Service Principal Name (SPN) getting this error and then sqlserver restarting. every time unable to find other thing in logs in event viewer and in sqlserver logs also , do we need to check with windows team to get a fix for this so that again and again this wont happens and sqlserver gets restart.
Message 1:-
SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.
Message 2:-
The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLlogin/my.db.local:app ] for the SQL Server service. Windows return code: 0x200b, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.
Thanks
Naga.Rohitkumar -
Did you register the SPN up front ? ( We always do, because we do not rely on dynamic port numbers for an instance )
If not:
- If you rely on Kerberos authentication, this is an issue.
- If needed, you can grant the sql server service account the needed auth in AD to register its SPN.
AFAIK this will not cause SQL Server to restart
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
message 1 is a warming so you can ignore it.
message 2 is informing you an SPN couldnt be created, what account do you run the sql server service under, guessing its a domain user account?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs"
-
I found it is best to create the SPN manually, and assign to it the permissions it needs. Doing things this way also requires that you use the same port each time you start the SQL service.
In order to create a SPN automatically, the SQL service account needs a high level of permissions within Windows. These are only needed for SPN creation, but then become a potential liability if the service account gets hacked.
Therefore for me, manual SPN creation, lower account privileges, and warning messages at SQL startup were the best way to go. Kerberos worked fine.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
- EdVassie wrote:
I found it is best to create the SPN manually, and assign to it the permissions it needs. Doing things this way also requires that you use the same port each time you start the SQL service.
Manually creating the spn is pointless if you give the account readserviceprincipalname and writeserviceprincipalname AD permissions, it deregisters and registers the spn when the service starts and stops.
EdVassie wrote:In order to create a SPN automatically, the SQL service account needs a high level of permissions within Windows. These are only needed for SPN creation, but then become a potential liability if the service account gets hacked.
this is not correct, the permissions are in Active Directory against the account and they’re not high risk, they’re permissions that are granted to msas and inbuilt accounts and they’re far lower risk than some other permissions that could be assigned.
EdVassie wrote:and warning messages at SQL startup were the best way to go. Kerberos worked fine.
sql server automatically warns at startup about spn generation failure and deregistration failures at shutdown
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs"
-
I would add to Perry's statements - ideally you should be using MSA or gMSA accounts to run SQL Server services.
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 - Jeffrey Williams wrote:
I would add to Perry's statements - ideally you should be using MSA or gMSA accounts to run SQL Server services.
agree, gMSAs are way forward.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs"
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply