spn error for SQL

Hi Experts

I have a customer who recently ran some SPN commands for some SQL servers and was completed successfully, and the AD object was updated properly as per SPN results. Later on, client restarted the server, however, still not able to connect to sql named instances because the Kerberos authentication still not working, see errors from one named instances below. We are using different IPs but all pointing to the same port number 1433 (sql default port).

Sql Alias: DEV04
2015-07-22 23:17:49.77 spid10s     Server is listening on [ X.X.X.X <ipv4> 1433].
2015-07-22 23:17:49.77 spid10s     Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\APPDEV ].
2015-07-22 23:17:49.77 spid10s     Server named pipe provider is ready to accept connection on [ \\.\pipe\MSSQL$DEV\sql\query ].
2015-07-22 23:17:49.77 Server      Server is listening on [ ::1 <ipv6> 50089].
2015-07-22 23:17:49.77 Server      Server is listening on [ 127.0.0.1 <ipv4> 50089].
2015-07-22 23:17:49.77 Server      Dedicated admin connection support was established for listening locally on port 50089.
2015-07-22 23:17:49.88 spid10s     SQL Server is now ready for client connections. This is an informational message; no user action is required.
2015-07-22 23:17:49.88 Server      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.

2015-07-22 23:17:50.02 Server      The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/sqlservername.domain.com:APPDEV ] for the SQL Server service. Windows return code: 0x2098, 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
Jerry SeinfieldAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jerry SeinfieldAuthor Commented:
Any updates?
0
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
I tend to run my SQL instances such that they don't have permission to write to AD either.  I have a batch file which makes the necessary changes in AD... but you'll need to run it with permissions to write these changes.
REM <batchfilename> <servername> <service_account_name>

setspn -a MSSQLSvc/%1 %2
setspn -a MSSQLSvc/%1:1433 %2
setspn -a MSSQLSvc/%1.<domain.suffix> %2
setspn -a MSSQLSvc/%1.<domain.suffix>:1433 %2

Open in new window

This assumes you are using the default SQL port (1433) (which you are), and the default instance, which I can't pick out in the minute or three left to me today.  :-(
0
Jerry SeinfieldAuthor Commented:
Any other suggestion? how can I resolve the original issue posted
0
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
I'm sorry.  If the correct setspn entries are made, that only resolves the kerberos authentication issue.  If the kerberos authentication isn't occurring, I'd strongly suspect erroneous SPN entries were created.  That doesn't necessarily make the msg go away.

You can make that information message go away, and ensure the correct SPN entries are made by allowing SQL to make the entries itself.  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.  (Rather than grant domain admin to the SQL service account, I'd be far more likely to delegate only the minimum permissions to the domain account under which SQL is running... which would be "Validated write to service principal name permission."
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.