Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Runtime, Packaging & Deployment _ Prevent Importing Odbc Tables From Accde

Posted by: hima193 Mar 10 2019, 12:50 AM

Hello guys
There is a problem in my access application and i hope you guys can help me

I use SQL server backend and everything is great and i dont have any problems but when i tried to distribute my workstations i tried to import the tables in the accde to a new accdb file and the shock was that the tables got imported
The more shocking thing was i extracted the connection string using tabledef.connecti am sure i am not the first programer to encounter this problem

Is there any fix for this issue cause the only thing i can do is using dao recordsets and delete the tables and this will require that i rebuild my forms so it uses dao instead of linked odbc tables

Please guys help me solving this security issue

Posted by: theDBguy Mar 10 2019, 01:39 AM

Hi,

Welcome to UtterAccess!

What kind of data are you trying to protect?

Posted by: hima193 Mar 10 2019, 01:58 AM

Thanks my friend

The table itself can be imported even if its in accde

Posted by: PhilS Mar 10 2019, 07:55 AM

QUOTE

Please guys help me solving this security issue

What exactly is the issue?

I see this question over and over again, and my opinion is opposed to what many other developers think.
The the hyper-condensed summary: If your backend database is secure, there is no issue at all.

You can read an extensive text on the issues involved, some solutions proposed and the reasoning behind my opinion the the article https://codekabinett.com/rdumps.php?Lang=2&targetDoc=SQL-server-authentication-access-application.

Posted by: hima193 Mar 10 2019, 09:01 AM

Thanks for your reply

The problem is in the linked odbc tables

The access let you import the tables even from accde then you can easly extract the connection string

I tried a different approach today and removed the tables and used ado connection to connect to SQL server and get the recordset

Posted by: GroverParkGeorge Mar 10 2019, 12:08 PM

If a user can get to your data in the linked tables in an accde, how is it less secure for them to be able to get that that same data from a different accdb?

Posted by: AlbertKallal Mar 10 2019, 06:44 PM

Actually you can prevent this issue.

The trick is that you can use Access to cache the user id and password and NOT INCLUDE the uid/password in your connection string used for linking the tables.

So, on access start up, you can execute a one-time logon (based on that uid/password). Once you do this then all of your linked tables to SQL server will now work.

And since you don’t include UID/password in the linked table(s) connection, then you enhance security by a rather large margin.

The result of the above is:

If users were to (by intention or access) look at the linked table connection string they will not find a uid/password in that string.

If they launch another copy of access and import the linked tables, they will find they don’t work when you click on them – again because no uid/password is included in the links.

So the approach is:

Execute a one-time logon.

Now link all of your tables WITHOUT the uid/password as part of the string.
(note that you ONLY do this one time - the tables are now linked without uid/password).
(you do not have to re-link on startup anymore). You can distribute the accDE (compiled) with this setup.
(and no DSN or workstation setup is required).

So until such time you execute that one time logon, none of your linked tables will work.

In fact, if a user were to launch the accDE file and NOT let your start up code run, then clicking on any linked table will NOT work.

And as noted, if they import the linked tables to another application (copy of Access running), those linked tables will not work either. (Because they don’t include the uid/password in the link used to SQL server).

There is also the added bonuses that if you were using different logons (SQL) for each user, you can do so without having to re-link tables. You could just prompt for the uid + password, execute a logon to SQL server, and now your linked tables will all work (and again, you don’t re-link and again the linked tables don’t have the uid/password included in their connection strings.

Needless to say, this quite much eliminates your concerns and issues here.

So you first need some logon code.

I use this:

CODE

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 TOP 1 * FROM sys.all_columns"
    qdf.SQL = "select 1 as t"

    qdf.Execute

    TestLogin = True

    Exit Function

TestError:
    TestLogin = False
    Exit Function

End Function

Now, just ensure you re-link all of your tables – but do NOT include the UID/password when you re-link.

And note that you can’t re-link until such time you execute that one time logon. That one time logon will be cached by access for the whole duration of the session. This includes un-handled errors. So to flush the cache, you have to exit access.

And keep in mind this approach works for all server based connection (ODBC). So SQL server, MySQL, Postgres etc.

The simple logon above code and my instructions will suffice, but a full article and explain of this process is outline here:

Power Tip: Improve the security of database connections

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

Now you can either prompt the user for the uid/password on startup, or have your code execute the logon. So additional steps such as some kind of munge on the uid/password you have in hard code could be hidden from power users that might open up the accDE file with a hex editor.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Posted by: hima193 Mar 12 2019, 09:52 AM

AlbertKallal

I cant thank you enough
Thats exactly what i was looking for
Thank you .. thank you .. thank you

Posted by: hishamzero1 Sep 1 2019, 01:28 AM

I have the same concern here..

Posted by: AlbertKallal Sep 1 2019, 02:10 PM

Hi,

QUOTE

I have the same concern here.

Keep in mind the context of this discussion. This whole thread and post ONLY applies to SQL server tables (or MySQL, Oracle etc.). The information here does not apply to regular linked tables to a back end.

The trick here is when you link to SQL server, you not include or save the password during a table link to SQL server. If you already have the linked tables working, but "saved" the password in the table links, then best just delete the table links, and re-link again. And after you delete the table links, exit Access, and THEN execute the logon code, and THEN link (re add) the linked tables.

The wee bit of code I posted should suffice for you, but you can follow the full article and instructions for a complete rundown of how to do this, and how this works.

Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada