5

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?

Zhang18
  • 4,800
  • 10
  • 50
  • 67
  • Do you have problem to access by using SQL login? Do you have problem to access locally, you RDP to that Windows box, open SSMS, Windows authentication, open option change network protocol from default to tcp? – Wendy Apr 21 '17 at 19:24
  • @Wendy Accessing locally is fine. I installed SSMS on the server itself and that works. When I try to log in using SQL login (username/password) on the client windows desktop, it simply says `Cannot connect to SERVERNAME. Login failed for user 'xxx'`. When I use SSMS locally and right click on server name, Properties -> Connections: Allow remote connections to this server is enabled. In Sql Server Configuration Manger, SQL Server Network Configuration -> Protocals for SERVERNAME has TCP/IP enabled... what else should I try? Thx! – Zhang18 Apr 21 '17 at 19:45
  • When access locally, SSMS is not using TCP to connect SQL Server. 'Connect to Server', there is Options>> button, change network protocol from default to tcp. If you're able to get in. check user 'xxx' has access or not, if not adding it and give permissions Then try to use this user id access remotely. – Wendy Apr 21 '17 at 20:08
  • @Wendy If I bring up the SSMS locally, I see xxx in Security->Logins, and has public as well as sysadmin roles. When I click the Options >> button in the remote SSMS, the Network protocal has as the *only* option (the drop down menu has no other choices)... – Zhang18 Apr 21 '17 at 20:20
  • Sorry, can't help you much. Remotely both SQL and Windows authentication are failed. Please check a. Windows event logs and SQL log more information. b. SQL Server configuration, under SQL Server network and SQL native network, both tcp/ip enabled? c.is this a default SQL Server instance or named instance? – Wendy Apr 21 '17 at 21:18
  • I would add the *active-directory* and *SPN* tags. – T-Heron Apr 21 '17 at 22:09
  • you can see the error code of 0x548 at https://www.monkeybreadsoftware.de/xojo/winerror.shtml . "ERROR_INVALID_SERVER_STATE" – Reza Bayat Feb 13 '23 at 08:25

2 Answers2

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:\>
Community
  • 1
  • 1
T-Heron
  • 5,385
  • 7
  • 26
  • 52
  • I am actually running the Keberos Configuration Manager locally on the SQL Server (which is also the AD DC - not ideal but that's the set up I have), and hence am running the suggested scripts locally too. – Zhang18 Apr 23 '17 at 02:05
  • The comma was a typo, corrected. I removed part of the domain name obfuscation. The first level was actually "internal", and the questionable command (one with -d) has "internal" as domain name (which is the strange part). The 2nd command (-s) always ran fine and never had a problem - yet it alone didn't solve my *misplaced SPN* problem... – Zhang18 Apr 23 '17 at 02:12
  • Edited the answer accordingly. Ensure you are logged into the *internal* domain in order to run the first command. – T-Heron Apr 23 '17 at 11:33
  • How exactly do I do that? As I mentioned before, the physical SQL server is also the AD server and Domain Controller. And `internal.domain.com` is the only domain that exists. So isn't it by definition as long as I'm logged onto that computer via a domain account I'm in the *internal* domain? – Zhang18 Apr 24 '17 at 13:16
  • Also, would you replace *my* in the 2nd command also to *internal* in your answer, just to remove confusion for future readers? Thx! – Zhang18 Apr 24 '17 at 13:18
  • Pertaining to my earlier comment - I'm quite sure I'm logged into the *internal* domain. So the original problem is still not resolved. Any suggestions? – Zhang18 Apr 24 '17 at 14:20
  • 1
    Turns out the `internal.domain.com` and `DOMAIN` are the same domain, the latter is the "pre-Windows 2000" name for the latter. So when I spelled out the full domain name it worked in deletion: `SetSPN -d "MSSQLSvc/SERVERNAME.internal.domain.com" "internal.domain.com\SERVERNAME$"`. However, after I run the 2nd command, Kerberos Configuration Manager complains about the same issue: "Misplaced SPN". I ran "SetSPN -L" and confirmed the two accounts `internal.domain.com\SERVERNAME$` and `DOMAIN\SERVERNAME$` have exactly the same list of SPNs. So I wonder if the misplaced issue is not important... – Zhang18 Apr 27 '17 at 12:54
  • 1
    My IPv4 settings were wrong, domain srv ip was not set correctly and your hint fixed my problem. – Brlja Oct 10 '18 at 11:35
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.

Zhang18
  • 4,800
  • 10
  • 50
  • 67