[dba-SQLServer] SQL Server Field Data Security

[David Emerson]

I see there are extended properties that can be added to a table.
Presumably a simple table script would reveal these but perhaps something
similar?

-----Original Message-----
From: dba-SQLServer
<dba-sqlserver-bounces+newsgrps=dalyn.co.nz at databaseadvisors.com> On Behalf
Of David Emerson
Sent: Monday, June 5, 2023 6:45 PM
To: 'Discussion concerning MS SQL Server'
<dba-sqlserver at databaseadvisors.com>
Subject: Re: [dba-SQLServer] SQL Server Field Data Security

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-usin
> 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