November 21, 2011 at 3:22 pm
I think i have a brick pattern left on my forehead from pounding it against the wall for so long., so I'm turning to the web for help.
I have confirmed that the SPN of SQL server is registered for the server.
setspn -L mySqlServer returns
MSSQLSERVER/mySqlServer.myDomain.com:1433
MSSQLSvc/mySqlServer.myDomain.com:1433
setspn -Q MSSQLSERVER/mySqlServer returns
MSSQLSERVER/mySqlServer.myDomain.com:1433
MSSQLSvc/mySqlServer.myDomain.com:1433
MSSQLSvc/mySqlServer.myDomain.com
MSSQLSvc/mySqlServer
When is query sys.dm_exec_connections the auth_scheme returned is NTLM.
I have restarted SQL Server services on the server.
I have flushed the DNS on my workstation.
I have rebooted my workstation.
Still I cannot get a Kerberos authenticated conenction. Other servers I have on the same subnet, running under the same service account, do authenticate using Kerberos. Fortunately this server is a test (non-produciton) server that i can beat on until a resolution is found.
The server is SQL Server 2008 R2 running on Windows Server 2008 R2. IIRC, the domain is Windows Server 2003. I do not have control over AD in my environment, so that is out of my hands.
My question to you, the collective genius that is the internet (enough butt kissing? ), is what should I look at next? What active directory permissions does the service account require to properly register an SPN? I have read the good articles written here and the articles on MSDN, and at this point am completely stumped.
Thanks in advance,
Greg
Greg Roberts
November 21, 2011 at 4:01 pm
What is the error message that you get?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 22, 2011 at 6:12 am
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.
How to use Kerberos authentication in SQL Server
November 22, 2011 at 6:27 am
SQLRNNR (11/21/2011)
What is the error message that you get?
No error messages, it's simply connecting using NTLM.
Greg Roberts
November 22, 2011 at 6:29 am
Dev (11/22/2011)
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.How to use Kerberos authentication in SQL Server
That was our initial problem. I worked with the network administrators to resolve that problem, and after a restart of SQL server the SPN was registered. I then had to flush the DNS on my workstation to get it to connect to the server. When i checked the connection properties, it shows as NTLM.
Greg Roberts
November 22, 2011 at 6:36 am
Check for 'Authentication Defaults' section in the article.
Registering a Service Principal Name
November 22, 2011 at 6:38 am
Scenario
The SPN maps to the correct domain or built-in account. For example, Local System or NETWORK SERVICE.
Authentication method
Local connections use NTLM, remote connections use Kerberos.
November 29, 2011 at 2:44 am
Perhaps the issue is that your workstation does not have a valid kerberos KDC key issued at your AD server. If this is the case you need to reboot the workstation to obtain a new Kerberos key. Flushing the DNS cache does not obtain a new key. Typically a new Kerberos key is generated when a AD / KDC server reboots and probably at other intervals for security reasons. If a new key is not obtained by the client, the old key is not recognized and SQL connections (attempt to) revert back to NTLM. More info on accessing Kerberos keys through the KDC here.
November 29, 2011 at 6:25 am
Sorry Gang, the holiday got in my way. 😉
So here's the update. I've rebooted with no success.
Here's a quick recap.
SQL Server A: AUTH_SCHEME = NTLM
SQL Server B: AUTH_SCHEME = KERBEROS
Both servers have dedicated AD (Active Directory) Service accounts. (SQLServerServiceA, SQLServerServiceB) and we have verified they are configured identically in AD.
Greg Roberts
November 29, 2011 at 6:40 am
Have you gone through the URL I have suggested? It looks like a known behavior.
http://msdn.microsoft.com/en-us/library/ms191153.aspx
Scenario
The SPN maps to the correct domain or built-in account. For example, Local System or NETWORK SERVICE.
Authentication method
Local connections use NTLM, remote connections use Kerberos.
November 29, 2011 at 6:56 am
Yes, that one got us on the right path as well as the other earlier post. It's odd that the SPN does come up when I ask SETSPN to list for the server. I'm going to go back to the network guys the verify the service account again. Otherwise I may have to burn a MS Support incident.
Greg Roberts
December 1, 2011 at 2:12 pm
Could it be caused by duplicate registrations of the same spn on different accounts? Have you read this social.technet.microsoft.com/wiki/contents/articles/717.aspx
Also, the service name MSSQLSERVER is not used by Sql Server, but that should have nothing to do with your problem.
December 2, 2011 at 7:19 am
MSSQLSERVER/server.domain is what was registered when the service restarted after correcting the account permissions.
There are not duplicate registrations for the server.
Despite what the netadmins are telling me, I think the service account does not have all of the permissions required to properly register the SPN. I just have to prove it before I go back to them.
Greg Roberts
December 2, 2011 at 7:29 am
I can't find any information anywhere that says anything about the SPN name being MSSQLSERVER. It has always been MSSQLSvc, ref http://msdn.microsoft.com/en-us/library/ms191153.aspx
The service account does not need any permissions to register the SPN if you or your netadmins have done it manually using setspn.
Did you run setspn -X to verify that no duplicate spns are registered?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply