connecting to sql server with windows authentication

[Mark]

I'm confounded how difficult it is to setup a connection from an ASP.NET
application to SQL Server on a different machine in the same windows domain
using windows authentication. My research has found the following options:

1. Use delegation to leverage the current user's account.
2. Replace the ASPNET local account that is running .NET applications on the
web server with a domain account.
3. Use impersonation specifiying a specific domain user and password in the
web.config.

In options 2 and 3 above, the new account must be granted all the rights
that the ASPNET account comes with by default. Moreover, if you have a
development machine, a live machine, and local installs of IIS for all your
developers, the rights must be recreated on every blasted box. That sounds
like a maintenance nightmare. Option 1 raises all sorts of security
concerns. Understandably, our DBA wants to keep SQL Server authentication
turned off since we have a windows network.

Am I missing something here? What is the "obvious" choice?

Thanks in advance.

Mark

 

[Alek Davis]

Hi Mark,

No, you are not missing anything: there is no silver bullet here. Because of
the issues you described, a typical approach in enterprise applications (at
least the ones I was involved in), would be to use SQL authentication. While
this is not the perfect option, it has potential to cause less issues
(security, administration, configuration, troubleshooting, etc.) then other
choices. The major hassle in this scenario is storage of SQL credentials (or
connection strings), but even though it is quite a challenge (if you want to
do it correctly), with some ingenuity it can be done. Even though MSFT does
not recommend running SQL Server in mixed (authentication) mode, in many
cases this recommendation is simply not practical. Frankly, comparing mixed
mode to security risks of other options (e.g. the ones you listed), I do not
see what the fuss is about.

Alek