April 16, 2012 at 1:26 pm
I am working in a dev environment and need to disable kerberos for some debugging and testing.
I ran setspn -l server.domain and got a list of all SPN's for the server.
I then ran setspn -d MSSQLSvc/server.domain domain\serviceAccount to remove the MSSqlServer SPN.
I then ran setspn -T domain -Q */server.domain to query if there were any SPN's associaited with MSSQLSvc that I missed. there were none.
Finally in ran KLIST PURGE to remove all cached tickets.
But when i connect to this machine via TCPIP i am Still being connected via kerberos ( visible when i check the sys.dm_exec_connections dmv)
I have restarted the sql server service but that has not helped. has anyone any ideas what i am missing?
EDIT: I have also tried restarting the actual server, but that did not resolve the issue.
April 16, 2012 at 3:11 pm
Not sure which order you were doing what, but for the record the SQL Server service will re-register an SPN if it has rights to Active Directory (LocalSystem, NETWORK SERVICE or domain user with privs to register SPNs in AD) whenever the service (or server) is restarted.
What do you get when you run this?
setspn -l server.domain
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 16, 2012 at 3:15 pm
opc.three (4/16/2012)
Not sure which order you were doing what, but for the record the SQL Server service will re-register an SPN if it has rights to Active Directory (LocalSystem, NETWORK SERVICE or domain user with privs to register SPNs in AD) whenever the service (or server) is restarted.What do you get when you run this?
setspn -l server.domain
As i understand it, if the Service Account SQL Server is running under has Domain Admin Rights, then it will automatically create the SPN.
But, the service account i am using does not have domain admin rights.
The most rights it has, is as an administrator on the Server running SQL.
I ran that command before and after i completed the steps, and after restarting the server, to check what SPN's exist. each time it does not show any MSSQLSvr related SPN.
April 16, 2012 at 3:19 pm
Who were you logged in as when you ran KLIST PURGE? The same account as the SQL Server service account?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 16, 2012 at 3:47 pm
opc.three (4/16/2012)
Who were you logged in as when you ran KLIST PURGE? The same account as the SQL Server service account?
I was logged in as my own domain account, which has Domain Admin Privileges.
April 16, 2012 at 3:58 pm
winston Smith (4/16/2012)
I ran setspn -l server.domain and got a list of all SPN's for the server.I then ran setspn -d MSSQLSvc/server.domain domain\serviceAccount to remove the MSSqlServer SPN.
I then ran setspn -T domain -Q */server.domain to query if there were any SPN's associaited with MSSQLSvc that I missed. there were none.
Finally in ran KLIST PURGE to remove all cached tickets.
But when i connect to this machine via TCPIP i am Still being connected via kerberos ( visible when i check the sys.dm_exec_connections dmv
That's because when running SQL server under a domain account SPN's are registered against the account and not the server object so, use
setspn -l domain\username
Also, unless your account has permissions to create and delete SPNs (defaults to domain admins) they won't be removed
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 16, 2012 at 4:12 pm
Perry Whittle (4/16/2012)
winston Smith (4/16/2012)
I ran setspn -l server.domain and got a list of all SPN's for the server.I then ran setspn -d MSSQLSvc/server.domain domain\serviceAccount to remove the MSSqlServer SPN.
I then ran setspn -T domain -Q */server.domain to query if there were any SPN's associaited with MSSQLSvc that I missed. there were none.
Finally in ran KLIST PURGE to remove all cached tickets.
But when i connect to this machine via TCPIP i am Still being connected via kerberos ( visible when i check the sys.dm_exec_connections dmv
That's because when running SQL server under a domain account SPN's are registered against the account and not the server object so, use
setspn -l domain\usernameAlso, unless your account has permissions to create and delete SPNs (defaults to domain admins) they won't be removed
Perry, you have made my day!
Just remoted in and gave this a try and it worked! I spent 3 hours looking at this before leaving it for the night to go get some rest and fresh eyes. You've saved me at least as many hours again debugging this tomrrow!
Thanks a Mil!
And thanks to all who offered help on this thread!
April 16, 2012 at 4:13 pm
winston Smith (4/16/2012)
Perry Whittle (4/16/2012)
winston Smith (4/16/2012)
I ran setspn -l server.domain and got a list of all SPN's for the server.I then ran setspn -d MSSQLSvc/server.domain domain\serviceAccount to remove the MSSqlServer SPN.
I then ran setspn -T domain -Q */server.domain to query if there were any SPN's associaited with MSSQLSvc that I missed. there were none.
Finally in ran KLIST PURGE to remove all cached tickets.
But when i connect to this machine via TCPIP i am Still being connected via kerberos ( visible when i check the sys.dm_exec_connections dmv
That's because when running SQL server under a domain account SPN's are registered against the account and not the server object so, use
setspn -l domain\usernameAlso, unless your account has permissions to create and delete SPNs (defaults to domain admins) they won't be removed
Perry, you have made my day!
Just remoted in and gave this a try and it worked! I spent 3 hours looking at this before leaving it for the night to go get some rest and fresh eyes. You've saved me at least as many hours again debugging this tomrrow!
Thanks a Mil!
And thanks to all who offered help on this thread!
For the record, did you run the delete from the server after remoting in only on this attempt? i.e. in the initial attempts were you trying to run the delete from your local machine?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 16, 2012 at 4:24 pm
all attempts were run from the actual server.
When I say I remote in, I meant I'm now at home and so had to remote into the servers, as opposed to being on site with the servers earlier.
April 16, 2012 at 4:26 pm
winston Smith (4/16/2012)
Perry, you have made my day!Just remoted in and gave this a try and it worked! I spent 3 hours looking at this before leaving it for the night to go get some rest and fresh eyes. You've saved me at least as many hours again debugging this tomrrow!
Thanks a Mil!
And thanks to all who offered help on this thread!
LOL no need to thank me but i appreciate it, just glad to help. Remember this for the future though,
When running the service account as a domain user the SPNs are registered against the user account. If the user account is granted create and delete SPN rights in AD then the SPNs are automatically created and deleted upon startup and shutdown.
When running as a local account such as system, etc the SPNs are registered against the computername object, when running as system SPNs are automatically created and deleted as the local system has inherited permissions to AD.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 16, 2012 at 4:32 pm
winston Smith (4/16/2012)
all attempts were run from the actual server.When I say I remote in, I meant I'm now at home and so had to remote into the servers, as opposed to being on site with the servers earlier.
Gotcha, thanks for posting back...good thing Perry jumped in with the goods. I was definitely headed down the wrong path with cached tickets and the user you were running stuff as. I am happy you got it resolved!
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 16, 2012 at 4:56 pm
opc.three (4/16/2012)
..good thing Perry jumped in with the goods. I was definitely headed down the wrong path with cached tickets and the user you were running stuff as. I am happy you got it resolved!
no problem, we're all here because we want to help each other 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 17, 2012 at 10:21 am
One thing to add here, which strikes me like peculiar behavior.
When you enable kerberos, you dont need to restart the service, kerberos simply takes over.
but to disable kerberos, after removing the SPN's you need to restart the service. i actually had to restart the service under another account and then restart it under the correct account before it would revert to NTLM. strikes me as odd behavior.
Is there by any chance some action i need to take at the domain controller, to ensure the new settings take hold as quick as possible? does it take a little while for settings to propogate through the network?
April 17, 2012 at 11:02 am
winston Smith (4/17/2012)
One thing to add here, which strikes me like peculiar behavior.When you enable kerberos, you dont need to restart the service, kerberos simply takes over.
but to disable kerberos, after removing the SPN's you need to restart the service. i actually had to restart the service under another account and then restart it under the correct account before it would revert to NTLM. strikes me as odd behavior.
Is there by any chance some action i need to take at the domain controller, to ensure the new settings take hold as quick as possible? does it take a little while for settings to propogate through the network?
Since the SPNs are registered in Active Directory and this uses a multi master replication model, changes have to propogate through the catalog, I've never had an issue before TBH
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply