[dba-SQLServer] SQL Server Field Data Security

Stuart McLachlan stuart at lexacorp.com.pg
Mon Jun 5 22:33:44 CDT 2023 

I've ported that VBA back to PowerBASIC and I've realised just how crappy that original 
code was (I didn't write it, I picked it up from elsewhere). 

I've now cleaned up the PB version and am working on redoing those two modules whith 
much cleaner code. I'll send out an update to the people who have requested a copy once 
I've done so.  Hopefully, it will be later today :)


On 6 Jun 2023 at 12:09, Stuart McLachlan wrote:

> 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.co
> > >>>> m> 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=GPJVL38Jx
> > >>>>> kM %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
> > 
> 
> 
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 
>

More information about the dba-SQLServer mailing list