April 28, 2010 at 1:44 pm
When I issue the following statement I am the only one with NTLM instead of Kerberos authentication and I do not understand why. I can write the code and the agent will run it under Kerberos, but this problem is hard to develop in. If I open management studio from the server I can get Kerberos, but not from my local machine. All the other IT folks get Kerberos as can be seen from the results of the following statement:
select sp.[program_name], sp.nt_username,sd.auth_scheme,net_transport,encrypt_option
from master.dbo.sysprocesses sp
join sys.dm_exec_connections sd
on sp.spid = sd.Session_id
Group by sp.[program_name], sp.nt_username,sd.auth_scheme,net_transport,encrypt_option
It returns the following:
auth_schemenet_transport
KERBEROSTCP
KERBEROSTCP
KERBEROSTCP
KERBEROSTCP
KERBEROSTCP
KERBEROSTCP
KERBEROSTCP
KERBEROSTCP
KERBEROSTCP
KERBEROSTCP
KERBEROSTCP
KERBEROSTCP
NTLM TCP
The last one is mine, and all the others are folks in the IT department. The SPN is obviosly registered correctly and Kerberos can obviously be used. All considerations in the following article are in effect, so why not my login?
April 29, 2010 at 6:53 am
Are you executing the query from the server itself? In other words, are you remoted in/logged in at the console of the server where SQL Server is installed and running SSMS there?
K. Brian Kelley
@kbriankelley
April 29, 2010 at 8:07 am
If I remote into the server and open a connection from SSMS there I get Kerberos. However, all the other connections from SSMS that are Kerberos are connecting from their local computers without a problem. It seems to have something to do with my local machine, because if I remote into any of the production/dev/test servers I can connect to any of the others with Kerberos just like anyone else from their local machine. But what could the issue be with my local machine?
The build on my machine and all the servers is post sp3 for SQL Server 2005. In addition I have an RTM build of 2008 on my machine.
April 29, 2010 at 8:35 am
Toby White (4/29/2010)
If I remote into the server and open a connection from SSMS there I get Kerberos. However, all the other connections from SSMS that are Kerberos are connecting from their local computers without a problem. It seems to have something to do with my local machine, because if I remote into any of the production/dev/test servers I can connect to any of the others with Kerberos just like anyone else from their local machine. But what could the issue be with my local machine?The build on my machine and all the servers is post sp3 for SQL Server 2005. In addition I have an RTM build of 2008 on my machine.
Have you used KerbTray to see if the server ticket is being created on your end? When you log on to the SQL Server, are there any events in the Security Event Log on the server indicating an issue?
K. Brian Kelley
@kbriankelley
April 29, 2010 at 1:42 pm
After considering what you wrote and what I was seeing I started having a sneaking susupicion that it might have something to do with the fact that it's been so long since my machine was rebooted. Either that or the fact that I had changed my password last week, and I might have just logged out and back on without rebooting my machine. Nothing else seemed to fit given the behavior.
Since I had a couple interactive profiler traces going and about 20 open applications it took me a while to get to a point where I could reboot. However, rebooting sure enough fixed the problem - Kerberos is working from my machine now. The development I need it for utilizes the double hop so this will make it much easier for me to make progress.
Thanks for your help Brian, and sorry to enlist you on my wild goose chase.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply