How to get Data Environment Connection Object to use system DSN password at run time

Hi all,
I am using VB6 on Win2000 pro and have a Data Environment with a connection object. The properties of this connection object are set using the Data Link Properties dialogue. Provider is 'Microsoft OLE DB Provider for ODBC Drivers'. in the Connection tab, I have selected 'Use data source name' and have provided 'BackOffice' as the name of the DSN. On this pane I have also provided the User name 'sa' and the Password 'saHomePW' to correctly allow me to log on to a SQL 6.5 server. I have also checked Allow password saving and provided the name of the initial catalog to use.
I have created a system DSN named 'BackOffice' and selected the name of the local SQL server to connect to. I have set the DSN to log on to SQL Server using SQL Server authentication provided by the user, checked 'Connect to SQL Server to obtain default settings for additional configuration options' and provided the Login ID as 'sa' and Password 'saHomePW'
When compiled an run, the application works as expected, and command objects on the connection object perform correctly.
My problem is this. when the application is packaged and distributed and installed on a machine at a remote location, and a similar system DSN created on this remote machine using a diferent SQL server (still 6.5) this new server has a difrent password for sa. This new password is entered into the remote machines BackOffice DSN, but when the application is run it cannot connect to the server. It would seem that although the application is correctly using the server name from the DSN, it insists in using the 'saHomePW' password regardless of the password entered in the remote machines DSN.
Could anyone help me please in using the password in the DSN rather than the one provided at compile time?
Thanks in advance.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Any Error Message.
SpringSnowmanAuthor Commented:
Hi MYLim
Only what you would expect if you attempted to log on to an SQL server with the wrong password;

Connection failed:
SQL State '28000'
SQL Server Error: 4002
[Microsoft][ODBC SQL Server Driver][SQL Server] Login failed
dataEnvironment should provide datasource to let u specify:

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

can you try this please:
in form load event or cmdbutton.

data1.datasource = "Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;"

Read the article below may give you idea
Specifying Data Sources Using ODBC
Follow this procedure to use data sources connected through ODBC.

To set the form's data environment to data sources using ODBC

After selecting ODBC in the Data Source tab, click Use connection string.
Type the connection string to connect to the Northwind.dbc database on your local or remote SQL Server. For example:
Driver={Sql Server};Server=localhost;Database=Northwind;Int Security=yes;
Note   You must have access to a local or remote SQL Server (SQL Server 7 or 2000) with Northwind.dbc database installed. If your SQL Server is not local, you can change the value of the Server= parameter in the connection string to the name of the remote server.
Click Test Connection to test the connection.
If the connection is successful, you can continue and retrieve data from the data source.

To continue this walkthrough, see Retrieving Data for Cursor Adapters.
Specifying Data Sources Using ADO
Follow this procedure to use data sources connected through ADO.

To set the form's data environment to data sources using ADO

After selecting ADO on the Data Source tab, click Use connection string.
Type the connection string to connect to the Northwind.dbc database on your local or remote SQL Server. For example:
Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=Northwind;Data Source=localhost
Note   You must have access to a local or remote SQL Server (SQL Server 7 or 2000) with Northwind.dbc database installed. If your SQL Server is not local, you can change the value of the Data Source= parameter in the connection string to the name of the remote server.
Click Test Connection to test the connection.
If the connection is successful, you can continue and retrieve data from the data source.

also try this one.not familiar with DE:)

DE.Open "Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;"

i just try to help you :)
SpringSnowmanAuthor Commented:
If I provide the password in a connection string, either in code as you sugest, or in the Data Link Properties Dialogue of the connection object, then the application does connect, but with the hard-coded password provided, not the password in the DSN.

If, for example, the sa password became compromised during the life of the application then i need to be able to set a new password on the SQL Server and modify the system DSNs of the client machines to the new password to resolve the situation.

Any situation where the application uses the hard-coded embedded password would require a re-compile and re-install on all the client machines in this case. This is why i need the password stored in the machines DSN to be used to connect.
'hope is My last Post:)
Private Sub CreateSQLODBC()
' This function setups a DSN common for remote Database servers
' Such as SQL or Oracle, keep in mind, this isnt a complete listing of parameters

   Dim DSN           As String
   Dim Server        As String
   Dim Address       As String
   Dim Database      As String
   Dim Description   As String
   Dim Security      As String
   'Basically the DSN Name you want to have
   DSN = "DSN=" & Trim(txtDatabase.Text)
   'The IP Addy of the server you want , if this is a remote connection
   Server = "SERVER=" & Trim(txtServer.Text)
   'Same as above
   Address = "ADDRESS=" & Trim(txtServer.Text)
   'The name of the database as known by the DB Server, such as SQL Server
   Database = "DATABASE=" & Trim(txtDatabase.Text)
   'An Optional Description Feild
   Description = "DESCRIPTION=" & Trim(txtDescription.Text)
   'This is optional, if you require a Security mode check the help files
   Security = "NETWORK=dbmssocn"
   'the next couple lines setup the Driver Text , that defines the type of DB Drivers
   ' you are using, if its anything other than the ones I've listed, check your DB
   ' documentation, or check the ODBC settings to see it's names
   'Also you will notice as each string peice is put together, they are seperated by
   'VbNullChar, this gives it a Null seperated array in a sense so that the API Command
   'can use the Parameters
   If OptSQL.Value = True Then
      SqlDriver = "SQL Server"
      SQLParameter = DSN & vbNullChar & Server & vbNullChar & Address & vbNullChar & Security & vbNullChar & _
         Database & vbNullChar & Description & vbNullChar & vbNullChar
   ElseIf OptOracle.Value = True Then
      SqlDriver = "Oracle73"
      SQLParameter = DSN & vbNullChar & Server & vbNullChar & Database & vbNullChar & _
         Description & vbNullChar & vbNullChar
   End If
   'calls SQLConfigDataSource , giving it the forms handle, the command to Add a System DSN
   'giving it the Driver name, and then the Null Seperated Parameter listing
   SQLConfigDataSource 0&, ODBC_ADD_SYS_DSN, SqlDriver, SQLParameter
   'Replace 0& with Me.hwnd if you wish for users to further configure settings such as a long
End Sub

Private Sub CreateAccessODBC()
'This common setup for an Access Database

   Dim Driver        As String
   Dim DSN           As String
   Dim Server        As String
   Dim Database      As String
   Dim Description   As String
   DSN = "DSN=" & Trim(AccessDSNtxt.Text)
   Database = "DBQ=" & Trim(AccessDBQTxt.Text) 'this is your physical path to the *.mdb
   Description = "DESCRIPTION=" & Trim(AccessDesc.Text)
   AccessParameter = DSN & vbNullChar & Database & vbNullChar & Description & vbNullChar & vbNullChar
   SQLConfigDataSource 0&, ODBC_ADD_SYS_DSN, "Microsoft Access Driver (*.mdb)", AccessParameter
End Sub

Private Sub CreateAccess_Click()
End Sub

Private Sub CreateServer_Click()
End Sub

In the Module

' -- ODBC Commands
Public Const ODBC_ADD_DSN = 1&
Public Const ODBC_CONFIG_DSN = 2&
Public Const ODBC_REMOVE_DSN = 3&
Public Const ODBC_ADD_SYS_DSN = 4&
Public Const ODBC_CONFIG_SYS_DSN = 5&
Public Const ODBC_REMOVE_SYS_DSN = 6&

' -- ODBC Error Codes
Public Const ODBC_ERROR_OUT_OF_MEM = 21

'API Command to create a Data Source Name, not used in this example
Public Declare Function SQLCreateDataSource Lib "odbccp32.dll" (ByVal hwnd&, ByVal lpszDS$) As Boolean
'API to modify/Edit/Create a Data Source Name
Public Declare Function SQLConfigDataSource Lib "odbccp32.dll" (ByVal hwnd As Long, ByVal fRequest As Integer, ByVal lpszDriver As String, ByVal lpszAttributes As String) As Boolean
Microsoft Knowledge Base Article - 171146    
HOWTO: Create and Remove a DSN in Visual Basic;EN-US;171146

SpringSnowmanAuthor Commented:
Thanks for the interest MYLim, but i think I must not have made my problem clear.
I do not want to create, modify or delete a DSN from Visual Basic.
I want to have my visual basic application use the password in an existing DSN to connect to a SQL server, not a password embedded in the application.
Thanks for trying to help me.
Programmatically Create SQL Server DSN

So tired,Go home now,see you,bye...
q(^ _ ^)p
OIC,but it ok :)
Passwords are not saved in DSN's. You only enter the password in a DSN so that it can go and get the list of databases for you to pick from in the next step, and also so you can tell the DSN wether you will be using SQL or windows security.

Once it has connected to get that list of DB's, it forgets your password.

So you cannot use a password stored in a DSN, as it isn't! If you connect through a DSN say from Access (or Excel or anything), you still have to put in a password no matter wether you have specified connection info or not.

I would suggest that either use Windows authentication, or add a another SQL user that everyone knows the password to. hard coding sa login is not good practice (even though everyone does it!!!). You might want to add an encryption algorithm so that the users password is different to the actual SQL Server password.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SpringSnowmanAuthor Commented:
Ouch! Well, I never knew that! (Obviously.) No wonder I was having trouble.
Thanks for putting me straight.
I am accepting your solution and enhancing it as follows;
Create a new user to get away from using sa, with a new password. Continue to use a DSN to allow the server name to be set for various locations. In the application, at start up, use an embedded key to extract a username and password from an external text file, encrypted with AES (excelent source code available here. I use the decode part only in the application. I hace a stand-alone application controlled by IT to generate new password files with the encryption side.) Sample code suitable for VB available here
This way, if the password is compromised, IT need only change the password on the server, build a new password encrypted text file with the supplied utility and copy out the new password file to each machine running the main app.
Thanks for your help. Much appreciated. (Althow you could have broken it more gently...;)
Oooops sorry I was probably a bit rude hey! It took me quite a while to work that out because the DSN wizard kind of implies that it is going to use those credentials forever when really it just chucks them away!

As far as the password issue goes, there is an even easier way to do it.

1. Add all users to a windows group called SQLUsers (or whatever)
2. Enable this windows group as a SQL Server user (in SQL 6.5, you need to use a seperate utility rather than Enterprise Manager)
3. Use Windows authentication instead of SQL authentication in your DSN

That way there is no password stored anywhere. If you want to give people access to your SQL Server, just add them to the windows group. If you want to remove their access, take them out.

The only disadvantages are that all users have to log on to the domain to use (good for security though) also if you have a complicated domain structure there could be issues.

SpringSnowmanAuthor Commented:
Nice one nmcdermaid. That should work nicely too. I intend to use the solution I outlined as I already have this operating for a difrent server within this app, so there is very little overhead or risk.
This morning I had a problem, now I have a choice of solutions!
Good to include this alternative in this thread so others seekers have a choice. 8^)
All the best!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.