ASP.NET App and SQL Server identity

Question

Post reply

ASP.NET App and SQL Server identity

JimS-Indy

SSCarpal Tunnel

Points: 4514
More actions
October 8, 2012 at 8:25 am

#262977

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
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

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);

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 2

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 SSCarpal Tunnel Points: 4514 More actions · Answer 3

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 SSC Guru Points: 182279 More actions · Answer 4

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 SSCarpal Tunnel Points: 4514 More actions · Answer 5

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 SSC Guru Points: 182279 More actions · Answer 6

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 SSCarpal Tunnel Points: 4514 More actions · Answer 7

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 SSC-Forever Points: 47174 More actions · Answer 8

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

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 9

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

mister.magoo SSC-Forever Points: 47174 More actions · Answer 10

opc.three (10/9/2012)
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

Thanks for adding those details - I sometimes forget because mostly I don't do web work and when I do it is usually not this way!

Edit: Strictly speaking, you don't have to have #2 though - but I would usually 🙂

MM

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