[dba-SQLServer] SQL Server Field Data Security

Mon Jun 5 12:08:42 CDT 2023

Exactly.

I've got some PB code that generates keys and encrypts and decrypts strings.  I'll look at  
converting it to VBA and sharing it.

On 5 Jun 2023 at 22:29, David Emerson wrote:

> I see where you are going with this.  The expiry date is not in the
> database, but in a separate file.
> 
> To develop the idea further, why not store the expiry date encrypted
> in a field in the database.  The SQL Administrators wouldn't be able
> to create valid encrypted data and there is no need for every user to
> have the licence file given to them?
> 
> 
> -----Original Message-----
> From: dba-SQLServer
> <dba-sqlserver-bounces+newsgrps=dalyn.co.nz at databaseadvisors.com> On
> Behalf Of Stuart McLachlan Sent: Monday, June 5, 2023 9:48 PM To:
> Discussion concerning MS SQL Server
> <dba-sqlserver at databaseadvisors.com> Subject: Re: [dba-SQLServer] SQL
> Server Field Data Security
> 
> Thinking about it some more, it is actually quite simple.
> 
> Use Public key encryption. Create a public/private key pair.
> You  embed your public key in the access application, it doesn't
> matter at all if anyone has access to the VBA and sees it.
> 
> You then periodically create a licence file containing the expiry date
> (and possibly some other validation text) that is encrypted with your
> private key. i.e. You create a file contain something like
> ABCDE20231231 and encrypt it with your private key.  
> 
> The data in the encryted file can then used to update the field in the
> database. Lots of ways to do that.  You can even include a routine in
> the application to do it on demand if they receive the encrypted
> licence file in an email.
> 
> Whn the application starts, it grabs the data, decrypts it with your
> public key and examines the result. If it starts with "ABCDE", then
> parse the next 8 characters as a date and compare it to the system
> date to see whether the licence is still valid.
> 
> No one is going to be ables to create a valid file that decrypts with
> your public key to an appropriate string. If they mess with that field
> in any way, the application won't start and they will have to ask you
> for a new copy.  :)
> 
> 
> On 5 Jun 2023 at 18:44, David Emerson wrote:
> 
> > Hi Gustav and Stuart,
> > 
> > The Access database will be locked down so that the key will not be
> > available to the users.
> > 
> > The option of a simple API service we host ourselves might be a
> > solution - I will investigate further.  Perhaps storing the info
> > fields in an external database may help but there is still the
> > problem that if a field is in an SQL database on the clients server
> > then the Administrators could change the field and override the
> > activation block (eg change the expiry date to a time in the future
> > so the application doesn't expire).
> > 
> > Is there somewhere else in the SQL database (perhaps a definable
> > "Property" field that could store the data but is not so obvious as
> > a table field?
> > 
> > -----Original Message-----
> > From: dba-SQLServer
> > <dba-sqlserver-bounces+newsgrps=dalyn.co.nz at databaseadvisors.com> On
> > Behalf Of Stuart McLachlan Sent: Monday, June 5, 2023 5:38 PM To:
> > Discussion concerning MS SQL Server
> > <dba-sqlserver at databaseadvisors.com> Subject: Re: [dba-SQLServer]
> > SQL Server Field Data Security
> > 
> > "Key = "Absolutely Curtains"
> > 
> > And therein lies the issue I pointed out in my previous post. If
> > they have access to the Accdb VBA,  encrypting/decrypting in Access
> > as protection of the data in the SQL Server database is pointless..
> > 
> > On 5 Jun 2023 at 6:23, Gustav Brock via dba-SQLServe wrote:
> > 
> > > 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-
> > > us in
> > > g-the-Microsoft-NG-Cryptography-CNG-API.html?preview=GPJVL38JxkM%3
> > > D
> > > 
> > > 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
> > > 
> > > _______________________________________________
> > > dba-SQLServer mailing list
> > > dba-SQLServer at databaseadvisors.com
> > > https://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > > http://www.databaseadvisors.com
> > > 
> > > 
> > 
> > 
> > _______________________________________________
> > dba-SQLServer mailing list
> > dba-SQLServer at databaseadvisors.com
> > https://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > http://www.databaseadvisors.com
> > 
> > _______________________________________________
> > dba-SQLServer mailing list
> > dba-SQLServer at databaseadvisors.com
> > https://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > http://www.databaseadvisors.com
> > 
> > 
> 
> 
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 
> 