[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6950
  • Last Modified:

SQL Server 2008: Login Failed, Untrusted domain for a domain account that worked previously

We have a SQL Server 2008 database installed on a Windows Server 2008 server.  This server was recently migrated to from a Windows Server 2003 configuration last month.  After the migration, users have intermittently been having untrusted domain login failures, though all users workstations are connected to the domain.  We are using Windows authentication solely for SQL Server access and all users are added as valid domain users in the database.  Users connect to the database via an ODBC connection.  Here is the error that is occurring:
 
SQLSTATE:28000
SQL Sever ERROR: 18452
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication

Especially tricky is one user who had been able to connect with no issues for 2 weeks and now she cannot connect to SQL Server at all (one week and counting), receiving the above untrusted domain error.  I had the user reboot their machine and relogin, change their domain user password and repeatedly try to log into the database with no success. Nothing has changed with her account or the SQL Server backend.

Other users have had similar problems but they either go away by themselves once they relogin to the domain or after their Windows 2008 domain accounts have been recreated and readded to SQL Server.  I would like to determine what could be causing these intermittent untrusted domain problems since this is very disruptive for our users and I receive calls regarding these errors from different users every day.  Has anyone encountered this type of issue and does anyone have any advice, suggestions or troubleshooting tips that can be used to resolve this issue?
0
dvrobins
Asked:
dvrobins
  • 14
  • 7
  • 4
2 Solutions
 
mass2612Commented:
Hi,

Did anything change for the user who now cannot connect at all? I have seen similar issues with kerberos and this blog helped me at the time. However I'm not convinced in your case. Are all the ODBC connections from the workstations definitely configured exactly the same?

Login failed. (Microsoft SQL Server, Error: 18452)
http://blog.michelbarneveld.nl/michel/archive/2009/11/11/login-failed-microsoft-sql-server-error-18452.aspx
0
 
dvrobinsAuthor Commented:
I've verified each time that each user's ODBC connection to the database is configured exactly the same.  The thing that bothers me the most with this problem is that it is intermittent and sometimes resolves itself without changing anything other than a restart of the user's workstation.
0
 
Anthony PerkinsCommented:
>>I had the user reboot their machine and relogin, change their domain user password and repeatedly try to log into the database with no success. Nothing has changed with her account or the SQL Server backend.<<
Did you (or someone else with appropriate access) try and log on from their machine?

What network protocol are they using? TCP/IP, Named Pipes?

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
dvrobinsAuthor Commented:
I have not tried to log on to the database from her workstation and her workstation is powered off at this time so I cannot access it, but I'll make sure to try it tomorrow morning when she gets in.

We are using TCP/IP protocol.
0
 
Anthony PerkinsCommented:
>>We are using TCP/IP protocol. <<
Right.  But have you verified that workstation is also using TCP/IP to connect to SQL Server.  Here is a simple query to verify that:
select * from sys.dm_exec_connections where session_id=@@spid

0
 
dvrobinsAuthor Commented:
The affected user still cannot access the database due to the untrusted domain error, so I cannot see how the user is connecting in since they are not connected.  I will attempt to login to the user's workstation as another user that currently can connect (but they normally connect using a different workstation) to see if the same problem arises.  
0
 
dvrobinsAuthor Commented:
I was able to login as another domain user on the affected user's workstation.  This user could access SQL Server while on the workstation, so the problem lies in the affected user's domain account.  Could it be related to how the user's active directory account credentials are validated through kerberos?
0
 
dvrobinsAuthor Commented:
Another thing that was verified:  the workstation is using TCPIP to connect.
0
 
Anthony PerkinsCommented:
>>Could it be related to how the user's active directory account credentials are validated through kerberos? <<
Perhaps.

>>Another thing that was verified:  the workstation is using TCPIP to connect. <<
How did you verify that?  If you tell me you looked at the connection, then I am afraid that does not cut it.  You need to use the following query I posted earlier:
SELECT  * FROM    sys.dm_exec_connections WHERE   session_id = @@spid

0
 
dvrobinsAuthor Commented:
I verified the tcp connection through the dm_exec_connections query specified above when the user was logged in.
0
 
Anthony PerkinsCommented:
I am out of my depth, I have no idea what else to suggest.
0
 
mass2612Commented:
Is SQL and Windows authentication definitely enabled for this SQL instance?
0
 
dvrobinsAuthor Commented:
Only windows authentication is enabled.  We are not using SQL Server authentication.
0
 
mass2612Commented:
How is you AD setup? Do you have multiple DC's and AD sites? Are all your domain controllers definately online and working.
0
 
mass2612Commented:
Is each users domain account added individually to the database or are you using groups? If they are individually mapped can you remove the users permissions from the databases (so you don't end up with an orphaned user) and then drop the users domain account from SQL then re-add the domain account and provide access to the databases.

If you are using groups maybe you can use these options to verify the users account is in fact getting access to the DB.

How does that AD user account get access to the database?
http://sqlblog.com/blogs/linchi_shea/archive/2009/03/26/how-does-that-ad-user-account-get-access-to-the-database.aspx
0
 
dvrobinsAuthor Commented:
Each user is individually mapped to the db. I removed the user from the db and SQL Server when the problem first started and reconnected the account and restored the user's db access, which provided no change for the user.

All users of the system connect to the database through a front end application that connects via an ODBC system dsn connection from their workstation.   The dsn is setup to use the user's AD credentials for authentication to the db.

I am working with our SA to find out how AD is setup. Our
0
 
dvrobinsAuthor Commented:
environment was upgraded to Windows Server 2008 last month and all problems started after the upgrade.  
0
 
mass2612Commented:
Was the old SQL instance (you mention migrating) and the current SQL instance both always only using Windows authentication mode? Is it possible the users with intermittent problems had SQL logins with the same username in the past that they are now using in AD? (Clutching at straws at this point, sorry).
0
 
mass2612Commented:
Are all the old Windows 2003 DC's removed from the domain properly or are they on, partially disabled etc?
0
 
dvrobinsAuthor Commented:
The old instance was Windows authentication only also.  There have never been SQL logins enabled in the system, so there would be no lingering accounts.  

Any help or suggestions are welcome and much appreciated. I've been scratching my head over this issue for the past 3 weeks and have been clutching at straws myself.  The fact that users can login to SS with no issues then suddenly be rejected by SS due to AD crendentials that still allows them access to other services on the network (access to their AD managed Outlook email works, mapped drive access is granted, etc, everything BUT sql server) is baffling.  
0
 
dvrobinsAuthor Commented:
I don't know if the 2003 DC (assuming you domain controller) was removed. I'll follow up with our SA.  

During the system upgrade, the server was actually replaced with a new more powerful server that had Windows Server 2008 already installed and data was migrated over to the new server from the old 2003 server.
0
 
mass2612Commented:
Might be worth having your SA run dcdiag against your domain controllers and see if there are any problems.

http://technet.microsoft.com/en-us/library/cc731968(WS.10).aspx
0
 
dvrobinsAuthor Commented:
We were able to find out the cause of this problem.  The user had a stored managed password that was not in sync with their current domain password.  The managed password was removed from the user's workstation and they were able to connect with no problem.  Thanks everyone for all of your troubleshooting advice.
0
 
dvrobinsAuthor Commented:
Even though neither expert provided the final solution, I would like to award partial points to each of them for their troubleshooting ideas, since it helped narrow the scope of where to troubleshoot.
0
 
dvrobinsAuthor Commented:
Awarded partial points to each expert for their troubleshooting advice.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 14
  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now