locked
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. RRS feed

  • Question

  • Has anybody come across the following issue?

    I have an intranet site that uses integrated windows security to access a SQL Server database. A few users have received the above error. Aha I hear you cry, have you checked the account IIS is running under etc etc delegation blah blah.

    I know the intranet site is working as each of the users has already successfully used the it to access the SQL Server database. One user has reported this error today after successfully using the site for a year. If I log on to a different PC as the same user the site works perfectly.

    I have found a couple of ways to get rid of the error, but it only comes back some time in the future. I have found if I either reinstall internet explorer 7 or recreate the users windows profile on their PC that the problem will go away. But it keeps coming back eventually. While the number of users with this problem is small it is copable with, however the number of users with the problem is slowly increasing. By slowly I mean three in a year.
    Wednesday, December 3, 2008 3:05 AM

All replies

  • I have covered how if fix this problem in both SQL Server and Asp.net in the thread below.  The link to the old site breaks so copy the link to your browser window.

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


    Asp.net MVP, MCPD Web C#, MCTS TFS, MCITP BI and DBA
    Sunday, December 14, 2008 1:02 AM
  • Thanks for your response Caddre but you have misunderstood my problem. I already have anonymous access denied in IIS and impersonation turned on in WEB.config.

    This is not the issue.

    My issue is that the intranet site works perfectly 95% of the time then throws the error for a few of users at random points in time. I am myself one of those users who has the issue and it's very annoying. If I reinstall IE7 the error goes away, for about a week then comes back.
    Wednesday, January 21, 2009 6:29 AM
  • I did not misunderstand your problem because IE settings and versions are not relevant to this problem, Asp.net runtime is what you are using to execute your intranet application if it is not in the same box with IIS you get this problem.

    SQL Server and Windows use different security context so either add the Asp.net runtime into SQL Server or get a DBA to add all your intranet users into SQL Server.  And look up the reason delegations may not work in all situations.


    MCPD Web C#, MCTS TFS, MCITP BI and DBA
    Wednesday, January 21, 2009 4:15 PM
  • Just to sum that up. You have SQL Server and IIS installed on the same box. Some users are able to get to the site and access data using their security context, others are not. Are you sure that while the access fails, the small icon on the right bottom of IE is showing that the connection is done to the intranet ? If not, IE will assume that you are navigating to a Internet place and will not pass the credentials to the server. This happens due to the setup in IE whereas by default credentials for Intranet sites are normally passed whereas Internet sites won´t get the security information. Can you confirm this and the information that the servers are running on the same box ?

    -Jens K. Suessmeyer

    Wednesday, January 21, 2009 8:09 PM
  • The SQL Server and IIS are on different boxes.

     At the moment all users can access the site and access data using the security context.

    The problem reoccured last week and reinstalling IE7 fixed the problem. I cannot confirm while the access fails, the small icon on the right bottom of IE is showing that the connection is done to the intranet. I will check this the next time the problem occurs.

     

    Apologies Caddre, it just seemed to me that we were talking along different lines. You are saying check the delegation for each user etc whereas I know this is working for all users.

    Tuesday, January 27, 2009 12:19 AM
  • SQL Server and IIS are on different boxes.

     

    As of 9:30 this morning all users could access the database on the intranet including myself.

    As of 11:30 this morning I can no longer access the database on the intranet. I get said error.

    I can confirm that the small icon on the right bottom of IE is showing that the connection is done to the local intranet, while IE is producing the error.

    I guess I reinstall IE7 again.

    Wednesday, January 28, 2009 12:50 AM
  • I have run into this same issue myself.

    We have an app that hosts about 500 intranet users...IIS and SQL on different boxes.

    Once in a while, a single user is no longer able to connect via integrated security.  All IE settings are correct, and the website is configured properly (obviously because 99% of the users can access the app).

    Our only solution to this problem has been to rebuild the user's profile on their machine.  This seems to be a permanent fix.  I haven't tried to re-install IE.

    Caddre is misunderstanding your problem.

    Friday, April 10, 2009 1:45 PM
  • No I am not misunderstanding anything in Asp.net the standard fix is to disable anonymous user and enable impersonation when you have double hop issue which is what you have the general fix is to configure delegation but delegation does not work most of the time. And doing anything in IE for Asp.net application is a hack not official solution. That is the reason the Asp.net AD membership uses IIS and LDAP to resolve permissions and not AD.


    MCPD Web C#, MCTS TFS, MCITP BI and DBA
    Friday, April 10, 2009 2:32 PM
  • No I am not misunderstanding anything in Asp.net the standard fix is to disable anonymous user and enable impersonation when you have double hop issue which is what you have the general fix is to configure delegation but delegation does not work most of the time. And doing anything in IE for Asp.net application is a hack not official solution. That is the reason the Asp.net AD membership uses IIS and LDAP to resolve permissions and not AD.


    MCPD Web C#, MCTS TFS, MCITP BI and DBA

    Well, perhaps I am misunderstanding you Caddre.  No offense intended.

    Since we are discussing integrated authentication, anonymous access is obviously already turned off.  Additionally, impersonation is already enabled.  I believe that was your proposed solution. 

    IMO, this is not an ASP.net issue (at least in my case).  It is also not a double hop issue (although it is a double hop scenario). Simply browsing to the webservice directly from IE forces a login (which should not happen when using integrated authentication).  The problem seems to be an IE problem which is why creating a new profile seems to solve it. 

    I do agree that having to solve this problem in this way is hackish...which is why I'm trying to find a better solution.

    If you can shed more light onto this issue, I'd love to hear it.  It has been something that has been a an aggravation.
    Friday, April 10, 2009 3:29 PM
  • I don't get offended because a user misunderstand Asp.net security which is through IIS and not IE and no integrated does not mean Anonymous is disabled and impersonation may not be enabled be default because Asp.net also provide the use of ACL(access control list) through authorization. In Asp.net architecture when components are in different boxes it is double hop.  The Asp.net team is looking into default Asp.net account that can pass the security token across hops but for now it is delegation.  Please read the article below and most things I am saying are in that article and the second link covers Asp.net double hop.

     

    http://msdn.microsoft.com/en-us/magazine/cc163740.aspx

    http://blogs.msdn.com/nunos/archive/2004/03/12/88468.aspx


    MCPD Web C#, MCTS TFS, MCITP BI and DBA
    Friday, April 10, 2009 4:18 PM
  • I'm glad you aren't offended.  And, thanks for the links, although neither apply to the problem that I am confronting.

    I am aware that ASP.net security is not only an IE issue.  However, I'm also aware that there are settings in IE that are necessary to successfully authenticating via Kerberos.  Additionally, Anonymous access must be turned off and impersonation must be turned on.  We are configured for the double hop scenario exactly as is listed in those 2 articles.  And, it functions correctly over 99% of the time.

    However, from time to time, a user (on his machine only) becomes no longer able to authenticate to the WebService.  Manually attempting to access the webservice requires a login even though that user is already authenticated on the network.  So, as you can see, it never gets to the point where it is making the 2nd hop.   Keep in mind that this is a user who previously functioned with no problems on this same machine. 

    The only solution that we have had for this has been to rebuild the profile on the user's machine.

    I appreciate any response.
    Friday, April 10, 2009 7:11 PM
  • I'm glad you aren't offended.  And, thanks for the links, although neither apply to the problem that I am confronting.

    You have decided it did not apply to you because you think AD should control security in Asp.net and SQL Server and I am telling you that is one of the reasons IIS 7 is not part of Windows it is part of the Developer division tool kit.


    Manually attempting to access the webservice requires a login even though that user is already authenticated on the network.

    You are confusing AD authentication to Asp.net authentication the two are not related,I have explained to you Asp.net uses IIS and LDAP to resolve AD membership because AD primary task is to authenticate users into a file and print server network nothing more.  That is the reason if you get this error in direct connection to SQL Server you must use both Windows and SQL Server authentication.


    The only solution that we have had for this has been to rebuild the profile on the user's machine.

    You are again using AD solution for Asp.net problem because Asp.net comes with a profile object which you can implement as needed, however you could be getting cookie problem.  If you want AD to manage your Asp.net user you need to write a lot of code to connect ADAM to AD membership and it will work.  And ironically how that AD profile object works is the reason Asp.net does not use AD it is dependent on the skills of company system admin and versions of Windows Server in the network.


    MCPD Web C#, MCTS TFS, MCITP BI and DBA
    Friday, April 10, 2009 7:46 PM
  • You have decided it did not apply to you because you think AD should control security in Asp.net

    I don't think that is what I'm doing at all.  What I'm saying is that we occasionally experience problems with a random user on a given machine that prevents said user from authenticating to an ASP.net webservice.  And, when we experience that problem, we can confirm that the problem is specific to a user's profile on that machine.

    We've implemented the exact configuration that is described in both articles you listed above.  When the authentication succeeds to the webserver, we are NOT having any problems authenticating to the SQL Server.  In other words, we would be experiencing the same problem, even if the SQL server was on the same machine as the IIS Server.

    You mentioned that we could be having cookie problems, etc.  I agree.  But, the only solution we've had is to rebuild the profile.  Nothing else has worked.
    Saturday, April 11, 2009 12:04 AM

  • If your problem is cookie the only fix is for the user to delete your company url from their cookie file, the other solution is to write the application with Asp.net profile object.  However here is how we in Asp.net fixes the problem add Asp.net worker process in SQL Server and database.  It is a two part article one for all in one box and one for separate boxes.

    http://geekswithblogs.net/ranganh/archive/2005/05/25/40503.aspx


    MCPD Web C#, MCTS TFS, MCITP BI and DBA
    Saturday, April 11, 2009 1:16 AM