SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ASP.NET App and SQL Server identity


ASP.NET App and SQL Server identity

Author
Message
JimS-Indy
JimS-Indy
SSC-Addicted
SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)

Group: General Forum Members
Points: 453 Visits: 444
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
mister.magoo
mister.magoo
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4118 Visits: 7865
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


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • Orlando Colamatteo
    Orlando Colamatteo
    SSChampion
    SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

    Group: General Forum Members
    Points: 14937 Visits: 14396
    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
    JimS-Indy
    JimS-Indy
    SSC-Addicted
    SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)

    Group: General Forum Members
    Points: 453 Visits: 444
    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
    Orlando Colamatteo
    Orlando Colamatteo
    SSChampion
    SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

    Group: General Forum Members
    Points: 14937 Visits: 14396
    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
    JimS-Indy
    JimS-Indy
    SSC-Addicted
    SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)

    Group: General Forum Members
    Points: 453 Visits: 444
    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
    Orlando Colamatteo
    Orlando Colamatteo
    SSChampion
    SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

    Group: General Forum Members
    Points: 14937 Visits: 14396
    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
    JimS-Indy
    JimS-Indy
    SSC-Addicted
    SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)

    Group: General Forum Members
    Points: 453 Visits: 444
    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
    mister.magoo
    mister.magoo
    SSCarpal Tunnel
    SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

    Group: General Forum Members
    Points: 4118 Visits: 7865
    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>
    <aspBigGrinetailsView 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>
    </aspBigGrinetailsView>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStringsBigGrinemoConnectionString %>"
    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


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • Orlando Colamatteo
    Orlando Colamatteo
    SSChampion
    SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

    Group: General Forum Members
    Points: 14937 Visits: 14396
    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
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search