For intranet development, our DBAs are asking web developers to use fixed domain NT ID accounts instead of SQL accounts to connect to backend databases in all web applications. 

We are: Windows XP workstations in a 2003 Active Directory Topology.

I don’t think that this is a good idea (I could say more:) but I have found very little to no information on this subject.

So I ask you guys... What are your thoughts? Why or why not?

When your intranet uses impersonation, it's a good idea, because then, you can control access to the data once and for all with the Active Directory, and you don't have to store any password in your source code.

Jos

Well, less work for DBA's

Let the Networking guys handle the new accounts/password changes/account lockouts.... etc..

I agree but impersonation is not used and so passing the viewers credentials to the SQL server is not possible on our network. (unless iis and sql server are the same box)

Also true

ndinakar:

Well, less work for DBA's

Let the Networking guys handle the new accounts/password changes/account lockouts.... etc..

I wanted to add that I was told an NT ID will be created (one for each database) and assigned dbo to the database.

I just did a quick look at how I would set the connection string in one of my asp.net applications and I didn't see where I could replace the SQL account with an NT ID account?

I also didn't see where I could set an NT ID in the ODBC for the classic asp applications.

I didn't think this was piratical but I assumed it was possible. Is this even possible?

Using NT ID will build trusted connections against your database so in a certain view, it's more safer than sql authentication.

I just did a quick look at how I would set the connection string in one of my asp.net applications and I didn't see where I could replace the SQL account with an NT ID account?

Put "Intergrated Security=True" in your connection string instead of sql user-name and password (Non't put windows ID/password in the connection string anymore), when your asp.net process accessing sql database, it will use the windows account assigned(NETWORK SERVICE in windos 2003 by defautl, while this value is configurable) automatically. And since you are building an intranet, impersonation is also a good idea.

Hope my suggestion helps

wfudge:

our DBAs are asking web developers to use fixed domain NT ID accounts instead of SQL account

Totally agree that this is the best way to do things -- and it's easy.  We set up a system account (ie, a windows group) to run everything under and use impersonation to become that account.  Impersonation can be easily done using the aspnet_setreg.exe utility to store the impersonated userid/pwd in encrypted strings in the registry (see link below for examples).  In your web.config you put something like this:

<identity impersonate="true"
userName="registry:HKLM\SOFTWARE\MY_SECURE_APP\identity\ASPNET_SETREG,userName"
password="registry:HKLM\SOFTWARE\MY_SECURE_APP\identity\ASPNET_SETREG,password" />

to do the actual impersonation.  You need to make sure the asp.net worker process has the permission to read this registry key, see http://support.microsoft.com/default.aspx/kb/329290 for details

You would then grant the system account  (ie, the windows group) all the needed permissions in your database -- that way you don't have to give your users any database permissions at all.

You can also lock down your web site itself by using the "allow/deny" feature of web.config, this is called URL control. 

See http://msdn2.microsoft.com/en-us/library/wce3kxhd.aspx. 

What we do in our shop is to create yet another system account, put all the users into that account, and allow that account and deny all others.  For example:

    <authorization>
        <allow roles="DOMAIN\AppGroup" />
        <allow users="DOMAIN\dbland07666"/>
        <deny users="*" />

In this example, everyone who is a member of AppGroup plus user dbland07666 has the authority to reach this URL.  You could make AppGroup the same windows group used for your database permissions, but that may reduce your flexibility in case you want to eg give some peopel r/o permissions in the DB and others r/w.

You can take this a step further by controlling the ACL on the folders themselves so no one can even see the contents of the folder in Windows Explorer (we do this for one highly sensitive web service).   Only let members of AppGroup see the folders to begin with.

This is very tight security if you use all these levels.  And, of course, you can use SSL too if you need to.