Posts Tagged ‘Service Principal Names (SETSPN)’

Service Principal Names for SQL Server

June 16, 2014 1 comment

Service Principal Names (SPN) get used for Kerberos authentication.  SQL Server require that all instances have SPNs configured, if not Kerberos authentication fails.  By default starting with Windows 2008 all communication between clients and SQL Server is first attempted on Kerberos.  If it fails, it falls back to NTLM.  Often if you have Kerberos issues you might also have SSPI error messages in your SQL Server errorlog.

So question can be asked, if it falls back to NTLM and I can get my work done.  Why do I care?  Simple answer is delegation.  With incorrect configured Kerberos, delegation cannot be completed and logins fail.  For example on delegation, read my post on Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’ with SQL Server Reporting Services.

So what SPNs we need? Simple answer two SPN per SQL Server instance.

  • MSSQLSvc/SQLServerName[:Port] Domain\SQLServerServiceAccount
  • MSSQLSvc/SQLServerName.FQDN[:Port] Domain\SQLServerServiceAccount

Depending on your configuration, SPNs can look different.  We can manage SPNs two ways; 1) using SETSPN utility 2) using Activity Tool call EDITADIS.  Most people rely on SETSPN, as other tool is for Active Directory administrators.  And they (AD Admins) don’t like it when we mess around with their stuff; like we don’t like it when they mess around with our stuff ;-).  So lets talk about various forms of SPNs you can create.

Example #1: Default Instance, Default Port (1433), Using Domain Service Account

  • MSSQLSvc/SQLServerName Domain\SQLServerServiceAccount
  • MSSQLSvc/SQLServerName.FQDN Domain\SQLServerServiceAccount

Example #2: Default Instance, Non-Default Port, Using Domain Service Account
Example #3: Named Instance, Static Non-Default Port, Using Domain Service Account

  • MSSQLSvc/SQLServerName:Port Domain\SQLServerServiceAccount
  • MSSQLSvc/SQLServerName.FQDN:Port Domain\SQLServerServiceAccount

Example #4: Default Instance, Default Port (1433), Using Computer Account *

  • MSSQLSvc/SQLServerName Domain\ServerName$
  • MSSQLSvc/SQLServerName.FQDN Domain\ServerName$

Example #5: Default Instance, Non-Default Port, Using Computer Account *
Example #6: Named Instance, Static Non-Default Port, Using Computer Account *

  • MSSQLSvc/SQLServerName:Port Domain\ServerName$
  • MSSQLSvc/SQLServerName.FQDN:Port Domain\ServerName$

Example #7: Named Instance, Dynamic Non-Default Port, Using Domain Service Account **

  • MSSQLSvc/SQLServerName:InstanceName Domain\SQLServerServiceAccount
  • MSSQLSvc/SQLServerName.FQDN:InstanceName Domain\SQLServerServiceAccount

Example #8: Named Instance, Dynamic Non-Default Port, Using Computer Account *

  • MSSQLSvc/SQLServerName:InstanceName Domain\ServerName$
  • MSSQLSvc/SQLServerName.FQDN:InstanceName Domain\ServerName$

* An instance is consider using computer account when it is running under built in accounts, such as Network Services.
** Generally we do not recommend dynamic port as managing SPN manually can be difficult.  Also for us to use InstanceName, the SQL Server Browser service must be running.

So after reading this you are going that’s TOO MUCH TO REMEMBER!

I agree, that is why SQL Server can create and destroy this SPNs by itself without user interaction. However in order to do this, we must grant SQL Server Service Account or Computer Account permissions to Read/Write SPN Property on itself (Reference).

Now there is one scenario where we do not want to grant this permission and mange the SPN manually.  Please reference KB2443457. Copied the issue from KB for completeness.

  1. The Sqlcluster instance is active on Node A and registered the SQL SPN in domain controller A during start up..
  2. The Sqlcluster instance fails over to Node B when Node A is shutdown normally.
  3. The Sqlcluster instance deregistered its SPN from domain controller A during the shutdown process on Node A.
  4. The SPN is removed from domain controller A but the change has not yet been replicated to domain controller B.
  5. When starting up on Node B, the Sqlcluster instance tries to register the SQL SPN with domain controller B. Since, the SPN still exists Node B does not register the SPN.
  6. After some time, domain controller A replicates the deletion of the SPN (from step 3) to domain controller B as part of Active Directory replication. The end result is that no valid SPN exists for the SQL instance in the domain and hence you see connection issues to the Sqlcluster instance.

Blog post cross posted on …

  • SQLCAN (WordPress), Link.
  • SQLCAN (MSDN), Link.

Login failed. The login is from an untrusted domain…

June 4, 2012 5 comments

TITLE: Connect to Server
Cannot connect to SQL2008R2.

Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (Microsoft SQL Server, Error: 18452)

For help, click:

This error is not directly related to SQL Server, but since I had to troubleshoot it because I got the wonderful error while teaching a Workshop (Urg!!).  And surprise, I have never seen this error before so I am running a bit lost as to what the hell does it mean?

Quickly open up search engine started snooping around, with no luck.  I get articles with my computer is not trusted on domain, can’t see domain controller, DNS Issue, SPNs are not registered, login is invalid, password as expired, whole host of different types of issues.  So, I started tackle it one issue at time.

DNS Issue

I pinged the server by IP; issues.  I pinged the client computer by IP from server. No issues.  I tried doing Reverse Lookup using “Ping IP -a”; both location couldn’t find it.  AHA! My DNS Reverse look is not configured (note I had no idea how that can be the issue).  At last I go on DNS Server (Yeap, I had access to DNS Server, it was my lab not production (phew!)), setup a Reverse Look using the wizard and hoped for best…


Still the stupid “PING IP -A” did not work, I figured I got DNS configured wrong.  Kept at it for at least an hours to figure out how to configure DNS, finally gave up (concluding to myself how can that cause trust issue, more I learned configuring DNS Server is not easy as I thought, Do’h!).

Can’t See Domain Controller & My Computer is not Trusted on Domain

Maybe from my client I can’t see the domain controller; I was like well how can that be?  Okay maybe the computer is using cached credentials.  I logged in using Local Administrator, deleted my domain account profile.  Relogged, forcing to authenticate to Domain Controller, No Issues.  So what gives? Something must have screwed up in AD and my computer doesn’t have proper permission in AD.  Removed my computer from AD, readded it, STILL NO GO.  By now I am about to destroy my entire LAB and rebuild it from ground up… but I continue.

SPNs are not registered

It occured to me now (just now, kind of feel stupid, but..), well if I am getting failed login error there must be errors logged on SQL Server and sure enough ERRORLOG has some interesting errors.

Error 17806, Severity: 20, State: 14
SSPI handshake failed with error code 0x80090311, state 14 while establishing a connection with integrated security; the connection has been closed.  Reason: AcceptSecurityContext failed.  The Windows error code indicates the cause of failure.

Uhmm, Nope. I don’t know what error message means.  But usually when I see SSPI, I think Kerberos.  When I see Kerberos related issues I just want to turn around and run the other way, they are not fun :(.  But as last as SQL Server DBA we can’t avoid them, so started digging into this now.   First thing I want to check with it comes to Kerberos is do I have SPNs created for the SQL Server?

We can do that using …

  • SETSPN -L ComputerName
  • SETSPN -L Domain\ServiceAccount

Executed both commands on AD Server, came back blank.  AHA! Stupid SPNs, created both SPNs.  The SQL Server was running under Local Computer account, so i couldn’t create SPN for that.  But I created them for SQL Server it self.  Went back to my client computer, still no go :(.  Now I am about to give up, instead I decided last ditch effort…

Login Account Issues

Maybe my windows account password expired, maybe it was locked out, maybe something happen on SQL Server?  After a little bit of snooping around, found out none of those are my issues.

Now, I am tired, been up for almost 18 hours, I give up and went to sleep.

Next day coming in, digging into issue again. Because I am having issue from all my client computers now, which work working at first (Son-of-a….).  So I can’t think of something, it MUST be I didn’t create SPN for the Service Account. I decided to change the Service Account for the SQL Server to a domain account.  Every time I try to set it, it says “password invalid”.  I changed password, I reset, I created new account, ALL FAILED~!

I thought maybe the SQL Server Configuration Manager was not working, I decided to verify the login using command line runas command.

runas /username:domain\username /noprofile cmd

I entered my password and I got another error, ohh it was something different.

1787: The security database on the server does not have a computer account for this workstation trust relationship.

The who and what now? I’ll admit I am completely in uncharted waters here.  I am the first person to admit, AD and I don’t get along.  We parted ways long time ago, this is why I do.  SQL I am good at it, thats it.  AD, I need you but stay away from me *angery face holding up a fist*.

Error 1787: The Security Database

I have no idea what database it is talking about?  I did some searching found [2].  It suggested some using ADSIEDIT.msc to look for the TrustType property for my computer.  Looked around couldn’t find it the list, I didn’t want to add it (my whole stay away from AD theme).   I didn’t do anything; but while in ADSIEDIT.msc I decided to snoop around.  I notice my SQL Server Computer computer and the Client computer properties were not the same (they are all Windows 2008 R2 Servers; so what is going on?).

Then I remember while adding my client computers to domain somehow my SQL Computer account got deleted from AD.  So I had manually added it back in (BIG MISTAKE), could it be that when the accounts are added using the Windows Join Domain there are some special permissions granted (I know Duh, of course they are; but its the whole AD and me not getting along again). Quickly on to next test.

Adding SQL Server Computer back to Domain

  1. Went to AD, deleted SQL Server Computer from AD.
  2. Went to SQL Server …
  3. Right click computer, properties.
  4. Change Settings under computer name.
  5. Change.
  6. Change to Workgroup, type “WS” in workgroup name. Click OK.
  7. Restarted computer.
  8. Logged in using Local Administrator account.
  9. Readded SQL Server to domain.
  10. Restarted Again.

Crossed my fingers hope to die (just kidding), but was really was my last-last-last effort.  And W00000h0000000000! It worked! I can access SQL Server from ALL CLIENT computers now.  So the whole error message from untrusted domain was VERY misleading.  The error might have been the client, but the really issue was the SQL Server was no longer trusted on the domain.  Therefore when clients try to authenticate to SQL Server, the Kerberos broke down as it could not hop over to AD to verify credentials.  So the error was caused by my own hands, like all other errors, heh, I need to stop messing around.  But then I wouldn’t learn new and cool stuff.

Reference Links

  1. Microsoft.  Source: MSSQLServer ID: 18452. Link.
  2. TechNet. The security database on the server does not have a computer account for this workstation trust relationship. Link.
%d bloggers like this: