I am deploying IIS (w/ASP.NET) and SQL on two seperate boxes. I am having
trouble using a trusted connection between the two. I have found many
suggested solutions from many "experts" for dealing with this, but I'm
looking for the most secure option. The documentation I've found to date
doens't discuss the ramifications of different options. Also, a lot of the
suggestions seem to be incomplete. Can someone help me with a secure solution
for this?

a) What is a "secure" solution in your mind? What are the risks you need to
mitigate?

b) What are the requirements of your application? do you want to use a fixed
identity to connect to SQL Server (ala Trusted SubSystem model)? Do you want
the user's credentials to flow all the way back to SQL Server?

There are several different ways you can go about doing this, and each has
pros/cons. You need to tell us what you want to do, and then we can tell
you. Asking for a "secure" solution is pointless. there is no such thing as
the secure solution, only solutions that are more secure than others.
However with greater security comes less convenience, and that tradeoff you
need to decide yourself.

Cheers
Ken

Show quoteHide quote

Secure in my mind is something that doesn't expose a known vulnerability or
make use of a risky solution.

My application requires access to retrieve, insert and modify data from the
SQL server. I'm concerned that putting a username and password for an account
in the web.config file is not the safest way to go, but I may just be too
suspicious for no reason. Do you have any thoughts on that? I would prefer to
use a trusted connection, but I'm having a hard time sifting through info
available to make an informed choice of how to implement it. If you're
familiar with the pros and cons of various methods, I'd love to get your
insight.

This application is for an internal intranet that will eventually be made
available via subportals to the Internet, so I am concerned with securing it
from unauthorized data access "as much as possible".

This is a circular definition. You are defining "secure" by reference to
something that is the opposite. But what is "risky"? What might be risky for
you is not risky for someone else.

If you do store a password somewhere, and someone gets it and uses it to
login to your SQL Server is that exploiting a "known vulnerability"? No, it
isn't. The vulnernability would only exist to facilitate the theft of the
user credentials in the first place. And the credentials could be stolen on
the server (e.g. from a web.config file if you are using a static account),
or from the user's PC (e.g. someone compromises the user's machine and
installs a key logger program).


Benefits of trusted connection:
a) no username/passwords stored in clear text in the connection string
(obviously, if you are forcing ASP.NET to impersonate a static account, you
will need to give ASP.NET those credentials somehow, and you will need to
protect the storage of those credentials)

b) leverage the Windows security model (e.g. force users to have complex
passwords, allow for account lockout, allow for password expiration, allow
logging of logon failures to the Windows event log)

c) single storage of credentials simplifies administration: you manage a set
of Windows user accounts via ADU&C, and there's no need to manage separate
accounts in SQL Server as well

Cons?
a) If you are flowing the user's credentials all the way back to SQL Server,
then you defeat connection pooling.

b) Alterantively, you have have ASP.NET use a set account to connect to SQL
Server, but that doesn't allow for granual control of permissions (since
there is only one account being used to access SQL Server). Your application
will need to implement it's own authorization system to determine which
users can do what.

c) You will need to use something like Kerberos to flow the user's
credentials back to SQL Server if you want to have a "more secure" solution.
Kerberos supports delegation. Otherwise you will need to use an insecure
authentication schem (Basic) so that IIS has the user's username/password to
directly impersonate the user.


Well, you probably need to look more at the design of your application. If
someone can subvert the application (e.g. via SQL Injection, XSS etc), they
can do whatever your user account can do in SQL Server. There's no need to
steal a username/password.

Here's a bunch of articles that can help you work through this stuff:

Here are a few articles to get you started:

IMPORTANT:
Read chapter 12 from the Building Secure ASP.Net Application Book - it has
very good information about building scalable, secure ASP.Net applications
(eg using a trusted subsystem model):
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/secnetlpMSDN.asp?frame=true

http://support.microsoft.com/?id=319723
INF: SQL Server 2000 Kerberos support including SQL Server virtual servers
on server clusters

http://support.microsoft.com/default.aspx?scid=kb;en-us;810572
HOW TO: Configure an ASP.NET Application for a Delegation Scenario

http://support.microsoft.com/?id=294382

Authentication May Fail with "401.3" Error If Web Site's "Host Header"
Differs from Server's NetBIOS Name

http://support.microsoft.com/default.aspx?kbid=325894
HOW TO: Configure Computer Accounts and User Accounts So That They Are
Trusted for Delegation in Windows Server 2003 Enterprise Edition (also
includes Windows 2000 instructions)

http://www.microsoft.com/resources/documentation/WindowsServ/2003/standard/proddocs/en-us/Default.asp?url=/resources/documentation/WindowsServ/2003/standard/proddocs/en-us/se_con_del_computer.asp
Configuring Users and Computers for delegation (there's a couple of pages -
use the links in the nav bar to get to them)

Windows 2003 Protocol Transition
http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/constdel.mspx


Cheers
Ken

On Tue, 10 May 2005 12:44:05 -0700, "Kimberly"
<Kimbe***@discussions.microsoft.com> wrote:

Have you looked at:

HOWTO: IIS and SQL Server on Separate Machines with Trusted
Connection:
http://support.microsoft.com/default.aspx?scid=kb;en-us;q176379

Jeff

Please help, directory level protection needed.
ASP.NET, IIS 6 Integrated Win Authentication, Domain usage
Multiple SSL identities on the same E3K front end server
HTTP Error 401.1 - Unauthorized: Access is denied due to invalid credentials. Digest authentication
IIS 6 Logfiles inherit permissions attribute
how do i block port 220
Guestbook in Frontpage
"the function requested is not supported" on IIS6 with Win2K client
Selfssl.exe for multiple vhosts
Need a free statistics tools for IIS