Connection String vs DSN, what to use and why?

Which is better to use a connection string or DSN? Why?

It seems like using a connection string is recommended but what exactly are
the reasons for this? Also, one of the nice things about DSN's is the
ability to quickly change what database your app is pointing to. How is
this done with connection strings? Registry entries? I thought I read
somewhere connections strings being held in the registry should be
encrypted, how should this be accomplished? Is there a semi-standard way of
doing all this?


Thanks for any help you may provide,
Dan

Bob
Mon Oct 27 15:12:08 CST 2003

DSNs apply (AFAIK) only to ODBC access to the database. Native access to
SQL Server (if that's your back end) is much faster. Native or OLE-DB
access to various back ends only works with connection strings.

There are as many ways to store connection strings as there are programmers
I suspect. The basic idea is that since they typically contain passwords,
etc., you need to store them someplace where they are not so vulnerable /
easy for someone to lift. In your source code is generally a bad place, but
what we do in our shop is we have a single project -- a Windows service --
that stores this info and other sensitive items as constants and returns
them to authorized requestors via .NET remoting. The client copy of the
assembly just describes the method signatures -- the actual implementation
is stubbed out with NotSupportedExceptions that state you can only make the
call remotely. In this way the strings don't end up copied all over the
place in distributed assemblies. The assembly that actually has the info
exists in one place only -- on a very secure internal server.

Once remoting is configured and the remote object instance retreived, each
application just makes a call along the lines of:

string strConnection = sharedData.GetData(strAppToken,"ConnectionString");

... to retreive the info appropriate for that app. Typically, this is done
once at startup, then it's stored someplace in memory for use throughout the
application -- e.g., the Application object in an ASP.NET app, loaded during
Application_OnStart().

Other ways to store sensitive info include using configuration files or the
registry ... in both cases, it's best to encrypt the data although in the
case of a web app you can make the argument that the config files are only
accessible to the web app so it's safe to store them in clear text. Still,
having the passwords in clear text even on a supposedly secure machine
that's open in any way to the public makes me nervous.

--Bob

"Daniel" <dhartley@data2you.net> wrote in message
news:eeFmB%23LnDHA.2404@TK2MSFTNGP12.phx.gbl...
> Which is better to use a connection string or DSN? Why?
>
> It seems like using a connection string is recommended but what exactly
are
> the reasons for this? Also, one of the nice things about DSN's is the
> ability to quickly change what database your app is pointing to. How is
> this done with connection strings? Registry entries? I thought I read
> somewhere connections strings being held in the registry should be
> encrypted, how should this be accomplished? Is there a semi-standard way
of
> doing all this?
>
>
> Thanks for any help you may provide,
> Dan
>
>

Daniel
Mon Oct 27 16:29:06 CST 2003

Thanks for the response.

What you say makes a lot of sense. Typically when we did projects, whether
they be ASP applications or VB executables, we used DSN's. We are starting
to move to .Net, and virtually all the examples use connection strings,
which I took as implicitly promoting that method of connecting to a
database. Native access being faster is enough to convince me that it is
worth the extra work (be it encrypting/decrypting a registry key or
whatever).

Thanks,
Dan

"Bob Grommes" <bob@bobgrommes.com> wrote in message
news:#wIKE9MnDHA.1408@TK2MSFTNGP11.phx.gbl...
> DSNs apply (AFAIK) only to ODBC access to the database. Native access to
> SQL Server (if that's your back end) is much faster. Native or OLE-DB
> access to various back ends only works with connection strings.
>
> There are as many ways to store connection strings as there are
programmers
> I suspect. The basic idea is that since they typically contain passwords,
> etc., you need to store them someplace where they are not so vulnerable /
> easy for someone to lift. In your source code is generally a bad place,
but
> what we do in our shop is we have a single project -- a Windows service --
> that stores this info and other sensitive items as constants and returns
> them to authorized requestors via .NET remoting. The client copy of the
> assembly just describes the method signatures -- the actual implementation
> is stubbed out with NotSupportedExceptions that state you can only make
the
> call remotely. In this way the strings don't end up copied all over the
> place in distributed assemblies. The assembly that actually has the info
> exists in one place only -- on a very secure internal server.
>
> Once remoting is configured and the remote object instance retreived, each
> application just makes a call along the lines of:
>
> string strConnection = sharedData.GetData(strAppToken,"ConnectionString");
>
> ... to retreive the info appropriate for that app. Typically, this is
done
> once at startup, then it's stored someplace in memory for use throughout
the
> application -- e.g., the Application object in an ASP.NET app, loaded
during
> Application_OnStart().
>
> Other ways to store sensitive info include using configuration files or
the
> registry ... in both cases, it's best to encrypt the data although in the
> case of a web app you can make the argument that the config files are only
> accessible to the web app so it's safe to store them in clear text.
Still,
> having the passwords in clear text even on a supposedly secure machine
> that's open in any way to the public makes me nervous.
>
> --Bob
>
> "Daniel" <dhartley@data2you.net> wrote in message
> news:eeFmB%23LnDHA.2404@TK2MSFTNGP12.phx.gbl...
> > Which is better to use a connection string or DSN? Why?
> >
> > It seems like using a connection string is recommended but what exactly
> are
> > the reasons for this? Also, one of the nice things about DSN's is the
> > ability to quickly change what database your app is pointing to. How is
> > this done with connection strings? Registry entries? I thought I read
> > somewhere connections strings being held in the registry should be
> > encrypted, how should this be accomplished? Is there a semi-standard
way
> of
> > doing all this?
> >
> >
> > Thanks for any help you may provide,
> > Dan
> >
> >
>
>

Bob
Mon Oct 27 22:24:00 CST 2003

Just be aware that "faster" is a relative term here. Certain data access
patterns aren't very demanding and you won't notice any difference at all.
Others are much less forgiving and you'll be glad for all the help you can
get.

In ADO.NET, if your DB is SQL Server and you are willing to be married to
it, use the native SQL classes -- they have the best benchmarks. If you
want to hedge your bets then either use the OleDb classes or design a way of
talking to the back end that lets you swap out the data layer if you need
to. If you have a legacy back-end that only has ODBC drivers, then use
ODBC.

--Bob

"Daniel" <dhartley@data2you.net> wrote in message
news:OlYqEnNnDHA.1676@TK2MSFTNGP09.phx.gbl...
> Thanks for the response.
>
> What you say makes a lot of sense. Typically when we did projects,
whether
> they be ASP applications or VB executables, we used DSN's. We are
starting
> to move to .Net, and virtually all the examples use connection strings,
> which I took as implicitly promoting that method of connecting to a
> database. Native access being faster is enough to convince me that it is
> worth the extra work (be it encrypting/decrypting a registry key or
> whatever).
>
> Thanks,
> Dan