Saturday, January 19, 2013

Got "Cannot generate SSPI context" error message after changing sql service account

Today I changed a sql server service startup account during testing, then when I tried to connect sql server with powershell, I got error message below:

The target principal name is incorrect.  Cannot generate SSPI context."


Here is the troubleshooting step I used
1. Rollback service startup account to old one, then the powershell script work. so it should be the issue with the new account.

2. Start sql server service again with new account. check sql server error log, then found error message in it


2013-01-20 00:53:36.49 Server      The SQL Server 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. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.


the error indicted sql server service can not register SPN during startup.

3. search the error code 0x2098, found link below
http://msdn.microsoft.com/en-us/library/ms681390(VS.85).aspx

ERROR_DS_INSUFF_ACCESS_RIGHTS
8344 (0x2098)
Insufficient access rights to perform the operation.
4. grant "Read servicePrincipalName" and "Write servicePrincipalName" permission to new account.
http://support.microsoft.com/kb/811889

for window 2012 AD, it is a little bit different

  1. Click Start, click Run, type Adsiedit.msc, and then click OK.
  2. In the ADSI Edit snap-in, expand Domain [DomainName], expand DC= RootDomainName, expand CN=Users, right-click CN= AccountName, and then click Properties. 
  3. In the CN= AccountName Properties dialog box, click the Security tab.
  4. On the Security tab, click Advanced.
  5. In the Advanced Security Settings dialog box, select one of "SELF"'s row just like the pic below

     6. Click Edit, Open Permission Entry dialog box.
     7. Make sure Pricipal is "SELF", Type is "Allow" and "Applied to" is "This Object Only", in Properties section, select the properties below:
Read servicePrincipalName
Write servicePrincipalName

     8. Click OK three times, and then exit the ADSI Edit snap-in.

after restart the sql server service, make sure the spn resgister correctly:
2013-01-20 03:08:27.20 Server      The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/machinename.domainname.com ] for the SQL Server service. 


1 comment: