[dba-SQLServer] SQL Server Field Data Security

Stuart McLachlan stuart at lexacorp.com.pg
Mon Jun 5 18:12:28 CDT 2023 

It's on its way :)

On 5 Jun 2023 at 22:51, Darryl Collins wrote:

> Duh.  Let's try staring with "Thanks Stuart...."  
> 
> -----Original Message-----
> From: Darryl Collins 
> Sent: Tuesday, June 6, 2023 8:50 AM
> To: Discussion concerning MS SQL Server
> <dba-sqlserver at databaseadvisors.com> Subject: RE: [dba-SQLServer] SQL
> Server Field Data Security
> 
> 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 databaseadvisor
> s.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=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
>

More information about the dba-SQLServer mailing list