Archive
Service Principal Names for SQL Server
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.
- The Sqlcluster instance is active on Node A and registered the SQL SPN in domain controller A during start up..
- The Sqlcluster instance fails over to Node B when Node A is shutdown normally.
- The Sqlcluster instance deregistered its SPN from domain controller A during the shutdown process on Node A.
- The SPN is removed from domain controller A but the change has not yet been replicated to domain controller B.
- 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.
- 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 …
Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’ with SQL Server Reporting Services
Before I talk about this error message, I want to apologize to followers and students I had promised this post a while back. Sorry for delay, I will try to be more prompt on my future posts.
Anyhow, so how many people have seen that exact message? “Login failed … ‘NT AUTHORITY\ANONYMOUS LOGON'”? What does this message even mean? And why do I have to deal with it?
In my experience I have seen this message in three or four difference cases. I think in all cases the issue is similar, so I hope this post helps your troubleshoot and fix this issue within your environment.
Problem Statement
When running reports on SQL Server Reporting Services (SSRS) for some reason I keep getting login failed with ‘NT AUTHORITY\ANONYMOUS LOGON’. I am not sure what is going on, the connection string for report is set to “Windows Integrated Security” like in image below. I have access to the server, I confirmed my login “Domain\username” has full rights to the SQL Instance and database, hell I am “sysadmin”; there should be no reason to get this error message.
So what is going on?
Well one can say it is an issue with your connection string? That is you have it set to “Windows integrated security” that is why you are getting this. So people often either a) hard code SQL Server login credentials or b) a service account credentials within the connection string. All-in-all, I don’t have problem with that however it makes it hard to control what an individual can execute or what they have permission to. Windows integrated security is a good approach.
That said what is the issue then?
Problem is simple, it is an issue with delegation. Windows 2008+ defaults to Kerberos authentication model. In which when you log into SSRS, it uses your credentials to authenticate you. However when you run the report, for SSRS to authenticate you to the SQL Server instance in question, we must have proper delegation setup with in active directory. For that we have to do following steps to resolve this problem…
Check to see if you have SSRS configured correctly
- RDP to SSRS server.
- Go to Reporting Services Configuration Manager.
- Go to Service Account.
- Confirm that you have Service Account set to a Domain Account and NOT Network Services (Default).
- Check for Service Principal Name using following command:”SETSPN -L Domain\ServiceAccountName” (this is your SSRS service account).
- You should have one SPN “http/servername.FQDN”. If you are using non-default port (other then 80); than it should be “http/servername.FQDN:PORT”.
- If you are missing the SPN please add it using the following command:”SETSPN -A http/servername.FQDN:Port Domain\ServiceAccountName”.
- Now go to your Active Directory administrator and ask them to give the SSRS service account we created SPN for in step #7, Delegation rights under Delegation Tab, “Trust this user for delegation to any service (Kerberos only)” as in screenshot below.
- Restart SSRS.
If you try to run your report it should work now :). If you guys have any other issues please let me know.
Few Notes…
- If you change service account in your SSRS, please back up the encryption key before changing.
- If you are using single service account for everything (not best practice), consider setting “Trust this user for delegation to specific services only”.
Login failed. The login is from an untrusted domain…
TITLE: Connect to Server
——————————
Cannot connect to SQL2008R2.
——————————
ADDITIONAL INFORMATION:Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (Microsoft SQL Server, Error: 18452)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18452&LinkId=20476
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…
Opps!
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
- Went to AD, deleted SQL Server Computer from AD.
- Went to SQL Server …
- Right click computer, properties.
- Change Settings under computer name.
- Change.
- Change to Workgroup, type “WS” in workgroup name. Click OK.
- Restarted computer.
- Logged in using Local Administrator account.
- Readded SQL Server to domain.
- 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
