IIS & SQL Issues

Discussion in 'ASP .Net' started by TipTop, Apr 17, 2004.

  1. TipTop

    TipTop Guest

    I am trying to use integrated Windows authentication and
    impersonation to run a page that accesses SQL Server via integrated
    security. It's not working -- sort of. I've set IIS security to
    integrated Windows authentication (and unchecked the other options).
    I've set the web.config to use Windows authentication and set
    impersonation to true. The connection string includes Integrated
    Security = SSPI. And it works... if you access the page from the same
    machine. If you call the page over the network (even when logged in
    under the same Windows account as when on the server), it fails with
    the "Login failed for user '(null)'" error. I display the results of
    Environment.UserName on the page (commenting out the sql connection
    code so that it will run when calling it over the network), and in all
    cases it shows the correct username. (I call the page over the network
    and sure enough it displays my account name.)
     
    TipTop, Apr 17, 2004
    #1
    1. Advertising

  2. Ok, you've misunderstood what integrated authentication is.

    When the integrated authentication check box is the only box checked on a
    web site that means that only computer accounts created on the server that
    have been given access to the folder the web site files are stored in may
    run the website.

    When you are using windows authentication in conjunction with sql server
    here is what you should do:

    1. Create an account on the server (or domain) which is specifically for
    the web site to use.
    2. In IIS right click on the web site and choose properties and then
    click on the directory security tab. Click on the Edit button. Now LEAVE the
    anonymous access checkbox checked. Click the Browse button. Set the web site
    to run under the account you created for it.
    3. In Sql Server add the same account you created for the web site to run
    as to sql server and give that account the appropriate permissions to the
    database.
    4. Set the site to use the account the web site is running for accessing
    the database by placing the tag:
    <identity impersonate="true" /> into the web.config file

    --
    Sincerely,

    S. Justin Gengo, MCP
    Web Developer / Programmer

    www.aboutfortunate.com

    "Out of chaos comes order."
    Nietzsche
    "TipTop" <> wrote in message
    news:...
    > I am trying to use integrated Windows authentication and
    > impersonation to run a page that accesses SQL Server via integrated
    > security. It's not working -- sort of. I've set IIS security to
    > integrated Windows authentication (and unchecked the other options).
    > I've set the web.config to use Windows authentication and set
    > impersonation to true. The connection string includes Integrated
    > Security = SSPI. And it works... if you access the page from the same
    > machine. If you call the page over the network (even when logged in
    > under the same Windows account as when on the server), it fails with
    > the "Login failed for user '(null)'" error. I display the results of
    > Environment.UserName on the page (commenting out the sql connection
    > code so that it will run when calling it over the network), and in all
    > cases it shows the correct username. (I call the page over the network
    > and sure enough it displays my account name.)
    >
    >
     
    S. Justin Gengo, Apr 17, 2004
    #2
    1. Advertising

  3. TipTop

    Chris Botha Guest

    It's called the "double hop" issue and happens if the SQL Server is not on
    the same computer as IIS (if they are on the same computer it should work -
    it works for me).
    Here is one article on it, search google for "double hop" and you should get
    more.
    http://weblogs.asp.net/nunos/archive/2004/03/12/88468.aspx

    "TipTop" <> wrote in message
    news:...
    > I am trying to use integrated Windows authentication and
    > impersonation to run a page that accesses SQL Server via integrated
    > security. It's not working -- sort of. I've set IIS security to
    > integrated Windows authentication (and unchecked the other options).
    > I've set the web.config to use Windows authentication and set
    > impersonation to true. The connection string includes Integrated
    > Security = SSPI. And it works... if you access the page from the same
    > machine. If you call the page over the network (even when logged in
    > under the same Windows account as when on the server), it fails with
    > the "Login failed for user '(null)'" error. I display the results of
    > Environment.UserName on the page (commenting out the sql connection
    > code so that it will run when calling it over the network), and in all
    > cases it shows the correct username. (I call the page over the network
    > and sure enough it displays my account name.)
    >
    >
     
    Chris Botha, Apr 17, 2004
    #3
  4. TipTop

    Ken Schaefer Guest

    a) if you do this, you will lose the benefits of connection pooling, as a
    separate connection will be used for each security context (each user
    account will have it's own pool). So, this solution will not scale to a
    large number of users. It's OK if you have a small number of users

    b) the problem is double-hop authentication. When using IWA, the webserver
    does not have the user's password. It just gets a token from the DC, but the
    token does not have permission to logon to network resources.

    Options:
    a) if you are using a Windows 2000 Domain, you can enable delegation. This
    allows the IIS server to impersonate the Windows account, and logon to the
    backend SQL Server. You need to use Kerberos authentication for this (not
    NTLM v2)

    b) if you are using a Windows 2003 Domain, when you enable constrained
    delegation, you can use Protocol Transition. This allows the user to
    authenticate using any of a number of mechanisms to the IIS server (eg
    Digest, or NTLM), and the webserver can still get an Kerberos token to logon
    to the SQL Server.

    Here are a few articles to get you started:

    IMPORTANT:
    Read chapter 12 from the Building Secure ASP.Net Application Book - it has
    very good information about building scalable, secure ASP.Net applications
    (eg using a trusted subsystem model):
    http://msdn.microsoft.com/library/d...-us/dnnetsec/html/secnetlpMSDN.asp?frame=true

    http://support.microsoft.com/?id=319723
    INF: SQL Server 2000 Kerberos support including SQL Server virtual servers
    on server clusters

    http://support.microsoft.com/default.aspx?scid=kb;en-us;810572
    HOW TO: Configure an ASP.NET Application for a Delegation Scenario

    http://support.microsoft.com/?id=294382
    Authentication May Fail with "401.3" Error If Web Site's "Host Header"
    Differs from Server's NetBIOS Name

    http://support.microsoft.com/default.aspx?kbid=325894
    HOW TO: Configure Computer Accounts and User Accounts So That They Are
    Trusted for Delegation in Windows Server 2003 Enterprise Edition (also
    includes Windows 2000 instructions)

    http://www.microsoft.com/resources/...andard/proddocs/en-us/se_con_del_computer.asp
    Configuring Users and Computers for delegation (there's a couple of pages -
    use the links in the nav bar to get to them)

    Windows 2003 Protocol Transition
    http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/constdel.mspx

    Cheers
    Ken

    "TipTop" <> wrote in message
    news:...
    : I am trying to use integrated Windows authentication and
    : impersonation to run a page that accesses SQL Server via integrated
    : security. It's not working -- sort of. I've set IIS security to
    : integrated Windows authentication (and unchecked the other options).
    : I've set the web.config to use Windows authentication and set
    : impersonation to true. The connection string includes Integrated
    : Security = SSPI. And it works... if you access the page from the same
    : machine. If you call the page over the network (even when logged in
    : under the same Windows account as when on the server), it fails with
    : the "Login failed for user '(null)'" error. I display the results of
    : Environment.UserName on the page (commenting out the sql connection
    : code so that it will run when calling it over the network), and in all
    : cases it shows the correct username. (I call the page over the network
    : and sure enough it displays my account name.)
    :
    :
     
    Ken Schaefer, Apr 18, 2004
    #4
  5. Hi all,

    I am having this problem also, but unfortunately company policy prohibits me
    from changing the accounts policy. So far to solve this problem I have
    disabled anonymous access in IIS and have enabled the integrated windows
    authentication, both in IIS and my web.config. I then changed on the IIS 6.0
    box the default worker process to run as a low privileged domain account
    user. I then added this account to have r\w permissions on the sql database.
    I then have disabled impersonation, this so far looks only to be the most
    appropriate solution.





    "TipTop" <> wrote in message
    news:...
    >I am trying to use integrated Windows authentication and
    > impersonation to run a page that accesses SQL Server via integrated
    > security. It's not working -- sort of. I've set IIS security to
    > integrated Windows authentication (and unchecked the other options).
    > I've set the web.config to use Windows authentication and set
    > impersonation to true. The connection string includes Integrated
    > Security = SSPI. And it works... if you access the page from the same
    > machine. If you call the page over the network (even when logged in
    > under the same Windows account as when on the server), it fails with
    > the "Login failed for user '(null)'" error. I display the results of
    > Environment.UserName on the page (commenting out the sql connection
    > code so that it will run when calling it over the network), and in all
    > cases it shows the correct username. (I call the page over the network
    > and sure enough it displays my account name.)
    >
    >
     
    Michael P Phillipson, Apr 18, 2004
    #5
    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. JMaelstrom

    IIS 6 vs IIS 5 ASP.NET Performance Issues

    JMaelstrom, Dec 9, 2003, in forum: ASP .Net
    Replies:
    2
    Views:
    4,706
    shan420
    Apr 30, 2010
  2. ecoolone
    Replies:
    0
    Views:
    797
    ecoolone
    Jan 3, 2008
  3. John Kennedy
    Replies:
    5
    Views:
    258
    Carl Prothman [MVP]
    Aug 14, 2003
  4. John Kennedy
    Replies:
    5
    Views:
    467
    Carl Prothman [MVP]
    Aug 14, 2003
  5. Jeremy Smith

    upload sql files from IIS to sql

    Jeremy Smith, Aug 10, 2004, in forum: ASP General
    Replies:
    4
    Views:
    249
    Jeff Cochran
    Aug 18, 2004
Loading...

Share This Page