January 16, 2007 at 6:54 am
Web server Windows 2003, IIS6, ASP.NET2.0
Database server Windows 2003, MS-SQL 2005
We are rebuilding our websites in ASP.NET and my webmaster is telling me that he can't connect from ASP.NET to SQL2005 using anything other than a standard SQL login, unless we switch to Windows Authentication with all users entered individually in the database server.
This is for an intranet site, with all users having our URL listed as an Intranet Site in Internet Explorer, and all users required to use IE5.5 and up (most are using IE6).
Many of the updated websites are essentially anonymous login, but a few use Scott Guthrie's Windows Authentication validation (http://weblogs.asp.net/scottgu/archive/2006/07/12/Recipe_3A00_-Enabling-Windows-Authentication-within-an-Intranet-ASP.NET-Web-application.aspx)
That compares the user's Windows account to roles held in a database to give correct access.
I would prefer to make my SQL box Windows Authentication only, but I do not want to create logins for all of our users.
With our previous web software, we had a single domain account in Windows Active Directory and used that account for all web/sql activity, via system DSN connections. We could pick up the Windows Authentication info and compare it to a roles database, but still use the domain account for web/sql communications.
Is a similar setup (one that can collect user Windows account info but uses a single Windows account for web/sql connections) truly not possible with ASP.NET?
Thanks very much.
January 17, 2007 at 1:31 am
In short yes, it is possible but there are a number of approaches.
1) Use integrated security/windows authentication and add the impersonate="true" switch to the web.config file. You will also need to set up active directory delegation for this scenario to work (as your web and database servers are on different boxes). Personally, I've always struggled to get delegation to work but if you've got good active directory guys this might be an option. In this case you can assign sql permissions on tables/stored procs etc to windows groups not individual users as your webmaster is suggesting.
2) An approach I have used successfully in the past is enable integrated security/windows authentication in the web.config and in IIS but disable impersonation. Then if you only need a trusted sql connection you can use the Network Service account's domain credentials and add that to sql (i.e. DomainName\IISServerName$) as this is the account that (by default) the ASP.NET application will be running under.
3) A slight enhancement to option 2 was needed when I needed a list of the users domain groups that they were a member of IN the database. In that instance I configured the website to run under an IIS6 application pool whose identity was a domain account with limited priviledges but enough to query active directory and also connect to sql. When a user logged on I queried AD, extracted their group membership and passed that to sql so it could be stored against the userid. Then any future calls to the database passed the id of the calling user which could be simply joined to the user/roles tables in the database.
There will inevitably be other approaches but I've usually ended up using either 2 or 3. Option 3 sounds like it would achieve what you want.
Hope this helps
January 17, 2007 at 1:38 am
Also, just found this which is a series of technet articles covering (in more details, and almost certainly better english) the approaches I touched on
January 19, 2007 at 9:02 am
Thanks very much for the assistance. In your link, it looks like we want to combine windows integrated authentication with trusted subsystem authorization. I knew this should be possible!
Thank you!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply