November 16, 2008 at 3:31 pm
"select auth_scheme from sys.dm_exec_connections where session_id=@@spid"
I can see NTLM. Do you know how to change the auth_scheme value from NTLM to Kerberos?
November 17, 2008 at 5:09 am
Hi Erin,
A few conditions need to be true for this to work
- Client & server are in the same domain
- You've run the setspn stuff against the SQL startup account in AD and enabled kerberos delegation
- You need to authenticate with TCP/IP - check your config mgr - you can specify TCP:ServerName
November 17, 2008 at 9:32 am
The client & server are in the same domain. We ran the setspn stuff against the SQL startup account in AD and enabled kerberos delegation.
I'm not sure how to authenticate with TCP/IP in config mgr. How do I specify TCP:ServerName?
November 17, 2008 at 9:43 am
SQL Server Side: Go to SQL Server Configuration Manager - Under SQL Native Client Configuration is your Client Protocols and the order your instance will use. You can put TCP/IP above Named pipes here.
Client Side - in your SSMS - try connecting with TCP:ServerName
Try running your query now. If you are still authenticating with NTLM, go back and check the AD bits are ok by running a SETSPN -L domain\SQLStartaccount which should give you an entry like
MSSQLSvc/ServerName:9999 (9999 is the port your instance runs on. This can be found in Config Mgr).
November 17, 2008 at 9:45 am
Erin (11/17/2008)
The client & server are in the same domain. We ran the setspn stuff against the SQL startup account in AD and enabled kerberos delegation.I'm not sure how to authenticate with TCP/IP in config mgr. How do I specify TCP:ServerName?
TCP/IP should be the default network protocol the client uses to connect.
As far as the configuration is concerned, you shouldn't have needed to configure Kerberos delegation just to get authentication working. If you need impersonation, that's what delegation does. There's a great whitepaper on how to troubleshoot Kerberos authentication.
It walks you through the basic reasons Kerberos authentication might fail and the tools to use to see it. Unfortunately, it's IIS centric, but it does give the barebones knowledge.
With that said, I usually see the SPNs are the issue. Either they aren't assigned against the correct service account or they don't have the ports configured correctly. Are you dealing with a named instance or a default instance?
K. Brian Kelley
@kbriankelley
November 17, 2008 at 9:46 am
I'm dealing with a default instance.
November 17, 2008 at 9:52 am
If there aren't any changes to the port configuration, then there are basically 4 SPNs you have to have set up (I'm assuming no changes to the port configuration):
MSSQLSvc/*NetBIOS Name* *Svc Account*
MSSQLSvc/*NetBIOS Name*:1433 *Svc Account*
MSSQLSvc/*Fully Qualified Domain Name* *Svc Account*
MSSQLSvc/*Fully Qualified Domain Name*:1433 *Svc Account*
So for instance:
MSSQLSvc/MyServer MyDomain\MyServiceAccount
MSSQLSvc/MyServer:1433 MyDomain\MyServiceAccount
MSSQLSvc/MyServer.MyDomain.com MyDomain\MyServiceAccount
MSSQLSvc/MyServer.MyDomain.com:1433 MyDomain\MyServiceAccount
If you've done all that, check to make sure there aren't multiple SPNs out there for any of these (the troubleshooting guide I believe tells how to do this with LDP). If a client queries Active Directory and finds the wrong one, it won't connect via Kerberos authentication.
K. Brian Kelley
@kbriankelley
November 17, 2008 at 11:32 am
We have port on MSSQLsvc with both netbios and FQDN and are running fine.
What are the SPN's without the port for?
We have MSOLAPsvc.3 setup without port numbers.
Greg E
November 17, 2008 at 11:55 am
here's a good link to a tool to diagnosis kerberos setup:
Besides the SPN's, the server will have to be allowed to delegate. And so will the domain user being used for the service.
Note that to create an SPN, you need to be a Domain Admin.
And if you have SP2 on W2003, you also will have a component to alter the permissions on.
Turning on kerberos logging also can help troubleshooting.
I haven't done SQL server on W2008, but have done IIS on this platform. Kerberos is more of a kernel level mode on this platform. My IIS setup was a bit different.
As for Domains - we had a trust and could do this across domains in the same AD forest. We had to migrate to a new forest, and had trouble with the functional level of the PDC not being W2003, so we could not create the trust. So if the setup is done correctly, it can be done across more than a domain.
Greg E
November 17, 2008 at 4:17 pm
Greg Edwards (11/17/2008)
Besides the SPN's, the server will have to be allowed to delegate. And so will the domain user being used for the service.Note that to create an SPN, you need to be a Domain Admin.
And if you have SP2 on W2003, you also will have a component to alter the permissions on.
Turning on kerberos logging also can help troubleshooting.
I haven't done SQL server on W2008, but have done IIS on this platform. Kerberos is more of a kernel level mode on this platform. My IIS setup was a bit different.
As for Domains - we had a trust and could do this across domains in the same AD forest. We had to migrate to a new forest, and had trouble with the functional level of the PDC not being W2003, so we could not create the trust. So if the setup is done correctly, it can be done across more than a domain.
The server or the service account shouldn't need to have delegation turned on if all you are configuring is authentication. Now if SQL Server is passing credentials on, then you're right, the delegation setup is needed. The typical scenario is IIS connecting back to SQL Server. In this case the server running IIS requires the check to be configured for delegation. And if you're running with a service account, it does, too. However, just for Kerberos authentication, you should only need the SPNs. One of the KB articles (319723 - How to use Kerberos authentication in SQL Server) gives a scenario situation which talks about SQL Server using a linked server connection and doesn't make it clear that if you're not in that scenario, you don't need to set up delegation for SQL Server. The exact requirements are given clearly in this KB article:
K. Brian Kelley
@kbriankelley
November 17, 2008 at 4:25 pm
Greg Edwards (11/17/2008)
We have port on MSSQLsvc with both netbios and FQDN and are running fine.What are the SPN's without the port for?
We have MSOLAPsvc.3 setup without port numbers.
Greg E
If you're not running a failover cluster, I don't believe you need the one without ports. I am just in the habit of adding it. But if you are running a failover cluster for a default instance, it's part of the steps for configuration:
How to: Enable Kerberos Authentication on a SQL Server Failover Cluster
It's also required in SQL Server 2008 to allow Kerberos authentication for more than just TCP/IP.
MSOLAPsvc.3 is an Analysis Services server, right?
K. Brian Kelley
@kbriankelley
November 18, 2008 at 2:26 am
NTLM is an authentication protocol used in various Microsoft network protocol implementations and supported by the NTLM Security Support Provider ("NTLMSSP"). Originally used for authentication and negotiation of secure DCE/RPC, NTLM is also used throughout Microsoft's systems as an integrated single sign-on mechanism.
Regards,
Sandesh Segu
http://www.SansSQL.com
November 18, 2008 at 10:25 am
If you're not running a failover cluster, I don't believe you need the one without ports. I am just in the habit of adding it. But if you are running a failover cluster for a default instance, it's part of the steps for configuration:
How to: Enable Kerberos Authentication on a SQL Server Failover Cluster
It's also required in SQL Server 2008 to allow Kerberos authentication for more than just TCP/IP.
MSOLAPsvc.3 is an Analysis Services server, right?
[/quote]
I'll have to remember that for failover cluster. We don't use that, but another group here has one. But since we plan to move to SQL Server 2008, it will apply to us at some point.
Thanks!
MSOLAPSvc.3 is SSAS.
Greg E
November 18, 2008 at 11:15 am
Sandesh (11/18/2008)
NTLM is an authentication protocol used in various Microsoft network protocol implementations and supported by the NTLM Security Support Provider ("NTLMSSP"). Originally used for authentication and negotiation of secure DCE/RPC, NTLM is also used throughout Microsoft's systems as an integrated single sign-on mechanism.
Yes, all this is true. However, in general you want to use Kerberos over NTLM because Kerberos provides enhancements like validating the server, preventing replay attacks, and reducing the overhead network traffic to authenticate back to the DCs.
K. Brian Kelley
@kbriankelley
November 18, 2008 at 11:18 am
Greg Edwards (11/18/2008)
have to remember that for failover cluster. We don't use that, but another group here has one. But since we plan to move to SQL Server 2008, it will apply to us at some point.Thanks!
MSOLAPSvc.3 is SSAS.
Greg E
There's a different service to configure SSAS for Kerberos authentication. I've not done this (haven't had the need to as of yet), so I can't speak to any issues you might encounter, but if you haven't seen this KB, here you go:
How to configure SQL Server 2005 Analysis Services to use Kerberos authentication
K. Brian Kelley
@kbriankelley
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply