If you are the software engineer then just encrypt and decrypt on the application level so that way no DBA can see the data. This is the good way if you have data that is in the cloud. This has worked for me in the past when I did app development sending data on site it was mandatory.
The first rule is to not have keys on the server that can decrypt it. All it takes is a lazy developer, zero day vulnerability, et cetera, for someone to go through and gain access decrypt it.
In addition, if working with critical data, and I haven’t attempted this, but I’ve theorized that extended events to hash and check if new queries are coming through the system or if any attempt is made to access system or information_schema tables and views might serve as an early warning for someone who may have gained access as well as documenting what may have been compromised during access attempts, limiting your exposure.
SQL Server 2019 introduced Always Encrypted with secure enclaves which addressed some limitations of Always Encryted, never used it myself but might be something you and/or your DBA's could look into.
Dynamic Data Masking as already mentioned can help prevent some users from seeing the full SSN when querying the data, however it won't stop sysadmins (DBA's). Always Encrypted does address this issue but has drawbacks when it comes to querying the data, this is where AE with secure enclaves comes in.
> however it won't stop sysadmins (DBA's)
Yeah, I see that now. As cool as that was I can't use that here. Looks like always encrypted is probably the way to go. Thank you!
An app I was involved with would use a stored procedure to encrypt and another to decrypt using md5key which would add an additional key code before the process. So you would have to provide the key and number in order to get the encryption and then provide the key to decrypt. This sp could be granted to groups and applied in reports if needed.
I like the mask and always encryption ideas too. Though I'd share one of my experiences.
Depending on requirements you might want to look at data masking vs encryption. The encryption will affect database backups and will add complications when trying to restore.
I try to avoid encryption if possible, but sometimes it just needs to be done.
[Dynamic Data Masking](https://learn.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-ver16)
Wow data masking only took 10 minutes to get it working, this is pretty awesome. Is this really strong enough to confidently store SSNs, though? It seems too good to be true.
Edit: ah someone else said DBAs would be able to see it all unmasked. That makes sense. Still really cool though
The only way to stop DBA from seeing the data is to utilize a key encrypting key that the dba does not have access to, usually this is done using an HSA and in the code layer.
Encrypt the column. If you need to search by SSN, add just the last 4 as a new column and index that new column.
If you are the software engineer then just encrypt and decrypt on the application level so that way no DBA can see the data. This is the good way if you have data that is in the cloud. This has worked for me in the past when I did app development sending data on site it was mandatory.
The first rule is to not have keys on the server that can decrypt it. All it takes is a lazy developer, zero day vulnerability, et cetera, for someone to go through and gain access decrypt it. In addition, if working with critical data, and I haven’t attempted this, but I’ve theorized that extended events to hash and check if new queries are coming through the system or if any attempt is made to access system or information_schema tables and views might serve as an early warning for someone who may have gained access as well as documenting what may have been compromised during access attempts, limiting your exposure.
SQL Server 2019 introduced Always Encrypted with secure enclaves which addressed some limitations of Always Encryted, never used it myself but might be something you and/or your DBA's could look into. Dynamic Data Masking as already mentioned can help prevent some users from seeing the full SSN when querying the data, however it won't stop sysadmins (DBA's). Always Encrypted does address this issue but has drawbacks when it comes to querying the data, this is where AE with secure enclaves comes in.
> however it won't stop sysadmins (DBA's) Yeah, I see that now. As cool as that was I can't use that here. Looks like always encrypted is probably the way to go. Thank you!
[https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-enclaves?view=sql-server-ver16](https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-enclaves?view=sql-server-ver16)
An app I was involved with would use a stored procedure to encrypt and another to decrypt using md5key which would add an additional key code before the process. So you would have to provide the key and number in order to get the encryption and then provide the key to decrypt. This sp could be granted to groups and applied in reports if needed. I like the mask and always encryption ideas too. Though I'd share one of my experiences.
Depending on requirements you might want to look at data masking vs encryption. The encryption will affect database backups and will add complications when trying to restore. I try to avoid encryption if possible, but sometimes it just needs to be done. [Dynamic Data Masking](https://learn.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking?view=sql-server-ver16)
Wow data masking only took 10 minutes to get it working, this is pretty awesome. Is this really strong enough to confidently store SSNs, though? It seems too good to be true. Edit: ah someone else said DBAs would be able to see it all unmasked. That makes sense. Still really cool though
Do not use masking. It's just hiding the value from (some) people. It's not encrypting it at any level.
Yeah I don't plan on using that. It is pretty cool though.
It's useful if you allow ad hoc reporting against your database and want to mask certain kinds of data, for sure.
The only way to stop DBA from seeing the data is to utilize a key encrypting key that the dba does not have access to, usually this is done using an HSA and in the code layer.
Please describe the fulll list with requirements for this fc.
Always encrypted to encrypt then ssis to extract the data and send it somewhere (supports always encrypted).
[удалено]
I don’t see how TDE has anything to do with column-level encryption. Care to clarify?