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.
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.
Any Error Message.
ASKER
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
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:
-Server
-LogonInfo
-Server
-LogonInfo
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;D
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;Integr
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.
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;D
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;Integr
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 :)
DE.Open "Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;"
i just try to help you :)
ASKER
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.
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()
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_LE
Public Const ODBC_ERROR_INVALID_HWND = 3
Public Const ODBC_ERROR_INVALID_STR = 4
Public Const ODBC_ERROR_INVALID_REQUEST
Public Const ODBC_ERROR_COMPONENT_NOT_F
Public Const ODBC_ERROR_INVALID_NAME = 7
Public Const ODBC_ERROR_INVALID_KEYWORD
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
Public Const ODBC_ERROR_INVALID_PARAM_S
Public Const ODBC_ERROR_INVALID_LOG_FIL
Public Const ODBC_ERROR_USER_CANCELED = 16
Public Const ODBC_ERROR_USAGE_UPDATE_FA
Public Const ODBC_ERROR_CREATE_DSN_FAIL
Public Const ODBC_ERROR_WRITING_SYSINFO
Public Const ODBC_ERROR_REMOVE_DSN_FAIL
Public Const ODBC_ERROR_OUT_OF_MEM = 21
Public Const ODBC_ERROR_OUTPUT_STRING_T
'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
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_LE
Public Const ODBC_ERROR_INVALID_HWND = 3
Public Const ODBC_ERROR_INVALID_STR = 4
Public Const ODBC_ERROR_INVALID_REQUEST
Public Const ODBC_ERROR_COMPONENT_NOT_F
Public Const ODBC_ERROR_INVALID_NAME = 7
Public Const ODBC_ERROR_INVALID_KEYWORD
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
Public Const ODBC_ERROR_INVALID_PARAM_S
Public Const ODBC_ERROR_INVALID_LOG_FIL
Public Const ODBC_ERROR_USER_CANCELED = 16
Public Const ODBC_ERROR_USAGE_UPDATE_FA
Public Const ODBC_ERROR_CREATE_DSN_FAIL
Public Const ODBC_ERROR_WRITING_SYSINFO
Public Const ODBC_ERROR_REMOVE_DSN_FAIL
Public Const ODBC_ERROR_OUT_OF_MEM = 21
Public Const ODBC_ERROR_OUTPUT_STRING_T
'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
**************************
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
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
ASKER
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.
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
**************************
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
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
try this:
https://www.experts-exchange.com/questions/10123990/How-can-I-Register-a-DSName-In-the-System-DSN-Of-ODBC.html?query=create+and+"system+DSN"&searchType=topic
So tired,Go home now,see you,bye...
q(^ _ ^)p
https://www.experts-exchange.com/questions/10123990/How-can-I-Register-a-DSName-In-the-System-DSN-Of-ODBC.html?query=create+and+"system+DSN"&searchType=topic
So tired,Go home now,see you,bye...
q(^ _ ^)p
OIC,but it ok :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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.
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.
ASKER
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^)
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!