SQL login failed because due to untrusted domain

user_1038554 0
Viewed 2013
  • I have a VM that houses both the SQL server and the IIS server.
  • My IIS server has an application pool that run a .NET web application.
  • This application pool identity is domain\account1
  • My application web.config connection string has "Trusted_Connection=True" and Data Source=NAME\INSTANCE01
  • My SQL server has "SQL Server and Window Authetnication mode" enabled. The account domain\account1 is added to the database security login.
  • I can RDP into this VM using domain\account1 and I can connect to the Database via SMS using Window Authentication and connecting to NAME\INSTANCE01
  • I verify that only have one domain on our AD and DC.

However, when I tried to log in my web application, the .NET error trace shows it fails at the login. Upon looking at the Database's log I see the error below. The error seems to indicate the Identity user (domain\account1) I'm using in my .NET application cannot log into the database due to this error.

enter image description here

This has been baffling because I researched around and found this error only occur if you connect from an outside domain but I don't know why it shows this in my case.

I tried and look into my AD but it looks like it is up because if it isn't I would not have been able to RDP into the VM using domain\account1 in the first place.

Any guidance that help me narrow down the cause are much welcomed.

active-directory sql-server .net
1 Answers

Based on our conversation, one common cause that might apply is that the SQL Server does not have a service principal name registered. This is something it needs in order for kerberos to work.

To do this, you use the setspn utility.

There's a lot in the document, here are the basics. A service principal name applies to some particular service (eg, sql server), on some particular machine and port, to some particular domain account. Let's pretend your server machine name is MyServer, and the account running SQL Server instance is MyDomain\SQLAccount, and it's a default instance (running on port 1433. If it's a named instance, find the port via sql configuration manager or by looking at the start of the sql log and subtitute it below)

To see a list of SPN's assigned to the account, you would use

setspn -l MyDomain\SQLAccount

To register a SPN for the SQL Server service, it's best to register for the netbios name and the fully qualified name:

setspn -a MSSQLSvc/MyServer:1433 MyDomain\SQLAccount
setspn -a MSSQLSvc/MyServer.mydomain.com:1433 MyDomain\SQLAccount

Now, if you have several domain controllers, the SPN may take a while to replicate around the domain. So you have to wait "long enough" to see this take effect. 15 minutes is common, 30 is definitely safe. So after running this, wait a while, then make a new connection to SQL server (using, eg, SSMS and windows auth) and select auth_scheme from sys.dm_exec_connections where session_id = @@spid again. If you see "kerberos", it worked. You can also run klist purge from command line to purge your tickets, but that's not usually necessary, and it won't help if the ticket hasn't replicated yet.

This isn't the only possible reason why you might get SSPI problems - see that MS blog for more. But it is a really common one.

EDIT:

Kerberos problems usually only happen when you have a "double hop" of credentials. For example, from a user's machine, to a web server, then from the web server to a SQL Server. That doesn't apply in your specific case. But I would still try this SPN setup anyway, because it's just such a common source of SSPI errors.

user_71652790
Related