[dba-SQLServer] SQL Server Field Data Security

Mon Jun 5 20:10:57 CDT 2023

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
> 