SQL Connectivity by Web App

G

Guest

Hi,

To start with, i'm not too sure if this is the right group to be posting in
so please let me know if these is a more suitable one.

I am trying to get my head around the mechanics behind .NET web applications
accessing SQL databases located on another pc. My current scenario is that
both PC's (IIS5 and SQL2000) are on the same domain but are physically
different. I wish to retrieve data from the database to include in the
asp.net application. As far as I can see I have two options when building
the connection:

a) pass credentials in a connection string;
b) change the impersonated user to a domain account with sql priivaleges.

I notice that the SQL box is set to use windows authentication. Does this
make a difference?

What I am looking for is are links to resources discussing the matter to
determine which method is best, if the are other methods and how to set up
the solution.

Any help would be appreciated.

Regards,

<M>ike
 
A

Alek Davis

Mike,

The most common approach to this common problem is to specify SQL
credentials in the connection string. The downside here is that you need to
protect the connection string at storage, which is a challenge, but unless
you have certain specific conditions, you do not have many alternatives. If
you want to propagate user's credentials to SQL server, you have to enable
delegation at the AD domain level, which is not a good idea from security
perspective. And what is worse, your app will not be able to use connection
pooling, so the scalability goes down he drain. If you want to connect to
SQL Server using credentials of the IIS worker process, you either need to
run the IIS process as a domain user or set them identically on both the SQL
serve and Web server using a local account (with the same password). I don't
think that either of these options is good, because if you do this (for one,
any application running under your Web site will be able to connect to SQL
server with privileged rights). The bottom line here is that you will
introduce more problems than you solve. Just go with the SQL credentials in
the connection string. From my experience, this is what most enterprise apps
do.

Alek
 
G

Guest

Thanks AleK,

That certainly sounds good and reinforces what I was thinking. I think I
will try to save the credentials in the Web.config file as AppSetting keys
so they are not saved in each page and should be more secure.

Cheers,

<M>ike
 
G

Guest

Top Tip:

I've also noticed that if you're using passed credentials to connect to the
SQL box it helps if the server is set to accept both SQL Server and Windows
authentication, otherwise you kep getting (and getting and getting) the good
old 'Not a Trusted Connection' error message.

<M>ike
 
G

Guest

Any top tips on how to encrypt these credentials. I guess the details are
decrypted by a function in a class within the project every time they are
needed?

<M>ike
 
M

Maras

Any top tips on how to encrypt these credentials. I guess the details are
decrypted by a function in a class within the project every time they are
needed?
Read about aspnet_setreg tool.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,743
Messages
2,569,478
Members
44,899
Latest member
RodneyMcAu

Latest Threads

Top