Access FE and SQL Server BE, security

[Guus2005]

Hi.

I work with a Access FE and a SQL Server BE in a lot of applications.
Now and again customers asking that if they start a different access database and an ODBC coupling if they can see the tables in the SQL Server database.

Then my answer would be yes you can. A lot of Access versions have passed since the beginning and i was wondering how to keep customers out of the SQL Server database.
Customers should only be allowed to access the tables through my Access application!

I was wondering if there is a token i can send with each request to let SQL Server know that the request came from a legit source.
Any other solution will do.

Background:
Access 2013, SQL Server 2008, mde, hidden navigation pane, shortcuts not allowed.

Thanks!

 

[Minty]

The secret is to use DSN Less connections, then there isn't a requirement to store an ODBC DSN on the local PC that a savvy user can use to connect to the server.

It take a little setting up, but I think is the recognised way around the issue.

 

[isladogs]

Agree with Minty.
That's the approach I've always used and it does exactly what you want.
If you also hide the ribbon there is no way they can create a link.

BTW Why are you using an MDE with Access 2013?
MDE files are far less secure than ACCDE.
In fact it is possible to reverse engineer these back to MDB files relatively easily.

 

[Guus2005]

atm i am using a dns less connection. it uses a connectionstring which is stored in a textfile on the customers computer. it is not encrypted.
Perhaps there's some room for improvement.

[edit]
and like you said ridders, and ACCDE instead of a MDE.
[/edit]

 

[isladogs]

Always store the connection string in code not a table then it's inaccessible to the user.

And as you say, encrypt any data that is sensitive but needs to be in a table.

 

[Galaxiom]

BTW Why are you using an MDE with Access 2013?
MDE files are far less secure than ACCDE.

An mde can be digitally signed. No farting around with trusted locations or signing packages.

In fact it is possible to reverse engineer these back to MDB files relatively easily.

Really? I always thought it was next to impossible to recover the source code.

Anyway my users would not know where to find the file let alone hack it.

 

[isladogs]

An mde can be digitally signed. No farting around with trusted locations or signing packages.

Really? I always thought it was next to impossible to recover the source code.

Anyway my users would not know where to find the file let alone hack it.

Hi Greg
All software is of course as secure as its weakest part.
As I made clear in my security challenges, no Access databases can ever be made 100% secure against a determined hacker with sufficient time and skills. However there are many ways of making it extremely difficult and not worth the effort for a typical user.
Even so, hacking an MDE file is far easier than an ACCDE file

There are specialist companies that can convert both MDE & ACCDE back to source code versions ... at a price ... some are more reputable than others.

However, you can do a lot for free with MDE files without having particularly advanced skills. For example:
a) change the design of forms & reports
b) edit tables
c) remove the database password (without knowing it)
d) remove the VBA project password (without knowing it)
e) re-enable the shift bypass ..... etc

NOTE: Its also possible to do d), e) in an ACCDE/ACCDB file but AFAIK not a), b), c)

For obvious reasons, i'm not going to provide details of the above in this post
However, if you want more info on these, send me an email using the link in my signature line and I'll get back to you

Other points:
I've never found trusted locations an issue when distributing applications as I include script to add the location in the registry as part of the installation process.

Its been a long time since I digitally signed an Access file and I can't remember all the benefits of doing so. However Phillip Stiefel has written about issues with doing this in ACCDB/ACCDE files - basically it doesn't work

 

[Guus2005]

@galaxiom: i've never digitally signed an mde. do you have a link i can investigate on the subject and when signed does that solve my problem?

@ridders: i will look into your security challenges.

Thanks for your input!

 

[isladogs]

Guus

Excellent - I look forward to hearing back from you. Suggest you do them in order as #1 is relatively simple to solve then they get steadily more difficult.

Their purpose is partly a fun challenge to solve but mainly to encourage developers to think carefully about how to make their own applications more secure

Here's an article about digital signing in A2003: https://www.pcreview.co.uk/threads/signing-a-vba-mde-mdb-access-2003.1090842/

Here is Phillip Stiefel's article about issues doing this with ACCDE files: https://codekabinett.com/rdumps.php?Lang=2&targetDoc=signing-vba-code-access-accdb

 

[Pat Hartman]

The biggest security hole is when the user knows his userID and password to the SQL Server database. Most users have no clue but occasionally, you have a more sophisticated user who takes it upon himself to build his own application. He creates a new Access FE, links to the SQL Server database, and he can do whatever he has permissions to do which is generally add/change/delete data but not objects.

My solution to this is to use the user's UserID but to generate his password algorithmic ally. The DBA has to be on board because he needs to control access for each user and we both have to be able to "calculate" the same password.

When a user logs in with his UserID and Access app passowrd, I generate his SQL Server password and connect to the BE behind the scenes.

 

[isladogs]

Pat
Whilst I agree that would be a security hole, I don't see any reason why standard users need a sql server username and password if you use DSN less connection strings.

Also, use other related settings for security including
- the connection string settings should be stored in code (not a table)
- use an ACCDE with shift key bypass and special keys disabled etc....
- remove the navigation pane/ribbon and remove Access options (so these settings can't be reversed)

 

[Galaxiom]

Pat
Whilst I agree that would be a security hole, I don't see any reason why standard users need a sql server username and password if you use DSN less connection strings.

For those who don't quite understand, the connection string can include the username and password if that is what it needs. You just need to stop the users getting hold of it by encrypting the string. Don't just put it in the code because strings in the code are not encrypted in an mde (or accde?).

I'm lucky to be on a domain where I am one of the admins so the permissions are above that level and entirely transparent to the users.

 

[isladogs]

Don't just put it in the code because strings in the code are not encrypted in an mde (or accde?).

In an ACCDB you can see the full connection path (including user name and password) using a Hex editor.

However its partly encrypted using an ACCDE

Of course if you password protect the FE, everything is encrypted