Fog Creek Software
Discussion Board




Welcome! and rules

Joel on Software

ASP.Net database security basics

What's the best security method for data access in a .Net app - Windows NT Integrated security, or create a user in SQL Server and pass the uid & pwd in the connection string?

At this point I'm just playing around with ASP.NET, using IIS on my local machine, but I figured I might as well get in the habit of using the preferred method, if there is one.

ASP.NET Newbie
Friday, August 15, 2003

This has been discussed before but I can't find it...

We do the latter ("create a user in SQL Server and pass the uid & pwd in the connection string"). The former (Windows Integrated) is fraught with issues:
  * Either your db server and IIS server are in the same domain or you have to keep the usernames/password sync'ed betwen the two machines. IIS servers are really best kept isolated trust-wise.
  * Getting the web app running under a specific user account is troublesome: ASP.NET only has one account for running the whole ASP.NET process - you can't grant different web apps different accounts (although SQL's Application Roles are designe to mitigate this, and have apps share an account and call sp_setapprole to assume a different role - doesn't sound great to me).
  * Ooh there's a bunch of other things I can't remember..

Whereas using good ole' plain SQL accounts and embedding the "UID=...PWD=..." in the connection string is very simple. If you're bothered about it being "out in the open" in the web.config (ie people don't trust the ACLs on the file) then encrypt it with DPAPI (so you don't have to worry about the encryption keys) -- see http://msdn.microsoft.com/library/en-us/dnnetsec/html/SecNetHT08.asp

HTH

Duncan Smart
Friday, August 15, 2003

*  Recent Topics

*  Fog Creek Home