Topics from the Microsoft SQL Server Protocols team - Netlibs, TDS, SQL Browser, etc.
Understanding the error message: “Login failed for user ''. The user is not associated with a trusted SQL Server connection.”
This exact Login Failed error, with the empty string for the user name, has two unrelated classes of causes, one of which has already been blogged about here: http://blogs.msdn.com/sql_protocols/archive/2005/09/28/474698.aspx. In addition to an extra space in the connection string, the other class of causes for this error message is an inability to resolve the Windows account trying to connect to SQL Server. This list is not intended to be exhaustive, but here are several known root causes for this error message.
1) If this error message occurs every time in an application using Windows Authentication, and the client and the SQL Server instance are on separate machines, then ensure that the account which is being used to access SQL Server is a domain account. If the account being used is a local account on the client machine, then this error message will occur because the SQL Server machine and the Domain Controller cannot recognize a local account on a different machine. The next step for this is to create a domain account, give it the appropriate access rights to SQL Server, and then use that domain account to run the client application. Note that this case also includes the special accounts “NT AUTHORITY\LOCAL SERVICE” and “NT AUTHORITY\NETWORK SERVICE” trying to connect to a remote SQL Server, when authentication uses NTLM rather than Kerberos.
One very common case where this can occur is when creating web applications with SQL Server and IIS; often, the web page will work during development, then errors occur with this message after deploying the web site. This occurs because the developer’s account has access to SQL Server, but the account IIS runs as does not have access. To fix this specific problem, refer to this kb article about impersonating a domain user in ASP.NET: http://support.microsoft.com/kb/306158
2) Similar to above: this error message can appear if the user logging in is a domain account from a different, untrusted domain from the SQL Server’s domain. The next step for this is either to move the client machine into the same domain as the SQL Server and set it up to use a domain account, or to set up mutual trust between the domains. Setting up mutual trust is a complicated procedure and should be done with a great deal of care and due security considerations.
3) This error message can appear immediately after a password change for the user account attempting to login. This occurs because of caching of the client user’s credentials. The next step here is to log out the application user with the old password, and re-login with the new password before running the application.
4) If this error message only appears sporadically in an application using Windows Authentication, it may result because the SQL Server cannot contact the Domain Controller to validate the user. This may be caused by high network load stressing the hardware, or to a faulty piece of networking equipment. The next step here is to troubleshoot the network hardware between the SQL Server and the Domain Controller by taking network traces and replacing network hardware as necessary.
5) This error message can appear consistently for local connections using trusted authentication, when SQL Server’s SPN is not interpreted by SSPI as belonging to the local machine. This can be caused either by a misconfiguration of DNS, or by a machine having multiple names. If your machine has multiple names, try to work around the need for multiple names and give it a unique name. If the machine just has one name, then check your DNS configuration.
Dan BenediktsonSQL Server ProtocolsDisclaimer: This posting is provided "AS IS" with no warranties, and confers no rights
a wonderful explanation about the error message: “Login failed for user ''. The user is not associated
I just had trouble with this error message after rebooting a domain controller.
For SQL Server I get the following log entries:
SSPI handshake failed with error code 0x80090304 while establishing a connection with integrated security; the connection has been closed.
Login failed for user ''. The user is not associated with a trusted SQL Server connection.
But in Security Enventlog i also get the following error:
The kerberos subsystem encountered a PAC verification failure.
This indicates that the PAC from the client SVC-Prod-03 in realm LOCAL.NET had a PAC which failed to
verify or was modified. Contact your system administrator.
And I got these errors still 5 minutes after the DC was up and running again.
What is so special with authentication in SQL Server that I get this kerberos error?
can anyone point me to a definitive page that outlines exact steps to confirm a properly installed and configured 'sqloledb(.#)' client connectivity library exists on a computer?
thanks in advance.
FYI Sometimes its as simple as "SQL Authentication" wasn't enabled when the server was setup.
To fix it (and establish the sa password), go to the server properties in the Management Studio and enable SQL Authentication.
Thanks for this. But I do have an issue not listed. When I use the integrated security=true connection string my domain account works fine. However when I use my login and password in the connection string, I get "login failed for user ...". Any ideas?
If you use login and password in the connection string, the connection assumes SQL login and password, not Windows login and password. For example, if SQL authentication is enabled and there is a SQL user "user1" with password "pwd1", you can use "user id=user1;password=pwd1" in the connection string to connect. DO NOT use "integrated security=true" in this context. If you use "integrated security=true", DO NOT specify your Windows domain account credentials.
I have BizTalk attempting to communicate to SQL 2005 on the same server but getting this error when it uses a connection string set in the Enterprise Library. I can verify that this connection works correctly but when executed via biztalk it fails with this error.
@Stoyko: so... if specifying hte name and password will make it not use windows authentication... how do I make it use windows authentication *and* give it my windows name and password?
For me, adding the
NT AUTHORITY\NETWORK SERVICE user solved the problem
There must another case where we this message is issued as well.
I have a SQL Server 2005 that has Mixed Authentication enabled and remote connection enabled.
I have users both in my domain and connection from outside the domain that I have added as users to SQL.
In my client app that uses the Microsoft Enterprise Library and .NetTiers, I have a connection string that uses Integrated security and attempts a SQL operation. If that fails, I prompt the user, change the connection string to one that uses the userid/pwd and try again.
For a user inside the network, I can connect with both variations of the connection string (I forced SQL Auth for kicks once) and I can see that in the SQL Profiler. The exact connection string I'm trying is:
Data Source=10.52.xx.xx;Initial Catalog=SomeDB;Trusted_connection=false;Integrated Security=false;User Id=Usern;Password=pwd
For a user outside the network, they see the dialog, enter their User/Pwd combo and that connection fails with the error message mentioned. The Event viewer only shows the two entries for the failed NTLM attempt, SQL Profiler shows nothing. netstat shows a connection on port 1433 for that user. I've sniffed all the network packets during a login attempt, but did not see any connection string come through (I assume it's encrypted somehow).
I'm at a loss as to how to fix this.
For me, logging in locally was failing using Windows Auth. The hosts file had 2 names for the same box. After removing the unused name, the connection worked. Thanks for the help.
I got the same error: SSPI handshake error and login failed as mentioned above.
i have tried and read alot , but finally i modified the local security policy .
go to Network access: change from Guest to Classic,
that's it !
hope it can help !
Good listing of the probable causes of this error msg.
Hi, I have this problem:
I have a SQLServer 2008 installed. This is not an update from SQL2005.
If I try to connect to it using the same provider as I use with SQL 2005 (SQLOLEDB.1) It doesn't work.
I have to use this provider: SQLNCLI10.1
Using it, I can connect to SQL 2008 throw an UDL file.
But when I try to use that UDL file to connect to my VB6 app, this error message appears: "Login failed. The login is from an untrusted domain and cannot be used with Windows authentication."
In the other hand, I have 3 SQL2005 instance each one updated to SQL2008. I don't have any problem with them. I can use 'SQLOLEDB.1' provider and works fine.
I cannot install SQL2005 and update it to 2008 because it's on a server which I can't modify.
The SQL2008 instance has mixed mode. I can login from the SQL Server Manager Studio 2008, but not from VB6. Finally, I've made a DSN and VB6 can connect to that database throw this DSN but I need to connect by a ConnectionString since I don´t want to make a DSN in each end user machine.
Do you have any idea?
i m using this connection string.
Data Source=server;database=DB;integrated security=true;
sometime it is connecting and sometime it replies "Login failed for user"
any idea regarding this problem.