Hide System Tables When Connecting To SQL Server

[Pat Hartman]

Don't save the password with the linked table

 

[Isaac]

You can always link the tables on the fly with credentials and use dsn-less tables. and a sql auth account

 

[GPGeorge]

Well one time, I linked to a clocking machine system table and changed my credentials to admin and I was able to make changes to the clock in/out data.

The IT Department controls were sub-par at best.

As I suggested, this is only partly an IT problem....

 

[KitaYama]

You can always link the tables on the fly with credentials and use dsn-less tables. and a sql auth account

Where can I find some tutorials?
The only thing that I know, dsn-less tables have a connect string and if I specify the password, the connect string contains the password.
What does exactly "Linking on the fly" means? How the queries that are the source of the bound forms behave then?

thanks.

 

[Isaac]

it means linking a table using vba, which doesn't necessarily require dsn-less connection. the queries will be no different once the linking is done.
if you have a well locked down accde, I don't really see people being able to get that connect string, unless they went into the vba project and used the immediate window, not sure that's even possible in an accde. access security isn't 100%, but these are ideas to get you closer.

yes, you then hvae a connect string in your db - but you also hvae a sql auth account that nobody can get to anywhere else. everything is a trade off

 

[KitaYama]

I don't really see people being able to get that connect string,

You can open a blank database, from vba create an Access object, open the database, loop through table def and read their connect string. Which takes us back to first point. If You can not hide the password, then I don’t see how Pat’s method can help.

If you have a method to hide password, her solution works. Otherwise it’s too much trouble for no additional security.

 

[GPGeorge]

Here are two videos of presentations on Access/SQL Server security. One is more technical, the other more conceptual.

 

[Pat Hartman]

then I don’t see how Pat’s method can help.

I've been looking for the code. I haven't watched the videos George posted but they might have the answer. When I had to solve this problem, I used code that logged into the server separate from the table links which did not include the password. Once I was logged in, the links without the password were fine.

Alternatively, if you hide the linked tables they cannot be seen by other databases so you cannot see the connection strings.

 

[GPGeorge]

I've been looking for the code. I haven't watched the videos George posted but they might have the answer. When I had to solve this problem, I used code that logged into the server separate from the table links which did not include the password. Once I was logged in, the links without the password were fine.

Alternatively, if you hide the linked tables they cannot be seen by other databases so you cannot see the connection strings.

Pat's approach seems to be basically the one demoed back then, although in retrospect my demo was a bit crude, IIRC.

The way I would do it is to create and run a "pilot" passthru query in VBA, using credentials stored in the VBA. That establishes the connection as you describe it. As long as the connection is complete, it stays alive during that session. And, if the passthru is deleted or not saved at all, it can't be used to retrieve the connection string.

You can password protect the VBA project itself even before creating an accde. Run from an accde, it should hide the credentials from all but the most determined and knowledgeable hacker. They'd have to inspect the binary in the accde to find the credentials, which I'm not even sure is possible anyway if it's been password protected.

Anders mostly talked about securing the database from the SQL Server side, but it's seminal stuff.

 

[Isaac]

Otherwise it’s too much trouble for no additional security

That's the only portion of your post I disagree with.

Would you rather:

1. Give people access to your SQL tables via Windows Authentication, which means they can easily connect to those sql tables from absolutely any microsoft office application - including excel or anything that supports vba or even non-vba things - and do literally anything they want on those tables, and not get fired, because that's not hacking, that's utilizing the tables YOU gave them access to, or
2. Would you rather password protect that access using a SQL Auth account, knowing that anyone who is knowledgeable enough to take the steps you listed could get the password? (but that person would be fired for hacking, presumably, b/c they have never been given access to those tables formally and they took known malicious steps to illegaly obtain the password)

I can see some situations, depending, where I'd prefer #2. Preferring #1 is not a given. #2 IS added security.

 

[Isaac]

The way I would do it is to create and run a "pilot" passthru query in VBA, using credentials stored in the VBA. That establishes the connection as you describe it. As long as the connection is complete, it stays alive during that session.

Great idea George! You can create and drop querydefs on the fly too. And the VBA project will be pw protected, plus it could be accde, so...

 

[Isaac]

Also, Kite, I'm not sure you're right about those steps you listed.

Can you really open up a blank database, use vba to open an accde, and get its connect strings? Maybe, but only while they also have the other app open, since you'd drop those connections upon close. The whole thing is pretty well obfuscated. They would have to be very knowledgeable and know how you were doing it to even be turned on to the possibility of that method. It's far more obfuscated (and thus a bit more secure) than just giving them full unrestricted access to your sql tables via Windows Authentication..

 

[GPGeorge]

Great idea George! You can create and drop querydefs on the fly too. And the VBA project will be pw protected, plus it could be accde, so...

Not my idea originally. Ben Clothier wrote a blog post about it a long time ago. (I'd go find it again, but <reasons for being lazy go here>.)

 

[Josef P.]

Note to temp query def and SQL auth user:
As soon as a linked table is opened in the FE, the password can be read out again (with a few “tricks”).
=> Windows Authentication is the most secure option for me. (I can assign the rights specifically to AD users or AD user groups.)

 

[GPGeorge]

Note to temp query def and SQL auth user:
As soon as a linked table is opened in the FE, the password can be read out again (with a few “tricks”).
=> Windows Authentication is the most secure option for me. (I can assign the rights specifically to AD users or AD user groups.)

Can you elaborate on the tricks?

Thank you.

 

[Isaac]

Not my idea originally. Ben Clothier wrote a blog post about it a long time ago. (I'd go find it again, but <reasons for being lazy go here>.)

In most of my databases, I have some queries called qryBucket, which just means "this is a container into which vba can through .sql property values into whenever it needs to", very handy!

 

[Isaac]

Windows Authentication is the most secure option for me. (I can assign the rights specifically to AD users or AD user groups.)

It's the most secure and preferred method from the dba's perspective and most everyone else's, Yes,,, until you introduce microsoft Access into the picture, and you give your company the impression that "don't worry, my app is the only way people will be able to mess with this data" - at that point, windows auth starts to look pretty bad.

 

[GPGeorge]

Also, Kite, I'm not sure you're right about those steps you listed.

Can you really open up a blank database, use vba to open an accde, and get its connect strings? Maybe, but only while they also have the other app open, since you'd drop those connections upon close. The whole thing is pretty well obfuscated. They would have to be very knowledgeable and know how you were doing it to even be turned on to the possibility of that method. It's far more obfuscated (and thus a bit more secure) than just giving them full unrestricted access to your sql tables via Windows Authentication..

I went a

Pat's approach seems to be basically the one demoed back then, although in retrospect my demo was a bit crude, IIRC.

Spoiler:

The way I would do it is to create and run a "pilot" passthru query in VBA, using credentials stored in the VBA. That establishes the connection as you describe it. As long as the connection is complete, it stays alive during that session. And, if the passthru is deleted or not saved at all, it can't be used to retrieve the connection string.

You can password protect the VBA project itself even before creating an accde. Run from an accde, it should hide the credentials from all but the most determined and knowledgeable hacker. They'd have to inspect the binary in the accde to find the credentials, which I'm not even sure is possible anyway if it's been password protected.

Anders mostly talked about securing the database from the SQL Server side, but it's seminal stuff.

After reviewing the video, I remembered that the first part of it involved the user knowing the UID and PWD, which is an alternative situation. That would not work for the user who is willing to bypass any legitimate approach and connect to the database from another accdb or via Excel, etc.

However, the method I demoed could be adapted to avoid even that.

 

[Josef P.]

The problem with ODBC linked tables is that Access keeps the connection in the cache.
Even if the password is not saved and it is only transmitted once when the connection is first established (in whatever way), Access/ACE knows the password and uses it to unlock other linked tables. This can be exploited.

 

[KitaYama]

Alternatively, if you hide the linked tables they cannot be seen by other databases so you cannot see the connection strings.

You can un-hide the tables from an external database too.
First unhide them, then read the connection string. (see #41)

Thanks again for your time.

Edit : You don't even need to unhide the tables. Connection string can be extracted from deep hidden tables too.