Best place for connection string

[martin de vroom]

Hi,

At present I have an include file that defines my database connection
string. This file is inluded on every page that uses database access.
However I would like to ask if a better place for this string would be in an
pplication variable.
I know it is not a good idea to cache the actually database connection, but
in this case I am only talking about the actual string.

One other point is that where ever the connection string is it currently had
the database username and password (sql authentication)
hard coded into it, is this the standard ay to do things or is there a trick
I can employ to hide the password, that is not too expensive.
I thought about placing it in the registry, but then maybe a trip to the
registry before every database connection is not a good idea.



cheers

martin.

[Ken Schaefer]

You could use a DSN, in which case the connection string parameters are
stored in the database, not hard coded in clear in your global.asa file.

On the other hand, it is preferable to use OLEDB Providers rather than ODBC
Drivers.

If you are using SQL Server, then you can switch to Windows Integrated
Authentication (rather than Mixed Mode). This means that you (or your ASP
page) logs in to SQL Server using its Windows account rather than
username/password passed in via the connection string. This means you have
no password anywhere in the clear.

Cheers
Ken

"martin de vroom" <martindevroom@hotmail.com> wrote in message
news:eGDWvkySDHA.2084@TK2MSFTNGP11.phx.gbl...
: Hi,
:
: At present I have an include file that defines my database connection
: string. This file is inluded on every page that uses database access.
: However I would like to ask if a better place for this string would be in
an
: pplication variable.
: I know it is not a good idea to cache the actually database connection,
but
: in this case I am only talking about the actual string.
:
: One other point is that where ever the connection string is it currently
had
: the database username and password (sql authentication)
: hard coded into it, is this the standard ay to do things or is there a
trick
: I can employ to hide the password, that is not too expensive.
: I thought about placing it in the registry, but then maybe a trip to the
: registry before every database connection is not a good idea.
:
:
:
: cheers
:
: martin.
:
:

[Chris Hohmann]

"martin de vroom" <martindevroom@hotmail.com> wrote in message
news:eGDWvkySDHA.2084@TK2MSFTNGP11.phx.gbl...

> Hi,
>
> At present I have an include file that defines my database

connection

> string. This file is inluded on every page that uses database access.
> However I would like to ask if a better place for this string would be

in an

> pplication variable.
> I know it is not a good idea to cache the actually database

connection, but

> in this case I am only talking about the actual string.
>
> One other point is that where ever the connection string is it

currently had

> the database username and password (sql authentication)
> hard coded into it, is this the standard ay to do things or is there a

trick

> I can employ to hide the password, that is not too expensive.
> I thought about placing it in the registry, but then maybe a trip to

the

> registry before every database connection is not a good idea.
>
>
>
> cheers
>
> martin.
>
>

OS? Database? Version? MDAC?

Consider using a Microsoft Datalink file.
[url]http://msdn.microsoft.com/library/en-us/oledb/htm/olprcore_chapter20_6.asp[/url]

The file can be stored outside the application root and your connection
string is simply a reference to the file. Therefore, even if there is an
unauthorized access to your application directory or an unhandled
exception is exploited to reveal the connection string, sensitive
information like database username/password is not exposed.

HTH
-Chris

[martin de vroom]

Thanks for that,

I'd prefer not to use a DSN.

however, how does connecting to SQL Server using windows integrated security
affect connection pooling.
for example using a sql authentication string for every connection ensures
that the connection strring is always the same.

admittedly it will be the same every time however it will pass different
credentials most times.
I take it that I will have to pick the windows ID up using NTLM, this is a
great solution for an intranet - which is the enviroment i am in -
as i will just add the evryone group to sql server and grant the appropraite
permissions.
however I would be interested to know if this affects connction pooling.

Is there anyway to test this.

cheers

martin



"Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
news:#0JMsyySDHA.2460@TK2MSFTNGP10.phx.gbl...

> You could use a DSN, in which case the connection string parameters are
> stored in the database, not hard coded in clear in your global.asa file.
>
> On the other hand, it is preferable to use OLEDB Providers rather than

ODBC

> Drivers.
>
> If you are using SQL Server, then you can switch to Windows Integrated
> Authentication (rather than Mixed Mode). This means that you (or your ASP
> page) logs in to SQL Server using its Windows account rather than
> username/password passed in via the connection string. This means you have
> no password anywhere in the clear.
>
> Cheers
> Ken
>
> "martin de vroom" <martindevroom@hotmail.com> wrote in message
> news:eGDWvkySDHA.2084@TK2MSFTNGP11.phx.gbl...
> : Hi,
> :
> : At present I have an include file that defines my database connection
> : string. This file is inluded on every page that uses database access.
> : However I would like to ask if a better place for this string would be

in

> an
> : pplication variable.
> : I know it is not a good idea to cache the actually database connection,
> but
> : in this case I am only talking about the actual string.
> :
> : One other point is that where ever the connection string is it currently
> had
> : the database username and password (sql authentication)
> : hard coded into it, is this the standard ay to do things or is there a
> trick
> : I can employ to hide the password, that is not too expensive.
> : I thought about placing it in the registry, but then maybe a trip to the
> : registry before every database connection is not a good idea.
> :
> :
> :
> : cheers
> :
> : martin.
> :
> :
>
>

[Jeff Cochran]

On Wed, 16 Jul 2003 11:30:04 +1200, "martin de vroom"
<martindevroom@hotmail.com> wrote:

> At present I have an include file that defines my database connection
>string. This file is inluded on every page that uses database access.
>However I would like to ask if a better place for this string would be in an
>pplication variable.
>I know it is not a good idea to cache the actually database connection, but
>in this case I am only talking about the actual string.

That might work, though OI have to admit I only use includes to handle
connections. Global.asa is another option.

>One other point is that where ever the connection string is it currently had
>the database username and password (sql authentication)
>hard coded into it, is this the standard ay to do things or is there a trick
>I can employ to hide the password, that is not too expensive.

Hide from whom? Assuming this is ASP, code is executed on the server
and not sent to the client.

Jeff
===================================
Jeff Cochran (IIS MVP)
[email]jcochran.nospam@naplesgov.com[/email] - Munged of Course

I don't get much time to respond to direct email,
so posts here will have a better chance of getting
an answer. Besides, everyone benefits here.

Suggested resources:
[url]http://www.iisfaq.com/[/url]
[url]http://www.iisanswers.com/[/url]
[url]http://www.iistoolshed.com/[/url]
[url]http://securityadmin.info/[/url]
[url]http://www.aspfaq.com/[/url]
[url]http://support.microsoft.com/[/url]
====================================

[Ken Schaefer]

If you are allowing IIS anonymous access then all access to SQL Server will
be under the IUSR_<machinename> (or whatever you have configured in IIS), so
connection pooling will still work.

If you are forcing users to authenticate against the Windows accounts
database (eg Basic or Integrated Authentication), then I'm not entirely sure
what you can do - maybe SQL Server app roles could be of use here.

Cheers
Ken

"martin de vroom" <martindevroom@hotmail.com> wrote in message
news:eS4dE%23ySDHA.1912@tk2msftngp13.phx.gbl...
: Thanks for that,
:
: I'd prefer not to use a DSN.
:
: however, how does connecting to SQL Server using windows integrated
security
: affect connection pooling.
: for example using a sql authentication string for every connection ensures
: that the connection strring is always the same.
:
: admittedly it will be the same every time however it will pass different
: credentials most times.
: I take it that I will have to pick the windows ID up using NTLM, this is a
: great solution for an intranet - which is the enviroment i am in -
: as i will just add the evryone group to sql server and grant the
appropraite
: permissions.
: however I would be interested to know if this affects connction pooling.
:
: Is there anyway to test this.
:
: cheers
:
: martin
:
:
:
: "Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
: news:#0JMsyySDHA.2460@TK2MSFTNGP10.phx.gbl...
: > You could use a DSN, in which case the connection string parameters are
: > stored in the database, not hard coded in clear in your global.asa file.
: >
: > On the other hand, it is preferable to use OLEDB Providers rather than
: ODBC
: > Drivers.
: >
: > If you are using SQL Server, then you can switch to Windows Integrated
: > Authentication (rather than Mixed Mode). This means that you (or your
ASP
: > page) logs in to SQL Server using its Windows account rather than
: > username/password passed in via the connection string. This means you
have
: > no password anywhere in the clear.
: >
: > Cheers
: > Ken
: >
: > "martin de vroom" <martindevroom@hotmail.com> wrote in message
: > news:eGDWvkySDHA.2084@TK2MSFTNGP11.phx.gbl...
: > : Hi,
: > :
: > : At present I have an include file that defines my database
connection
: > : string. This file is inluded on every page that uses database access.
: > : However I would like to ask if a better place for this string would be
: in
: > an
: > : pplication variable.
: > : I know it is not a good idea to cache the actually database
connection,
: > but
: > : in this case I am only talking about the actual string.
: > :
: > : One other point is that where ever the connection string is it
currently
: > had
: > : the database username and password (sql authentication)
: > : hard coded into it, is this the standard ay to do things or is there a
: > trick
: > : I can employ to hide the password, that is not too expensive.
: > : I thought about placing it in the registry, but then maybe a trip to
the
: > : registry before every database connection is not a good idea.
: > :
: > :
: > :
: > : cheers
: > :
: > : martin.
: > :
: > :
: >
: >
:
:

[martin de vroom]

Hi Ken,

As I understand app roles, you still have to connect using either sql or
windows authentication, and then execute the sp to make app roles kick in.
maybe I'll just stick with sql server authtitcation first.

any idea how i would test how logging on with windows authentication affects
connection pooling.

cheers

martin.


"Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
news:uIbYLt7SDHA.2276@TK2MSFTNGP10.phx.gbl...

> If you are allowing IIS anonymous access then all access to SQL Server

will

> be under the IUSR_<machinename> (or whatever you have configured in IIS),

so

> connection pooling will still work.
>
> If you are forcing users to authenticate against the Windows accounts
> database (eg Basic or Integrated Authentication), then I'm not entirely

sure

> what you can do - maybe SQL Server app roles could be of use here.
>
> Cheers
> Ken
>
> "martin de vroom" <martindevroom@hotmail.com> wrote in message
> news:eS4dE%23ySDHA.1912@tk2msftngp13.phx.gbl...
> : Thanks for that,
> :
> : I'd prefer not to use a DSN.
> :
> : however, how does connecting to SQL Server using windows integrated
> security
> : affect connection pooling.
> : for example using a sql authentication string for every connection

ensures

> : that the connection strring is always the same.
> :
> : admittedly it will be the same every time however it will pass different
> : credentials most times.
> : I take it that I will have to pick the windows ID up using NTLM, this is

a

> : great solution for an intranet - which is the enviroment i am in -
> : as i will just add the evryone group to sql server and grant the
> appropraite
> : permissions.
> : however I would be interested to know if this affects connction pooling.
> :
> : Is there anyway to test this.
> :
> : cheers
> :
> : martin
> :
> :
> :
> : "Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
> : news:#0JMsyySDHA.2460@TK2MSFTNGP10.phx.gbl...
> : > You could use a DSN, in which case the connection string parameters

are

> : > stored in the database, not hard coded in clear in your global.asa

file.

> : >
> : > On the other hand, it is preferable to use OLEDB Providers rather than
> : ODBC
> : > Drivers.
> : >
> : > If you are using SQL Server, then you can switch to Windows Integrated
> : > Authentication (rather than Mixed Mode). This means that you (or your
> ASP
> : > page) logs in to SQL Server using its Windows account rather than
> : > username/password passed in via the connection string. This means you
> have
> : > no password anywhere in the clear.
> : >
> : > Cheers
> : > Ken
> : >
> : > "martin de vroom" <martindevroom@hotmail.com> wrote in message
> : > news:eGDWvkySDHA.2084@TK2MSFTNGP11.phx.gbl...
> : > : Hi,
> : > :
> : > : At present I have an include file that defines my database
> connection
> : > : string. This file is inluded on every page that uses database

access.

> : > : However I would like to ask if a better place for this string would

be

> : in
> : > an
> : > : pplication variable.
> : > : I know it is not a good idea to cache the actually database
> connection,
> : > but
> : > : in this case I am only talking about the actual string.
> : > :
> : > : One other point is that where ever the connection string is it
> currently
> : > had
> : > : the database username and password (sql authentication)
> : > : hard coded into it, is this the standard ay to do things or is there

a

> : > trick
> : > : I can employ to hide the password, that is not too expensive.
> : > : I thought about placing it in the registry, but then maybe a trip to
> the
> : > : registry before every database connection is not a good idea.
> : > :
> : > :
> : > :
> : > : cheers
> : > :
> : > : martin.
> : > :
> : > :
> : >
> : >
> :
> :
>
>

[Ken Schaefer]

You can use Profiler if you want lots of info.

sp_who / sp_who2 can be used to get a "snap shot" view

Cheers
Ken


"martin de vroom" <martindevroom@hotmail.com> wrote in message
news:eDXVEO%23SDHA.3700@tk2msftngp13.phx.gbl...
: Hi Ken,
:
: As I understand app roles, you still have to connect using either sql
or
: windows authentication, and then execute the sp to make app roles kick in.
: maybe I'll just stick with sql server authtitcation first.
:
: any idea how i would test how logging on with windows authentication
affects
: connection pooling.
:
: cheers
:
: martin.
:
:
: "Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
: news:uIbYLt7SDHA.2276@TK2MSFTNGP10.phx.gbl...
: > If you are allowing IIS anonymous access then all access to SQL Server
: will
: > be under the IUSR_<machinename> (or whatever you have configured in
IIS),
: so
: > connection pooling will still work.
: >
: > If you are forcing users to authenticate against the Windows accounts
: > database (eg Basic or Integrated Authentication), then I'm not entirely
: sure
: > what you can do - maybe SQL Server app roles could be of use here.
: >
: > Cheers
: > Ken
: >
: > "martin de vroom" <martindevroom@hotmail.com> wrote in message
: > news:eS4dE%23ySDHA.1912@tk2msftngp13.phx.gbl...
: > : Thanks for that,
: > :
: > : I'd prefer not to use a DSN.
: > :
: > : however, how does connecting to SQL Server using windows integrated
: > security
: > : affect connection pooling.
: > : for example using a sql authentication string for every connection
: ensures
: > : that the connection strring is always the same.
: > :
: > : admittedly it will be the same every time however it will pass
different
: > : credentials most times.
: > : I take it that I will have to pick the windows ID up using NTLM, this
is
: a
: > : great solution for an intranet - which is the enviroment i am in -
: > : as i will just add the evryone group to sql server and grant the
: > appropraite
: > : permissions.
: > : however I would be interested to know if this affects connction
pooling.
: > :
: > : Is there anyway to test this.
: > :
: > : cheers
: > :
: > : martin
: > :
: > :
: > :
: > : "Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
: > : news:#0JMsyySDHA.2460@TK2MSFTNGP10.phx.gbl...
: > : > You could use a DSN, in which case the connection string parameters
: are
: > : > stored in the database, not hard coded in clear in your global.asa
: file.
: > : >
: > : > On the other hand, it is preferable to use OLEDB Providers rather
than
: > : ODBC
: > : > Drivers.
: > : >
: > : > If you are using SQL Server, then you can switch to Windows
Integrated
: > : > Authentication (rather than Mixed Mode). This means that you (or
your
: > ASP
: > : > page) logs in to SQL Server using its Windows account rather than
: > : > username/password passed in via the connection string. This means
you
: > have
: > : > no password anywhere in the clear.
: > : >
: > : > Cheers
: > : > Ken
: > : >
: > : > "martin de vroom" <martindevroom@hotmail.com> wrote in message
: > : > news:eGDWvkySDHA.2084@TK2MSFTNGP11.phx.gbl...
: > : > : Hi,
: > : > :
: > : > : At present I have an include file that defines my database
: > connection
: > : > : string. This file is inluded on every page that uses database
: access.
: > : > : However I would like to ask if a better place for this string
would
: be
: > : in
: > : > an
: > : > : pplication variable.
: > : > : I know it is not a good idea to cache the actually database
: > connection,
: > : > but
: > : > : in this case I am only talking about the actual string.
: > : > :
: > : > : One other point is that where ever the connection string is it
: > currently
: > : > had
: > : > : the database username and password (sql authentication)
: > : > : hard coded into it, is this the standard ay to do things or is
there
: a
: > : > trick
: > : > : I can employ to hide the password, that is not too expensive.
: > : > : I thought about placing it in the registry, but then maybe a trip
to
: > the
: > : > : registry before every database connection is not a good idea.
: > : > :
: > : > :
: > : > :
: > : > : cheers
: > : > :
: > : > : martin.
: > : > :
: > : > :
: > : >
: > : >
: > :
: > :
: >
: >
:
: