[dba-SQLServer] SQL Server Field Data Security

[Gustav Brock]

Hi David

Set up an encrypted field where you store the license info using a key made from a combo of the user ID and some other field, for example the record ID. See my article:

Encryption in VBA using the Microsoft NG Cryptography (CNG) API

https://www.experts-exchange.com/articles/37113/Encryption-in-VBA-using-the-Microsoft-NG-Cryptography-CNG-API.html?preview=GPJVL38JxkM%3D

Or retrieve the info from a simple API service you host somewhere. But that will require the user to have access to the internet.

/gustav

-----Oprindelig meddelelse-----
Fra: dba-SQLServer <dba-sqlserver-bounces+gustav=cactus.dk at databaseadvisors.com> På vegne af David Emerson
Sendt: 5. juni 2023 07:37
Til: AccessDSQL <dba-SQLServer at databaseadvisors.com>
Emne: [dba-SQLServer] SQL Server Field Data Security

Hi Listers,

 

I have a couple of fields that I would like inaccessible to the client's IT department that might have access to my SQL database.  They store data that is used internally by my Access application to determine whether the users should still be able to use the software.

 

I don't want to store the values in the access application itself as the unlocking of the software should only be done once and apply to all users (who will have their own copies of the FE).

 

My thought is that they would be in a separate table and I would prevent anyone with SA or administrator roles from being able to look at the table design and data (or at least not change the data) unless it is me (but how do I prevent Administrator users from changing the permissions?).

 

What is the best way of doing this?  Is there another method (similar to Access Database Properties) where I could store the data in another place in SQL instead?

 

Regards

 

David Emerson

Dalyn Software Ltd

Adelaide, Australia