If SPN is manually registered, will SQL Server try to re register every time it is started ?

Question

If SPN is manually registered, will SQL Server try to re register every time it is started ?

Joy Smith San

SSC-Insane

Points: 24877
More actions
June 29, 2015 at 3:50 am

#319496

Hi All,
If SPN is manually registered, will SQL Server try to re register every time it is started ?
Earlier there were some issues with the service account OU, got it fixed and manually registered SPN.
However still the message, "The SQL Server Network Interface library could not register the Service Principal Name (SPN)" is logged in sql server error log.
Why is it trying to register SPN when is it already created manually.
Thanks in advance.
San.

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

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

Perry Whittle SSC Guru Points: 233825 More actions · Answer 1

the account sql server runs under will attempt to register and deregister the SPN each time it starts and stops. If the account hasn't been provided with the necessary AD permissions then it will fail, the message you see in the log confirms this

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 2

Perry Whittle (6/29/2015)
the account sql server runs under will attempt to register and deregister the SPN each time it starts and stops. If the account hasn't been provided with the necessary AD permissions then it will fail, the message you see in the log confirms this

Thanks Perry. So its normal.

Just one more thing. Howe to ensure the SPN we registered manually is working fine and SQL Server is using the same ?

How can we confirm this ?

Thanks again,

San.

Perry Whittle SSC Guru Points: 233825 More actions · Answer 3

Joy Smith San (6/29/2015)
Howe to ensure the SPN we registered manually is working fine and SQL Server is using the same ?
How can we confirm this ?
Thanks again,
San.

Connect via windows auth and check your connection is using Kerberos via the following query

SELECT net_transport, auth_scheme

FROM sys.dm_exec_connections

WHERE session_id = @@SPID;

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 4

Hi Perry,

Yes, connected from a remote machine and confirmed. Thanks a lot for your support.

San.

Perry Whittle SSC Guru Points: 233825 More actions · Answer 5

😉

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 6

Hi Perry,

The issue is still not resolved it seems. Yesterday it was showing KERBEROS.

But today when checked, again back to NTLM. I connected using windows account from active node SSMS.

SetSPN -L command shows the SPN is registered. What else to be checked now ?

Thanks.

Perry Whittle SSC Guru Points: 233825 More actions · Answer 7

has the account been trusted for delegation?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 8

Perry Whittle (7/2/2015)
has the account been trusted for delegation?

It was NOT. But now I got it done "Trust this use for delegation to any service (Kerberos only)".

I rebooted both the nodes however NO luck, it still uses the NTLM. :w00t:

Thanks Perry.

JimmyJones SSCrazy Points: 2178 More actions · Answer 9

Joy Smith San (7/3/2015)
Perry Whittle (7/2/2015)
has the account been trusted for delegation?
It was NOT. But now I got it done "Trust this use for delegation to any service (Kerberos only)".
I rebooted both the nodes however NO luck, it still uses the NTLM. :w00t:
Thanks Perry.

Hi,

Just a wild guess, are you connecting to SQL server locally on the hosting machine?

________________________________________________________________
"Accept conditions as they exist, or accept the responsibility for changing them."

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 10

Hi,

I try from both active and passive nodes and from some remote other machines also.

Thanks.

JimmyJones SSCrazy Points: 2178 More actions · Answer 11

Hmm it was wild guess, some time ago I've learnt that SQL in local connection can fall back to NTLM. But if you still see NTLM from a remote machine, you can check this article.

http://mssqlwiki.com/2013/12/09/sql-server-connectivity-kerberos-authentication-and-sql-server-spn-service-principal-name-for-sql-server/

________________________________________________________________
"Accept conditions as they exist, or accept the responsibility for changing them."