|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Alternative to using DSN to connect to databaseI 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 oConn.Open "DSN=mySystemDSN;" & _
"Uid=myUsername;" & _ "Pwd=myPassword" Show quote "MDFS" <M***@discussions.microsoft.com> wrote in message news:4D7CBCEC-45CC-4AEC-B6DE-9C93865F45C9@microsoft.com... > 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 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() 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 Show quote "MDFS" <M***@discussions.microsoft.com> wrote in message news:4D7CBCEC-45CC-4AEC-B6DE-9C93865F45C9@microsoft.com... > 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 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 Show quote "Dumitru Sbenghe" wrote: > 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 > 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. Show quote "MDFS" <M***@discussions.microsoft.com> wrote in message news:1113FCDC-81FF-4AE9-9320-FA5847BD98E9@microsoft.com... > 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 > > > "Dumitru Sbenghe" wrote: > >> 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 >> > 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 Show quote "Norman Yuan" wrote: > 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. > 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?). Show quote "MDFS" <M***@discussions.microsoft.com> wrote in message news:E0E29104-FDBB-42DE-A1B4-119F37FD92A1@microsoft.com... > 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 > > > "Norman Yuan" wrote: > >> 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. >> > You try the impossible
"MDFS" <M***@discussions.microsoft.com> wrote in message The .NET Sql Server provider is not supposed to work with DSN-ODBC; so of news:1113FCDC-81FF-4AE9-9320-FA5847BD98E9@microsoft.com... > 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() course is failing. > ' This will work UNTIL the database is moved to another machine on The OLEDB .NET provider is a wrapper on top of native OLEDB drivers; and it > the > ' network... Basically the command ignores DSN and defaults to > local! > cnn2.ConnectionString = "Provider=SQLOLEDB;DSN=MyDSN;UID=sa" > cnn2.Open() > Myles 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 On Wed, 16 Nov 2005 14:40:53 -0800, "MDFS" <M***@discussions.microsoft.com> wrote: ¤ 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) 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 -------------------------------------------------------------------------------- All that glitters has a high refractive index. www.mendhak.com Show quote "Paul Clement" <UseAdddressAtEndofMess***@swspectrum.com> wrote in message news:6n6pn19i0j42jmqpltdguh8gnebg1u6h68@4ax.com... > On Wed, 16 Nov 2005 14:40:53 -0800, "MDFS" > <M***@discussions.microsoft.com> wrote: > > ¤ 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 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. Show quote "S.M. Altaf [MVP]" wrote: > 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 > > -------------------------------------------------------------------------------- > All that glitters has a high refractive index. > www.mendhak.com > > > "Paul Clement" <UseAdddressAtEndofMess***@swspectrum.com> wrote in message > news:6n6pn19i0j42jmqpltdguh8gnebg1u6h68@4ax.com... > > On Wed, 16 Nov 2005 14:40:53 -0800, "MDFS" > > <M***@discussions.microsoft.com> wrote: > > > > ¤ 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 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. Show quote "Paul Clement" wrote: > On Wed, 16 Nov 2005 14:40:53 -0800, "MDFS" <M***@discussions.microsoft.com> wrote: > > ¤ 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) > 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 Show quote "MDFS" wrote: > 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. > > "Paul Clement" wrote: > > > On Wed, 16 Nov 2005 14:40:53 -0800, "MDFS" <M***@discussions.microsoft.com> wrote: > > > > ¤ 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) > >  |
|||||||||||||||||||||||
Other interesting topics