December 11, 2008 at 9:23 am
Kevin Rathgeber (12/11/2008)
Yes I agree it does. I understand where you are coming from....the funny part....the Microsoft guys told us not to look at what they had published. Stupidest thing I ever heard. We were trying to follow what they had posted time and again and were having problems which prompted our call to MS. Now If I can find the eMails they sent us on this issue - I will get back to this thread in more detail on what was indicated to us.
I guess the only thing I can say to this is, "It wouldn't be the first time..." 😉
K. Brian Kelley
@kbriankelley
December 11, 2008 at 9:55 am
These were the two little tidbits of information that helped us with the problem (one directly one indirectly):
Setting the NTAuthenticationProviders in IIS to NTLM was one of the things that was never documented in the process we had seen online. If using Kerberos you would set this to "Negotiate,NTLM" instead of NTLM.
This was how we setup the NTAuthenticationProviders setting in the IIS metabase to work with NTLM.
C:\Inetpub\AdminScripts>adsutil.vbs set w3svc/401108787/root/NTAuthenticationProviders "NTLM"
The one other tidbit that was suggested to us was the following (for performance):
"On IIS6 AuthPersistSingleRequest is set to True by default which means that every request needs to reauthenticate. On IIS4/5 AuthPersistSingleRequest is False meaning that IIS remembers who you are as long as a request comes in on a socket (ip/port) that has already gone through authentication. We want to set AuthPersistSingleRequest to False on your IIS6 server (opposite of the new default) so that it will stop treating each single request like a new connection (which forces authentication). We want IIS to remember the user to improve performance."
More info on the AuthPersist setting
http://technet.microsoft.com/en-us/library/cc781339.aspx
Now maybe there is more going on under the hood than what we were seeing. I really wish we still had this setup available (everything is Kerberos now).....I would post the results of Brian's DelegConfig tool as well as the event log entries on each of the servers which did show NTLM connections.
December 11, 2008 at 10:05 am
Mr. Kelley, did I maybe get this wrong in what I was explaining above. Setting the NTAuthenticationProviders to NTLM, is it possible that I am getting mixed up and that that enforces NTLM from the client to the IIS server, but then uses Kerberos from IIS out to sql server (through SPNs) in essence only creating one hop and not two?
If this is the case, I will wipe the egg off my face and appologize directly for this misundertanding.
December 11, 2008 at 10:42 am
Great article, I really needed this!
Do I have to go to each server and create the SPN's or can I install SETSPN.EXE on my local workstation and run SETSPN from there to deploy SPN for each of my SQL servers? I also have some clustered SQl servers, Do I need to create SPNs for the cluster virtual sever name and actual server name?
Thanks!
December 11, 2008 at 10:53 am
Kevin Rathgeber (12/11/2008)
Mr. Kelley, did I maybe get this wrong in what I was explaining above. Setting the NTAuthenticationProviders to NTLM, is it possible that I am getting mixed up and that that enforces NTLM from the client to the IIS server, but then uses Kerberos from IIS out to sql server (through SPNs) in essence only creating one hop and not two?If this is the case, I will wipe the egg off my face and appologize directly for this misundertanding.
This enforces NTLM from the client to the IIS box. Were folks being prompted for their login? Also, was delegation turned on for the IIS server?
K. Brian Kelley
@kbriankelley
December 11, 2008 at 10:55 am
jabbott (12/11/2008)
Great article, I really needed this!Do I have to go to each server and create the SPN's or can I install SETSPN.EXE on my local workstation and run SETSPN from there to deploy SPN for each of my SQL servers? I also have some clustered SQl servers, Do I need to create SPNs for the cluster virtual sever name and actual server name?
Thanks!
SETSPN can be run from anywhere. It's a command-line tool that talks with the domain controller. The one proviso I have found with it is it takes to the domain for where the computer account is located. If your computer accounts are on the same domain as your user accounts, there's nothing to worry about. Also, SETSPN does require domain admin rights to be able to add or delete SPNs from Active Directory. You can use the -L (list) option to get information, but you can't make any changes unless you have those rights.
K. Brian Kelley
@kbriankelley
December 11, 2008 at 11:03 am
K. Brian Kelley (12/11/2008)
Kevin Rathgeber (12/11/2008)
Mr. Kelley, did I maybe get this wrong in what I was explaining above. Setting the NTAuthenticationProviders to NTLM, is it possible that I am getting mixed up and that that enforces NTLM from the client to the IIS server, but then uses Kerberos from IIS out to sql server (through SPNs) in essence only creating one hop and not two?If this is the case, I will wipe the egg off my face and appologize directly for this misundertanding.
This enforces NTLM from the client to the IIS box. Were folks being prompted for their login? Also, was delegation turned on for the IIS server?
They were not being prompted......the servers were internal and by default we have the Automatic logon only in intranet zone as well as Enable Integrated Windows Authentication enbled on our browsers. Yes delegation was turned on, on the servers.
We had the Delegation tab in Active Directory for the IIS server set to "Trust this computer for delegation to specified services only" and under that "Use any authentication protocol". We then added all SPNs for services that IIS service would be talking to, to the "Services to which this account can present delegated credentials" list. This included SPNs for the sql servers it would talk to as well as one of our file servers (our source directories for IIS were contained on a remote share so delegation had to work out to those servers as well)
December 11, 2008 at 1:09 pm
Great article. Do you have a version of query that will work with SQL 2000?
Thanks
Kindest Regards,Mark S
December 11, 2008 at 1:45 pm
Mark Scro (12/11/2008)
Great article. Do you have a version of query that will work with SQL 2000?Thanks
Unfortunately, no. SQL Server 2000 does not provide that sort of information in any of its system tables or views. About the only thing you can do is have a process connect and check the security event log on the OS to see if a Kerberos connection was made on successful login.
K. Brian Kelley
@kbriankelley
December 11, 2008 at 3:30 pm
Brian, I use your sql2005 query and found out some of my sqlserver2005 were not using kerbores, I think the reason as you pointed out, is that sqlserver service is running under neither a system account nor a network service account. it's running under a local account.
my question is that if I modify the sqlserver service account to local system account or network service account, will it automatically register the SPN to active directory or I have to run the setspn.exe to add it? once It's added, if someone change the service account back to an local account, will the SPN be automatically removed from active directory?
I guess my question is overall a how to maintain the SPN kind of question.
thanks.
jiulu
Jiulu Sun
Senior DBA; Oracle/MSSQL/Sybase; CTV television inc.
December 11, 2008 at 5:13 pm
Neither the local System nor the Network Service account is recommended for the SQL Server service account. Your best bet would be to get a domain or account administrator in your organization to create a domain user account to run your SQL Server service under. If you change the service account using SQL Server Configuration Manager (assuming you're an administrator on the server where SQL Server is installed), the tool will ensure that tool has all the rights it needs to run SQL Server properly. Then get a domain admin to use the SETSPN utility to add the appropriate SPNs against that domain user account.
K. Brian Kelley
@kbriankelley
December 11, 2008 at 10:10 pm
Wonderful article with superb explanation:)...
December 12, 2008 at 3:36 am
This is a great article. I never clearly understood why I was getting the informational message in my SQL Error logs
"The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098, state: 15. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos"
My question(s)...I guess we need to be enabling Kerberos using the tool for all our named instances for added security (we use fixed ports)?
Will this need a SQL Service or Server restart?
We use Windows Domain accounts to run our SQL server services however our SQL Servers are hosting databases for websites so the Web servers connect to the databases using SQL lServer ogin accounts in the Web.config file. Do we need to do anything on the Web servers? i.e run the SPN tool, could it break the website?
Thanks in advance, once again brilliant article
December 12, 2008 at 8:07 am
schwizzla (12/12/2008)
This is a great article. I never clearly understood why I was getting the informational message in my SQL Error logs"The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098, state: 15. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos"
My question(s)...I guess we need to be enabling Kerberos using the tool for all our named instances for added security (we use fixed ports)?
Will this need a SQL Service or Server restart?
We use Windows Domain accounts to run our SQL server services however our SQL Servers are hosting databases for websites so the Web servers connect to the databases using SQL lServer ogin accounts in the Web.config file. Do we need to do anything on the Web servers? i.e run the SPN tool, could it break the website?
Thanks in advance, once again brilliant article
Once the SPNs are in place you should not need a SQL Server service or server restart. And with respect to your websites, Kerberos only applies to Windows authentication. Since they are connecting via SQL Server logins, they are not affected in any way.
K. Brian Kelley
@kbriankelley
December 14, 2008 at 10:51 am
Excellent article! Thanks 🙂
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
Viewing 15 posts - 31 through 45 (of 89 total)
You must be logged in to reply to this topic. Login to reply