Bill thanks for the offline comments, we are going to try and move it off a SQL cluster for this time, if that doesn't help stuff then well go the SPN route, which I believe its going to have to happen anyways to fix the clusters accordingly, Unless I temporarly make the accounts DA, recycle the servers and see if the SPN creates ( I don't think it will but its an idea to get around fiddingly with asdiedit or the setspn)
Z Edward E. Ziots CISSP, Network +, Security + Network Engineer Lifespan Organization Email:ezi...@lifespan.org Cell:401-639-3505 -----Original Message----- From: Mayo, Bill [mailto:bem...@pittcountync.gov] Sent: Friday, October 08, 2010 1:53 PM To: NT System Admin Issues Subject: RE: Setting SPN's on Clustered SQL (2005) I have had this problem before. I don't remember a lot firsthand, but I do have my notes about it. Copied/pasted below. When multiple computers are traversed for integrated authentication (e.g. computer connects to web server which connects to SQL server), there are certain requirements for Kerberos to work properly. One of the key things needed in this scenario is for the Service Principal Name (SPN) to be properly set on the service account in Active Directory. This normally happens transparently, but some extra configuration may be required with clustered servers. If authentication fails in a scenario like this, one of the first things to check is the SPN. Basic troubleshooting steps follow. NOTE: The SetSPN utility is required and must be installed on the local computer (not server). 1.Confirm the port on which SQL Server is listening. When a single instance is installed, this should be 1433. When multiple instances are installed, such as with a cluster, you will need to check. 1.1.On the SQL Server in question, open SQL Server Configuration Manager. 1.2.Expand SQL Server 2005 Network Configuration. 1.3.There should be a "Protocols for..." entry for each named instance. Select the appropriate named instance. 1.4.In the right column, open TCP/IP. 1.5.Choose the IP Addresses tab in the resulting window. 1.6.Scroll down to the bottom, finding the section with the header "IPAll". Record the value of "TCP Dynamic Ports". 1.7.Close all windows. 2.From the workstation with SetSPN installed, run the following command, where serviceaccountname represents the service account running the SQL Server service instance: setspn -L serviceaccountname 3.Look for an entry for the server/instance name in question and note the port indicated (at the end of the line). If an entry exists and the port matches, this is not the problem. NOTE: Technical documents from Microsoft indicate that clustered instances should have an entry without a port and one with. I have not been able to confirm that the record without a port number is absolutely necessary, but add it when it doesn't exist and there is a problem. 4.If the entry doesn't exist, add it with the following command (where serviceaccountname is the service name, clustername is the cluster name, and 9999 is the port number recorded earlier): setspn -A MSSQLSvc/clustername.mydomain.local:9999 serviceaccountname 5.Per Microsoft's recommendation, you can also add an entry without the port number: setspn -A MSSQLSvc/clustername.mydomain.local serviceaccountname 6.Do another list to confirm the entries were properly added. 7.Synchronize the domain to replicate the changes and try again. -----Original Message----- From: Ziots, Edward [mailto:ezi...@lifespan.org] Sent: Friday, October 08, 2010 1:34 PM To: NT System Admin Issues Subject: Setting SPN's on Clustered SQL (2005) Has anyone had to manually add a SPN to a multi-node cluster SQL 2005 box before? I used the spn_query.vbs script from Microsoft to look at each of the nodes of the cluster and the Cluster Name and the SQL Server name ( Still default instance) Used the best practices that doesn't have the SQL Service accounts for SQLServer,Agent and Full Text Search as a normal user during the installation which leads me to believe that the SPN's didn't get written because when I look at the properties of the service account they don't have permissions to read or write SPN. And I get this error when troubleshooting Shavlik 7.60 with Domain Accounts from multiple consoles... SPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: IP_Address_of_client. Has anyone had to do this before for their clusters? Been looking at Microsoft KB 811889 which talks about the "Cannot Generate SSPI Context" error message. http://support.microsoft.com/kb/811889 Any ideas on this one? Z Edward E. Ziots CISSP, Network +, Security + Network Engineer Lifespan Organization Email:ezi...@lifespan.org Cell:401-639-3505 ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ <http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/> ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to listmana...@lyris.sunbeltsoftware.com with the body: unsubscribe ntsysadmin ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ <http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/> ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to listmana...@lyris.sunbeltsoftware.com with the body: unsubscribe ntsysadmin ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ <http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/> ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to listmana...@lyris.sunbeltsoftware.com with the body: unsubscribe ntsysadmin
