Search code examples
sqlpowershellms-accessconnection-stringoledb

Hard code credentials in MS Access connection string (password protected)

I want to hardcode the credentials for connection to an Access database.

My connection string currently looks like

$strConn = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = $dbName;

And this works fine, but I am prompted to enter a "User Name" and "Password". I have researched Access connection strings, but I can only find one that includes password (not user)

$strConn = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source=c:\App1\Your_Database_Name.mdb; Jet OLEDB:Database Password=Your_Password"

I have tried using this (as well as combinations of user/username/uid etc) but have not found anything that works.

Here is the window that is popping up (service name is automatically populated):

enter image description here

Looks similar to this: Oracle ODBC Driver Connect always asking for password

I believe it has something to do with the Access database being linked to an oracle database. Is this out of my hands?

Solution

  • You can get direct access to a linked table by using its connection string, and filling in the user and password. For Oracle, the connection string structure can vary upon the used provider. View ConnectionStrings.com for a list of options (most likely option is Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=myUsername; Password=myPassword;).

    You can obtain the current connection string for the linked table by querying MSysObjects inside Microsoft Access:

    SELECT MSysObjects.Connect
FROM MSysObjects
WHERE MSysObjects.Name="MyLinkedTableName";

    You can even change the connection string to include your username and password, if you wanted (see this answer).

    However, take note that the one who originally linked the tables in Access, chose not to include a username and password. Including a username and password in an unsecured Access database might pose a security risk.

    Also, take note that if you connect directly to the Oracle database, you must reference the table names as defined there, and use the proper SQL variant to query it.