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)
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.
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 . 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 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.