SetSPN unable to locate account

▲
4
▼

I have SQL Server 2014 installed on a Windows Server 2012 R2, which is also an AD DC. When I try to connect to it using SQL Server Management Studio (SSMS) from a client desktop on the same local domain, I got this error message:

The target principal name is incorrect. Cannot generate SSPI context. (Microsoft SQL Server)

So following other posts on the same issue, I downloaded Kerberos Configuration Manager on the SQL server, which found 2 Misplaced SPN. The SPN Script commands proposed by the Kerberos Configuration Manager are as follows:

SetSPN -d "MSSQLSvc/SERVERNAME.internal.domain.com" "internal\SERVERNAME$"
SetSPN -s "MSSQLSvc/SERVERNAME.internal.domain.com" "DOMAIN\SERVERNAME$"

But when I tried to run the first command in cmd on the server (the "SetSPN -d" one), I got this error: 

FindDomainForAccount: Call to DsGetDcNameWithAccountW failed with return value 0x0000054B
Unable to locate account SERVERNAME$

I'm not sure how to move forward from here. Googling around hasn't turned out the right answer. Please help. The questions are:

1) Is the misplaced SPN the culprit? If so, how to correct?

2) If not, how can I connect to SQL Server from a client desktop on the same local domain, using Windows authentication?

sql-server
kerberos
sspi
asked on Stack Overflow Apr 21, 2017 by Zhang18 • edited Apr 23, 2017 by Zhang18

2 Answers

▲
4
▼

Ensure you are running the script from a machine joined to the Active Directory domain and the machine's DNS is resolving to AD correctly. To find SERVERNAME$, the machine needs to ask DNS for the location of an Active Directory domain controller to query. The SPN Script is also wrong.

  1. Get rid of the quotation marks, they're not needed in this context, especially given that there are no embedded spaces to enclose.

  2. Ensure you are logged into the internal domain in order to run the first command. The 2nd command is run while logged into DOMAIN.

    I think the suggested script of:

SetSPN -d "MSSQLSvc/SERVERNAME.internal.domain.com" "internal\SERVERNAME$"
SetSPN -s "MSSQLSvc/SERVERNAME.internal.domain.com" "DOMAIN\SERVERNAME$"

should have been this instead:

SetSPN -d MSSQLSvc/SERVERNAME.internal.domain.com internal\SERVERNAME$
SetSPN -s MSSQLSvc/SERVERNAME.internal.domain.com DOMAIN\SERVERNAME$

I just tested the second line in my environment and it worked. I don't need to obfuscate my test environment, so it actually was the following:

SetSPN -s MSSQLSvc/dc1.dev.local DEV\dc1$

...and the result:

Checking domain DC=dev,DC=local

    Registering ServicePrincipalNames for CN=DC1,OU=Domain Controllers,DC=dev,DC=local
            MSSQLSvc/dc1.dev.local
    Updated object
    
    C:\>
answered on Stack Overflow Apr 21, 2017 by T-Heron • edited Jun 20, 2020 by Community
▲
3
▼

Per my last comment to @T-Heron's answer, the internal\SERVERNAME$ account does not exist. It should be internal.domain.com\SERVERNAME$, which is the same account as DOMAIN\SERVERNAME$, where DOMAIN is the pre-Windows 2000 name for internal.domain.com.

As a result, the misplaced SPN as identified by Kerberos Configuration Manager is not the root cause of the initial error message: The target principal name is incorrect. Cannot generate SSPI context. (Microsoft SQL Server)

The solution to the initial problem came from this post, where several steps are performed on both the client and server side to resolve the issue.

answered on Stack Overflow Apr 27, 2017 by Zhang18

User contributions licensed under CC BY-SA 3.0