SQLServerCentral / SQL Server 2008 / SQL Server Newbies / ASP.NET App and SQL Server identity / Latest PostsInstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 11:12:16 GMT20RE: ASP.NET App and SQL Server identityhttp://www.sqlservercentral.com/Forums/Topic1369846-1292-1.aspx[quote][b]opc.three (10/9/2012)[/b][hr][quote][b]mister.magoo (10/9/2012)[/b][hr]Obviously, you must ensure the web site has only ASP.NET Impersonation and Windows Authentication enabled in the "Authentication" settings for this to work...[/quote]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 intact2. 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[/quote]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 [u]have[/u] to have #2 though - but I would usually :-)Tue, 09 Oct 2012 16:36:17 GMTmister.magooRE: ASP.NET App and SQL Server identityhttp://www.sqlservercentral.com/Forums/Topic1369846-1292-1.aspx[quote][b]mister.magoo (10/9/2012)[/b][hr]Obviously, you must ensure the web site has only ASP.NET Impersonation and Windows Authentication enabled in the "Authentication" settings for this to work...[/quote]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 intact2. 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/passwordEdit: 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 #1Tue, 09 Oct 2012 16:34:19 GMTopc.threeRE: ASP.NET App and SQL Server identityhttp://www.sqlservercentral.com/Forums/Topic1369846-1292-1.aspx[quote][b]JimS-Indy (10/9/2012)[/b][hr]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?[/quote]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[code="xml"]<?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>[/code]And the web page:[code="xml"]<%@ 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>[/code]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.Tue, 09 Oct 2012 15:54:16 GMTmister.magooRE: ASP.NET App and SQL Server identityhttp://www.sqlservercentral.com/Forums/Topic1369846-1292-1.aspx[quote]I'm also really confused about App pools. Any resources on those? They look cool, and maybe dangerous?They are not dangerous at all. [/quote]Well, maybe in my hands....lolThanks, I'll do some reading tonight!Tue, 09 Oct 2012 12:08:03 GMTJimS-IndyRE: ASP.NET App and SQL Server identityhttp://www.sqlservercentral.com/Forums/Topic1369846-1292-1.aspx[quote][b]JimS-Indy (10/9/2012)[/b][hr]I see. Any resources on how to verify the user is in an AD group?[/quote][u][url=http://msdn.microsoft.com/en-us/library/system.directoryservices.accountmanagement(v=vs.110).aspx].NET System.DirectoryServices.AccountManagement Namespace[/url][/u][quote]I'm also really confused about App pools. Any resources on those? They look cool, and maybe dangerous?[/quote]They are not dangerous at all. [u][url=http://technet.microsoft.com/en-us/library/cc753449(v=ws.10).aspx]Managing Application Pools in IIS 7[/url][/u]Tue, 09 Oct 2012 10:59:31 GMTopc.threeRE: ASP.NET App and SQL Server identityhttp://www.sqlservercentral.com/Forums/Topic1369846-1292-1.aspxI 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:[url]http://stackoverflow.com/questions/1032351/how-to-write-ldap-query-to-test-if-user-is-member-of-a-group[/url]Tue, 09 Oct 2012 10:51:45 GMTJimS-IndyRE: ASP.NET App and SQL Server identityhttp://www.sqlservercentral.com/Forums/Topic1369846-1292-1.aspx[quote][b]JimS-Indy (10/9/2012)[/b][hr]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?[/quote]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.Tue, 09 Oct 2012 10:40:49 GMTopc.threeRE: ASP.NET App and SQL Server identityhttp://www.sqlservercentral.com/Forums/Topic1369846-1292-1.aspxYeah, 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?Tue, 09 Oct 2012 07:08:01 GMTJimS-IndyRE: ASP.NET App and SQL Server identityhttp://www.sqlservercentral.com/Forums/Topic1369846-1292-1.aspxThis article is a good primer on the different options and still applies to current versions of .NET:[u][url=http://support.microsoft.com/kb/306158]How to implement impersonation in an ASP.NET application[/url][/u]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 [u][url=http://blogs.technet.com/b/askds/archive/2008/06/13/understanding-kerberos-double-hop.aspx]double-hop[/url][/u]) and requires some work on the system-side to setup SPNs.Mon, 08 Oct 2012 13:52:12 GMTopc.threeRE: ASP.NET App and SQL Server identityhttp://www.sqlservercentral.com/Forums/Topic1369846-1292-1.aspxI 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.Mon, 08 Oct 2012 10:18:05 GMTmister.magooASP.NET App and SQL Server identityhttp://www.sqlservercentral.com/Forums/Topic1369846-1292-1.aspxWell, 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?Mon, 08 Oct 2012 08:25:12 GMTJimS-Indy