November 18, 2008 at 12:17 pm
Kerberos is used to impersonate over the double hop scenario. Most often this is when you have IIS on one server, and SQL server on another, or SSAS on another server. NTLM cannot do this, so it's Single Sign On is more limited.
K Brian Kelley mentioned this earlier.
Erin -
Is the double hop situation why you want to set up Kerberos? If it is, try the tool at this link. It does a good job of troubleshooting this scenario.
Greg E
February 26, 2009 at 1:31 am
I have 2 database servers with identical setup..
In one server, I got SSPI error, etc. I solved this by setspn and now connections are using Kerberos
However, in the other server, connections are using NTLM without problem..
I'm confused now.. where to start troubleshoot..
February 26, 2009 at 2:10 pm
Miz Zahan (2/26/2009)
I have 2 database servers with identical setup..In one server, I got SSPI error, etc. I solved this by setspn and now connections are using Kerberos
However, in the other server, connections are using NTLM without problem..
I'm confused now.. where to start troubleshoot..
Without knowing the exact details of the SSPI error, it's hard to troubleshoot. You may have duplicate SPNs, registered under different accounts and when clients search, they find the ones you most recently entered first. But like I said, we'd need the error to know.
K. Brian Kelley
@kbriankelley
February 26, 2009 at 2:23 pm
One of the things I noticed is that the SPN record must match exactly how you are connecting to SQL Server. In other words, if your SPN is setup using the FQDN and you connect using just the server name - it will drop to NTLM.
So, you need to have an SPN record that matches how you are connecting - or you need to modify your connection string to match the valid SPN.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 5, 2009 at 7:15 am
Hi Erin,
I know, I'm quite late with my reply, but I just faced this problem a few days ago and finally found the solution after 4 days of research (the mother of all crisis has some positive aspect after all...without this crisis I would never have been able to spend that much time on a problem :-)).
One thing you need to remember with Kerberos: mutual authentication, that means you need to configure both ends of the request to see "KERBEROS" as result of your query.
I won't go further with the application end: the explanations given in BOL are good, just use Setspn.exe or ADSIEdit on your AD DS machine to make sure the following 2 SPNs are registered:
- MSSQLSvc/
- MSSQLSvc/
On the other end of the request, the client PC, configure the firewall with the following exceptions:
- DNS service port 53 TCP
- DNS service port 53 UDP
- Kerberos Ticket Granting service port 88 TCP
- Kerberos Ticket Granting service port 88 UDP
- Time service port 123 TCP
- Time service port 123 UDP
That's it! Open a connection from your client PC, type your query and now your result will read "KERBEROS"
This is one typical example of a problem that should have been easilly avoided, should the writers of BOL be complete with their explanations.
Luc Magnee
Antwerp - Belgium
MCTS SQL Server BI
Luc Magnée
MCTS BI
Antwerp - Belgium
April 17, 2009 at 12:32 pm
I am still foggy on how exactly Kerberos is enabled and to verify it’s usage. I done the setspn, account delegation and use tcp protocol. Most of the time when I check the sql servers remotely from my PC by running:
osql -S tcp:SomeSQLserver -E -Q"select net_transport, auth_scheme from sys.dm_exec_connections where session_id=@@spid"
the result is NTLM. However, right after I rebooted my PC, the result is usually Kerberos until a day later the result is NTLM again. Can someone explain this to me what might be wrong? How else I could verify that my SQL server is using Kerberos? Thanks.
April 17, 2009 at 12:41 pm
One more question on Kerberos. Kb article 909801 stated that
only a domain administrator account or the local system ccount has the required permissions to register an SPN. So if my service account does not have domain admin right I have to manually run Setspn.exe every time after the sql server is rebooted? Thanks again.
April 17, 2009 at 1:17 pm
February 20, 2012 at 1:41 pm
Imagine you have an sql server with 2 instances in cluster.
First instance is called: sqlservernameone\instanceone
This instance is using port 1433
The user that starts SQL service is: domain\sqlserviceinstanceone
FDQN name of the instance is: sqlserviceinstanceone.domain.es
Second instance is called: sqlservernametwo\instancetwo
This instance is using port 2571
The user that starts SQL service is: domain\sqlserviceinstancetwo
FDQN name of the instance is: sqlserviceinstancetwo.domain.es
To register de spn's for this cluster you have to do:
Logon as Domain Administrator and run:
setspn -A MSSQLSvc/sqlservernameone:1433 domain\sqlserviceinstanceone
setspn -A MSSQLSvc/sqlserviceinstanceone.domain.es:1433 domain\sqlserviceinstanceone
setspn -A MSSQLSvc/sqlservernameone domain\sqlserviceinstanceone
setspn -A MSSQLSvc/sqlserviceinstanceone.domain.es domain\sqlserviceinstanceone
setspn -A MSSQLSvc/sqlservernametwo:2571 domain\sqlserviceinstancetwo
setspn -A MSSQLSvc/sqlserviceinstancetwo.domain.es:2571 domain\sqlserviceinstancetwo
setspn -A MSSQLSvc/sqlservernametwo domain\sqlserviceinstancetwo
setspn -A MSSQLSvc/sqlserviceinstancetwo.domain.es domain\sqlserviceinstancetwo
For a non cluster you must have do it only with the port as this:
setspn -A MSSQLSvc/sqlservernameone:1433 domain\sqlserviceinstanceone
setspn -A MSSQLSvc/sqlserviceinstanceone.domain.es:1433 domain\sqlserviceinstanceone
setspn -A MSSQLSvc/sqlservernametwo:2571 domain\sqlserviceinstancetwo
setspn -A MSSQLSvc/sqlserviceinstancetwo.domain.es:2571 domain\sqlserviceinstancetwo
If you don´t have instances just use the server name:
Server named: sqltest
This instance is using port 1753
The user that starts SQL service is: domain\sqlservicetest
FDQN name of the instance is: sqltest.domain.es
setspn -A MSSQLSvc/sqltest:1753 domain\sqlservicetest
setspn -A MSSQLSvc/sqltest.domain.es:1753 domain\sqlservicetest
in a cluster you must add as well the command without the port as this:
setspn -A MSSQLSvc/sqltest domain\sqlservicetest
setspn -A MSSQLSvc/sqltest.domain.es domain\sqlservicetest
To check if is registered type:
SETSPN -L domain\sqlserviceinstanceone (the user that starts SQL)
SETSPN -L domain\sqlserviceinstancetwo (the user that starts SQL)
SETSPN -L domain\sqlservicetest (the user that starts SQL)
The sql query that checks if you are working with kerberos or NTLM is:
select @ServiceaccountName, @@servername, auth_scheme from sys.dm_exec_connections where session_id=@@spid
and MUST BE RUN FROM ANOTHER SERVER to verify that te server is in kerberos.
Regards
Gonzalo Moles
🙂
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply