ASP.NET App and SQL Server identity

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » ASP.NET App and SQL Server identity

11 posts, Page 1 of 2

1 2 »»

ASP.NET App and SQL Server identity

Rate Topic

Display Mode

Topic Options

Author

Message

JimS-Indy

Posted Monday, October 08, 2012 8:25 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, May 10, 2013 7:30 AM
Points: 225, Visits: 317

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

mister.magoo

Posted Monday, October 08, 2012 10:18 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 12:46 PM
Points: 1,290, Visits: 3,860

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

Post #1369933

opc.three

Posted Monday, October 08, 2012 1:52 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 2:29 PM
Points: 6,715, Visits: 11,752

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

JimS-Indy

Posted Tuesday, October 09, 2012 7:08 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, May 10, 2013 7:30 AM
Points: 225, Visits: 317

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

opc.three

Posted Tuesday, October 09, 2012 10:40 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 2:29 PM
Points: 6,715, Visits: 11,752

JimS-Indy (10/9/2012)

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

JimS-Indy

Posted Tuesday, October 09, 2012 10:51 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, May 10, 2013 7:30 AM
Points: 225, Visits: 317

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

opc.three

Posted Tuesday, October 09, 2012 10:59 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 2:29 PM
Points: 6,715, Visits: 11,752

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

JimS-Indy

Posted Tuesday, October 09, 2012 12:08 PM

SSC Veteran

Group: General Forum Members
Last Login: Friday, May 10, 2013 7:30 AM
Points: 225, Visits: 317

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

mister.magoo

Posted Tuesday, October 09, 2012 3:54 PM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 12:46 PM
Points: 1,290, Visits: 3,860

JimS-Indy (10/9/2012)

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

Post #1370602

opc.three

Posted Tuesday, October 09, 2012 4:34 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 2:29 PM
Points: 6,715, Visits: 11,752

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 »

11 posts, Page 1 of 2

1 2 »»

Permissions