Hide System Tables When Connecting To SQL Server

[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.

 

[KitaYama]

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?

Try it for yourself :

Public Function tests()
 
    Dim db As DAO.Database
    Dim tdf As TableDef
    Dim index As Integer

    Set db = OpenDatabase("D:\*******.accde")
    For Each tdf In db.TableDefs
        If Not (tdf.Name Like "USys*" Or tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then
            Debug.Print index & "  " & tdf.Name & " --> " & tdf.Connect
            index = index + 1
        End If
    Next

End Function

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..

Does it matter how I have done it? As stated before, once you open a connection with a password, even if you close the connection, the linked tables show the connect string (even if the connection is dropped.

They would have to be very knowledgeable? Really? The time that "make it hard, then they get tired and stop" is over. Any AI, even Google gives you the methods you need in less than 15 minutes of research.

Please don't misunderstand me. I'm not telling you not to secure the database. I'm just saying Pat's method doesn't work anymore. (Or at least I don't understand how it can work). I've been researching for a way to secure sql server for a long time, but couldn't find a reasonable way.
Once you enable windows authentication, any app can see the structure of the tables.
If you disable windows authentication and use accounts, then the password can be reviled, no matter how many functions and algorithms you use. The last used password is sitting there in connection string and can be used to connect to server with SSMS, Excel, Access or any other application.

I haven't watched the videos yet, but your #31 reply seems that you understand the method. It would be great if you have some links for reading on the mentioned pass through query. I'll watch the videos this weekend. Maybe I can find the answer there.

Thanks.

 

[KitaYama]

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!

Could you please elaborate on this?

Thanks.

 

[tvanstiphout]

Hi KitaYama,
I presume he means code like this:
public function RunAnyActionQuery(byval sql as string)
dim qd as dao.querydef
set qd = currentdb.querydefs("qryBucket")
qd.sql = sql
qd.execute dbFailOnError
RunAnyActionQuery = qd.RecordsAffected
end function

public function RunAnySelectQuery(byval sql as string) as dao.recordset
dim qd as dao.querydef
set qd = currentdb.querydefs("qryBucket")
qd.sql = sql
qd.ReturnsRecords = True
RunAnySelectQuery = qd.OpenRecordset(dbOpenSnapshot)
end function

Note that this can be dangerous in cases where a form is opened and RunAnySelectQuery is called multiple times.
In my code I have a bunch of such queries, and use them round-robin.

 

[GPGeorge]

Try it for yourself :

Public Function tests()

    Dim db As DAO.Database
    Dim tdf As TableDef
    Dim index As Integer

    Set db = OpenDatabase("D:\*******.accde")
    For Each tdf In db.TableDefs
        If Not (tdf.Name Like "USys*" Or tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then
            Debug.Print index & "  " & tdf.Name & " --> " & tdf.Connect
            index = index + 1
        End If
    Next

End Function

Does it matter how I have done it? As stated before, once you open a connection with a password, even if you close the connection, the linked tables show the connect string (even if the connection is dropped.

They would have to be very knowledgeable? Really? The time that "make it hard, then they get tired and stop" is over. Any AI, even Google gives you the methods you need in less than 15 minutes of research.

Please don't misunderstand me. I'm not telling you not to secure the database. I'm just saying Pat's method doesn't work anymore. (Or at least I don't understand how it can work). I've been researching for a way to secure sql server for a long time, but couldn't find a reasonable way.
Once you enable windows authentication, any app can see the structure of the tables.
If you disable windows authentication and use accounts, then the password can be reviled, no matter how many functions and algorithms you use. The last used password is sitting there in connection string and can be used to connect to server with SSMS, Excel, Access or any other application.

I haven't watched the videos yet, but your #31 reply seems that you understand the method. It would be great if you have some links for reading on the mentioned pass through query. I'll watch the videos this weekend. Maybe I can find the answer there.

Thanks.

The connection string in the table is not altered.

The connection is cached in Access after one successful attempt to run a passthru or open a linked table; this is done in VBA at startup.

That cached connection stays in that session until Access is closed, even if that accdb is closed. When Access closes, the cached connection is deleted.

Connection strings in the tables are not altered. Connection strings in Passthrus are not altered.

I'm working on a new video to illustrate this, I hope better than the earlier version.

Note that once I run a passthru to establish a connection, any table or pt can be opened during that session, but the connection string does not include UID or PWD. It does include the server and database, so that's still a potential issue, although the UID and PWD are not available.

I don't know of a way to get at the cached connection string; if someone does, I'd appreciate hearing how.

 

[KitaYama]

That cached connection stays in that session until Access is closed, even if that accdb is closed. When Access closes, the cached connection is deleted.

Connection strings in the tables are not altered. Connection strings in Passthrus are not altered.

I'll test and check. Thanks for explaining.

I'm working on a new video to illustrate this, I hope better than the earlier version.

It's much appreciated.

It does include the server and database, so that's still a potential issue, although the UID and PWD are not available.

For now, I think it's OK. (at least for me.)

Thanks again.