Can't get data into Excel from encrypted Access database: keeps asking for password

[jasmith4]

No coding involved here, but a very curious problem:

I have some Access 2010 databases I have to secure, and currently I have a spreadsheet with queries to get their data. When I encrypt one database with a password and close it, I then open an Excel query pointing to it, save the password into its connection string, and then refresh. But it asks for the password anyway -- repeatedly! The only way out is to hit Cancel, after which, obviously, it doesn't pull in data.

I understand there's no more user-level security as of Access 2007, so am I supposed to downgrade to 2003 format to get that? In general, how do I secure an Access 2010 database such that I can get its data into Excel only if I know its password?

[jonh]

data > connections > [select connection] > properties > definition > save password

[jasmith4]

data > connections > [select connection] > properties > definition > save password

That's what I meant by "I saved the password in the connection string". FYR here's the string:

Provider=Microsoft.ACE.OLEDB.12.0;Password="MY_PASSWORD";User ID=Admin;Data Source=C:\MY_PATH\MY_DATABASE.accdb;Mode=Share Deny Write;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDBatabase Password="";Jet OLEDB:Engine Type=6;Jet OLEDBatabase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDBon't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False

[jasmith4]

I went every way there is: from Data / From Access, from Data / Connections, even creating an ODBC data source first and then using Data / From Other Sources / MS Query; ODBC kept saying "Not a valid password".

I even tried decrypting the database, then setting up the query in Excel (which got the data successfully), then encrypting the database, then adding the password to the query. Still it repeatedly asks for the password.

And when I try Data / From Other Sources / Data Connection Wizard and choose the MSO 12.0 Access DB engine OLE provider, I can't create any connection: it says it can't initialize a provider because the workgroup information file is missing or opened exclusively. And I can't use the Jet 4.0 provider, because it doesn't deal with ACCDB's, only MDBs!

[jonh]

Yeah I tried various methods as well (creating a file dsn with password, modifying the connection string) but this is the only way I could get it to work...

(created a new encrypted db with password and added 2 tables)

Excel > new workbook
1/ data > connections > add
2/ select database
3/ enter password
4/ select table (table1)
5/ goto connection properties and check 'save password'

repeat steps 1-5 for table2

select sheet1 > data > existing connections > insert connection 1
select sheet2 > data > existing connections > insert connection 2

save + close workbook

open workbook
data > refresh all
and I don't have to enter the password.

This is really an Excel question. If the above doesn't work you may get better answers reposting your question in the Excel forum.

[jasmith4]

At step 3 there are various things to do. The one I didn't try was clicking Browse for More, and entering the full path and name to the database there. But it sill asks for the password repeatedly.

Other alternatives I tried were creating a DSN and creating an ODC, so I could select them from the dialog box after step 2, but neither of those works. All I ever get is repeated password prompts.

[jonh]

I tried it on a linked table in an unsecured db and since the pw is stored in the table connection it works ok. Maybe try a passthrough query?

[jasmith4]

I don't understand: if it's unsecured, why is there any need for any password? And what passes through to what?

It's simple: I have an Access database. I have an Excel workbook with a query into that database. That all works.
Now I encrypt the database with a password. I burn the password into the query's connection string.
As the query refreshes, it nevertheless asks for the password. I give it the correct password.
It asks again. And again and again and again. I have to Cancel, and I get no data.

I've tried Data / From Access, Data / Other / OLE DB, and creating an ODBC entry and then using Data / Other / MS Query. I've tried adding a connection and using Data / Existing Connections.

No matter what I do, even manually inserting the password into the Registry and DSN/ODC files, all I ever get is repeated prompts for the password that don't accept the correct password.

[jonh]

I thought you were just complaining about needing a password for each connection.
If Excel isn't accepting any password for any connection I have no idea. Maybe something isn't installed properly.

But to answer the question above, I linked a table from the secure db to an unsecure db and connected that to Excel.
Access resolves the password, so Excel doesn't ask for it.
Obviously that makes the table(s) insecure, so if you can create a query for the report you expose less data but you'd need to be able to specify the pw in the query. And my initial thought on that was a passthrough query (but I can't remember if they only work with sql server atm.)

[jasmith4]

I just tried that, and it didn't work.

Specifically: I created new database, immediately clicked External Data / Access, chose Link and pointed to my encrypted database. It asked for the password, I gave it, it accepted it, I saw the list of tables, I chose Select All, and clicked OK. I got an external-table link, and whenever I double-click it, I can see the data without a password prompt. I then close the database.

But in Excel I use Data / From Access to point to this new linker database, and it says "no visible tables". I try Data / Other / Data Connection Wizard, choose the MSO 12.0 Access provider, point to the linker database, click Test Connection, it succeeds, it has me choose a default table, so I choose Table1, I have to save the ODC, so I do, and it now says "the query didn't run, or the database cannot be opened." Even if I embed the source database's password into the connection string for the linker database -- which shouldn't need it -- it still asks me for the password, then quits with an error message.

[jonh]

I'm not a my pc. Going from memory... Try microsoft query as the data source. When you select the database it says no visible tables. There should be an options or properties button. Click that and look for an option like 'show system tables'.
You should then see linked tanles.

[jasmith4]

OK, I saw the my table among all the MSys* tables. But I select it, and it still said "Not a valid password". This is the linker table I'm selecting, so it shouldn't need a password, but when I add the source database's password to the connection string, same thing.

[jonh]

And if you open the 'linker' db and open the linked table you don't need a password?

[jasmith4]

And if you open the 'linker' db and open the linked table you don't need a password?

Correct. The password is part of the linker db's table object's connect string, so the data just appear.

[jonh]

Try this:

http://stackoverflow.com/questions/1...ile-from-excel

https://social.microsoft.com/Forums/...forum=Offtopic

I'm on 2007 which is probably why it works for me..

[jasmith4]

SOLVED: File / Options / Client Settings / Use Legacy Encryption.
Hey Microsoft -- there's a BUG here!