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
SQL Canada (SQLCAN) 
Hi Mohit. Thanks for the detailed breakdown. This article was very helpful in troubleshooting the same error as you describe. However, in my case it turned out that my database server, in fact did have trouble communicating with the DC as a result of a duplicable SID (I have been using a cloned R2 base image using VMWare to create my lab environment). I ended up running sysprep on the database server (c:\Windows\System32\sysprep\sysprep.exe) with the “Generalize option”, which assigned a new SID and removed the machine from the domain. After it rebooted, I added it back to the domain and viola… I was able to authenticate using Windows Authentication. I have not run into any problems using cloned VMs in the past. Only with SQL Server. However, it does makes sense to run sysprep utility before adding any cloned VM to the domain. I hope this helps someone else in my shoes.
Great blog! Keep up the good work!
Milan Zdimal
Heh I hear yaa, I learned same painful lesson myself just yesterday. Heh. I been spoiled until now, always had someone else do VM steps for me. I am finally playing with Hyper-V for first time and forgot to sysprep. Now I got a single image that has been sysprep’d and shutdown. Then I exported it and use it as template for all future virtual machines. Working great…
Thanks for sharing that with me, I’ll remember that. AD is evil, but as my AD PFE buddy says, AD is easy. And after setting up my first Domain Controller since 1999, it really was easy. Domain Controller, DNS, iSCSI Target/Initiator setup, Windows Failovercluster. Yeeh I am on a roll, I can apply for system administrator job too now (j/king ;-), I’ll stick with SQL hehe).
Useful information. Fortunate me I discovered your
site unintentionally, and I am surprised why this accident didn’t came about in advance! I bookmarked it.
Thank you very much!!! Your solution worked for me also!!! Though I am still having some minor issues and working on them… my domain is srv2003 and my sql server srv2008. I have 2 vista clients and 2 win7 clients that sometimes get the sspi error (sspi cannot generate context) and if i try to add an AD login on sql’s security – logins. i can see that there is a directory “domainname” but its empty…… /scratch.
Glad it helped you Nick. This post only talked one one of the issues that can cause SSPI error. There are multiple other issues that can cause it. Check out the Security Event Viewer Log, to see if any failed audits are logged. That can help you potentially track down what other issues can happen. Other thing is you can look at doing Kerberos troubleshooting by using kerbtray (http://www.microsoft.com/en-us/download/details.aspx?id=17657).