0

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?

Thomas
  • 193
  • 1
  • 6
  • 14
  • What exactly is your security situation? Is it an encrypted .mdb, a secured .mdb with a workgroup file or a plane .mdb? Are there any linked tables in your .mdb? (If so, you probably want to connect directly to those linked tables and not through Access). Can you use the access database without entering login information and without getting this prompt? – Erik A May 24 '17 at 19:36
  • Are you trying to link an Access table into Oracle? I would try the reverse. Migrate the table to Oracle and link it back to Access. – SunKnight0 May 24 '17 at 19:56
  • @ErikvonAsmuth I am not sure what type of .mdb it is. I do not think it is secured, because I am not prompted for credentials when I open it. However, it does have linked tables in it. When I attempt to open one, I am presented with the same 'Oracle ODBC Driver Connect' window as above. How can I access the linked tables directly? It is probably worth mentioning that this database belongs to a large company and that I have little control over it, so it would be very difficult for me to restructure it like some of the other comments suggest. – Thomas May 25 '17 at 11:28
  • I tried using ODBC as well, but that pops the same prompt up `$strConn = "Provider=MSDASQL;FileDSN=$db;;UID=USERNAME;PWD=PASS;"`I had to create a Data Source Name for the mdb do use it though. – Thomas May 25 '17 at 12:33

2 Answers2

3

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.

Erik A
  • 31,639
  • 12
  • 42
  • 67
0

According to the handy reference page ConnectionStrings.com, pass User Id=<username> and Password=<password> like so,

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;User Id=admin;
Password=Pa$$w0rd;
vonPryz
  • 22,996
  • 7
  • 54
  • 65
  • This was one of the first things I tried. It does not work for some reason. – Thomas May 24 '17 at 19:23
  • While ConnectionStrings usually is a good resource, User Id is only used in the case of workgroup authentication with a workgroup file, and in that case you will want to specify the workgroup file: `Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb; Jet OLEDB:System Database=system.mdw;User ID=myUsername;Password=myPassword;`. However, I don't think that is the case here. – Erik A May 24 '17 at 20:37