none
Login failed for user ''. The user is not associated with a trusted SQL Server connection. RRS feed

  • Question

  • We have a ASP.NET 1.1 app that uses Windows Authentication to access SQL Server 2005 backend.  When the app runs, the data layer cannot open a connection.  I get the following error:
     
    Login failed for user ''. The user is not associated with a trusted SQL Server connection.
     
    The weird thing is that in the VS.NET 2003 IDE's Server Explorer, I can connect to the database just fine.  Also, in the UI layer, we use Windows Authentication and the ASPX files correctly see my identity.  (It's almost as if my identity isn't being passed down from the UI to the data access layer - not sure.)
     
    Any ideas on why this isn't working for me?
     
    Thanks.
    Tuesday, September 18, 2007 7:49 PM

Answers

  • OK, I figured it out.  It was the machine.config file.  Apparently, she (or someone) modified it but never checked it into VSS.  I made sure no one reformatted the HD so I was able to get a copy from her PC.
     
    Thanks to everyone for their help!
    Friday, September 21, 2007 6:30 PM

All replies


  • Cause
    The SQL server has been configured to operate in "Windows Authentication Mode (Windows Authentication)" and doesn't allow the use of SQL accounts.

    solution
    Change the Authentication Mode of the SQL server from "Windows Authentication Mode (Windows Authentication)"
    to "Mixed Mode (Windows Authentication and SQL Server Authentication)".


    Thank u
    Baba

    Please remember to click "Mark as Answer" on this post if it helped you.
    Wednesday, September 19, 2007 3:15 AM
  • This is going to depend upon your configuration. The error typically occurs when your web server and SQL Server are on two different machines. Would this happen to be the case?

     

    Also, have you enabled impersonation for the web application?

     

    Wednesday, September 19, 2007 12:00 PM
  • This problem is from IIS you should Deny Anonymous users and Enable impersonation in the Web.Config and it will go away. Check the thread below.

     

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2079875&SiteID=1

     

    Wednesday, September 19, 2007 12:21 PM
  • First, thanks to everyone for their replies.  Unfortunately, I am still getting the error.  
     
    Just a little background.  The developer who wrote the application quit the company and there wasn't much time for transition.  She did check in all her source code before she left so I have the correct code.  I'm thinking that the problem is some sort of configuration issue in IIS or Windows.  I don't want to change the source code because if it worked on her PC, it should work on mine.  Also, I can't ask the DBA to make any changes to their SQL Server configuration.
     
    Now to answer everyone's questions:
    - The web server and SQL Server are on two different machines.
    - Impersonation is not enabled in the application.
    - Annonomous access in IIS has been disabled.
     
    What should I check next?
    Thursday, September 20, 2007 6:18 PM
  • What you are getting is called the Double Hop problem change SQL Server to mixed mode and add the Network Service account in SQL Server on the server level and database in Win2003 and in Win2k add the Asp.net account at the server and database level in SQL Server. I think you still need to enable Impersonation in IIS, Microsoft have covered it in details.  Hope this helps.


    http://support.microsoft.com/kb/316989/

     

    Thursday, September 20, 2007 6:38 PM
  • The problem is that the ASP.NET worker process your application is executing under is running under a local account, and this account will not exist on the SQL Server machine so credential delegation will fail. If impersonation is not enabled then this account would be ASPNET or NetworkService (Windows 2003).

     

    There are several ways to work around this issue. I would check the following documentation for more information:

     

     
     
    Thursday, September 20, 2007 7:01 PM
  • I tried aspnet_regiis.exe -ga machineName\userName but this does not work on WinXP.  Out of curiousity, I tried changing the web.config file to include <identity impersonate="true"/> and I can successfully open a connection.  But again, this application worked fine on another computer, so I shouldn't have to change the web.config for it to work on mine.  Is there a way to do this without changing the config or any code?
    Friday, September 21, 2007 4:13 PM
  • I am Asp.net MVP you are not changing the code rather adding needed omission by your developer in a double hop situation. And I don't recommend aspnet_regiis in such situations because that only works if IIS installation is wrong which also depends on operating systems. In Win2003 I will tell you to remove IIS and install manually again but in Win2k it is better to run aspnet_regiis. Hope this helps.

    Friday, September 21, 2007 4:36 PM
  • OK, I'm a little confused. Are you trying to get this to work from your development machine or from a dedicated web server?

     

    Once again, if impersonation is not enabled then your app is running under the default ASP.NET account, which if it's from XP then it's the local ASPNET account, however, you won't be able to authenticate via Windows Integrated Security with the SQL Server machine unless there is a mirrored local account with the same password on the SQL Server machine.

     

    You can connect from your development machine because once you enable impersonation *your* account (which is a domain account) is being used to authenticate with the SQL Server machine. There is no double-hop issue in this instance because you're authenticating with a domain account that is logged on locally to the machine (unlike the web server scenario).

    Friday, September 21, 2007 5:29 PM
  • > you are not changing the code rather adding needed omission
    > by your developer in a double hop situation
     
    This app is a couple years old.  How was it working all this time?
    Friday, September 21, 2007 5:43 PM
  • > OK, I'm a little confused. Are you trying to get this to work from your

    > development machine or from a dedicated web server?

     

    Sorry.  I'm trying to get it to work from my development machine.

     

    > if impersonation is not enabled then your app is running under the

    > default ASP.NET account, which if it's from XP then it's the local ASPNET account

     

    Out of curiousity, why does the error message have null for the user name?  If it's authenticating using tthe ASPNET account, shouldn't it say "Login failed for user ASPNET"?

     

     

    Friday, September 21, 2007 5:46 PM
  • Probably because there is no mirrored account on the SQL Server machine or the password for the mirrored account does not match the one on the developer's machine (or the machine attempting to connect).

     

    You also have to keep in mind that some of these error messages are generic and can represent different issues.

    Friday, September 21, 2007 6:00 PM
  •  I-DotNET wrote:
    > you are not changing the code rather adding needed omission
    > by your developer in a double hop situation
     
    This app is a couple years old.  How was it working all this time?

     

    That is actually simple in XP you are running IIS5.1 so the account used to run Asp.net is actually Asp.net while in Win2003 it is IIS6 require different configurations.  That is the reason I tell users to uninstall and reinstall IIS6 because there are so many components needed to run Asp.net that are not enabled with the default installation.  Win2k is also IIS5 but the difference is Win2k being a server comes with IIS user group which makes correct configuration easy with aspnet_regiis. The user being Null is related correct permissions needed for subsystems to perform tasks in SQL Server.

    Friday, September 21, 2007 6:10 PM
  • OK, I figured it out.  It was the machine.config file.  Apparently, she (or someone) modified it but never checked it into VSS.  I made sure no one reformatted the HD so I was able to get a copy from her PC.
     
    Thanks to everyone for their help!
    Friday, September 21, 2007 6:30 PM
  • In Asp.net Web.Config is the modifiable file not Machine.Config you have a pedestrian error that is best resolved as Paul and I told you not modifying Machine.Config because SQL Server gives this error even without a web application .  I am not saying Machine.Config should not be modified just not for simple errors as you had which means different thing in different situation.  What I am trying to say is you performed surgery for aspirin problem.  Good Luck.

     

    Friday, September 21, 2007 7:16 PM
  • You may want to indicate what it was that was modified just in case someone else has the same problem.

    Friday, September 21, 2007 8:18 PM

  • Hi I-DotNET,


    Can you tell me how did you fix this bug?

    I've exactely the same problem than you.

    Thank you


    Tuesday, October 9, 2007 1:26 PM
  • Will you please elaborate on the solution. I am having the same problem, that is "Login failed for user ''. The user is not associated with a trusted SQL Server connection." But in  my case, both the application and the database server are both on the same machine.

     

    What should I change in the machine.config?

     

    Thanks in advance,

     

    Wango

    Saturday, July 19, 2008 11:53 PM
  • We would need the following info:

    What authentication is your web app configured for? Anonymous? Basic? Integrated Windows?
    Is your web app configured for impersonation?
    Are you using SQL Server security or Integrated Windows security?
    Monday, July 21, 2008 11:59 AM
  • I have the same problem!

    Intregrated Windows!
    NO Impersonation!

    My connection string is set to Itegrated Security

    Thanks
    Thursday, October 16, 2008 2:39 PM