March 22, 2017 at 6:12 am
What is the difference between 'AlwaysEncrypted' AND 'Forcing Encryption from Server'?
You can check your connections' encryption status with the following query:
>> SELECT session_id, client_net_address, encrypt_option FROM sys.dm_exec_connections
then... take a look at the column output for "encrypt_option" (it will be either TRUE or FALSE). Now, to configure ENCRYPTION for your SQL Server:
Configure SQL Server
- Log on to the SQL Server and launch the 'SQL Configuration Manager'. In my example I am using SQL 2016.
- Within SQL Server Configuration Manager, expand 'SQL Server Network Configuration'
- Below that you will see 'Protocols for MSSQLServer' and/or any addt'l instances
- Right Click on 'Protocols for MSSQLServer' and select Properties
- On the 'Flags' tab, you can set 'Force Encryption' to Yes or No to set the 'Force Encryption' option
- If you set 'Force Encryption' to Yes and run this SQL, your connections should be Encrypted:
>> SELECT session_id, client_net_address, encrypt_option FROM sys.dm_exec_connections
Again, my question is: What is the difference between 'AlwaysEncrypted' AND 'Forcing Encryption from Server'?
thx in advance
March 22, 2017 at 6:29 am
Force Encryption only encrypts the network connection between the database server and the client(s) using SSL (maybe TLS) while Always Encrypted encrypts the data at rest (at the column level, so it's not a TDE replacement,) and the encryption / decryption is handled on the client side (so without the client app, you can't decrypt the data in the table.)
So Force Encryption (FE) will protect the data in the pipeline as well as login information (so if you're using SQL logins, you want to do this) as well as any other data.
Always Encrypted (AE) will only encrypt the selected data (say for instance SSN,) but not other data being sent across the wire.
TDE will protect the entire database AT REST, but not anything being sent across the wire.
So for a fairly secure configuration, you would use TDE to protect the entire database, including backups, Force Encryption to protect all the traffic between the server and clients, and Always Encrypted to further protect sensitive data from all except those who should be able to view it (say SSNs or your Grandmas' prize winning cherry pie recipe.)
This article: https://www.simple-talk.com/sql/database-administration/sql-server-encryption-always-encrypted/ has a good explanation of AE right at the start, although it doesn't mention FE.
March 23, 2017 at 8:23 am
jasona.work - Wednesday, March 22, 2017 6:29 AMSo for a fairly secure configuration, you would use TDE to protect the entire database, including backups, Force Encryption to protect all the traffic between the server and clients, and Always Encrypted to further protect sensitive data
Implementing all of these is a little too much and could well have performance ramifications. TDE can be negated by using encrypted backups (either natively or via 3rd party app) and following server hardening BPs, securing the NTFS filesystem and limiting server administrator membership, etc.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 23, 2017 at 9:12 am
Perry Whittle - Thursday, March 23, 2017 8:23 AMjasona.work - Wednesday, March 22, 2017 6:29 AMSo for a fairly secure configuration, you would use TDE to protect the entire database, including backups, Force Encryption to protect all the traffic between the server and clients, and Always Encrypted to further protect sensitive dataImplementing all of these is a little too much and could well have performance ramifications. TDE can be negated by using encrypted backups (either natively or via 3rd party app) and following server hardening BPs, securing the NTFS filesystem and limiting server administrator membership, etc.
I don't disagree on the performance hit, but I can still see a use for TDE in spite of encrypted backups. The classic "drives retired and not wiped / properly destroyed" situation. NTFS security can be overrode (I believe) when such a drive is connected to a new machine, leaving the DB easily accessible. You could enable Encrypting File System on the NTFS drive, but that just moves the performance hit for TDE around a bit (For sensible reasons, MS does not recommend enabling BOTH TDE and EFS. I shudder to think of the hit *that* would cause)
Whether you need to go to the extent of using all three of the methods (Force Encryption + TDE + Always Encrypted) comes down to a couple factors:
1. Is there any regulatory rules / laws that would be best satisfied by such
2. What is your tolerance for the potential risk / costs of your data getting out
3. How confident are you in your procedures for sanitizing replaced hardware
Those aren't (entirely) yes/no factors, every situation (even within the same environment) will be slightly different.
(Side note. I'm required to use both Force Encryption and TDE on all my servers, despite the servers being in a locked server room, in a key card accessed room, on a facility protected by armed guards. And I don't have access to either of the rooms...)
March 23, 2017 at 9:17 am
jasona.work - Thursday, March 23, 2017 9:12 AMPerry Whittle - Thursday, March 23, 2017 8:23 AMjasona.work - Wednesday, March 22, 2017 6:29 AMSo for a fairly secure configuration, you would use TDE to protect the entire database, including backups, Force Encryption to protect all the traffic between the server and clients, and Always Encrypted to further protect sensitive dataImplementing all of these is a little too much and could well have performance ramifications. TDE can be negated by using encrypted backups (either natively or via 3rd party app) and following server hardening BPs, securing the NTFS filesystem and limiting server administrator membership, etc.
I don't disagree on the performance hit, but I can still see a use for TDE in spite of encrypted backups. The classic "drives retired and not wiped / properly destroyed" situation. NTFS security can be overrode (I believe) when such a drive is connected to a new machine, leaving the DB easily accessible. You could enable Encrypting File System on the NTFS drive, but that just moves the performance hit for TDE around a bit (For sensible reasons, MS does not recommend enabling BOTH TDE and EFS. I shudder to think of the hit *that* would cause)
Whether you need to go to the extent of using all three of the methods (Force Encryption + TDE + Always Encrypted) comes down to a couple factors:
1. Is there any regulatory rules / laws that would be best satisfied by such
2. What is your tolerance for the potential risk / costs of your data getting out
3. How confident are you in your procedures for sanitizing replaced hardware
Those aren't (entirely) yes/no factors, every situation (even within the same environment) will be slightly different.(Side note. I'm required to use both Force Encryption and TDE on all my servers, despite the servers being in a locked server room, in a key card accessed room, on a facility protected by armed guards. And I don't have access to either of the rooms...)
And the servers are probably on a secured network as well, right?
March 23, 2017 at 10:01 am
Lynn Pettis - Thursday, March 23, 2017 9:17 AMjasona.work - Thursday, March 23, 2017 9:12 AMPerry Whittle - Thursday, March 23, 2017 8:23 AMjasona.work - Wednesday, March 22, 2017 6:29 AMSo for a fairly secure configuration, you would use TDE to protect the entire database, including backups, Force Encryption to protect all the traffic between the server and clients, and Always Encrypted to further protect sensitive dataImplementing all of these is a little too much and could well have performance ramifications. TDE can be negated by using encrypted backups (either natively or via 3rd party app) and following server hardening BPs, securing the NTFS filesystem and limiting server administrator membership, etc.
I don't disagree on the performance hit, but I can still see a use for TDE in spite of encrypted backups. The classic "drives retired and not wiped / properly destroyed" situation. NTFS security can be overrode (I believe) when such a drive is connected to a new machine, leaving the DB easily accessible. You could enable Encrypting File System on the NTFS drive, but that just moves the performance hit for TDE around a bit (For sensible reasons, MS does not recommend enabling BOTH TDE and EFS. I shudder to think of the hit *that* would cause)
Whether you need to go to the extent of using all three of the methods (Force Encryption + TDE + Always Encrypted) comes down to a couple factors:
1. Is there any regulatory rules / laws that would be best satisfied by such
2. What is your tolerance for the potential risk / costs of your data getting out
3. How confident are you in your procedures for sanitizing replaced hardware
Those aren't (entirely) yes/no factors, every situation (even within the same environment) will be slightly different.(Side note. I'm required to use both Force Encryption and TDE on all my servers, despite the servers being in a locked server room, in a key card accessed room, on a facility protected by armed guards. And I don't have access to either of the rooms...)
And the servers are probably on a secured network as well, right?
Wow! It's like we work for the same employer!
:w00t: :hehe:
March 23, 2017 at 10:06 am
jasona.work - Thursday, March 23, 2017 10:01 AMLynn Pettis - Thursday, March 23, 2017 9:17 AMjasona.work - Thursday, March 23, 2017 9:12 AMPerry Whittle - Thursday, March 23, 2017 8:23 AMjasona.work - Wednesday, March 22, 2017 6:29 AMSo for a fairly secure configuration, you would use TDE to protect the entire database, including backups, Force Encryption to protect all the traffic between the server and clients, and Always Encrypted to further protect sensitive dataImplementing all of these is a little too much and could well have performance ramifications. TDE can be negated by using encrypted backups (either natively or via 3rd party app) and following server hardening BPs, securing the NTFS filesystem and limiting server administrator membership, etc.
I don't disagree on the performance hit, but I can still see a use for TDE in spite of encrypted backups. The classic "drives retired and not wiped / properly destroyed" situation. NTFS security can be overrode (I believe) when such a drive is connected to a new machine, leaving the DB easily accessible. You could enable Encrypting File System on the NTFS drive, but that just moves the performance hit for TDE around a bit (For sensible reasons, MS does not recommend enabling BOTH TDE and EFS. I shudder to think of the hit *that* would cause)
Whether you need to go to the extent of using all three of the methods (Force Encryption + TDE + Always Encrypted) comes down to a couple factors:
1. Is there any regulatory rules / laws that would be best satisfied by such
2. What is your tolerance for the potential risk / costs of your data getting out
3. How confident are you in your procedures for sanitizing replaced hardware
Those aren't (entirely) yes/no factors, every situation (even within the same environment) will be slightly different.(Side note. I'm required to use both Force Encryption and TDE on all my servers, despite the servers being in a locked server room, in a key card accessed room, on a facility protected by armed guards. And I don't have access to either of the rooms...)
And the servers are probably on a secured network as well, right?
Wow! It's like we work for the same employer!
:w00t: :hehe:
Directly or indirectly, you are probably right. Only difference at the moment, haven't had to go to the lengths you are.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply