SQL Connectivity by Web App

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

[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

"<M>ike" <mikedotdinnisatabraxas-ukdotcom> wrote in message
news:uO3P$j6VEHA.2840@TK2MSFTNGP11.phx.gbl...

> 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
>
>

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


"Alek Davis" <alek_xDOTx_davis_xATx_intel_xDOTx_com> wrote in message
news:OB9NoC7VEHA.3596@tk2msftngp13.phx.gbl...

> 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
>
> "<M>ike" <mikedotdinnisatabraxas-ukdotcom> wrote in message
> news:uO3P$j6VEHA.2840@TK2MSFTNGP11.phx.gbl...

> > 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
> >
> >

>
>

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

[Alek Davis]

Just make sure that the credentials are encrypted.

Alek

"<M>ike" <mikedotdinnisatabraxas-ukdotcom> wrote in message
news:e1tvO1DWEHA.2716@tk2msftngp13.phx.gbl...

> 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
>
>
> "Alek Davis" <alek_xDOTx_davis_xATx_intel_xDOTx_com> wrote in message
> news:OB9NoC7VEHA.3596@tk2msftngp13.phx.gbl...

> > 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
> >
> > "<M>ike" <mikedotdinnisatabraxas-ukdotcom> wrote in message
> > news:uO3P$j6VEHA.2840@TK2MSFTNGP11.phx.gbl...

> > > 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
> > >
> > >

> >
> >

>
>

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

"Alek Davis" <alek_xDOTx_davis_xATx_intel_xDOTx_com> wrote in message
news:uuoHeGHWEHA.556@tk2msftngp13.phx.gbl...

> Just make sure that the credentials are encrypted.
>
> Alek
>
> "<M>ike" <mikedotdinnisatabraxas-ukdotcom> wrote in message
> news:e1tvO1DWEHA.2716@tk2msftngp13.phx.gbl...

> > 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
> >
> >
> > "Alek Davis" <alek_xDOTx_davis_xATx_intel_xDOTx_com> wrote in message
> > news:OB9NoC7VEHA.3596@tk2msftngp13.phx.gbl...

> > > 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
> > >
> > > "<M>ike" <mikedotdinnisatabraxas-ukdotcom> wrote in message
> > > news:uO3P$j6VEHA.2840@TK2MSFTNGP11.phx.gbl...
> > > > 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
> > > >
> > > >
> > >
> > >

> >
> >

>
>

[Maras]

On Wed, 23 Jun 2004 11:50:37 +0100, <M>ike wrote:

> 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.

--
Best regards
Maras

[Alek Davis]

This is a tough problem to solve and depending on your environment some
approaches may be worse than others. Check the "Protect It: Safeguard
Database Connection Strings and Other Sensitive Settings in Your Code"
article at [url]http://msdn.microsoft.com/msdnmag/issues/03/11/ProtectYourData/[/url],
it addresses this topic.

Alek

"<M>ike" <mikedotdinnisatabraxas-ukdotcom> wrote in message
news:uR8ls$QWEHA.2340@TK2MSFTNGP09.phx.gbl...

> 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
>
> "Alek Davis" <alek_xDOTx_davis_xATx_intel_xDOTx_com> wrote in message
> news:uuoHeGHWEHA.556@tk2msftngp13.phx.gbl...

> > Just make sure that the credentials are encrypted.
> >
> > Alek
> >
> > "<M>ike" <mikedotdinnisatabraxas-ukdotcom> wrote in message
> > news:e1tvO1DWEHA.2716@tk2msftngp13.phx.gbl...

> > > 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
> > >
> > >
> > > "Alek Davis" <alek_xDOTx_davis_xATx_intel_xDOTx_com> wrote in message
> > > news:OB9NoC7VEHA.3596@tk2msftngp13.phx.gbl...
> > > > 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
> > > >
> > > > "<M>ike" <mikedotdinnisatabraxas-ukdotcom> wrote in message
> > > > news:uO3P$j6VEHA.2840@TK2MSFTNGP11.phx.gbl...
> > > > > 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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >

> >
> >

>
>