none
Access 2007: Fatal security issue RRS feed

  • Question

  • An A2007 application (ACCDR) is linked to a password protected database. How to get the password?

    1. Rename an A2007 ACCDR to ACCDE.

    2. Open the ACCDE in Access while pressing Shift key.

    3. Check for some linked tables. All you need to know is a name of a linked table in a "password protected" database.

    4. Press Ctrl+G to switch  to Immediate window.

    5. In Immediate window write down the following and press Enter:

    ?CurrentDb.TableDefs("LinkedTableName").Connect

    6. Voilá, you've got connection string with password in it!!!

    I have tried to protect A2007 project DB with a password and translate it to ACCDE + rename to ACCDR. The problem is that an end user cannot run the application in A2007 Runtime without knowing the project's password.

    Is there any workaround, please?

    Thank you very much in advance.


    P.S. My former account was Vladimir Cvajniga but I can't access it anymore, see http://social.msdn.microsoft.com/Forums/office/en-US/5b1284eb-a74f-4c55-9d4c-d6fb6eb25d48/fatal-how-can-i-login-to-my-account?forum=Offtopic#5b1284eb-a74f-4c55-9d4c-d6fb6eb25d48.




    Thursday, November 5, 2015 11:21 AM

Answers

  • I've done some research, and it's a bit more complicated than I thought.  The principel holds, but Access won't let you create a linked table to a password-protected Jet/ACE (.mdb or .accdb format) back-end without supplying the password and storing it in the tabledef's .Connect property.  But, MVP Brent Spauding told me how to get around that.

    What you do is:

    1. Start with an UN-protected back-end -- no password required to connect to it.  You link the front-end to that back-end.  There's no password, so there's no password to store in the linked tables' Connect properties. 

    2. Close the front-end.

    3. Password-protect the back-end.

    4. Open the front-end in such a way that it makes no attempt to connect to any of the linked tables.

    5. Add code in your start-up routines that opens the back-end directly, using the OpenDatabase method and supplying the database password.  Keep the connection to the database alive -- maybe by assigning from OpenDatabase to a global object variable or to a module-level variable in the code of a hidden form that remains open for the duration of the session, or by opening a hidden form that is bound to one of the linked tables, and keeping *that* open for the session.

    So long as the connection to the database is kept alive, Access will cache it and remember the password for all the linked tables.

    I can see complications, though, in a case like yours where you are linking to several different alternative back-ends.  The need to create the initial linked tables with unprotected copies of the back-end makes it awkward.  I haven't tried to work through it, but maybe an answer would be to create, at design time, several whole sets of linked tabledefs -- one set for each back-end -- all linked to an unpassworded version.  Then, when you want to switch back-ends at run time, you swap a different set of tabledefs in by renaming.  Still, that would only work if you have a known set of possible back-ends, so you could create the tabledefs in advance.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Friday, November 6, 2015 5:10 AM

All replies

  • I don't recall the details, but the basic idea to prevent disclosing the database password through the TableDef's connection string is something like this:

    1. When linking the tables, tell Access not to store the password.

    2. In the front-end's startup code, use the OpenDatabase method to open a connection to the back-end, providing the password which you have either hard-coded or stored in some concealed manner.

    3. Access will automatically use that connection for access to the linked tables, and won't prompt for the password again.

    I think you may need to keep the Database object you opened alive for the whole Access session, but I'm not completely sure of that.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Thursday, November 5, 2015 3:30 PM
  • An A2007 application (ACCDR) is linked to a password protected database. How to get the password?

    1. Rename an A2007 ACCDR to ACCDE.

    2. Open the ACCDE in Access while pressing Shift key.

    3. Check for some linked tables. All you need to know is a name of a linked table in a "password protected" database.

    4. Press Ctrl+G to switch  to debug window.

    5. In debug window write down the following and press Enter:

    ?CurrentDb.TableDefs("LinkedTableName").Connect

    6. Voilá, you've got connection string with password in it!!!

    I have tried to protect A2007 project DB with a password and translate it to ACCDE + rename to ACCDR. The problem is that an end user cannot run the application in A2007 Runtime without knowing the project's password.

    Is there any workaround, please?

    Thank you very much in advance.


    P.S. My former account was Vladimir Cvajniga but I can't access it anymore, see http://social.msdn.microsoft.com/Forums/office/en-US/5b1284eb-a74f-4c55-9d4c-d6fb6eb25d48/fatal-how-can-i-login-to-my-account?forum=Offtopic#5b1284eb-a74f-4c55-9d4c-d6fb6eb25d48.


    Off Topic - I hope you get your former account unlocked.

    On Topic

    Personal choice, I would never distribute an .accdr for production. The process for making a db .accde rather than just renaming the extension, removes the visible portion of the project and can't be read by humans. I am guessing you meant to say that renaming an .accdr to .accdb and holding down the shift key will allow these things to happen.

    You could disable the shiftkey in the project by adding a little code.

    You can password protect the project.

    I don't know your skill level but I am somewhat familiar with Dirk's. I hesitated to post because I thought maybe there is more here than meets the eye, but then decided to post anyway.

    Is it possible to use the TableDef's connection string to catch the PW to the BE when using a true .accde file instead of a renamed one?


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Thursday, November 5, 2015 7:02 PM
  • In a test of a true .accde, the code - ?CurrentDb.TableDefs("LinkedTableName").Connect

    returns an error; Run-time error '3265': Item not found in this collection.


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Thursday, November 5, 2015 7:14 PM
  • In a test of a true .accde, the code - ?CurrentDb.TableDefs("LinkedTableName").Connect

    returns an error; Run-time error '3265': Item not found in this collection.


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    You should replace LinkedTableName with a table name as it appears in your project. Eg., if you have a table named MyTable, you should do it like this:

    ?CurrentDb.TableDefs("MyTable").Connect


    P.S. My former account was Vladimir Cvajniga but I can't access it anymore, see http://social.msdn.microsoft.com/Forums/office/en-US/5b1284eb-a74f-4c55-9d4c-d6fb6eb25d48/fatal-how-can-i-login-to-my-account?forum=Offtopic#5b1284eb-a74f-4c55-9d4c-d6fb6eb25d48.


    Thursday, November 5, 2015 8:06 PM
  • End user can work with several databases with the same structure. My projects have a function which give the user an opportunity to choose a database (using CommonDialog comdlg32.dll to choose a file). The second step is to relink a FE to a different BE. All is done via VBA code. I have no idea how to tell Access not to use password in this occasion:

    Air code

    Private Function fncConnect() As Integer Dim objTable As TableDef Dim db As Database Dim dbName As String Dim strPWD As String Set db = CurrentDb dbName = "D:\DB\MyDB.accdb" strPWD = "My" & "Secret" & "Pwd" & "128" & "89911" & "x" For Each objTable In db.TableDefs If (objTable.Attributes And dbSystemObject) Then 'reserved 'don't do anything Else If (objTable.Attributes And dbAttachedTable) Or _ (objTable.Attributes And dbAttachedODBC) Then 'linked tables only

    objTable.Connect = ";DATABASE=" & dbName & ";PWD=" & strPWD objTable.RefreshLink End If End If Next objTable End Function



    P.S. My former account was Vladimir Cvajniga but I can't access it anymore, see http://social.msdn.microsoft.com/Forums/office/en-US/5b1284eb-a74f-4c55-9d4c-d6fb6eb25d48/fatal-how-can-i-login-to-my-account?forum=Offtopic#5b1284eb-a74f-4c55-9d4c-d6fb6eb25d48.





    Thursday, November 5, 2015 8:22 PM
  • In a test of a true .accde, the code - ?CurrentDb.TableDefs("LinkedTableName").Connect

    returns an error; Run-time error '3265': Item not found in this collection.

    That must be because you used a table name that didn't actually exist.  I assure you that, in an ACCDE, if the password is stored in the connection string, it will be exposed by displaying the tabledef's connect property in the manner described.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Thursday, November 5, 2015 8:24 PM
  • ACCDRs are created by Package And Deployment Wizard (PDW) by Microsoft. ACCDRs are created automatically:

    1. In PDW you add an ACCDE into a list of distributed files (translated project, no VBA code available in ACCDE)

    2. PDW creates a copy of ACCDE in an MSI package; ACCDE is automatically renamed to ACCDR during the packaging process; ie. you cannot access any VBA code in ACCDR even if you rename it to ACCDE (ACCDB?)

    You should try PDW to get the idea.

    P.S. I do not know how to disable the shiftkey: "You could disable the shiftkey in the project by adding a little code."

    P.P.S: "You can password protect the project."  See my original post: "I have tried to protect A2007 project DB with a password and translate it to ACCDE + rename to ACCDR. The problem is that an end user cannot run the application in A2007 Runtime without knowing the project's password."


    P.S. My former account was Vladimir Cvajniga but I can't access it anymore, see http://social.msdn.microsoft.com/Forums/office/en-US/5b1284eb-a74f-4c55-9d4c-d6fb6eb25d48/fatal-how-can-i-login-to-my-account?forum=Offtopic#5b1284eb-a74f-4c55-9d4c-d6fb6eb25d48.


    Thursday, November 5, 2015 8:35 PM
  • In a test of a true .accde, the code - ?CurrentDb.TableDefs("LinkedTableName").Connect

    returns an error; Run-time error '3265': Item not found in this collection.

    That must be because you used a table name that didn't actually exist.  I assure you that, in an ACCDE, if the password is stored in the connection string, it will be exposed by displaying the tabledef's connect property in the manner described.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    It's still easier than that! You can search for "PWD" string in an ACCDE when you open it in a hex-editor! Yes, is's much easier than ABC to get the password. My lord... :-(

    IMHO, it is a fatal bug in MS Access.


    P.S. My former account was Vladimir Cvajniga but I can't access it anymore, see http://social.msdn.microsoft.com/Forums/office/en-US/5b1284eb-a74f-4c55-9d4c-d6fb6eb25d48/fatal-how-can-i-login-to-my-account?forum=Offtopic#5b1284eb-a74f-4c55-9d4c-d6fb6eb25d48.




    Thursday, November 5, 2015 8:50 PM
  • P.S. I do not know how to disable the shiftkey: "You could disable the shiftkey in the project by adding a little code."

    Here is a code for controlling the Shift key.

    Option Compare Database
    Option Explicit
    
    
    'to disable or enable use one of the following then hit enter and save
    'ap_DisableShift
    'ap_EnableShift
    
    Function ap_DisableShift()
    'This function disable the shift at startup. This action causes
    'the Autoexec macro and Startup properties to always be executed.
    
    On Error GoTo errDisableShift
    
    Dim db As DAO.Database
    Dim prop As DAO.Property
    Const conPropNotFound = 3270
    
    Set db = CurrentDb()
    
    'This next line disables the shift key on startup.
    db.Properties("AllowByPassKey") = False
    
    'The function is successful.
    Exit Function
    
    errDisableShift:
    'The first part of this error routine creates the "AllowByPassKey
    'property if it does not exist.
    If Err = conPropNotFound Then
    Set prop = db.CreateProperty("AllowByPassKey", _
    dbBoolean, False)
    db.Properties.Append prop
    Resume Next
    Else
    MsgBox "Function 'ap_DisableShift' did not complete successfully."
    Exit Function
    End If
    
    End Function
    
    Function ap_EnableShift()
    'This function enables the SHIFT key at startup. This action causes
    'the Autoexec macro and the Startup properties to be bypassed
    'if the user holds down the SHIFT key when the user opens the database.
    
    On Error GoTo errEnableShift
    
    Dim db As DAO.Database
    Dim prop As DAO.Property
    Const conPropNotFound = 3270
    
    Set db = CurrentDb()
    
    'This next line of code disables the SHIFT key on startup.
    db.Properties("AllowByPassKey") = True
    
    'function successful
    Exit Function
    
    errEnableShift:
    'The first part of this error routine creates the "AllowByPassKey
    'property if it does not exist.
    If Err = conPropNotFound Then
    Set prop = db.CreateProperty("AllowByPassKey", _
    dbBoolean, True)
    db.Properties.Append prop
    Resume Next
    Else
    MsgBox "Function 'ap_DisableShift' did not complete successfully."
    Exit Function
    End If
    
    End Function
    
    


    Just takes a click to give thanks for a helpful post or answer.
    Please vote “Helpful” or Mark as “Answer” as appropriate.
    Chris Ward
    Microsoft Community Contributor 2012

    Thursday, November 5, 2015 10:48 PM
  • Your code is working fine! Thank you very much for posting it.

    Unfortunately, even then I can switch to Immediate window by Ctrl+G... even in ACCDE! :-( In Immediate window it's very easy to get a connection string of any table.

    EDIT: I'll play with macros to disable or "re-map" Ctrl+G. :-) I'll let you know ASAP.


    P.S. My former account was Vladimir Cvajniga but I can't access it anymore, see http://social.msdn.microsoft.com/Forums/office/en-US/5b1284eb-a74f-4c55-9d4c-d6fb6eb25d48/fatal-how-can-i-login-to-my-account?forum=Offtopic#5b1284eb-a74f-4c55-9d4c-d6fb6eb25d48.








    Thursday, November 5, 2015 11:21 PM
  • It's very easy to "re-map" Ctrl+G to different keystroke:

    Public Function fncCtrlG() As Integer
       DoCmd.RunCommand acCmdDebugWindow
       Debug.Print "Killroy was HERE."
    End Function
    
    Public Function fncCtrlGMsgDisabled() As Integer
       MsgBox "Ctrl+G is disabled", vbOKOnly + vbCritical
       Debug.Print "Killroy was here."
    End Function
    

    Plus Autokeys macros:

    Macro name: {SomeKey(s)Here} (optionaly preceded by Ctrl/Alt/Shift)
    Action: RunCode
    Command line: fncCtrlG()
    Comment: Re-map Ctrl+G

    Macro name: ^{G}
    Action: RunCode
    Command line: fncCtrlGMsgDisabled()
    Comment: Disable Ctrl+G

    P.S.

    Unfortunately, database password can be found in a hex-editor.


    P.S. My former account was Vladimir Cvajniga but I can't access it anymore, see http://social.msdn.microsoft.com/Forums/office/en-US/5b1284eb-a74f-4c55-9d4c-d6fb6eb25d48/fatal-how-can-i-login-to-my-account?forum=Offtopic#5b1284eb-a74f-4c55-9d4c-d6fb6eb25d48.


    Friday, November 6, 2015 12:34 AM
  • It's very easy to "re-map" Ctrl+G to different keystroke


    You don't need to do that.  If you open the Options dialog to the Current Database tab and uncheck the "Use Access Special Keys" option, it will disable the Ctrl+G combination for opening the Immediate window.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Friday, November 6, 2015 3:29 AM
  • I've done some research, and it's a bit more complicated than I thought.  The principel holds, but Access won't let you create a linked table to a password-protected Jet/ACE (.mdb or .accdb format) back-end without supplying the password and storing it in the tabledef's .Connect property.  But, MVP Brent Spauding told me how to get around that.

    What you do is:

    1. Start with an UN-protected back-end -- no password required to connect to it.  You link the front-end to that back-end.  There's no password, so there's no password to store in the linked tables' Connect properties. 

    2. Close the front-end.

    3. Password-protect the back-end.

    4. Open the front-end in such a way that it makes no attempt to connect to any of the linked tables.

    5. Add code in your start-up routines that opens the back-end directly, using the OpenDatabase method and supplying the database password.  Keep the connection to the database alive -- maybe by assigning from OpenDatabase to a global object variable or to a module-level variable in the code of a hidden form that remains open for the duration of the session, or by opening a hidden form that is bound to one of the linked tables, and keeping *that* open for the session.

    So long as the connection to the database is kept alive, Access will cache it and remember the password for all the linked tables.

    I can see complications, though, in a case like yours where you are linking to several different alternative back-ends.  The need to create the initial linked tables with unprotected copies of the back-end makes it awkward.  I haven't tried to work through it, but maybe an answer would be to create, at design time, several whole sets of linked tabledefs -- one set for each back-end -- all linked to an unpassworded version.  Then, when you want to switch back-ends at run time, you swap a different set of tabledefs in by renaming.  Still, that would only work if you have a known set of possible back-ends, so you could create the tabledefs in advance.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Friday, November 6, 2015 5:10 AM
  • Thank you very much! I think it might work. And it should be quite easy since I use persistent connection to BE anyway. I just didn't know there was no need to add PWD string to a (Table) Connect property when there was persistent connection to BE. I'll let you know about my progress soon.

    I would not try "several whole sets of linked tabledefs -- one set for each back-end" for several reasons:
    1. Project would be static as to BEs.
       Current solution enables end user to create a new BE anytime he/she wants/needs. New BE is a copy of a template database. User selects a folder where the BE will be stored. All done via VBA.
    2. There would be problems with large projects (thousands of linked tables).
    3. There would be big problems with data-sources (QueryDefs & dynamic SQL-strings).


    P.S. My former account was Vladimir Cvajniga but I can't access it anymore, see http://social.msdn.microsoft.com/Forums/office/en-US/5b1284eb-a74f-4c55-9d4c-d6fb6eb25d48/fatal-how-can-i-login-to-my-account?forum=Offtopic#5b1284eb-a74f-4c55-9d4c-d6fb6eb25d48.


    Friday, November 6, 2015 8:11 AM
  • You don't need to do that.  If you open the Options dialog to the Current Database tab and uncheck the "Use Access Special Keys" option, it will disable the Ctrl+G combination for opening the Immediate window.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Oops, I missed "Use Access Special Keys" option. :-)
    Anyway, I need a backdoor just to be sure that I won't lose access to my project (access to Immediate window).

    P.S. My former account was Vladimir Cvajniga but I can't access it anymore, see http://social.msdn.microsoft.com/Forums/office/en-US/5b1284eb-a74f-4c55-9d4c-d6fb6eb25d48/fatal-how-can-i-login-to-my-account?forum=Offtopic#5b1284eb-a74f-4c55-9d4c-d6fb6eb25d48.

    Friday, November 6, 2015 8:20 AM
  • Thanks a million!!! Thanks to Brent Spauding, too!

    I've created a sample project: http://www.streamline.cz/download/access/Blog/PwdProtectedBE.zip. Place MyProject.accde and PwdProtectedBE.accdb to the same folder and run MyProject.accde. Copy the files to different folder and run MyProject.accde. Note that FE reconnects to BE correctly. And finally, AFAIK, there's not an easy way to get the BE's password.

    EDIT: In the sample project you can even remove links from FE to BE before you create ACCDE. :-)

    NOTE: It is necessary to block Ctrl+G (either way), otherwise you could get password just by displaying (Table) Connect property in Immediate window, no matter password protected persistent connection.


    P.S. My former account was Vladimir Cvajniga but I can't access it anymore, see http://social.msdn.microsoft.com/Forums/office/en-US/5b1284eb-a74f-4c55-9d4c-d6fb6eb25d48/fatal-how-can-i-login-to-my-account?forum=Offtopic#5b1284eb-a74f-4c55-9d4c-d6fb6eb25d48.









    Friday, November 6, 2015 10:29 AM