InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 23:20:11 GMT20http://www.sqlservercentral.com/Forums/Topic617608-59-1.aspxHi,page 14 of your presentation "Security Enhancements in SQL Server 2008" suggests that "Kerberos possible without SPN registered in AD"could you pls point me towards a resource explaining how this works.Many thanks Erdöl BiramenSenior DBAALSTOM / SwitzerlandTue, 08 May 2012 02:22:06 GMTebiramenhttp://www.sqlservercentral.com/Forums/Topic617608-59-1.aspx[quote][b]Ludo Bernaerts (3/25/2011)[/b][hr]great article but I have a question.I configured a sql instance like mentioned in the article and see all connections coming in with Kerberos auth.However the connections coming from his own (sqlagent & OS) are still NTLM. What can be the cause of this?[/quote]If this is on a cluster, then Kerberos is not guaranteed. A lot of connections will be via NTLM. Also, if you've only configured the SPNs with the ports, then Named Pipes isn't covered (or if you have a SQL Server 2005 instance, which doesn't include Kerberos support for Named Pipes) so if the local connections are being made that way, then you'll see NTLM also.Sat, 26 Mar 2011 00:23:25 GMTK. Brian Kelleyhttp://www.sqlservercentral.com/Forums/Topic617608-59-1.aspxThe timing on this re-print couldn't be better. Thanks!!!Fri, 25 Mar 2011 18:03:46 GMTEvil Kraig Fhttp://www.sqlservercentral.com/Forums/Topic617608-59-1.aspxAbsolutely wonderful article. This is what Friday should be like!Two discoveries, and I apologize if they were in the article or someone already pointed them out and I missed them. 1. Obviously, the cluster name should be used for the server name in a cluster situation. 2. NTLM seems to be used for local connections, even when Kerberos is functionally available.Fri, 25 Mar 2011 13:41:04 GMTRich Weisslerhttp://www.sqlservercentral.com/Forums/Topic617608-59-1.aspx[quote][b]Ludo Bernaerts (3/25/2011)[/b][hr]great article but I have a question.I configured a sql instance like mentioned in the article and see all connections coming in with Kerberos auth.However the connections coming from his own (sqlagent & OS) are still NTLM. What can be the cause of this?[/quote]1000 possible reasons...download kerbtray.exe from microsoft and see if tickets are getting passed successfully.Carlton.Fri, 25 Mar 2011 11:04:12 GMTCarlton Leachhttp://www.sqlservercentral.com/Forums/Topic617608-59-1.aspxFew days ago, I had problems with connections using Kerberos.Thanks Brian, your article help me a lot to understand all the behavior that involves SSPIFri, 25 Mar 2011 10:02:37 GMTLeonel Umaña Arayahttp://www.sqlservercentral.com/Forums/Topic617608-59-1.aspxBrian you should contact http://www.devproconnections.com/ and have this article printed. What a great piece of writting.I ran your T-SQL on a SharePoint Content DB ---- WSS_Content DB and I was suprised to see auth_scheme is KERBEROS which is great news.However, my question is I did not configure that, so I am assuming this is something SharePoint 2010 does. Thank you so much for the article.I will keep this in my Favorites for SQL Server when configuring SSRS. You made the start of my day nice with my cup of coffee.Fri, 25 Mar 2011 08:33:29 GMTmoojjoohttp://www.sqlservercentral.com/Forums/Topic617608-59-1.aspxgreat article but I have a question.I configured a sql instance like mentioned in the article and see all connections coming in with Kerberos auth.However the connections coming from his own (sqlagent & OS) are still NTLM. What can be the cause of this?Fri, 25 Mar 2011 03:44:27 GMTLudo Bernaertshttp://www.sqlservercentral.com/Forums/Topic617608-59-1.aspxFor sql service domain accounts that are NOT domain admins you can configure the service account(s) to create its own SPN on startup so you dont have to worry about the correct syntax for creating the SPN yourself (and also whether or not to create multiple SPNs for clustered instances). If you have 50, 100, 200 sql servers do you really want to be manually configuring/administrating SPNs ?For each sql service account (which can be running 1 + N sql services) ask the domain administrator to ensure delegation is selected in the domain user account settings. Also you will need to request (or do this yourself CAREFULLY if you have privileges) that your domain admin make the following changes in AD :Using ADSIEDIT for the sql service account (s) grant permissions to SELF for the following properties : Read servicePrincipalName Write servicePrincipalNameThis way the SQL server instance will create its own correctly formatted SPN at [b]startup[/b], sql restart is required using this method. Also sql server will not perform any maintenance on this or other SPNs so if you have a large server base you should consider listing the current SPNs to determine if any are redundant or no longer required.Fri, 25 Mar 2011 03:12:04 GMTdanny.thomas 57541http://www.sqlservercentral.com/Forums/Topic617608-59-1.aspxI recently had a problem where a SQL Server was falling back to NTLM. The solution was rather simple, and in case its of help (I don't think I've seen it mentioned in this yet) - check for duplicate SPN's. [code="other"]setspn -Q MSSQLSvc/servername.blah:1433[/code]Showed me that another accountname had the same SPN registered. After deleting the duplicate SPN, querying sys.dm_exec_connections reported any new connections using Kerberos auth scheme.Tue, 19 Oct 2010 10:17:48 GMTspongemagnethttp://www.sqlservercentral.com/Forums/Topic617608-59-1.aspxThe Microsoft publication is great. But after I set SPN for the service account, I went to AD (based on page 6, Figure 2), the registered SPN entry is not there. There is no way to grant delegation.Anybody has any experiences on this?We had to use designated slq login on linkserver as workaround. (that does not meet single sign on principle)by the way, after SPN registering, the entry will only show up under SETSPN -L domain\serviceaccountusernot under SETSPN -L hostnameIs that normal?Wed, 28 Jul 2010 10:03:10 GMTjswong05http://www.sqlservercentral.com/Forums/Topic617608-59-1.aspx[quote][b]jswong05 (7/27/2010)[/b][hr][quote][b]Harold Buckner (12/11/2008)[/b][hr][quote]Bradley Deem (12/11/2008)I have the EXACT same problem with Kerberos. Resulting in the NT AUTHORITY\ANONYMOUS LOGON. It does work though, because I'm able to connect from a Web Server to the SQL server using Kerberos and from the Web Server to SSRS on another server. It just breaks like Harold described above. Log off/Log on to resolve.[/quote]I'm glad I'm not the only one.[/quote]Well written article. I followed the instruction. Set up SPN on where I can see correct SPN using SETSPN -LMy linkserver between the two hosts (SQL2005) using windows authentication (impersonation) is still getting same error:Msg 18456, Level 14, State 1, Line 1Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.[/quote]I didn't cover Kerberos delegation. Both SQL Servers will need to be configured for SQL Server Authentication. Also, the initial SQL Server contacted will need to be set up to use Kerberos delegation:[url=http://msdn.microsoft.com/en-us/library/ee191523(SQL.100).aspx]How to Implement Kerberos Constrained Delegation with SQL Server 2008[/url]Tue, 27 Jul 2010 13:20:03 GMTK. Brian Kelleyhttp://www.sqlservercentral.com/Forums/Topic617608-59-1.aspx[quote][b]Harold Buckner (12/11/2008)[/b][hr][quote]Bradley Deem (12/11/2008)I have the EXACT same problem with Kerberos. Resulting in the NT AUTHORITY\ANONYMOUS LOGON. It does work though, because I'm able to connect from a Web Server to the SQL server using Kerberos and from the Web Server to SSRS on another server. It just breaks like Harold described above. Log off/Log on to resolve.[/quote]I'm glad I'm not the only one.[/quote]Well written article. I followed the instruction. Set up SPN on where I can see correct SPN using SETSPN -LMy linkserver between the two hosts (SQL2005) using windows authentication (impersonation) is still getting same error:Msg 18456, Level 14, State 1, Line 1Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.Tue, 27 Jul 2010 10:44:51 GMTjswong05http://www.sqlservercentral.com/Forums/Topic617608-59-1.aspxI've now resolved the issue.For anyone else who has issues configuring Kerberos authentication, take a look at this white paper, which helped me to get to the bottom of my issues.[url]http://www.microsoft.com/DownLoads/details.aspx?FamilyID=99b0f94f-e28a-4726-bffe-2f64ae2f59a2&displaylang=en[/url] which I found on another useful site: [url]http://callumhibbert.blogspot.com/2009/02/kerberos-delegation-and-sql-reporting.html[/url]Tue, 30 Mar 2010 09:41:13 GMTdave-djhttp://www.sqlservercentral.com/Forums/Topic617608-59-1.aspxI have noticed one other problem as well, running on SQLTEST02.After switching Reporting Services to run on SQLTest02 and connecting to it using one of my DNS entries: - sqltestrs/reports - iristestrs/reports - scatsrs/reportsI get the Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.If I logon via sqltest02/reports I can run the report and it authenticates using kerberos.I can then go back to using one of: - sqltestrs/reports - iristestrs/reports - scatsrs/reportsand the report will run successfully, even after closing my web browser and launching a new web browser session? It would appear I get a ticket for HTTP/sqltest02.forthports.net - which would possible explain why it works, but why is it not working for my dns entries?I had to configue Reporting Services to [b]RSWebApplication.config[/b] file <ReportServerUrl> to:[code="xml"] <UI> <ReportServerUrl>http://sqltest02/ReportServer</ReportServerUrl> <ReportServerVirtualDirectory></ReportServerVirtualDirectory> <ReportBuilderTrustLevel>FullTrust</ReportBuilderTrustLevel> </UI>[/code]as the reporting services website is not the default website. (I also setup the host headers)Is the config file part of the reason why it only generates a ticket for sqltest02 and not my dns references, that I have created http SPN's for?Tue, 23 Mar 2010 05:10:28 GMTdave-djhttp://www.sqlservercentral.com/Forums/Topic617608-59-1.aspxHi Brian.I've checked both servers are using configured for Kerberos, both SQLTest01 and SQLTest02 return the following NTAuthenticationProviders:[quote]NTAuthenticationProviders : (STRING) "Negotiate,NTLM"[/quote]I have some more information, which I hope is helpful:1. When I checked it first thing this morning, the 'live' reporting services was running on SQLTest01.I could logon and run reports successfully using Kerberos authentication, using all of the following: - sqltest01 - sqltestrs.forthprots.net ('live' report server dns entry) - iristestrs.forthprots.net ('live' report server dns entry) - scatstestrs.forthprots.net ('live' report server dns entry)[i]All of these tested successfully, from my laptop logged on as my standard domain account.[/i]I then failed-over the report server to run SQLTest02. I could run reports if I connected to sqltest02/reports, which successfully authenticated in Kerberos, but not using - sqltestrs.forthprots.net ('live' report server dns entry) - iristestrs.forthprots.net ('live' report server dns entry) - scatstestrs.forthprots.net ('live' report server dns entry)After a period of time, this then worked?? is this a ticket issue?(I purged this tickets on the server)2. Having switched back to sqltest01, to try and find out what is different, SQLTest 01 no longer worked in kerberos authentication?[i]On the sqltest01 server [/i] I purged the tickets, using kerbtray and if I logged on to sqltest01/reports, I can see a ticket pop up for MSSQLSvc/iris_test.forthports.net:1433 (my sql server data source).Back on my laptop, when I connect to sqltest01/reports and run a report, it run the report, using kerberos authentication, but for my admin account DL.Admin(but I'm logged on as my standrard account - DL.Standard)?? I logged on to the server sqltest01 as DL.Admin, but why is it picking up when I access the report server from my laptop? I noticed though, that a new ticket didn't appear in kerbtray. (note the report I running, runs the query from your article, to define what time of connect I have with the database)Let me know if I not using the kerbtray right, but what I do, to test tickets are being issued, is on the machine I access report server from, I purge the tickets, and then connect.confused and dazed - DaveMon, 22 Mar 2010 05:26:07 GMTdave-djhttp://www.sqlservercentral.com/Forums/Topic617608-59-1.aspxAre the IIS servers configured to use Negotiate,NTLM for NTAuthentication providers? Because you're working with SQLTest02 is not necessarily a sign that Kerberos is being used. Because the web server and the SQL Server are the same physical box, NTLM will work in that case. So if IIS is not accepting Kerberos authentication but only NTLM, you'd work in the first case but not the second.One easy way to tell is to connect to the server SQLTest02 and then check the Windows security log. How you connected should be logged. Also, the use of KerbTray here would be invaluable. If you aren't seeing the tickets pop up, you aren't connecting via Kerberos.Fri, 19 Mar 2010 12:35:10 GMTK. Brian Kelleyhttp://www.sqlservercentral.com/Forums/Topic617608-59-1.aspxHi Brian,Great article - Thanks.I'm having a few issue getting this working however (although I had it working earlier this morning, I've now broken it - not sure how). As far as I can see I've set up everying that's required, but something is clearly missing.I'll try and give you as much information as I can.[b]Configuration:[/b]- My solution invovles 2 servers [i]SQLTEST01[/i] and [i]SQLTEST02[/i].- Both servers are trusted for delegation withing ActiveDirectory- both have the following services running under a domain account [i]DOMAIN\SQLTest.svc[/i] - SQL Server (Default Instance) - SSRS (default Instance) - IIS 6.0 - ReportServer & Report Manager Use an application pools, running as [i]DOMAIN\SQLTest.svc[/i] - Website security is set to [i]Integrated Windows Authentication[/i][b]SSRS > Database Connection[/b]I'm using SSRS with mirrored database, so I use a DNS record to point users to the correct Database server, which will hold the Principle database, which will float between the servers.[b]ServicePrincipalNames[/b]I have that all working - but it creates this 'double-hop' scenario, from SSRSSo I have setup the following SPN records for the service [b]account [i]DOMAIN\SQLTest.svc[/i]:[/b]Registered ServicePrincipalNames for CN=SQL Test,OU=Service Accounts,OU=Tilbury,C=MyDomain,DC=net: MSSQLSvc/scats_test:1433 MSSQLSvc/iris_test:1433 http/sqltest01 http/sqltest02 http/iristestrs http/scatstestrs http/sqltest01.MyDomain.net http/scatstestrs.MyDomain.net http/iristestrs.MyDomain.net http/sqltest02.MyDomain.net MSSQLSvc/scats_test.MyDomain.net:1433 MSSQLSvc/iris_test.MyDomain.net:1433The [b]scats_test[/b] and [b]iris_test[/b] are for the 'Principle' server, which could be either SQLTEST01 or SQLTEST02. to simply the SSRS side, I and login on to SQLTEST02 Report Server, which has the following DNS References: - scatstestrs.MyDomain.net - iristestrs.MyDomain.netWhen running a report, where I have set the RS Data Source to use [i]Windows Integrated Security[/i], I get the Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' - so it's not using kerberos. I can't spot why though.Your help - or that of anyone else who has it working would be appreciated. :ermm:[b]Some additional information to add:[/b]It would appear, if I connect to my report server on SQLTEST02 using [b]http://sqltest02/Reports/[/b] Kerberos connection works, but if I use [b]http://iristestrs/Reports/[/b] i get the Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.What do I need to do to get the SPN for iristestrs working?Fri, 19 Mar 2010 06:29:12 GMTdave-djhttp://www.sqlservercentral.com/Forums/Topic617608-59-1.aspx[quote][b]james.menke (7/16/2009)[/b][hr]Your article tipped me off to find my solution.I had the same exact issue as listed in this forum.I changed the SQL Service to run as a domain administrator - Started then Stopped the service. I then changed the SQL Service to run as the original windows user that I had intended. Apparently that gave the service enough rights to generate the correct SPN.[/quote]The SPN would have been registered under the domain admin, which means it would likely be wrong. This would be a good thing to check with SETSPN -L *service account* to see.Thu, 16 Jul 2009 07:30:07 GMTK. Brian Kelleyhttp://www.sqlservercentral.com/Forums/Topic617608-59-1.aspx[quote][b]Carlton Leach (6/25/2009)[/b][hr]Hi Brian,Further to this article: what services are required to be running on the SQL server? I presume Kerberos Key Distribution Centre service has to be activated on the SQL server itself, is this correct? also is there anything else that needs to be running?Cheers,Carlton..[/quote]The KDC only needs to run on the domain controllers. Actually, nothing out of the ordinary for a standard member server needs to run on the SQL Server.Thu, 16 Jul 2009 07:29:05 GMTK. Brian Kelleyhttp://www.sqlservercentral.com/Forums/Topic617608-59-1.aspxYour article tipped me off to find my solution.I had the same exact issue as listed in this forum.I changed the SQL Service to run as a domain administrator - Started then Stopped the service. I then changed the SQL Service to run as the original windows user that I had intended. Apparently that gave the service enough rights to generate the correct SPN.Thu, 16 Jul 2009 07:09:44 GMTjames.menkehttp://www.sqlservercentral.com/Forums/Topic617608-59-1.aspxHi Brian,Further to this article: what services are required to be running on the SQL server? I presume Kerberos Key Distribution Centre service has to be activated on the SQL server itself, is this correct? also is there anything else that needs to be running?Cheers,Carlton..Thu, 25 Jun 2009 17:38:42 GMTCarlton Leachhttp://www.sqlservercentral.com/Forums/Topic617608-59-1.aspxIs it the SSPI which attempts to fall back to NTLM if Kerberos authentication fails?Wed, 20 May 2009 09:10:49 GMTwaitstatehttp://www.sqlservercentral.com/Forums/Topic617608-59-1.aspx[quote][b]Taylor (5/19/2009)[/b][hr]Great article!Any chance of another great article for Kerberos Delegation?[/quote]At some point, yes. Generating the screenshots is what is problematic since I'm no longer a domain admin (took a transfer to go back to being a development DBA).Wed, 20 May 2009 08:51:09 GMTK. Brian Kelleyhttp://www.sqlservercentral.com/Forums/Topic617608-59-1.aspx[quote][b]Kevin Rathgeber (5/19/2009)[/b][hr]While on the whole Kerberos subject here...Have you ever run into a problem where it appears that Kerberos appears to just stop working (not sure if it is reverting back to NTLM or not)? What happens is we can have our impersonation/delegation working fine and then all of a sudden you start getting the generic basic login screen that IE presents. It is like the web site does not receive the proper credentials (or any for that matter). The only solution we have come across so far as is to reboot the computer and then everything works fine again.[/quote]I've seen cases like this where, for whatever reason, the computer system stops getting the Kerberos ticket from the DC. Different reasons. Usually you also see errors in the System event log where the computer is having trouble talking to the DC.Wed, 20 May 2009 08:50:17 GMTK. Brian Kelleyhttp://www.sqlservercentral.com/Forums/Topic617608-59-1.aspxWhile on the whole Kerberos subject here...Have you ever run into a problem where it appears that Kerberos appears to just stop working (not sure if it is reverting back to NTLM or not)? What happens is we can have our impersonation/delegation working fine and then all of a sudden you start getting the generic basic login screen that IE presents. It is like the web site does not receive the proper credentials (or any for that matter). The only solution we have come across so far as is to reboot the computer and then everything works fine again.Tue, 19 May 2009 16:36:18 GMTKevin Rathgeberhttp://www.sqlservercentral.com/Forums/Topic617608-59-1.aspxGreat article!Any chance of another great article for Kerberos Delegation?Tue, 19 May 2009 15:56:33 GMTwaitstatehttp://www.sqlservercentral.com/Forums/Topic617608-59-1.aspxThey should not have a detrimental effect if you remove them. You might use kerbtray to see if the tickets are even being created/assigned. Thu, 30 Apr 2009 15:59:44 GMTK. Brian Kelleyhttp://www.sqlservercentral.com/Forums/Topic617608-59-1.aspx[quote][b]K. Brian Kelley (4/30/2009)[/b][hr][quote][b]Carlton Leach (4/29/2009)[/b][hr]Hi Brian, Great article I agree. I am having trouble getting Kerberos set up tho.I am using:- a Virtual Machine (VMWare)- sql 2005 SP3I have run the 4 steps adived: SETSPN -A MSSQLSvc/MyDBServer MyDomain\SQLServerServiceSETSPN -A MSSQLSvc/MyDBServer:1433 MyDomain\SQLServerServiceSETSPN -A MSSQLSvc/MyDBServer.mydomain.com MyDomain\SQLServerServiceSETSPN -A MSSQLSvc/MyDBServer.mydomain.com:1433 MyDomain\SQLServerServicenow when I run SETSPN - L MyDBServer I get:Host/MyDBServer HOST/MyDBServer.mydomain.comand for SETSPN - L MyDomain\SQLServerService I get:MSSQLSvc/MyDBServer.mydomain.comMSSQLSvc/MyDBServer.mydomain.com:1433MSSQLSvc/MyDBServerMSSQLSvc/MyDBServer:1433I am a little confused, one thing I know is that the MyDomain\SQLServerService is locked down quite heavily via gpedit, it can logon as a service and lock pages in memory but not much more. I'm wondering if it needs a specific permission of some sort?I can see that sessions are still using NTLM, any ideas as to what is nobbling me?Thanks,Carlton..[/quote]The lockdown shouldn't affect anything. Are the clients in the same domain as the SQL Server? I'm assuming yes, but want to confirm. The reason I ask is that when you cross forests (not domains, but we'll start with the simplest model) you have to set up a forest level trust to do Kerberos authentication across.Also, is this for a default instance listening on the default port of 1433? If so, those SPNs are correct.[/quote]Hi Brian,Thanks for the swift response, Yes this is a default SQL instance listening on port 1433. And yes the clients are in the same domain (I am one of them). I am waiting for windows lot to come back to me with what type of domain (2k or 2k03 etc) but I don't think this is going to matter.I have also asked if there is a specific setting (possibly group policy) that is going to prevent authentication via kerberos.One question: Are these SPN's going to have any kind of detrimental affect on anything if I do not remove them?Thanks again,Carlton..Thu, 30 Apr 2009 14:43:44 GMTCarlton Leachhttp://www.sqlservercentral.com/Forums/Topic617608-59-1.aspx[quote][b]Carlton Leach (4/29/2009)[/b][hr]Hi Brian, Great article I agree. I am having trouble getting Kerberos set up tho.I am using:- a Virtual Machine (VMWare)- sql 2005 SP3I have run the 4 steps adived: SETSPN -A MSSQLSvc/MyDBServer MyDomain\SQLServerServiceSETSPN -A MSSQLSvc/MyDBServer:1433 MyDomain\SQLServerServiceSETSPN -A MSSQLSvc/MyDBServer.mydomain.com MyDomain\SQLServerServiceSETSPN -A MSSQLSvc/MyDBServer.mydomain.com:1433 MyDomain\SQLServerServicenow when I run SETSPN - L MyDBServer I get:Host/MyDBServer HOST/MyDBServer.mydomain.comand for SETSPN - L MyDomain\SQLServerService I get:MSSQLSvc/MyDBServer.mydomain.comMSSQLSvc/MyDBServer.mydomain.com:1433MSSQLSvc/MyDBServerMSSQLSvc/MyDBServer:1433I am a little confused, one thing I know is that the MyDomain\SQLServerService is locked down quite heavily via gpedit, it can logon as a service and lock pages in memory but not much more. I'm wondering if it needs a specific permission of some sort?I can see that sessions are still using NTLM, any ideas as to what is nobbling me?Thanks,Carlton..[/quote]The lockdown shouldn't affect anything. Are the clients in the same domain as the SQL Server? I'm assuming yes, but want to confirm. The reason I ask is that when you cross forests (not domains, but we'll start with the simplest model) you have to set up a forest level trust to do Kerberos authentication across.Also, is this for a default instance listening on the default port of 1433? If so, those SPNs are correct.Thu, 30 Apr 2009 03:54:45 GMTK. Brian Kelleyhttp://www.sqlservercentral.com/Forums/Topic617608-59-1.aspxHi Brian, Great article I agree. I am having trouble getting Kerberos set up tho.I am using:- a Virtual Machine (VMWare)- sql 2005 SP3I have run the 4 steps adived: SETSPN -A MSSQLSvc/MyDBServer MyDomain\SQLServerServiceSETSPN -A MSSQLSvc/MyDBServer:1433 MyDomain\SQLServerServiceSETSPN -A MSSQLSvc/MyDBServer.mydomain.com MyDomain\SQLServerServiceSETSPN -A MSSQLSvc/MyDBServer.mydomain.com:1433 MyDomain\SQLServerServicenow when I run SETSPN - L MyDBServer I get:Host/MyDBServer HOST/MyDBServer.mydomain.comand for SETSPN - L MyDomain\SQLServerService I get:MSSQLSvc/MyDBServer.mydomain.comMSSQLSvc/MyDBServer.mydomain.com:1433MSSQLSvc/MyDBServerMSSQLSvc/MyDBServer:1433I am a little confused, one thing I know is that the MyDomain\SQLServerService is locked down quite heavily via gpedit, it can logon as a service and lock pages in memory but not much more. I'm wondering if it needs a specific permission of some sort?I can see that sessions are still using NTLM, any ideas as to what is nobbling me?Thanks,Carlton..Wed, 29 Apr 2009 16:53:27 GMTCarlton Leachhttp://www.sqlservercentral.com/Forums/Topic617608-59-1.aspxThe SPN does not need to be recreated when SQL Server is stopped and restarted. The only reason this would be necessary is the SQL Server is using dynamic ports and it has a port change. If SQL Server is configured to use static ports (which it should), then the SPN only has to be registered once.Fri, 16 Jan 2009 17:37:12 GMTK. Brian Kelleyhttp://www.sqlservercentral.com/Forums/Topic617608-59-1.aspx[quote][b]SA (1/15/2009)[/b][hr]The article is great, but I've read that the SPN needs to be re-registered when SQL is stopped and re-started.I usually provide the service account read-write SPN privileges on AD. Is someone else doing the same? This avoids having to re-register the SPN if it un-registers during a reboot.[/quote]I would be very interested in knowing more about that as well.Fri, 16 Jan 2009 08:32:13 GMTMaxerhttp://www.sqlservercentral.com/Forums/Topic617608-59-1.aspxThe article is great, but I've read that the SPN needs to be re-registered when SQL is stopped and re-started.I usually provide the service account read-write SPN privileges on AD. Is someone else doing the same? This avoids having to re-register the SPN if it un-registers during a reboot.Thu, 15 Jan 2009 15:11:12 GMTSA-1http://www.sqlservercentral.com/Forums/Topic617608-59-1.aspxUpdate: problem resolved. We kept trying to delete that SPN but didn't have the right syntax. The AD admin got it to work though with the following:[quote]setspn -D MSSQLSvc/SQL1.CORP.[MYCOMPANY].com:1433 SQL1[/quote]Thanks for the great article and forum replies Brian. MS has done a lame job with the documentation and tools for this. It's good to have somebody in the community like you that can help make sense of it all.Tue, 16 Dec 2008 07:55:10 GMTRob Symondshttp://www.sqlservercentral.com/Forums/Topic617608-59-1.aspx[quote]Is SQL1 running under the local System or Network Service account?[/quote]SQL1 is running under the domain account: CORP\SqlServices. But what I'm thinking is that when the last DBA installed it last year, he installed it (or maybe upgraded it) under a local account? I don't have any way of knowing. I wasn't here so I don't have a history on the box. I just inherited it with the problem.Thanks.Tue, 16 Dec 2008 05:54:08 GMTRob Symondshttp://www.sqlservercentral.com/Forums/Topic617608-59-1.aspx[quote][b]Rob Symonds (12/15/2008)[/b][hr]We tried deleting the MSSQLSvc SPN on SQL1. It returns an object updated message but SETSPN -L shows it in the list again.-- A previous DBA installed SQL Server 2005 on SQL1. I wonder if he installed it under the default account the installer uses and only later updated the services to run under the domain account. My suspicion is that this is what happened and is somehow the cause of the problem. What do you think?Thanks again![/quote]Is SQL1 running under the local System or Network Service account?Mon, 15 Dec 2008 21:00:30 GMTK. Brian Kelleyhttp://www.sqlservercentral.com/Forums/Topic617608-59-1.aspxThanks Brian. The MS docs on SPNs are confusing. I'm looking at things with our AD admin. More investigation reveals the following:All services for SQL Server run under the CORP\SqlServices domain account. SETSPN -L CORP\SqlServices shows no SPNs attached. However, SETSPN -L SQL1 (where SQL1 is the name of my SQL box) shows the MSSQLSvc SPN:[quote]HOST/SQL1$.CORPHOST/SQL1$MSSQLSvc/SQL1.CORP.[MYCOMPANY].COM:1433HOST/SQL1.CORP.[MYCOMPANY].COMHOST/SQL1[/quote]SETSPN -L SQL2, SQL3, etc (where SQL2, SQL3 are the names of other SQL boxes) shows no SPNs:[quote]MSSQLSvc/SQL2.CORP.[MYCOMPANY].COM:1433HOST/SQL2.CORP.[MYCOMPANY].COMHOST/SQL2[/quote][quote]MSSQLSvc/SQL3.CORP.[MYCOMPANY].COM:1433HOST/SQL3.CORP.[MYCOMPANY].COMHOST/SQL3[/quote]We tried deleting the MSSQLSvc SPN on SQL1. It returns an object updated message but SETSPN -L shows it in the list again.-- A previous DBA installed SQL Server 2005 on SQL1. I wonder if he installed it under the default account the installer uses and only later updated the services to run under the domain account. My suspicion is that this is what happened and is somehow the cause of the problem. What do you think?Thanks again!Mon, 15 Dec 2008 16:00:08 GMTRob Symondshttp://www.sqlservercentral.com/Forums/Topic617608-59-1.aspx[quote][b]Rob Symonds (12/15/2008)[/b][hr]I should've checked before I posted: I can see using the query from the article that all the logins are using NTLM. I'm still not sure why I get the SSPI error when trying to login to that one SQL server though. Any ideas?[/quote]There are quite a few reasons. One of the most common is that an SPN did get registered, but to a different account. Here's how to look for that:[url=http://technet.microsoft.com/en-us/library/cc772897.aspx]Service Logons Fail Due to Incorrectly Set SPNs[/url]Look for the section on how to use LDP to find duplicate SPNs. A duplicate or incorrect SPN will definitely cause that failure. Otherwise, if you go to support.microsoft.com and search for "cannot generate SSPI context" you'll get a handful of articles that talk about the issues.And with respect to one domain account for multiple SQL Servers, adding the additional SPNs is fine and everything will work. Mon, 15 Dec 2008 09:29:51 GMTK. Brian Kelleyhttp://www.sqlservercentral.com/Forums/Topic617608-59-1.aspxI should've checked before I posted: I can see using the query from the article that all the logins are using NTLM. I'm still not sure why I get the SSPI error when trying to login to that one SQL server though. Any ideas?Mon, 15 Dec 2008 09:12:01 GMTRob Symonds