Hide System Tables When Connecting To SQL Server

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