securityms-accessodbcms-access-2013dsn

MS Access; how to move it into inaccessible state / DSN-less table without password


I tried to import tables from an already opened MS Access accdb file into a new accdb file using the built in import menu, but I got an error message.
The error message said that the "admin" user has set the file in an inaccessible state. (this is my translation from Hungarian)
So I was not able to import any table or query.

I would like to know, how to move an accdb or accde file into this inaccessible state programmatically, using VBA code.

Reason behind: I would like to distribute a front end accde file which protects itself from anyone stealing odbc connection info, like MySQL server user and password. But the Msysobjects table can be imported into another access file.
I can create tables and queries at startup and delete them at application close, but how to prevent table & query import from it while it is running?
This inaccessible state would come handy.
If there is another way to block table import into other access files, please let me know.

*** Edited section: ***
Design view trick:

DoCmd.OpenForm "frmUnused",acDesign,,,,acHidden  

Works with accdb, table import locked while editing. As expected, doesn't work with accde.

Connection cache approach:
Works with queries.
FIXED: No matter how hard I tried, user and password has been saved with table. I provided a connect string without user and password, but it appeared in the connect string after appending the tabledef to the collection.
Method A saved the unprovided password, method B works only with manual logon.

Remarks: I know that storing user & password in VBA inside an accde isn't the most secure option, but.. 1. Creating a new user on server side & assigning a role might be painfully slow. (see also: beaurocracy) 2. Also handing over server credentials to a few thousand users makes social engineering easier.
Const values, even Private Const values are viewable, if you open the accde file in a text editor. Use functions instead to echo values.

Actual user and password has been replaced in the text below.
LOGON PASS/FAIL and .Connect before/after lines were copied from debug.print output.

I have tried two variations:

A: / proposed by Albert D. Kallal /

B: / works with manual logon /

VBA code used for testing:

Public Function tabletest()

  Dim MyConWithPassWord   As String
  Dim MyCon               As String

  MyCon = "ODBC;DRIVER={MySQL ODBC 5.3 Unicode Driver};Server=localhost;Database=mesterlista"
  MyConWithPassWord = MyCon & ";user=***;password=***"
  
   If TestLogon(MyConWithPassWord) = False Then
      Debug.Print "LOGON FAIL"
      Exit Function
   End If
   
   Debug.Print "LOGON PASS"
   AddOneTable (MyCon)
   
End Function

Public Function TestLogon(strCon As String) As Boolean

   On Error GoTo TestError
   
   Dim dbs          As DAO.Database
   Dim qdf          As DAO.QueryDef
   
   Set dbs = CurrentDb()
   Set qdf = dbs.CreateQueryDef("")
   
   qdf.Connect = strCon
   qdf.ReturnsRecords = False
   
   'Any VALID SQL statement that runs on server will work below.
   qdf.SQL = "SELECT 1;"
   qdf.Execute
   
   TestLogon = True
   
Exit Function
   
TestError:
   TestLogon = False
      
   Debug.Print Err.Number, Err.Description
   Debug.Print strCon

End Function


Public Function AddOneTable(strCon As String)
   On Error GoTo myerr

  Dim tdfcurrent As DAO.TableDef

  Dim LocalTable    As String      ' name of local table link
  Dim ServerTable   As String      ' name of table on SQL server

  LocalTable = "cimke"
  ServerTable = "cimke"

  Set tdfcurrent = CurrentDb.CreateTableDef(LocalTable)

  tdfcurrent.SourceTableName = ServerTable
  tdfcurrent.Connect = strCon
  
  Debug.Print ".Connect before = " & tdfcurrent.Connect
  
  CurrentDb.TableDefs.Append tdfcurrent
  
  CurrentDb.TableDefs.Refresh
  Debug.Print ".Connect after = " & CurrentDb.TableDefs(LocalTable).Connect

  ' fix: removing user / password from tabledef.connect:
  Set tdfcurrent = CurrentDb.TableDefs(LocalTable)
  tdfcurrent.Connect = strCon
  tdfcurrent.RefreshLink
 
  Debug.Print ".Connect after RELINK = " & CurrentDb.TableDefs(LocalTable).Connect



Exit Function

myerr:
   Debug.Print Err.Number, Err.Description
   Debug.Print strCon

End Function   

Solution

  • Yes, you can do this.

    You do NOT have to include the UID + password in the connection strings.

    What this means is that on code start up you can execute a logon. You can either:

    Embed the logon in your VBA code. Only a ONE TIME logon at start up is required and then ALL OF your existing linked tables will work, and this works EVEN WHEN the linked tables don’t have the user + password included in those connection string.

    And you can use some type of encrypting, or hash code in VBA to convert the embedded user + password if you are worried about someone uses some kind of file HEX editor etc. to parse or view the contexts of the accDE file.

    The other simple approach is to prompt the userid + password on start-up (ask the user for id + password). As this point you simply then execute a logon. Once that logon has been executed, then all of the linked tables will work just fine, and those linked tables DO NOT require the UID+PASSOWRD to be included, nor imbedded in those linked tables.

    I cannot stress that a re-link of tables is NOT required if you do this.

    The reason and trick for above is access will cache the logon ONCE a legitimate logon has occurred. ONCE a logon has occurred (a successful connection), then ALL OTHER linked tables will use this cached connection.

    So, I suggest you delete all your linked tables, and then execute a logon, and then re-link all of the tables and LEAVE OUT the user + password when you do this. (Just remember to NOT include the user + password in the connection string used to link the tables. You can do this using the built-in table manager, or you can use code – either way will work.

    Once you link this way, if you launch the application, the table links will NOT work, and they ONLY work AFTER you execute a logon.

    What this means then if someone attempts to open, or import the table links to another database, the linked tables will not work since the linked tables do not include the user + password.

    To execute a logon, use the following code:

    strCon = "valid connection string PLUS USERID + PASSWORD"

    So take the existing connection string (without userID/password), add to the string the userid/password, and then execute the logon code like this:

    TestLogon(strCon)
    

    The code to execute the logon is thus:

    Function TestLogin(strCon As String) As Boolean
    
      On Error GoTo TestError
    
      Dim dbs          As DAO.Database
      Dim qdf          As DAO.QueryDef
    
      Set dbs = CurrentDb()
      Set qdf = dbs.CreateQueryDef("")
    
       qdf.connect = strCon
    
       qdf.ReturnsRecords = False
    
       'Any VALID SQL statement that runs on server will work below.
    
       qdf.sql = "SELECT 1 as t"
       qdf.Execute
    
       TestLogin = True
    
       Exit Function
    
    TestError:
        TestLogin = False
        Exit Function
    
    End Function
    

    Once you execute the above, then the linked tables without the user + password will now work!

    If someone attempts to open, or import the table links, then the user + password will NOT be included, nor do you want to include the user + password in the connection string used to link the table.

    So delete your table links. Execute the above logon code. Now re-link the tables – but do NOT choose the option to include the user + password.

    If you skip the logon code above, and attempt to open a table, then you will get prompted for the userid + password by the ODBC driver. (So what kind of prompt you get will depend on how the database vendor setup their odbc drivers to handle this.

    The above is all you require. The “idea” for the above code and how this works is outlined in the following article:

    https://blogs.office.com/en-us/2011/04/08/power-tip-improve-the-security-of-database-connections/

    However, the same code and the explain in as per above should suffice.

    YOU DO NOT HAVE to include the userid + password in the linked tables, and for obvious reasons of security, you should not.

    Keep in mind that near all client software needs some logon, but if you don’t embed the uid/password anywhere in the code, then the huge hole of security in regards to linked tables in Access is eliminated. If someone attempts to disassemble the application, grab the linked table connection strings etc., they WILL NOT get the password. So if one prompts at startup, logs on, and then nulls out those values, once again the uid/password is NOT embedded in the application.

    Anyone can dissemble the code, and they not get the uid/password if you follow this approach in Access.

    If a user imports those links, and they have the uid/password in plain view, then they will see the uid/password. However, if you link the tables without the uid/password, then there is no requirement to embed the uid/password ANYWHERE in the application to work. All that is required is to execute a valid logon using the above code - once done, then all connections will work without issue.

    So before, or after executing the logon, at no point in time will the linked tables show or have the uid/password. In fact, this even includes if one is using a accDB (non compiled application). If you jump into the debug window and inspect the connection strings used for the active working linked tables, you will see that the uid/password IS NOT INCLUDED IN THE CONNECTION STRING!

    The vast majority of client software systems do have to connect to a database, and if one does not include the uid/password anywhere in that code or application, then that is a "reasonable" level of security.

    Here is some sample code I used:

    Sub TEst222()
    
      Dim MyConWithPassWord   As String
      Dim MyCon               As String
    
      MyCon = "ODBC;DRIVER=SQL Server;SERVER=albertkallal-pc\SQLEXPRESS;DATABASE=test3"
    
      MyConWithPassWord = MyCon & ";UID=TEST3;pwd=password"
    
      TestLogon (MyConWithPassWord)
      AddOneTable (MyCon)
    
    End Sub
    

    TestLogon used was:

    Function TestLogon(strCon As String) As Boolean
    
      On Error GoTo TestError
    
      Dim dbs          As DAO.Database
      Dim qdf          As DAO.QueryDef
    
      Set dbs = CurrentDb()
      Set qdf = dbs.CreateQueryDef("")
    
       qdf.Connect = strCon
       qdf.ReturnsRecords = False
    
       'Any VALID SQL statement that runs on server will work below.
    
       qdf.SQL = "SELECT 1"
    
       qdf.Execute
    
       TestLogon = True
    
    Exit Function
    
    TestError:
       TestLogon = False
       Exit Function
    
    End Function
    

    and to add a table link:

    Sub AddOneTable(strCon As String)
    
      Dim tdfcurrent As DAO.tableDef
    
      Dim LocalTable    As String      ' name of local table link
      Dim ServerTable   As String      ' name of table on SQL server
    
      LocalTable = "MyTable"
      ServerTable = "dbo.MyTable"
    
      Set tdfcurrent = CurrentDb.CreateTableDef(LocalTable)
    
      tdfcurrent.SourceTableName = ServerTable
      tdfcurrent.Connect = strCon
      CurrentDb.TableDefs.Append tdfcurrent
    
    End Sub
    

    My example code is for SQL server. However the article that I link to above is rather good, and the example code was for MySQL.

    Here is a MySQL example:

    Dim MyConWithPassWord   As String
    Dim MyCon               As String
    
    MyCon = "ODBC;DRIVER={MySQL ODBC 3.51 Driver};Server=localhost;Database=test3"
    
    MyConWithPassWord = MyCon & ";User=TEST3;Password=TEST3"
    
    If TestLogon(MyConWithPassWord) = False Then
       MsgBox "LOGON FAIL"
       Exit Sub
    End If
    
    AddOneTable (MyCon)
    

    So you need to test if you actually logged on (that was the WHOLE point of this post).

    If you are receiving a prompt during the re-link, then that means what you have is not working. THE WHOLE POINT here is to eliminate the need to logon.

    Once this is working. you can launch Access, and double click on a table - the ODBC logon will prompt. However, if you execute your logon code first, then you can click on the table without a odbc logon prompt.