[dba-SQLServer] SQL Server Field Data Security

Mon Jun 5 21:09:54 CDT 2023

Sent!

On 6 Jun 2023 at 1:10, John Bartow wrote:

> Hi Stuart,
> I´d like a copy too. Not going to program anything with it but would
> just like to check out the code.
> 
> John B
> 
> > On Jun 5, 2023, at 5:49 PM, Stuart McLachlan
> > <stuart at lexacorp.com.pg> wrote:
> > 
> > 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
> > 
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 