Solved

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

Posted on 2003-10-24
18
712 Views
Last Modified: 2013-12-25
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.
0
Comment
Question by:SpringSnowman
  • 10
  • 5
  • 3
18 Comments
 
LVL 8

Expert Comment

by:MYLim
Comment Utility
Any Error Message.
0
 

Author Comment

by:SpringSnowman
Comment Utility
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
0
 
LVL 8

Expert Comment

by:MYLim
Comment Utility
dataEnvironment should provide datasource to let u specify:
-Server
-LogonInfo

0
 
LVL 8

Expert Comment

by:MYLim
Comment Utility
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;"
data1.refresh




====================================================
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.



0
 
LVL 8

Expert Comment

by:MYLim
Comment Utility
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 :)
0
 

Author Comment

by:SpringSnowman
Comment Utility
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.
0
 
LVL 8

Expert Comment

by:MYLim
Comment Utility
'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()
    CreateAccessODBC
End Sub

Private Sub CreateServer_Click()
    CreateSQLODBC
End Sub


In the Module


' ODBC API
' -- 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&
Public Const ODBC_REMOVE_DEFAULT_DSN = 7&

' -- ODBC Error Codes
Public Const ODBC_ERROR_GENERAL_ERR = 1
Public Const ODBC_ERROR_INVALID_BUFF_LEN = 2
Public Const ODBC_ERROR_INVALID_HWND = 3
Public Const ODBC_ERROR_INVALID_STR = 4
Public Const ODBC_ERROR_INVALID_REQUEST_TYPE = 5
Public Const ODBC_ERROR_COMPONENT_NOT_FOUND = 6
Public Const ODBC_ERROR_INVALID_NAME = 7
Public Const ODBC_ERROR_INVALID_KEYWORD_VALUE = 8
Public Const ODBC_ERROR_INVALID_DSN = 9
Public Const ODBC_ERROR_INVALID_INF = 10
Public Const ODBC_ERROR_REQUEST_FAILED = 11
Public Const ODBC_ERROR_INVALID_PATH = 12
Public Const ODBC_ERROR_LOAD_LIB_FAILED = 13
Public Const ODBC_ERROR_INVALID_PARAM_SEQUENCE = 14
Public Const ODBC_ERROR_INVALID_LOG_FILE = 15
Public Const ODBC_ERROR_USER_CANCELED = 16
Public Const ODBC_ERROR_USAGE_UPDATE_FAILED = 17
Public Const ODBC_ERROR_CREATE_DSN_FAILED = 18
Public Const ODBC_ERROR_WRITING_SYSINFO_FAILED = 19
Public Const ODBC_ERROR_REMOVE_DSN_FAILED = 20
Public Const ODBC_ERROR_OUT_OF_MEM = 21
Public Const ODBC_ERROR_OUTPUT_STRING_TRUNCATED = 22

'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
0
 
LVL 8

Expert Comment

by:MYLim
Comment Utility
********************************************************
README
********************************************************
Microsoft Knowledge Base Article - 171146    
HOWTO: Create and Remove a DSN in Visual Basic

http://support.microsoft.com/default.aspx?scid=kb;EN-US;171146

 
0
 
LVL 8

Expert Comment

by:MYLim
Comment Utility
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:SpringSnowman
Comment Utility
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.
0
 
LVL 8

Expert Comment

by:MYLim
Comment Utility
*************************************
Programmatically Create SQL Server DSN
*************************************
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q184/6/08.asp&NoWebContent=1

So tired,Go home now,see you,bye...
q(^ _ ^)p
0
 
LVL 8

Expert Comment

by:MYLim
Comment Utility
0
 
LVL 8

Expert Comment

by:MYLim
Comment Utility
OIC,but it ok :)
0
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 500 total points
Comment Utility
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.



0
 

Author Comment

by:SpringSnowman
Comment Utility
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 http://www.frez.co.uk/freecode.htm#rijndael
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...;)
Cheers
SpringSnowman
0
 
LVL 30

Expert Comment

by:nmcdermaid
Comment Utility
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.



0
 

Author Comment

by:SpringSnowman
Comment Utility
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^)
0
 
LVL 30

Expert Comment

by:nmcdermaid
Comment Utility
All the best!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now