InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 05:19:32 GMT20http://www.sqlservercentral.com/Forums/Topic553157-146-1.aspxI am currently getting the error in sql server logs:2011-04-19 08:59:10 - ! [298] SQLServer Error: 802, Cannot generate SSPI context [SQLSTATE HY000] 2011-04-19 08:59:10 - ! [382] Logon to server '(local)' failed (JobManager)2011-04-19 08:59:10 - ! [298] SQLServer Error: 802, SQL Network Interfaces: The target principal name is incorrect. [SQLSTATE HY000] 2011-04-19 08:59:10 - ! [298] SQLServer Error: 802, Cannot generate SSPI context [SQLSTATE HY000] 2011-04-19 08:59:10 - ! [382] Logon to server '(local)' failed (ConnUpdateJobActivity_NextScheduledRunDate)2011-04-19 08:59:10 - ! [298] SQLServer Error: 802, SQL Network Interfaces: The target principal name is incorrect. [SQLSTATE HY000] 2011-04-19 08:59:10 - ! [298] SQLServer Error: 802, Cannot generate SSPI context [SQLSTATE HY000] 2011-04-19 08:59:10 - ! [382] Logon to server '(local)' failed (ConnAttemptCachableOp)2011-04-19 08:59:10 - ! [298] SQLServer Error: 802, SQL Network Interfaces: The target principal name is incorrect. [SQLSTATE HY000] 2011-04-19 08:59:10 - ! [298] SQLServer Error: 802, Cannot generate SSPI context [SQLSTATE HY000] 2011-04-19 08:59:10 - ! [382] Logon to server '(local)' failed (ConnAttemptCachableOp)2011-04-19 09:18:14 - ! [298] SQLServer Error: 802, SQL Network Interfaces: The target principal name is incorrect. [SQLSTATE HY000] 2011-04-19 09:18:14 - ! [298] SQLServer Error: 802, Cannot generate SSPI context [SQLSTATE HY000] 2011-04-19 09:18:14 - ! [382] Logon to server '(local)' failed (SaveAllSchedules)Tue, 19 Apr 2011 03:10:47 GMTforsqlserverhttp://www.sqlservercentral.com/Forums/Topic553157-146-1.aspxHi, just to clarify the Write/Read SPN permission on Active Directory is not recommended on clusters as for the active directory replication delay can make you have some problems as the registration need to be done before the service is brought up, and when the cluster node fail over the SPN is gonna get deleted and re-added, so for clusters this is not recommended.Tue, 22 Feb 2011 06:17:03 GMTGabriel Guerreirohttp://www.sqlservercentral.com/Forums/Topic553157-146-1.aspxPerry, thanks for your reply. You are right, I can use the setspn command to create the SPN manually. It is a good way to do it, but to give the "write service principal name" permission to the domain account is more flexible and that's why I decide to choose that approach.Sat, 23 Aug 2008 01:34:27 GMTIgnacio A. Salom Rangelhttp://www.sqlservercentral.com/Forums/Topic553157-146-1.aspxHi Brian,After testing giving the "Write Service PrincipalName" permission to a normal domain user (the account that starts the MSSQL service), I found out that it generates the SPN dynamically. I am planning to implement this on the production enviroment soon. Once again thank you for your help.Sat, 23 Aug 2008 01:29:54 GMTIgnacio A. Salom Rangelhttp://www.sqlservercentral.com/Forums/Topic553157-146-1.aspxyou can generate the SPN's manually usingSETSPN.EXEsyntax is[code]setspn -A ServiceClass/Host: Port AccountName[/code]so for sql instance issetspn -A MSSQLSvc/mysqlserver.domain.co.uk:staticsqlportnumber domain\sqlserviceuseraccountuse setspn -L domain\sqlserviceuseraccountto list the SPN's:cool:Tue, 19 Aug 2008 09:47:04 GMTPerry Whittlehttp://www.sqlservercentral.com/Forums/Topic553157-146-1.aspxThanks for your reply. I thought that giving the domain account the "write service principalname" permission will allow that account to create an SPN.Tue, 19 Aug 2008 07:50:27 GMTIgnacio A. Salom Rangelhttp://www.sqlservercentral.com/Forums/Topic553157-146-1.aspx[quote][b]Ignacio A. Salom Rangel (8/18/2008)[/b][hr]Hi Brian,We start the SQL service with a domain account. This account does not have the "write service principalname" permission. That is why there is no SPN created in the active directory. The connections to the SQL service are made using the NTLM protocol. I have been testing on the testcluster and if I use a domain account to start the sql service then the SPN is created and I can connect using the kerberos protocol. The kerberos protocol is disabled on the production server (I don't know the reason). I have to check if the kerberos protocol is enabled on the other SQL servers.I will keep in mind your recommendations about setting the TCP port for static. Something that is confusing me is that I thing that the SPN is automatically registered each time I restart the SQL service, so why do I have to set the tcp poort to static?Thank for your help.[/quote]It is only set automatically if SQL Server is running under something that comes in as the computer account (System in 2000 and Network Service in 2003) or a Domain Admin account. If it's a regular domain user account, it doesn't have rights to create the SPN. And running as either of the other two accounts is considered a violation of best practice. The first doesn't work on a cluster. The second is just an absolute security no-no.Mon, 18 Aug 2008 13:54:38 GMTK. Brian Kelleyhttp://www.sqlservercentral.com/Forums/Topic553157-146-1.aspxHi Brian,We start the SQL service with a domain account. This account does not have the "write service principalname" permission. That is why there is no SPN created in the active directory. The connections to the SQL service are made using the NTLM protocol. I have been testing on the testcluster and if I use a domain account to start the sql service then the SPN is created and I can connect using the kerberos protocol. The kerberos protocol is disabled on the production server (I don't know the reason). I have to check if the kerberos protocol is enabled on the other SQL servers.I will keep in mind your recommendations about setting the TCP port for static. Something that is confusing me is that I thing that the SPN is automatically registered each time I restart the SQL service, so why do I have to set the tcp poort to static?Thanks for your help.Mon, 18 Aug 2008 06:01:10 GMTIgnacio A. Salom Rangelhttp://www.sqlservercentral.com/Forums/Topic553157-146-1.aspxIf the SPN needs fixing, verify that you have set the TCP port set for static and not dynamic. If it's set for dynamic and for some reason couldn't grab the previous TCP port, it would change, since it's a named instance. This, of course, would automatically break the SPN since that keys on port.Thu, 14 Aug 2008 17:40:19 GMTK. Brian Kelleyhttp://www.sqlservercentral.com/Forums/Topic553157-146-1.aspxI connected with the SQL account, I think that the SPN is still an issue. I will check it tomorrow first thing in the morning (It is 00:33!). You save me! Thanks a lot. When I check the SPN I will let you know.Thu, 14 Aug 2008 16:33:24 GMTIgnacio A. Salom Rangelhttp://www.sqlservercentral.com/Forums/Topic553157-146-1.aspxDid the SPN have to be corrected? Thu, 14 Aug 2008 16:24:45 GMTK. Brian Kelleyhttp://www.sqlservercentral.com/Forums/Topic553157-146-1.aspxIt works! Thanks! The problem is solved!Thu, 14 Aug 2008 16:22:29 GMTIgnacio A. Salom Rangelhttp://www.sqlservercentral.com/Forums/Topic553157-146-1.aspxThanks for your reply. I will test it and let you know.Thu, 14 Aug 2008 15:58:05 GMTIgnacio A. Salom Rangelhttp://www.sqlservercentral.com/Forums/Topic553157-146-1.aspxDid an SPN get put in place in Active Directory to allow Kerberos authentication? Can you connect with a SQL Server based login like sa?Thu, 14 Aug 2008 15:41:56 GMTK. Brian Kelleyhttp://www.sqlservercentral.com/Forums/Topic553157-146-1.aspxAfter restarting one of the instances of my sql2005 cluster on single user mode I can't connect to the instance using "SQLCMD -S servername\instancename Error". I get the following error: "SQL Network Interfaces: The target principal name is incorrect. Sqlcmd: Error: Microsoft SQL Native Client : Cannot generate SSPI context". I don't know what can I do to restore the master database on this node. Any ideas are welcome. Thanks in advance.Thu, 14 Aug 2008 15:29:03 GMTIgnacio A. Salom Rangel