Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

ASP.NET App and SQL Server identity Expand / Collapse
Author
Message
Posted Monday, October 08, 2012 8:25 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, January 10, 2014 10:16 PM
Points: 314, Visits: 434
Well, I've just written my first ASP.NET (C#) app using Entity Framework and SQL Server (2008). It's a simple thing, just pulls up a list I could do easily a hundred other ways, but I'm just experimenting so far.

I got it to work (still not sure how....) But, in order to use integrated security, I had to enable NT Authority/Network Service as a user on my db. What I really want is for my users' NT identity to pass through. I'm getting the impression this isn't gonna happen. Have I got it all wrong? Do I now need to handle all authentication in the ASP.NET application, and leave the db with no authentication (other than "NT Authority/Network Service...?)

What's standard practice?

Anyone with resources for me? Links, etc?


Jim
Post #1369846
Posted Monday, October 08, 2012 10:18 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:51 PM
Points: 1,651, Visits: 5,198
I have done this, but can't access the source code right now.

From memory, you need to use windows authentication on your web site and then impersonate the end user in your ASP.net code.


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1369933
    Posted Monday, October 08, 2012 1:52 PM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Today @ 9:25 AM
    Points: 7,070, Visits: 12,523
    This article is a good primer on the different options and still applies to current versions of .NET:

    How to implement impersonation in an ASP.NET application

    I think you're asking for the person accessing the site to be the person whose credentials are used to authenticate to the database instance (section "Impersonate the IIS Authenticated Account or User" in the article). If so then it becomes a little challenging (lookup double-hop) and requires some work on the system-side to setup SPNs.


    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

    Believe you can and you're halfway there. --Theodore Roosevelt

    Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

    The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

    1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
    Post #1370021
    Posted Tuesday, October 09, 2012 7:08 AM


    Old Hand

    Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

    Group: General Forum Members
    Last Login: Friday, January 10, 2014 10:16 PM
    Points: 314, Visits: 434
    Yeah, so I'm getting the impression the ASP.NET app must accept all comers, authenticate them, then use the Network Service to access the db. I can live with that. If I'm in an AD environment, can I authenticate from the ASP.NET app without asking the user to log in again?

    Jim
    Post #1370376
    Posted Tuesday, October 09, 2012 10:40 AM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Today @ 9:25 AM
    Points: 7,070, Visits: 12,523
    JimS-Indy (10/9/2012)
    Yeah, so I'm getting the impression the ASP.NET app must accept all comers, authenticate them, then use the Network Service to access the db. I can live with that. If I'm in an AD environment, can I authenticate from the ASP.NET app without asking the user to log in again?

    What did you have in mind when you say "authenticate them"?

    It's a common model to let the ASP.NET app check the incoming user's AD Group memberships to decide whether they can access the site, but once they're accepted in the website authenticates to the database as a service account. If you go that route I would recommend against having the site use the built-in NETWORK SERVICE account to authenticate to the database. I would create a new service account just for the website and set the App Pool in IIS to run as that account. Then in your web.config you won't be doing any impersonation at all, you'll just use Trusted Authentication in your connection strings.


    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

    Believe you can and you're halfway there. --Theodore Roosevelt

    Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

    The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

    1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
    Post #1370484
    Posted Tuesday, October 09, 2012 10:51 AM


    Old Hand

    Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

    Group: General Forum Members
    Last Login: Friday, January 10, 2014 10:16 PM
    Points: 314, Visits: 434
    I see. Any resources on how to verify the user is in an AD group? I'm also really confused about App pools. Any resources on those? They look cool, and maybe dangerous? Anyway, if you could point me to links describing that stuff, I'd appreciate it. I'm heavily into printing out web pages and reading them on airplanes....

    Thanks, what you say makes sense.

    PS...I am finding resources on LDAP queries ??? to determine if a user is a member of a group in Stack Overflow:

    http://stackoverflow.com/questions/1032351/how-to-write-ldap-query-to-test-if-user-is-member-of-a-group


    Jim
    Post #1370487
    Posted Tuesday, October 09, 2012 10:59 AM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Today @ 9:25 AM
    Points: 7,070, Visits: 12,523
    JimS-Indy (10/9/2012)
    I see. Any resources on how to verify the user is in an AD group?

    .NET System.DirectoryServices.AccountManagement Namespace

    I'm also really confused about App pools. Any resources on those? They look cool, and maybe dangerous?

    They are not dangerous at all.

    Managing Application Pools in IIS 7


    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

    Believe you can and you're halfway there. --Theodore Roosevelt

    Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

    The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

    1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
    Post #1370488
    Posted Tuesday, October 09, 2012 12:08 PM


    Old Hand

    Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

    Group: General Forum Members
    Last Login: Friday, January 10, 2014 10:16 PM
    Points: 314, Visits: 434
    I'm also really confused about App pools. Any resources on those? They look cool, and maybe dangerous?
    They are not dangerous at all.


    Well, maybe in my hands....lol

    Thanks, I'll do some reading tonight!


    Jim
    Post #1370521
    Posted Tuesday, October 09, 2012 3:54 PM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 4:51 PM
    Points: 1,651, Visits: 5,198
    JimS-Indy (10/9/2012)
    Yeah, so I'm getting the impression the ASP.NET app must accept all comers, authenticate them, then use the Network Service to access the db. I can live with that. If I'm in an AD environment, can I authenticate from the ASP.NET app without asking the user to log in again?


    No, it doesn't have to use Network Service or a fixed account to access the db.

    Here is a very simple sample web.config and default.aspx that shows how simple it can be to use Windows Auth in ASP.NET to connect.

    Web.config


    <?xml version="1.0"?>
    <configuration>
    <connectionStrings>
    <add name="DemoConnectionString" connectionString="Data Source=MyServer;Initial Catalog=master;Integrated Security=True"
    providerName="System.Data.SqlClient" />
    </connectionStrings>
    <system.web>
    <compilation debug="true" targetFramework="4.0" />
    <authentication mode="Windows">
    </authentication>
    </system.web>
    </configuration>


    And the web page:


    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="WinAuthToSQLDemo.Default" %>

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
    <title></title>
    </head>
    <body>
    <form id="form1" runat="server">
    <div>
    <asp:DetailsView ID="DetailsView1" runat="server" Height="50px" Width="125px"
    AutoGenerateRows="False" DataSourceID="SqlDataSource1">
    <Fields>
    <asp:BoundField DataField="ConnectedUser" HeaderText="Connected User"
    ReadOnly="True" SortExpression="ConnectedUser" />
    </Fields>
    </asp:DetailsView>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:DemoConnectionString %>"
    SelectCommand="SELECT SUSER_SNAME() AS ConnectedUser"></asp:SqlDataSource>
    </div>
    </form>
    </body>
    </html>


    You will see that it couldn't really be any simpler - this simple web page will connect to your SQL server and return the name of the user.

    Obviously, you must ensure the web site has only ASP.NET Impersonation and Windows Authentication enabled in the "Authentication" settings for this to work...

    Edit: fix wording in last sentence.


    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1370602
    Posted Tuesday, October 09, 2012 4:34 PM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Today @ 9:25 AM
    Points: 7,070, Visits: 12,523
    mister.magoo (10/9/2012)
    Obviously, you must ensure the web site has only ASP.NET Impersonation and Windows Authentication enabled in the "Authentication" settings for this to work...

    You do make it seem simple, but it appears there are two pieces of the puzzle not stated in your solution that allow your code to function in a distributed environment.

    1. SPN for MSSQLSVC must exist under account running database service which allows the client's Kerberos ticket to make it to the database server intact
    2. browser must be configured for "Integrated Windows Authentication" which allows the client credential to be passed to the web server through browser without asking the client to supply username/password

    Edit: I had the reply-screen up for a while. I just noticed your edit "fix wording in last sentence" to include the browser piece (piece #2 above) so that just leaves puzzle piece #1


    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

    Believe you can and you're halfway there. --Theodore Roosevelt

    Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

    The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

    1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
    Post #1370616
    « Prev Topic | Next Topic »

    Add to briefcase 12»»

    Permissions Expand / Collapse