none
Can not connect to ENCRYPTED Access db (accdb) from VSTO (Excel Add-In) - System.Data.OleDb.OleDbException RRS feed

  • Question

  • From within the Add-In I tried to connect to an encrypted Access Database.

    The code is basically as follows:

            public void TestAccessLogin()
            {
                string connString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\temp\encryptedDb.accdb;" +
                        @"Jet OLEDB:Database Password=1234;";
    
                using (var oleDbConn = new OleDbConnection(connString))
                {
                    oleDbConn.Open();  // <---- triggers the following error
                                        // Error: { System.Data.OleDb.OleDbErrorCollection} 
                                        // Source: "Microsoft Access Database Engine"
                                        // Message: "Not a valid password"
                }
            }

    The code depicted above runs without problems on a console app. So there is no problem with the connection string or the password.

    The code also runs without problems if the targeted database is not encrypted.

    The Exception does not contain any inner exception.

    I found that some people faced a similar problem (the same error message), when trying to connect to an encrypted accdb from ASP.NET and the permissions for the web process were not correct. Could this be a hint? IMHO the Add-In should run with user privileges, so no problem...

    Thanks for your ideas!

    Thursday, May 16, 2019 12:48 PM

Answers

  • Update 2019/05/22 - final conclusion:

    To the best of my knowledge there is no standard way to connect to a 'standard' (hic!) encrypted Access database. (besides using ODBC that is)

    I ended up writing a small program that detects the current encryption and reencrypts the database using 'legacy' encryption.

    Nice job, MS guys. I hope this post saves at least someone wasting his/her time.

    Update 2019/05/17 #3:

    I tried to implement data access through ADODB in the VSTO Add-In. It failed with the same error.

    Then I tried to connect to the encrypted accdb directly in VBA using ADODB. 

    Conclusion: VBA behaves differently!!!!  

    In Access everything works as expected whereas in Excel the connection to the 'Standard' (hic!) encrypted accdb fails with the same error.

    In case you feel like trying yourself: Just create an empty accdb. Make two versions and encrypt one using 'Standard' encryption and one using 'Legacy' encryption. 

    Then paste the code below in an Access and an Excel VBA-Window and run it.

    Public Sub ConnectToEncryptedDb()
        'Set a reference to msdao15.dll
        Dim conn As New ADODB.Connection
        Dim rs As New ADODB.Recordset
        Dim conStrEnc As String             'Connection string to a 'Standard' encrypted accdb
        Dim conStrLegacyEnc As String       'Connection string to a 'Legacy' encrypted accdb
    
        conStrEnc = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Temp\DbStandardEncrypted.accdb;Jet OLEDB:Database Password=p@ssw0rd;"
        conStrLegacyEnc = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Temp\DbStandardLegacyEncrypted.accdb;Jet OLEDB:Database Password=p@ssw0rd;"
        
        With conn
            .Open conStrLegacyEnc  'Works in both Excel VBA and Access VBA
            .Close
            .Open conStrEnc        'Works in Access VBA, but FAILS in Excel VBA
            .Close
        End With
    
    End Sub
    Seriously, this sucks!



    • Edited by papa_bear Wednesday, May 22, 2019 10:31 AM new knowledge
    • Marked as answer by papa_bear Wednesday, May 22, 2019 10:31 AM
    Friday, May 17, 2019 4:24 PM

All replies

  • Update 2019/05/17:
    I found that if I encrypted the accdb in "legacy encryption", the connection works! :-(
    To reproduce:

    In Access:

    • Decrypt the database (DB must be opened exclusively)
    • Go to File -> Options -> Client-Settings: There scroll to the "Advanced" section and tick "Legacy encryption" (instead of "Standard" - which is set by default)
    • Close the database (might be optional)
    • Reopen the database exclusively
    • Encrypt the database.

    Et voilà - now the database can be opened using the password from the Add-In.

    Dear MS-guys - what the heck! The non working "default" encryption is there at since Office 2010 at least.

    In addition: I did tried to connect directly to the accdb from Excel 365. Data -> Get Data.

    • -> Database ->Access. Non encrypted (OK), Encrypted (Fail)
    • -> Other Sources -> OLEDB: (here you can provide a connection string). Non encrypted (OK), Encrypted (Fail)
    • -> Other Sources -> ODBC: (Set up a new ODBC-Connection, here you get to see some really cool dialog boxes from the last century): Non encrypted (OK), Encrypted (OK)

    Well, it seems that one has to accept that Microsoft can't properly integrate their own database to their office suite. And hey, it's only been about 10 years than this is not working.

    If anybody gets an idea of how to properly connect using OleDb, I am still happy for any input!

    Friday, May 17, 2019 10:40 AM
  • Have no problems using this connection string:

    string con = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + DatabaseFile + ";Persist Security Info=False;Jet OLEDB:Database Password=" + Password;


    Friday, May 17, 2019 11:10 AM
  • Hi Vytautas A

    I tried your connection string - which only includes 'Persist Security Info=False'.

    But unfortunately to no avail.

    Can you confirm that this connection string works for you to connect to a 'standard'-encrypted (not legacy) accdb from a VSTO Add-In?

    Friday, May 17, 2019 1:57 PM
  • Update 2019/05/17 #2:

    Just to make sure that it's not something Excel specific, I also created an Add-In for Word. Same experience.

    So basically I can connect either by changing the encryption model (not very practical because there are many instances out there), or by using and ODBC-Connection (no good fit because I want to use EF Core - in this case for Access - (i know, i know, ... Access is about to be phased out)) or by just directly using ADODB or by remoting Access.

    But, I still have the feeling that the .NET class SHOULD behave like a .NET class - even if it's hosted in an Office Application.

    I put the connection code in a new DLL project. Then I use this dll in an Add-In project and a Console-Application. Both contain basically the same two lines of production code:

    var ac = new AccessConnectTest(); // <- class in own assembly
    ac.TestAccessLogin();

    Console works. Add-In does not.

    How is this possible? Does office fiddle with Assembly Resolution? 

    Are there any MS guys around?

    I am thankful for any help.




    • Edited by papa_bear Friday, May 17, 2019 4:26 PM typo
    Friday, May 17, 2019 3:00 PM
  • Update 2019/05/22 - final conclusion:

    To the best of my knowledge there is no standard way to connect to a 'standard' (hic!) encrypted Access database. (besides using ODBC that is)

    I ended up writing a small program that detects the current encryption and reencrypts the database using 'legacy' encryption.

    Nice job, MS guys. I hope this post saves at least someone wasting his/her time.

    Update 2019/05/17 #3:

    I tried to implement data access through ADODB in the VSTO Add-In. It failed with the same error.

    Then I tried to connect to the encrypted accdb directly in VBA using ADODB. 

    Conclusion: VBA behaves differently!!!!  

    In Access everything works as expected whereas in Excel the connection to the 'Standard' (hic!) encrypted accdb fails with the same error.

    In case you feel like trying yourself: Just create an empty accdb. Make two versions and encrypt one using 'Standard' encryption and one using 'Legacy' encryption. 

    Then paste the code below in an Access and an Excel VBA-Window and run it.

    Public Sub ConnectToEncryptedDb()
        'Set a reference to msdao15.dll
        Dim conn As New ADODB.Connection
        Dim rs As New ADODB.Recordset
        Dim conStrEnc As String             'Connection string to a 'Standard' encrypted accdb
        Dim conStrLegacyEnc As String       'Connection string to a 'Legacy' encrypted accdb
    
        conStrEnc = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Temp\DbStandardEncrypted.accdb;Jet OLEDB:Database Password=p@ssw0rd;"
        conStrLegacyEnc = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Temp\DbStandardLegacyEncrypted.accdb;Jet OLEDB:Database Password=p@ssw0rd;"
        
        With conn
            .Open conStrLegacyEnc  'Works in both Excel VBA and Access VBA
            .Close
            .Open conStrEnc        'Works in Access VBA, but FAILS in Excel VBA
            .Close
        End With
    
    End Sub
    Seriously, this sucks!



    • Edited by papa_bear Wednesday, May 22, 2019 10:31 AM new knowledge
    • Marked as answer by papa_bear Wednesday, May 22, 2019 10:31 AM
    Friday, May 17, 2019 4:24 PM