Understanding Kerberos and NTLM authentication in SQL Server Connections

re: Understanding Kerberos and NTLM authentication in SQL Server Connections

DocCritic — Tue, 12 Dec 2006 00:45:06 GMT

This is not a blog post.

This is documentation.

"Cannot Generate SSPI Context" error message, Poisoned DNS

SQL Protocols — Tue, 02 Jan 2007 11:18:16 GMT

Incorrect DNS can lead to various network connectivity issues. In this post, I explain how it affects

Also checkout KB925001

Joel Mansford — Wed, 03 Jan 2007 14:49:01 GMT

I followed these instructions and couldn't work out why it worked then stopped working later. I eventually discovered that it was due to a bug in SQL Server 2005 RTM + SS2005 SP1.

The workaround of using Named Pipes for the connection between the linked servers works a treat and is much less hassle.

http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx

re: Understanding Kerberos and NTLM authentication in SQL Server Connections

ASE — Tue, 06 Feb 2007 02:59:43 GMT

IF YOU WERE LOOKING FOR ONE HERE YOU GO..........The Axis StorPoint CD+ is a cd/dvd network storage server. This device is ideal for multiple users with workstations using different operating systems. It's a match made in heaven for IT departments, manufacturing, education and medical professionals.

http://cm.ebay.com/cm/ck/1065-29296-2357-0?uid=121939807&site=0&ver=LCA080805&item=250080916580&lk=URL

re: Understanding Kerberos and NTLM authentication in SQL Server Connections

Andrew Fitch — Sat, 24 Mar 2007 00:16:02 GMT

I'm running into an issue where a Windows Service that I wrote and runs as Local System tries to query my SQL 2005 DB on boot using Windows Authentication(SQL Express is the only dependency I have set) and I get a login failed error and it doesn't do what it is supposed to, but if I manually stop and start the service once the computer is booted it works fine, what other services are dependent in order to make this authenticate right away?

re: Understanding Kerberos and NTLM authentication in SQL Server Connections

John Gordon — Mon, 26 Mar 2007 21:47:17 GMT

You will definitely need Lsass.exe to be running before Windows authentication (remote or otherwise) can be used. I am unsure if you will need IIS to be running or not.

Hope this helps,

John

re: Understanding Kerberos and NTLM authentication in SQL Server Connections

Sergey Krutous — Tue, 26 Jun 2007 09:14:26 GMT

Hi,

Can the following configuration work:

ASP.NET application works on domain machine, the application pool is running under domain account.Database is running on workgroup machine. I've configured local account with the same name and password (as domain account used by ASP.NET application).

Still I get "Login failed for user '<account_name>'." message.

Can this configuration work? I understand that I need NTLM since the database is not in domain. What additional configuration is required for NTLM protocol? If it can not work at all can you shortly explain what step in NTLM authentication does not allow this?

regards,

Sergey.

re: Understanding Kerberos and NTLM authentication in SQL Server Connections

Christian vik — Mon, 26 Nov 2007 16:39:38 GMT

Hi,

I have domain users who try to access a website from XP client computers that are not a member of the domain.

Internet Explorer -> IIS6 -> SQL Server 2005

Is it possible to log in when double hop Kerberos is used?

Currently it only works inside the domain. Outside it switches to NTLM and the user gets the "Login Failed for user 'NT Authority\ANONYMOUS' LOGON" error message.

Is it possible to use NTLM and Named Pipes and use impersonated users all the way to the database?

re: Understanding Kerberos and NTLM authentication in SQL Server Connections

Christian Vik — Mon, 26 Nov 2007 16:41:02 GMT

Just want to add that all users, inside and outside of the domain, uses the same hostname to access the site and that SPN has been set up.

re: Understanding Kerberos and NTLM authentication in SQL Server Connections

Ray — Mon, 11 Feb 2008 21:51:22 GMT

Can someone shed some light on the following error:

NT Status: STATUS_LOGON_FAILURE (0xc000006d)

Is this in anyway way related to a failed Kerberose authentication failure?

We are seeing this from the client machine. What would be the cause for this typically?

We have been unable to resolve this error and have been unable to locate possible causes for this online.

Thanks,

re: Understanding Kerberos and NTLM authentication in SQL Server Connections

Robert — Wed, 15 Oct 2008 05:03:34 GMT

We had a similar issue and we worked with Microsoft and they determined that our DBA installed SQL Server not under the default SA account but under a different user. We had to make a custom SPN because the one that SQL installed didn't work.

http://www.teamofcoders.com

Troubleshooting Error: 18452 Login failed for user

Troubleshooting Microsoft SQL Server — Wed, 03 Dec 2008 05:29:19 GMT

Error: 18452 Login failed for user ‘null‘ , … • “Null” or ‘’ means that client windows token is not trusted

re: Understanding Kerberos and NTLM authentication in SQL Server Connections

Sam — Mon, 23 Feb 2009 01:48:38 GMT

Hi,

I have a stand-alone(not connected to any domain) 64 bit machine with Windows Server 2008 Enterprise SP1.

On this machine I have SQL Server 2008 enterprise edition version 10.0.1600.22 64-bit default instance. It is running under 'LocalSystem' account. The authetication is 'mixed' mode.

when the SQL server service starts, it gives the following message, which is fine.

"The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies."

Then when I try to login through SQL server management studio (from the same machine) using windows authentication, I get the following error:

"Login failed for user 'mymachinename\Administrator'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <named pipe>]"

I have only 'named pipe' enabled as a protocol. I have used "net view \\server" command to verify that NTLM is working fine.

What could be the problem here? is NTLM fallback not working?

Any help or pointers will be greatly appreciated.

Thanks

re: Understanding Kerberos and NTLM authentication in SQL Server Connections

SQL Protocols — Thu, 26 Feb 2009 01:56:05 GMT

Sam,

NTLM is used here. The error message about SPN is normal. The errorlog should contain more details about the login failure, then you can refer this blog to find out more.

http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx

Thanks,

Xinwei

re: Understanding Kerberos and NTLM authentication in SQL Server Connections

Uri — Sun, 22 Mar 2009 16:41:58 GMT

Hi,

I'm new to SQL Server and NTLM.

The IT installed a device that capture all the trafic to the SQL Server.

They told me to change the SQL Server Agent Job to use this device (from security reasons).

When I changed the SQL Server Agent Job (using the alias property) it failed with the error: "Login failed for user ''. The user is not ...

Even though that the SQL Server Agent Job and the SQL Server are on the same machine (I just route the network to use the new device).

Thanks,

Uri

SQL Server Security Links

Carpe Datum — Thu, 26 Mar 2009 17:43:16 GMT

I was asked yesterday about sharing my security links for SQL Server, so I thought I would post those

re: Understanding Kerberos and NTLM authentication in SQL Server Connections

Bakki — Tue, 21 Jul 2009 10:03:47 GMT

I am new to MS SQL and NTLM protocol. I want to know which version of the SQL server jdbc driver file supports NTLM authentication and what is the syntax for using it.

Thanks for your help.

re: Understanding Kerberos and NTLM authentication in SQL Server Connections

Sumo — Tue, 21 Jul 2009 10:31:22 GMT

http://aspdotnetexpert.blogspot.com/2008/06/login-failed-for-user-sa-reason-not.html

re: Understanding Kerberos and NTLM authentication in SQL Server Connections

tangrl — Fri, 31 Jul 2009 03:42:39 GMT

I try to setup Kerberos delegation from a Web Server (IIS 6.0) to a SQL Server (2005). I had success before but this time is different: the Windows users and the Servers are in different domains! I don't know if it can still be done. Here is the situation:

1. All servers are in Domain1. So Web server is Domain1\Machine1 and the SQL Server is Domain1\Machine2.

2. All Users are in Domain2.

3. Domain2 is one-way trusted to Domain1. So Users in Domain2 can see Domain1, not the other way around.

4. The ASP.Net web site is running under account Domain2\UserA.

5. The SQL Server is running user account Domain2\UserB.

6. All application users are from Domain2.

Can I run the following command to setup a SPN?

setspn -A MSSQLSvc/Machine2.xyz.com domain2\UserB

Can I run the command on the SQL Server?

In which domain the SPN will be created?

And the big question: Can the application Users from Domain2 be delegated all the way to the SQL Server?

Thanks in advance!

Richard.

re: Understanding Kerberos and NTLM authentication in SQL Server Connections

Rogerio Torres — Wed, 12 Aug 2009 00:41:26 GMT

I tried realize that in SQl Server 2000 down AD with function level Windowns 2000, and I don't get success result.

re: Understanding Kerberos and NTLM authentication in SQL Server Connections

Ryan — Wed, 09 Dec 2009 18:03:16 GMT

What do you mean by "NP Connection"? It's mentioned a few times in the article, but it never defined.