problem with trusted connection (asp.net)

Discussion in 'ASP .Net Security' started by Mad Scientist Jr, Nov 15, 2006.

  1. I am getting the following error when trying to access a database with
    a trusted connection:

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

    My connection string is:

    "Server=MyServer; Database=MyCatalog; Trusted_Connection=True;"

    Note that Anonymous Access is off in IIS, and I am able to read my
    Windows login with:

    Textbox1.text =
    System.Web.HttpContext.Current.User.Identity.Name.Substring(System.Web.HttpContext.Current.User.Identity.Name.IndexOf("\")
    + 1).ToLower()

    Can anyone explain how to query the db using a trusted connection?

    PS Is there a way to dynamically retrieve a list of databases the
    current user has SQL rights on, by specifying only the server, again
    using trusted connection?

    Thanks...
     
    Mad Scientist Jr, Nov 15, 2006
    #1
    1. Advertising

  2. Mad Scientist Jr

    Hari Prasad Guest

    Hello,

    Have you added the OS user into the SQL Server? If not add the particular
    user into SQL Server Logins and try connecting.

    Thanks
    Hari




    "Mad Scientist Jr" <> wrote in message
    news:...
    >I am getting the following error when trying to access a database with
    > a trusted connection:
    >
    > "Login failed for user '(null)'. Reason: Not associated with a trusted
    > SQL Server connection."
    >
    > My connection string is:
    >
    > "Server=MyServer; Database=MyCatalog; Trusted_Connection=True;"
    >
    > Note that Anonymous Access is off in IIS, and I am able to read my
    > Windows login with:
    >
    > Textbox1.text =
    > System.Web.HttpContext.Current.User.Identity.Name.Substring(System.Web.HttpContext.Current.User.Identity.Name.IndexOf("\")
    > + 1).ToLower()
    >
    > Can anyone explain how to query the db using a trusted connection?
    >
    > PS Is there a way to dynamically retrieve a list of databases the
    > current user has SQL rights on, by specifying only the server, again
    > using trusted connection?
    >
    > Thanks...
    >
     
    Hari Prasad, Nov 15, 2006
    #2
    1. Advertising

  3. Mad Scientist Jr

    Damien Guest

    Mad Scientist Jr wrote:

    > I am getting the following error when trying to access a database with
    > a trusted connection:
    >
    > "Login failed for user '(null)'. Reason: Not associated with a trusted
    > SQL Server connection."
    >
    > My connection string is:
    >
    > "Server=MyServer; Database=MyCatalog; Trusted_Connection=True;"
    >
    > Note that Anonymous Access is off in IIS, and I am able to read my
    > Windows login with:
    >
    > Textbox1.text =
    > System.Web.HttpContext.Current.User.Identity.Name.Substring(System.Web.HttpContext.Current.User.Identity.Name.IndexOf("\")
    > + 1).ToLower()
    >
    > Can anyone explain how to query the db using a trusted connection?
    >
    > PS Is there a way to dynamically retrieve a list of databases the
    > current user has SQL rights on, by specifying only the server, again
    > using trusted connection?
    >
    > Thanks...


    You're almost certainly encountering the "double hop" issue. This
    arises when IIS and SQL Server are on separate servers. If that isn't
    true in your circumstance, then ignore the rest of this post.

    The problem arises because your client machine authenticates you to the
    IIS server, but the IIS server has no means of authenticating you to
    the SQL Server box (It can't make the second "hop" of authentication).
    There was a superb MSDN Magazine article some time back that describes
    this, but I can't seem to find it right now. I have found this
    knowledge base article:

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

    which seems to cover the same areas.

    Damien
     
    Damien, Nov 15, 2006
    #3
  4. Thanks for your reply...

    I read up on it and fixed the problem on my local machine by adding the
    following to my web.config file:

    <identity impersonate="true"/>

    However when I try running this on the live web server I get this
    error:

    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

    Any ideas?



    > You're almost certainly encountering the "double hop" issue. This
    > arises when IIS and SQL Server are on separate servers. If that isn't
    > true in your circumstance, then ignore the rest of this post.
    >
    > The problem arises because your client machine authenticates you to the
    > IIS server, but the IIS server has no means of authenticating you to
    > the SQL Server box (It can't make the second "hop" of authentication).




    Damien wrote:
    > Mad Scientist Jr wrote:
    >
    > > I am getting the following error when trying to access a database with
    > > a trusted connection:
    > >
    > > "Login failed for user '(null)'. Reason: Not associated with a trusted
    > > SQL Server connection."
    > >
    > > My connection string is:
    > >
    > > "Server=MyServer; Database=MyCatalog; Trusted_Connection=True;"
    > >
    > > Note that Anonymous Access is off in IIS, and I am able to read my
    > > Windows login with:
    > >
    > > Textbox1.text =
    > > System.Web.HttpContext.Current.User.Identity.Name.Substring(System.Web.HttpContext.Current.User.Identity.Name.IndexOf("\")
    > > + 1).ToLower()
    > >
    > > Can anyone explain how to query the db using a trusted connection?
    > >
    > > PS Is there a way to dynamically retrieve a list of databases the
    > > current user has SQL rights on, by specifying only the server, again
    > > using trusted connection?
    > >
    > > Thanks...

    >
    > You're almost certainly encountering the "double hop" issue. This
    > arises when IIS and SQL Server are on separate servers. If that isn't
    > true in your circumstance, then ignore the rest of this post.
    >
    > The problem arises because your client machine authenticates you to the
    > IIS server, but the IIS server has no means of authenticating you to
    > the SQL Server box (It can't make the second "hop" of authentication).
    > There was a superb MSDN Magazine article some time back that describes
    > this, but I can't seem to find it right now. I have found this
    > knowledge base article:
    >
    > http://support.microsoft.com/kb/810572
    >
    > which seems to cover the same areas.
    >
    > Damien
     
    Mad Scientist Jr, Nov 15, 2006
    #4
  5. Mad Scientist Jr

    Joe Kaplan Guest

    Like Damien said, this is a double hop issue and is solved by implementing
    Kerberos delegation. I'd suggest reading the various technet docs on
    Kerberos delegation, as they are quite good, as well as reading Keith
    Brown's MSDN magazine articles that discuss this.

    You will not get this to work using IWA auth, impersonation and SQL on a
    different machine unless you do this.

    Joe K.

    --
    Joe Kaplan-MS MVP Directory Services Programming
    Co-author of "The .NET Developer's Guide to Directory Services Programming"
    http://www.directoryprogramming.net
    --
    "Mad Scientist Jr" <> wrote in message
    news:...
    > Thanks for your reply...
    >
    > I read up on it and fixed the problem on my local machine by adding the
    > following to my web.config file:
    >
    > <identity impersonate="true"/>
    >
    > However when I try running this on the live web server I get this
    > error:
    >
    > Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
    >
    > Any ideas?
    >
    >
    >
    >> You're almost certainly encountering the "double hop" issue. This
    >> arises when IIS and SQL Server are on separate servers. If that isn't
    >> true in your circumstance, then ignore the rest of this post.
    >>
    >> The problem arises because your client machine authenticates you to the
    >> IIS server, but the IIS server has no means of authenticating you to
    >> the SQL Server box (It can't make the second "hop" of authentication).

    >
    >
    >
    > Damien wrote:
    >> Mad Scientist Jr wrote:
    >>
    >> > I am getting the following error when trying to access a database with
    >> > a trusted connection:
    >> >
    >> > "Login failed for user '(null)'. Reason: Not associated with a trusted
    >> > SQL Server connection."
    >> >
    >> > My connection string is:
    >> >
    >> > "Server=MyServer; Database=MyCatalog; Trusted_Connection=True;"
    >> >
    >> > Note that Anonymous Access is off in IIS, and I am able to read my
    >> > Windows login with:
    >> >
    >> > Textbox1.text =
    >> > System.Web.HttpContext.Current.User.Identity.Name.Substring(System.Web.HttpContext.Current.User.Identity.Name.IndexOf("\")
    >> > + 1).ToLower()
    >> >
    >> > Can anyone explain how to query the db using a trusted connection?
    >> >
    >> > PS Is there a way to dynamically retrieve a list of databases the
    >> > current user has SQL rights on, by specifying only the server, again
    >> > using trusted connection?
    >> >
    >> > Thanks...

    >>
    >> You're almost certainly encountering the "double hop" issue. This
    >> arises when IIS and SQL Server are on separate servers. If that isn't
    >> true in your circumstance, then ignore the rest of this post.
    >>
    >> The problem arises because your client machine authenticates you to the
    >> IIS server, but the IIS server has no means of authenticating you to
    >> the SQL Server box (It can't make the second "hop" of authentication).
    >> There was a superb MSDN Magazine article some time back that describes
    >> this, but I can't seem to find it right now. I have found this
    >> knowledge base article:
    >>
    >> http://support.microsoft.com/kb/810572
    >>
    >> which seems to cover the same areas.
    >>
    >> Damien

    >
     
    Joe Kaplan, Nov 15, 2006
    #5
  6. I read the articles below and it makes sense. I am working with my
    system admin to set this up. Thanks to everyone for your replies.

    Once I get this working, I have a second question - from ASP.NET is
    there a way to dynamically retrieve a list of databases on a given SQL
    server that the current user has SQL rights on?

    Thanks again

    How to configure an ASP.NET application for a delegation scenario
    http://support.microsoft.com/kb/810572

    Allow a computer to be trusted for delegation
    http://technet2.microsoft.com/Windo...a055-43f7-b9be-20599b694a311033.mspx?mfr=true

    How to Open Active Directory Users and Computers
    http://www.microsoft.com/technet/pr...ce2-5557-4a3e-b2f7-df3f65640671.mspx?mfr=true




    Joe Kaplan wrote:
    > Like Damien said, this is a double hop issue and is solved by implementing
    > Kerberos delegation. I'd suggest reading the various technet docs on
    > Kerberos delegation, as they are quite good, as well as reading Keith
    > Brown's MSDN magazine articles that discuss this.
    >
    > You will not get this to work using IWA auth, impersonation and SQL on a
    > different machine unless you do this.
    >
    > Joe K.
    >
    > --
    > Joe Kaplan-MS MVP Directory Services Programming
    > Co-author of "The .NET Developer's Guide to Directory Services Programming"
    > http://www.directoryprogramming.net
    > --
    > "Mad Scientist Jr" <> wrote in message
    > news:...
    > > Thanks for your reply...
    > >
    > > I read up on it and fixed the problem on my local machine by adding the
    > > following to my web.config file:
    > >
    > > <identity impersonate="true"/>
    > >
    > > However when I try running this on the live web server I get this
    > > error:
    > >
    > > Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
    > >
    > > Any ideas?
    > >
    > >
    > >
    > >> You're almost certainly encountering the "double hop" issue. This
    > >> arises when IIS and SQL Server are on separate servers. If that isn't
    > >> true in your circumstance, then ignore the rest of this post.
    > >>
    > >> The problem arises because your client machine authenticates you to the
    > >> IIS server, but the IIS server has no means of authenticating you to
    > >> the SQL Server box (It can't make the second "hop" of authentication).

    > >
    > >
    > >
    > > Damien wrote:
    > >> Mad Scientist Jr wrote:
    > >>
    > >> > I am getting the following error when trying to access a database with
    > >> > a trusted connection:
    > >> >
    > >> > "Login failed for user '(null)'. Reason: Not associated with a trusted
    > >> > SQL Server connection."
    > >> >
    > >> > My connection string is:
    > >> >
    > >> > "Server=MyServer; Database=MyCatalog; Trusted_Connection=True;"
    > >> >
    > >> > Note that Anonymous Access is off in IIS, and I am able to read my
    > >> > Windows login with:
    > >> >
    > >> > Textbox1.text =
    > >> > System.Web.HttpContext.Current.User.Identity.Name.Substring(System.Web.HttpContext.Current.User.Identity.Name.IndexOf("\")
    > >> > + 1).ToLower()
    > >> >
    > >> > Can anyone explain how to query the db using a trusted connection?
    > >> >
    > >> > PS Is there a way to dynamically retrieve a list of databases the
    > >> > current user has SQL rights on, by specifying only the server, again
    > >> > using trusted connection?
    > >> >
    > >> > Thanks...
    > >>
    > >> You're almost certainly encountering the "double hop" issue. This
    > >> arises when IIS and SQL Server are on separate servers. If that isn't
    > >> true in your circumstance, then ignore the rest of this post.
    > >>
    > >> The problem arises because your client machine authenticates you to the
    > >> IIS server, but the IIS server has no means of authenticating you to
    > >> the SQL Server box (It can't make the second "hop" of authentication).
    > >> There was a superb MSDN Magazine article some time back that describes
    > >> this, but I can't seem to find it right now. I have found this
    > >> knowledge base article:
    > >>
    > >> http://support.microsoft.com/kb/810572
    > >>
    > >> which seems to cover the same areas.
    > >>
    > >> Damien

    > >
     
    Mad Scientist Jr, Nov 15, 2006
    #6
  7. Mad Scientist Jr

    Joe Kaplan Guest

    You may also need to read the "troubleshooting Kerberos delegation" paper
    from TechNet as well if this doesn't work right. Unfortunately, it can be a
    bit painful when it doesn't just work and there are a bunch of options which
    make things complicated in Windows Server 2003 (constrained delegation and
    protocol transition).

    I can't answer the second question. I'm a security guy. :)

    Joe K.

    --
    Joe Kaplan-MS MVP Directory Services Programming
    Co-author of "The .NET Developer's Guide to Directory Services Programming"
    http://www.directoryprogramming.net
    --
    "Mad Scientist Jr" <> wrote in message
    news:...
    >I read the articles below and it makes sense. I am working with my
    > system admin to set this up. Thanks to everyone for your replies.
    >
    > Once I get this working, I have a second question - from ASP.NET is
    > there a way to dynamically retrieve a list of databases on a given SQL
    > server that the current user has SQL rights on?
    >
    > Thanks again
    >
    > How to configure an ASP.NET application for a delegation scenario
    > http://support.microsoft.com/kb/810572
    >
    > Allow a computer to be trusted for delegation
    > http://technet2.microsoft.com/Windo...a055-43f7-b9be-20599b694a311033.mspx?mfr=true
    >
    > How to Open Active Directory Users and Computers
    > http://www.microsoft.com/technet/pr...ce2-5557-4a3e-b2f7-df3f65640671.mspx?mfr=true
    >
    >
    >
    >
    > Joe Kaplan wrote:
    >> Like Damien said, this is a double hop issue and is solved by
    >> implementing
    >> Kerberos delegation. I'd suggest reading the various technet docs on
    >> Kerberos delegation, as they are quite good, as well as reading Keith
    >> Brown's MSDN magazine articles that discuss this.
    >>
    >> You will not get this to work using IWA auth, impersonation and SQL on a
    >> different machine unless you do this.
    >>
    >> Joe K.
    >>
    >> --
    >> Joe Kaplan-MS MVP Directory Services Programming
    >> Co-author of "The .NET Developer's Guide to Directory Services
    >> Programming"
    >> http://www.directoryprogramming.net
    >> --
    >> "Mad Scientist Jr" <> wrote in message
    >> news:...
    >> > Thanks for your reply...
    >> >
    >> > I read up on it and fixed the problem on my local machine by adding the
    >> > following to my web.config file:
    >> >
    >> > <identity impersonate="true"/>
    >> >
    >> > However when I try running this on the live web server I get this
    >> > error:
    >> >
    >> > Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
    >> >
    >> > Any ideas?
    >> >
    >> >
    >> >
    >> >> You're almost certainly encountering the "double hop" issue. This
    >> >> arises when IIS and SQL Server are on separate servers. If that isn't
    >> >> true in your circumstance, then ignore the rest of this post.
    >> >>
    >> >> The problem arises because your client machine authenticates you to
    >> >> the
    >> >> IIS server, but the IIS server has no means of authenticating you to
    >> >> the SQL Server box (It can't make the second "hop" of authentication).
    >> >
    >> >
    >> >
    >> > Damien wrote:
    >> >> Mad Scientist Jr wrote:
    >> >>
    >> >> > I am getting the following error when trying to access a database
    >> >> > with
    >> >> > a trusted connection:
    >> >> >
    >> >> > "Login failed for user '(null)'. Reason: Not associated with a
    >> >> > trusted
    >> >> > SQL Server connection."
    >> >> >
    >> >> > My connection string is:
    >> >> >
    >> >> > "Server=MyServer; Database=MyCatalog;
    >> >> > Trusted_Connection=True;"
    >> >> >
    >> >> > Note that Anonymous Access is off in IIS, and I am able to read my
    >> >> > Windows login with:
    >> >> >
    >> >> > Textbox1.text =
    >> >> > System.Web.HttpContext.Current.User.Identity.Name.Substring(System.Web.HttpContext.Current.User.Identity.Name.IndexOf("\")
    >> >> > + 1).ToLower()
    >> >> >
    >> >> > Can anyone explain how to query the db using a trusted connection?
    >> >> >
    >> >> > PS Is there a way to dynamically retrieve a list of databases the
    >> >> > current user has SQL rights on, by specifying only the server, again
    >> >> > using trusted connection?
    >> >> >
    >> >> > Thanks...
    >> >>
    >> >> You're almost certainly encountering the "double hop" issue. This
    >> >> arises when IIS and SQL Server are on separate servers. If that isn't
    >> >> true in your circumstance, then ignore the rest of this post.
    >> >>
    >> >> The problem arises because your client machine authenticates you to
    >> >> the
    >> >> IIS server, but the IIS server has no means of authenticating you to
    >> >> the SQL Server box (It can't make the second "hop" of authentication).
    >> >> There was a superb MSDN Magazine article some time back that describes
    >> >> this, but I can't seem to find it right now. I have found this
    >> >> knowledge base article:
    >> >>
    >> >> http://support.microsoft.com/kb/810572
    >> >>
    >> >> which seems to cover the same areas.
    >> >>
    >> >> Damien
    >> >

    >
     
    Joe Kaplan, Nov 15, 2006
    #7
  8. Mad Scientist Jr

    Jason Guest

    We ran into the same problem a couple of weeks ago. Turn off the
    impersonation and just grant db access to what ever account the appPool is
    using, default is NETWORKSERVICE. You'll have to script the user into the db
    since you can't browse for MACHINENAME$ (the server's NETWORKSERVICE account).

    this article explains it al
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/PAGHT000008.asp?_r=1

    "Mad Scientist Jr" wrote:

    > I am getting the following error when trying to access a database with
    > a trusted connection:
    >
    > "Login failed for user '(null)'. Reason: Not associated with a trusted
    > SQL Server connection."
    >
    > My connection string is:
    >
    > "Server=MyServer; Database=MyCatalog; Trusted_Connection=True;"
    >
    > Note that Anonymous Access is off in IIS, and I am able to read my
    > Windows login with:
    >
    > Textbox1.text =
    > System.Web.HttpContext.Current.User.Identity.Name.Substring(System.Web.HttpContext.Current.User.Identity.Name.IndexOf("\")
    > + 1).ToLower()
    >
    > Can anyone explain how to query the db using a trusted connection?
    >
    > PS Is there a way to dynamically retrieve a list of databases the
    > current user has SQL rights on, by specifying only the server, again
    > using trusted connection?
    >
    > Thanks...
    >
    >
     
    Jason, Nov 16, 2006
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Peter Afonin
    Replies:
    1
    Views:
    2,200
    Peter Afonin
    Aug 29, 2003
  2. Brian Henry

    trusted sql server connection problem!?

    Brian Henry, Oct 14, 2003, in forum: ASP .Net
    Replies:
    5
    Views:
    505
    Angel Saenz-Badillos[MS]
    Oct 15, 2003
  3. Mythran
    Replies:
    5
    Views:
    4,959
    Mythran
    Oct 5, 2005
  4. Jim Corey
    Replies:
    2
    Views:
    138
    Bob Barrows [MVP]
    Dec 28, 2004
  5. CD

    Trusted Connection via ASP page.

    CD, Feb 24, 2005, in forum: ASP General
    Replies:
    4
    Views:
    119
Loading...

Share This Page