How to get Data Environment Connection Object to use system DSN password at run time
Posted on 2003-10-24
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.