[dba-SQLServer] SQL Server Field Data Security

Mon Jun 5 17:50:20 CDT 2023

Thanks David.  I have been lurking and listening.  If you can send me a copy that would be appreciated.

Kind regards
Darryl Collins.

-----Original Message-----
From: dba-SQLServer <dba-sqlserver-bounces+darryl=whittleconsulting.com.au at databaseadvisors.com> On Behalf Of Stuart McLachlan
Sent: Tuesday, June 6, 2023 8:40 AM
To: Discussion concerning MS SQL Server <dba-sqlserver at databaseadvisors.com>
Subject: Re: [dba-SQLServer] SQL Server Field Data Security

I've just sent David a zip file containing a couple of small VBA modules that do this. If anyone else want them, let me know.

On 6 Jun 2023 at 3:08, Stuart McLachlan wrote:

> 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-VB
> > > > A- 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
> > 
> > 
> 
> 
> _______________________________________________
> 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