Connection String vs DSN, what to use and why?

  • Thread starter Thread starter Daniel
  • Start date Start date
D

Daniel

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