[dba-SQLServer] SQL Server Field Data Security

[David Emerson]

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-VBA-us
> > in
> > g-the-Microsoft-NG-Cryptography-CNG-API.html?preview=GPJVL38JxkM%3D
> > 
> > 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