Michel Barneveld
To unpathed waters, undreamed shores
Login failed. (Microsoft SQL Server, Error: 18452)

 

Today I created a new virtual machine to do some SharePoint 2010 testing. First installed Windows 2008 R2, then added and configured the DNS and Active Directory roles. Installed Sql Server 2008 R2. Started the install of SharePoint 2010. Untill this point everything went very smooth. But during the installation you have to give the database name where the SharePoint databases will be created. And since I didn't like the hostname, I wanted a dns entry for it with a better name. Should be simple, right?

Started the dns server management console and added the entry sql.mb.local pointing to my fixed ip. Used the new name in the SharePoint installation screen and got login failures.
Hmm, what is going on here? Is Sql service running? Did I used the wrong ip? Those all checked out fine. So I started the SQL Server Management Studio. I could connect using the hostname and ip addresses but not through the dns name. If I did that I got a nice little box saying: "Login Failed. The login is from an untrusted domain and cannot be used with Windows authentication. (Microsoft SQL Server, Error: 18452)"

 

Searched for the errors on internet and found some blog postings about enabling Mixed Mode authentication, instead of Windows only authentication. But I was sure it must also work with just Windows authentication. Next step was to look in the eventviewer and there was an event 17806 in the Application Log saying "SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed." and in the Security Log there was an event 4625 saying "An Error occured during Logon".

I also tried it with a CNAME record as alias for the host name and that worked perfectly. I also checked if there were SPN's registered to the sql service account. None were registered. Also checked for MSSQLSvc spn in the domain, also none found. But by this time I was convinced it had to be some kind of Kerberos issue or NTLM reflection protection. But in both cases I am still puzzled why using a HOST A record doesn't work, while the hostname or CNAME does work. But I tried the standard fixes for both problems and they both work (Technically both solutions work for the NTLM reflection protection problem):

 

Solution 1 - Register a SPN for the SQL Service

Execute the following command to register a SPN

SetSpn -A MSSQLSvc/sql.mb.local:1433 MB\sql_service

(Where MSSQLSvc is the service class for SQL Server,  sql.mb.local is my dns entry, 1433 is the port my sql instance is listening on and MB\sql_service is the account sql server is running as)

 

Solution 2 - Disable NTLM reflection protection

Create a new DWORD with the name DisableLoopbackCheck and value 1 in the registry key HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa and restart the computer.

This will disable NTLM reflection protection on the whole machine, which opens it for Man In The Middle attacks, but on my test machine this is ok ;-)
For production machine reference these KB articles for other ways to disable the protection: Users experience authentication issues... and MS08-068: Vulnerability in SMB could allow remote code execution

 


Posted 11-11-2009 8:33 PM by Michel Barneveld
Filed under: , ,

Comments

Phil wrote re: Login failed. (Microsoft SQL Server, Error: 18452)
on 08-02-2011 4:02 PM

Option 1 works great!  Required a reboot of the server also.

Pawel wrote re: Login failed. (Microsoft SQL Server, Error: 18452)
on 12-29-2011 1:56 PM

Great thanx. Best regards from Poland, Mr.BadWolf

Lubos wrote re: Login failed. (Microsoft SQL Server, Error: 18452)
on 01-11-2012 9:31 AM

I think for the Option 1, it should be enough to execute GPUPDATE /FORCE, reboot is not needed.

JeffD wrote re: Login failed. (Microsoft SQL Server, Error: 18452)
on 07-21-2012 5:48 PM

Excellent work. Many thanks.

Brian wrote re: Login failed. (Microsoft SQL Server, Error: 18452)
on 04-16-2013 8:49 PM

Where are these commands on what server?

Simran Sachdeva wrote re: Login failed. (Microsoft SQL Server, Error: 18452)
on 06-21-2013 4:41 PM

This is the first time, i am adding a comment to any post. This worked really well. However, i had to use both the solutions. Michel..You rock!!!!

Jonathan Barahona(Beginner) wrote re: Login failed. (Microsoft SQL Server, Error: 18452)
on 11-26-2013 1:56 AM

Any example for Solution 1:

SetSpn -A MSSQLSvc/sql.mb.local:1433 MB\sql_service

how can I find my sql account or dns entry

Add a Comment

(required)  
(optional)
(required)  
Remember Me?

  Enter code:
Copyright © 2003 - 2009 Michel Barneveld
Powered by Community Server (Non-Commercial Edition), by Telligent Systems