I am working on some legacy code (VB6) that connects to a database to retrieve some information. We have changed some passwords recently and now some accounts contain a ; character in the password. For some connection strings this is not a problem, but for others it is. Ideally I would like a solution that will work for all Microsoft Server DBs, but at least it should support the two I'm using in my examples.
The connection strings I have that work are:
Dim connectionString = "Server=PROD;Database=Main;UID=User1;Password=""B3Z@2Q>N<;5"";PROVIDER=MSOLEDBSQL;"
Dim connectionString = "driver={SQL Server};DATA PROVIDER=SQLOLEDB;SERVER=PROD;DATABASE=Main;PROVIDER=MSDASQL;UID=User2;Pasword=""?H7=9GHS?Y"";"
This allows me to connect using MSOLEDBSQL and SQLOLEDB. However, for the latter, passwords with semicolon such as User1's password fail and give an error saying
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.
For example, see the below connection string that matches the second working example format but uses the UID and Password from the first working example:
Dim connectionString = "driver={SQL Server};DATA PROVIDER=SQLOLEDB;SERVER=PROD;DATABASE=Main;PROVIDER=MSDASQL;UID=User1;Password=""B3Z@2Q>N<;5"";"
I'm at my wit's end. I've been scratching my head and searching the internet for days to no avail.