X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Prevent Importing Odbc Tables From Accde, Access 2016    
post Mar 10 2019, 12:50 AM

Posts: 4
Joined: 17-November 18

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
Go to the top of the page
post Mar 10 2019, 01:39 AM

UA Moderator
Posts: 77,566
Joined: 19-June 07
From: SunnySandyEggo


Welcome to UtterAccess!

What kind of data are you trying to protect?

Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
post Mar 10 2019, 01:58 AM

Posts: 4
Joined: 17-November 18

Thanks my friend

The table itself can be imported even if its in accde
This post has been edited by hima193: Mar 10 2019, 01:58 AM
Go to the top of the page
post Mar 10 2019, 07:55 AM

Posts: 680
Joined: 26-May 15
From: The middle of Germany

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 Authentication Mechanisms for Access + SQL-Server-Applications.

A professional Access developer tool: Find and Replace for Access and VBA
Go to the top of the page
post Mar 10 2019, 09:01 AM

Posts: 4
Joined: 17-November 18

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
Go to the top of the page
post Mar 10 2019, 12:08 PM

UA Admin
Posts: 36,800
Joined: 20-June 02
From: Newcastle, WA

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?

My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
post Mar 10 2019, 06:44 PM

UtterAccess VIP
Posts: 2,955
Joined: 12-April 07
From: Edmonton, Alberta Canada

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:

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"

    TestLogin = True
    Exit Function

    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


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.

Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
Go to the top of the page
post Mar 12 2019, 09:52 AM

Posts: 4
Joined: 17-November 18


I cant thank you enough
Thats exactly what i was looking for
Thank you .. thank you .. thank you
Go to the top of the page
post Sep 1 2019, 01:28 AM

Posts: 3
Joined: 1-September 19

I have the same concern here..
Go to the top of the page
post Sep 1 2019, 02:10 PM

UtterAccess VIP
Posts: 2,955
Joined: 12-April 07
From: Edmonton, Alberta Canada


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

Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    28th February 2020 - 03:28 AM