Alternative to using DSN to connect to database

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

VS 2003 question

I know that this question has been asked alot but I have seen no direct
answer to it as yet.

From control panel it is possible to specify the connection parameters for a
database. Then from code simply request the DSN and the connection manager
would resolve the connection to the database.

This was great if the database server location moved it was a simple process
for any user (from small to large enterprises) to change the setting using
the control panel manager.

Am I right in saying that there is no corrisponding facility for doing this
when using an SQL Client Connection or OLEDB Connection object in .NET?


Regards,
Myles
 
Of course you can.

The ODBC .net provider is supposed to work on top of any ODBC native driver.
So you have the same facilities in configuration as using a native ODBC
driver.



The "right" way is to use a managed .NET provider targeted for a database;
if you want to change the connection string dynamically you can put the
connection string in a configuration file (eventually encrypt the connection
string if needed).



Dumitru
 
Scott nice try but no joy here's an example
Dim cnn As New SqlClient.SqlConnection
Dim cnn2 As New OleDb.OleDbConnection
' This will fail because DSN is NOT supported
cnn.ConnectionString = "DSN=MyDSN;UID=sa"
cnn.Open()
' This will work UNTIL the database is moved to another machine on the
' network... Basically the command ignores DSN and defaults to local!
cnn2.ConnectionString = "Provider=SQLOLEDB;DSN=MyDSN;UID=sa"
cnn2.Open()
 
Dumitru,
Thanks for that. I understand that. The question is what about when you
are using OLEDB Connections and SQLClient Connections by way of an example:

Dim cnn As New SqlClient.SqlConnection
Dim cnn2 As New OleDb.OleDbConnection
' This will fail because DSN is NOT supported... OK so we try OLEDB
cnn.ConnectionString = "DSN=MyDSN;UID=sa"
cnn.Open()
' This will work UNTIL the database is moved to another machine on the
' network... Basically the command ignores DSN and defaults to local!
cnn2.ConnectionString = "Provider=SQLOLEDB;DSN=MyDSN;UID=sa"
cnn2.Open()
Myles
 
I am curious one thing: is your system REQUIRES you to use DSN, not managed
..NET provider, for particular technical reason?
As for SQL Server could be moved to other computer than the original
location when the system is designed, you could simply change the server
name. Note, even you use DSN, you have to change server name in the DSN
anyway.

You should always use SQL Server's native .NET provider, instead of ODBC
(DSN), unless you has special reason. Since your original question is about
alternative to using DSN, then, simply look at ConnectionString in almost
all ADO.NET sample: you only need to give server name, database name and
user/password (if you use SQL security).

So, I really confused on what your problem is.
 
¤ VS 2003 question
¤
¤ I know that this question has been asked alot but I have seen no direct
¤ answer to it as yet.
¤
¤ From control panel it is possible to specify the connection parameters for a
¤ database. Then from code simply request the DSN and the connection manager
¤ would resolve the connection to the database.
¤
¤ This was great if the database server location moved it was a simple process
¤ for any user (from small to large enterprises) to change the setting using
¤ the control panel manager.
¤
¤ Am I right in saying that there is no corrisponding facility for doing this
¤ when using an SQL Client Connection or OLEDB Connection object in .NET?

Yes, you are correct.

Legacy DSN type connections have been essentially replaced, although they are still supported via
the native .NET ODBC provider.

Keep in mind that you don't have to hard code connection strings. These can be place in a .config
file where that can just as easily be modified or pushed out to the client.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Hi Norman,
First of all I do not have a requirement to use ODBC I would prefer and will
use a native driver where possible.

Basically my problem is that the ODBC Connection Manager in Control Panel
acts as a central repository for connection information to named systems.
This allows for the system administrator to manage and move the database
without impacting the application or modify the application settings directly.

The situation that we are generating for ourselves with the .net way is that
there appears to be a seperate and custom connection repository for each
application. For example I have a customer that has 4 database systems and
numerous bespoke and customised applications accessing the database (lets say
4 for this example). At present (since nearly all use the ODBC DSN Connection
information) if the database(s) are moved all that needs to happen is the 4
ODBC Settings on the workstations are updated on the next logon by the
workstation and everything is up and running.

With the .Net native drivers it appears that each application holds it's own
connection information since the DSN facility is not available. Now we have a
situation that when the database server moves the 4*4=16 settings need to be
altered. Depending on how the developer has designed their system this may
require, Registry Updates, File Update OR each application being accessed
manually on each workstation and the settings changed (not good if its a
large organisation).

And remember each one of these systems could have been designed by a
different software vendor. I hope that you can begin to see the nightmare
ahead of us and I deal with regulated industries which I have no doubt when
they realise the potential threat to systems integrity will go nuts. Imagine
a production plant where on one workstation the production management system
is pointing at the live database but the extract and order management system
is pointing at the test server because they both have their own connection
setting store!

From my point of view (development) I want to know if there is an equivilant
connection information repository that can be used when using the native
drivers?

I hope this clarifys the question and raises the importance of having a
central connection setting store that is independant of the application being
run

Regards,
Myles
 
What Paul said. You can store your connection string in a config file, so
you needn't worry about server name changes. If you need security on top of
that (as it's in plain text) you can store it in encrypted format. There
are lots of web pages that illustrate this method.

-Altaf
 
Hi Pay that answers the question :) although I don't particularly like the
answer.

If you look at the mail in response to Norman I outlined the benifits of a
central connection store. I supose that the real problem I have with it is
that we have lost a facility from the operating environment that was very
useful from everyones perspective.

Unfortunatly the only people who could really provide a replacement facility
is microsoft as they are the only one that everyone will accept...

Thanks for the response

Myles.
 
Hi Altaf,
Thanks for that, I have to admit though my views on storing passwords are
very simple. I don't store UserID's and Passwords ever unless it is a server
based application that is connecting to a non NT platform and then I would
use the OS security to manage access to restricted information (granted I may
still encrypt it).

Basically my attitude to workstation user id's and passwords is that the
application should either Prompt for a User ID and Password or use NT
integrated authority. Anything else and your not really serious about
security from your desktop to your database.

Thanks again for the input

Myles.
 
If you (or your system administrator) want to use ODBC (DSN) to manage data
access, then you are forced to give up native .NET SQL Server provider, and
you have to use .NET ODBC provider. In this case, your data access to SQL
Server is NOT optimized.

As Paul pointed out, ODBC is old technology and should be avoided if
possible. Also as Paul suggested, you can use *.config file to store
connectionstring. With a bit more imagination, you can even make a
centralized config file to be read by all data access apps in your office.
Just because of the Admiistrator thinking DSN is easier for him to control
data access, then you have to give up optimal data access technology you are
supposed to use, IMO, this is not good situation I'd like to be. (again,
unless there is reason that makes DSN is only option. What it could be?).
 
You try the impossible

MDFS said:
Dumitru,
Thanks for that. I understand that. The question is what about when you
are using OLEDB Connections and SQLClient Connections by way of an
example:

Dim cnn As New SqlClient.SqlConnection
Dim cnn2 As New OleDb.OleDbConnection
' This will fail because DSN is NOT supported... OK so we try OLEDB
cnn.ConnectionString = "DSN=MyDSN;UID=sa"
cnn.Open()
Click to expand...

The .NET Sql Server provider is not supposed to work with DSN-ODBC; so of
course is failing.
' This will work UNTIL the database is moved to another machine on
the
' network... Basically the command ignores DSN and defaults to
local!
cnn2.ConnectionString = "Provider=SQLOLEDB;DSN=MyDSN;UID=sa"
cnn2.Open()
Myles
Click to expand...

The OLEDB .NET provider is a wrapper on top of native OLEDB drivers; and it
is supposed to work with every OLEDB native driver (at least in theory),
except Microsoft OLEDB provider for ODBC (for that you must use the ODBC
..NET provider). So the OLEDB .NET provider will never work with DSN-ODBC

If you really, really want to use DSN-ODBC from .NET you must use ODBC .NET
provider, which I see you didn't try yet :) . You don't have another
solution ...

Dumitru
 
Myles,

I was reading this last post and I am in a similiar situation. I am a
system administrator managing the .NET environment for a new application. We
are connecting to a DB2 database (OS390) for updates (not just read-only).
It is the policy of the company to not allow the username and password to be
hard coded into the code, but to be ready from a 'property file' or something
like that (we are actually a heavy weblogic shop wherein the application
server administrator configures the connection pools and connection
information within the JVM and the development team is unaware of the
username/password information).

We liked the idea that with the ODBC data source administrator you can
configure the username/passwords and connection pooling independently from
the application, but we are not sure how to do this with ADO.NET.

Was the external config file containing the username and password the only
method which you were able to use? Did you encrypt the configuration file or
leave it as plain txt?

Any help on this would be much appreciated.

Thanks,


Matt
 
Back
Top