SQL 2016 security feature called Always Encrypted- column-level encryption,

may I know if the overall procedure is :

1. Prepare the Environment:

   • Ensure you have a compatible edition of SQL Server 2016, such as Standard or Enterprise.
   • Make sure you have the necessary permissions to configure Always Encrypted.

2. Create Column Master Key (CMK):

   • Generate or obtain a certificate or an asymmetric key that will be used as the Column Master Key.
   • Import the certificate or key into the Windows Certificate Store or Azure Key Vault.

3. Create Column Encryption Key (CEK):

   • Use SQL Server Management Studio (SSMS) or Transact-SQL (T-SQL) to create a Column Encryption Key.
   • Associate the CMK created in the previous step with the CEK.

4. Configure Column Encryption:

   • Identify the columns in your database that you want to encrypt using Always Encrypted.
   • Alter the table schema to specify the encryption settings for the desired columns.

5. Modify Application Connection Strings:

   • Update your application's connection strings to include the necessary encryption settings.
   • Specify the column encryption settings in the connection string, such as the location of the CMK.

6. Test and Validate:

   • Execute queries and operations on the encrypted columns using your application or SSMS.
   • Ensure that the data is properly encrypted and decrypted as expected.

?

The other option you handle the encryption on the application level, whatever accesses the data that it will be the one encrypting single column data. The DB never sees unencrypted data.

DBA will have no knowledge about that, right?

so on DB side, only TDE and always encrypted will works!

but is always encrypted feature in SQL server better than encryption on the application level.

The issue you want to be prepared for is the server fails, HW issue. If you are using a VM, you should still test it that way to make sure you cover all the possible variants..

this involve the migration of the secure key to another TARGET server if the original falled, right? 

The issue you want to be prepared for is the server fails, HW issue. If you are using a VM, you should still test it that way to make sure you cover all the possible variants..

this mean we can still restore the OS to another VM guess host and the restore still work as usual ?

The application encryption keys have to be handled .....  

by application team you mean ?

It is often better to start with a definitional what your requirements are and then go from there.

  As there is hacking news for big org and hacker ask for money. here, out CTO said do we have TDE enabled for all user database? this is it , and later on, team think about how about column data encryption, what we can do to make it better and TELL CTO we are done !

 This is it ! and we have already enable TDE on some DB only and we don't know why, then we ask to enable the TDE for the rest of UAT DB and test it , so we should try to test the backup and restore to match our release behaviour,e.g we backup the DB before any deployment except told to not to be! ok now, how to restore it and can it really can be restore and we can still read the data?

  our enabled TDE is good as it is transparent to the user and application.

  so now how about column encryptoin? it is not transparent to user and application anymore right ?

  how can we set  that up so that only some account can read the encrypted column?

The other deals with SQL server level encryption, the process if the key/cert is "compromised"
what are the steps to change the key/cert...
Does the change require a decrypt all/re-encrypt?

usually how can we know if the key has been compromised ?

You are the DBA or the system admin, i.e. when they come to you when things hit the wall, what is the task you are expected to perform to rectify it?

DBA

Restore the Db from backup, confirm it is operational....?

this is what I want to full test this week. I planned to :

1) restore the DB with different name on the same server, it should works..

2) restore the DB with the same name,    on different server, it should not works as they key on 2x server is not the same.

did you tried to restore to the same server using different name and it should work ?

I run this script to find out any DB do not have TDE enabled:

SELECT 

d.name,

d.is_encrypted,

dek.encryption_state,

dek.percent_complete,

dek.key_algorithm,

dek.key_length

FROM

sys.databases as d

INNER JOIN sys.dm_database_encryption_keys AS dek

ON d.database_id = dek.database_id

and we found out the some key length is 256 and some are 128, any way to change it all to 256 bit ?

hi,

I also found this one :

Always Encrypted in SQL Server 2019 has several limitations12345:

1.   Limited Query Capabilities: Always Encrypted supports only a limited subset of SQL queries1. For example, it supports equality comparisons (only available with deterministic encryption)2. However, it does not support other types of comparisons (for example, LIKE) or arithmetic operations5.

2.   Inefficiency in Large Tables: If you need to perform searches on encrypted data, you’ll have to decrypt the entire column, which can be inefficient, especially for large tables4.

3.   Encryption Inside the Engine: If a field is Always Encrypted, then it’s encrypted inside the engine, and SQL Server can’t do a string search inside the contents3.

4.   Client-Side Operations: Without the enhancements discussed in this article, Always Encrypted protects the data by encrypting it on the client side and never allowing the data or the corresponding cryptographic keys to appear in plaintext inside the Database Engine2. As a result, the functionality on encrypted columns inside the database is severely restricted2.

5.   Choice of Encryption Types: The initial Always Encrypted offer allowed the choice of two encryption types: Randomized and Deterministic5. If you require the ability to do equality comparison and indexing, you must choose Deterministic5.

did you try this one on your platform ? how limiitation you found out ?

and find out this:

There are several alternatives to Always Encrypted in SQL Server 2019:

1.   Transparent Data Encryption (TDE): TDE is a feature of SQL Server Enterprise Edition that performs real-time I/O encryption and decryption of the data and log files1.

2.   NetLib Encryptionizer: This is a third-party tool that provides Transparent Data Encryption and Column Encryption for all versions of SQL Server1. It is easy to set up and maintain, and has virtually no impact on database performance1.

1.   Secure Data Encryption | NetLib Security | Encryptionizer

2.   Performance: Encryptionizer has a fast performance with less than 1% performance impact on a properly sized server2.

3.   DBDefence: This is another third-party tool that provides encryption for SQL Server1. It injects code into the running SQL Process using the Detours SDK1.

4.   Role-Based Access Control (RBAC): You can work with a DBA to create roles and restrict access to sensitive columns2. In this case, you don’t have to encrypt the data2.

5.   Selective Encryption: You can selectively encrypt only the columns having sensitive data2. Also, use relatively faster algorithms like AES2.

it seems other than TDE and Always encrypted, this is all encryption method except item 4?

hi,

in terms of test, what I have done is, on the restored DB on the same server:

1. Execute queries and perform tests to ensure that the restored database is functioning correctly.  

2. Perform operations that access the data in the restored database, such as querying, inserting, updating, or deleting records. 

3. Verify that the data is properly encrypted and decrypted as expected

what else you can think of ? restore the DB to different server and it MUST NOT working ?

Are you looking to see whether the DB backup if obtained invalidate the TDE in use to secure the data. I.e. does the backup have these field encrypted?

it just simple thing , the DB is has encrypted at rest enabled.

and we are also considering always encrypted feature. please suggest the steps on enabling always encrypted feature all the way from planning to post configuration/test.

You should take the time and get knowledgeable with the process especially restoring from backup following hardware failure.

restore test on different hardware right?

 if the logins are not present, they have .....

compromised ?

If not mistaken, your DBs are large. so caution should be the foremost...

NO! all DB is different! it is just because of the regulation! it is financial firm here! 

Are you the Architect or the Administror in the Security Application of the new Policy??

NO !

Complexity is that you have to maintain the existing front end app functional without interruption?

preferrably no code change! this is what TDE is about !

what we CURRENTLY have, TDE is ON on some DB already ! the point is , CTO is asking if encryption at rest is enabled for all DB of the systems/application?

The. You could look at encryption at rest. No doubt they devs have a load generating testing tool.
You could benchmark the current performance and the. See the impact of the changes.

backup size a bit larger once TDE Is ON. I told them to test and no any concern at all after I turn it ON.

You should work out where the encryption keys/certificates will be stored and who has access and how fast in a situation.

another Team own the SQL server handle the key and it is under control.

You should look at the requirements, best practices for the industry.

any good/ workable URL for reference?

You potentially have to make sure your backups are encrypted to secure the data sent offsite....

what if we do not enter any value in key section ? how is that impact on restore? the same thing as TDE ?  but once TDE enabled the backup should be encrypted already? why need this 
?

so any idea on how to make the best use of always encrypted feature of SQL 2019 ?

Have they tested restoring from backup to confirm it is a good backup?

I restored as another DB name for them to test and verify it is STILL TDE ed AFTER restore, and the only thing I didn't test is restore to another DB, which we do not have any test DB at all.

can't test therefore.

Or your asymmetric key is X number of spaces?

?

best use of always encrypted feature of SQL 2019 ?

any example on this ?

hi,

from my point of view, TDE only protect us from some one restore the DB backup to another SQL DB server only , the original DB and restored TDE backup on the SAME server should have all data visible for e.g. DBA, right?

if we need to control who can not see the encrypted data we can only:

1) use role based access control.

2) Always encrypted on row/column level

what is the encrypted backup trying to protect?

 I read this;

https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver16

it say something like this for TDE

a malicious party who steals physical media like drives or backup tapes can restore or attach the database and browse its data.

I don't understand why stealing the disk or backup tapes can restore to another SQL server, any idea?

and that URL always say:

After you enable TDE, immediately back up the certificate and its associated private key. If the certificate becomes unavailable, or if you restore or attach the database on another server, you need backups of the certificate and private key. Otherwise, you can't open the database.

This means if the SQL server the TDE DB is going to restore has the key, e.g. restore to the same DB server, the restore can be done, which is what we tested.

actually what should be  the test plan after enabled TDE? right now I only test :

1) TDE can be enabled

2) the TDE backup can be restore to the SAME SQL server as the same key is there.

3) compared the data in TDE enabled DB and the restored TDE ed DB with different name and we compare if we see the same data.

what else we should test?

some other studies I found is :

test the security of the encrypted data by attempting to access it from unauthorized users or application, we should not be able to read the data without the proper encryption key .

May I know how to test it ?  if I restore the TDE DB from backup to the SAME server, it can be restore and I can see data in the tables of the database, it should be it?

in what situtation I can't see the data inside the TDE DB and the DB stored from TDEed Backup ?

You should test restore to new "hardware" to make sure the procedure is in place through the three groups.

This mean once a new hardware is there as the old hardware host the SQL server with TDE DB dead, we move all DB to the new hardware and we must first restore Cert. first , then TDE DB backup?

May I know what the 3 groups is about ?

 or get the other three groups into testing things out before implementing something like this.

teams are testing their application after we enable the TDE for the missing TDE user database.

presumably people move up, move on, so you would need to make sure the procedure is in pace and is working ....

yeah, usually what is the procedure we should have for it.

1) how to setup TDE

2) how to restore DB From TDEed backup ?

3) how to move TDEed DB to another hardware?

4) how to manage the key,

and . ?

how about my question on always encryption? e.g.

1) what is the different between TDE and encypted backup ?

2) Always encrypted enable end to end encryption between client and SQL server.

for this :

https://stackoverflow.com/questions/21599272/what-can-be-used-as-an-alternative-to-encryption-on-sql-server

I don't see what is the tools involved to encrypted column data, any idea?

now we want to implement always encrypted as an additional layer of protection, as far as I know the encryptin key for TDE and Always encrypted is not the same, any Doc/URL tell us how to setup the always on key with information on where to setup the key and where the key store? 

I know that TDE has the key locally setup on SQL server it protect, Always encrypted has the key do not store locally on the SQL server it is going to protect, right?

we have tested the TDE backup by restoring the TDE DB backup to another SQL server and it is failed as the target SQL server can't find the key.

We have passed a lot of developer test and now finsiehd the restore test on another SQL server, and now how can we test:

• Verify that the data remains encrypted at rest, even when taking backups or copying database files.
• Validate that TDE protects against unauthorized access to the physical storage media (e.g., stolen hard drives).
• Test the behavior of the system when attempting to access encrypted data without the necessary encryption keys?

the second one should I just detach the TDE DB and then try to attach to another SQL server 2019 which do not have the same key ?

I found this procedure:

2. Configure SQL Server 2019:

After installing SQL Server 2019, you need to configure it to use Always Encrypted. You can do this by running the SQL Server Management Studio (SSMS) and connecting to your server. Open the "SQL Server" menu, click on "Configuration Manager," and select "SQL Server Settings." In the "SQL Server Settings" window, expand the "Advanced" section and scroll down to the "Always Encrypted" section. Select the "Enabled" option to enable Always Encrypted.

3. Create a Key Store:

Next, you need to create a key store to store the encryption keys. You can do this by using the "CREATE KEYSTORE" statement. The key store should be stored on a secure location, such as a USB drive or a secure server. Here's an example statement to create a key store:

CREATE KEYSTORE MyKeyStore ON (FILE = 'C:\MyKeyStore.bek');

1. Create a Symmetric Key:

After creating a key store, you need to create a symmetric key to encrypt the data. You can do this by using the "CREATE SYMMETRIC KEY" statement. Here's an example statement to create a symmetric key:

CREATE SYMMETRIC KEY MySymmetricKey WITH ALGORITHM = AES_256_CBC_HMAC_SHA_256;

1. Create a Column Master Key:

Next, you need to create a column master key (CMK) to encrypt the data. You can do this by using the "CREATE MASTER KEY" statement. Here's an example statement to create a CMK:

CREATE MASTER KEY MyCMK WITH ENCRYPTION BY MySymmetricKey;

1. Create a Column Encryption Key:

After creating a CMK, you need to create a column encryption key (CEK) to encrypt the data. You can do this by using the "CREATE ENCRYPTION KEY" statement. Here's an example statement to create a CEK:

CREATE ENCRYPTION KEY MyCEK WITH COLUMN_ENCRYPTION_KEY = MyCMK;

1. Enable Always Encrypted for a Column:

Next, you need to enable Always Encrypted for a column that you want to encrypt. You can do this by using the "ALTER COLUMN" statement. Here's an example statement to enable Always Encrypted for a column:

ALTER COLUMN MyColumn VARCHAR(50) WITH ENCRYPTION = MyCEK;

1. Test the Configuration:

After enabling Always Encrypted for a column, you can test the configuration by running a query to encrypt the data. Here's an example query to encrypt a string:

SELECT ENCRYPTBYKEY(MySymmetricKey, 'Hello World') AS EncryptedString;

This will return an encrypted string that can only be decrypted using the corresponding key.

1. Configure Key Location Management:

Finally, you need to configure Key Location Management (KLM) to manage the encryption keys. You can do this by using the "CREATE KEY LOCATION" statement. Here's an example statement to create a key location:

CREATE KEY LOCATION MyKeyLocation WITHFile = 'C:\MyKeyStore.bek';

This will create a key location that points to the key store file.

1. Update the Connection String:

Finally, you need to update the connection string to include the key location. You can do this by adding the "KEYLOCATION" clause to the connection string. Here's an example connection string that includes the key location:

Data Source=MyServer;Initial Catalog=MyDatabase;Integrated Security=True;KEYLOCATION=MyKeyLocation;

This will connect to the database using the specified key location.

question now is, by this, is that mean is, this is what different from TDE on the encryption key location, which in the example :

CREATE KEYSTORE MyKeyStore ON (FILE = 'C:\MyKeyStore.bek');

we SET a file in the location we want, and that location can be an external  USB and external files system and that file ONLY keep by IT security manager ?

and in this example, the symmetric key  and then the symmetric key to encrypt the CMK , called MyCMK and we  create the final CEK using CMK to encrypt column data?

so the CEK is key tools we use for data encryption ? and it store in the  KEYSTORE, MyKeyStore?

so by this we can pass this files to IT security manager and he keep it ! then what if next time , once a year, we change the key ? what IT security manager supposed to do and run ?   

here I am not sure which value/item is the key value so we can assign a very long one ?

so IT security manager can locate the keystore any where he like and just pass us/developer the full path to the keystore file? and he is the only one change/knows the new key?

and each time when IT security manager change the CEK, no change in application at all ? 

hi,

any update for me sir 
?

there is not much about the answer on TDE instead of always encrypted.

I will do one more test on TDE, e.g. detech the TDE DB and attach to another SQL serve do not have the cert. the result should be it is not working.

hi,

any idea if after disabling TDE if we test, after enabling TDE, application is not working any more, we should only do :

USE <user database>;
DROP DATABASE ENCRYPTION KEY;

Select allOpen in new window

or we also need to:

1) Drop the Certificate and 

2) Drop the Master Key: 

it should not need right ? Dropping the cert and key make all TDEed user database do not have TDE feature anymore right?

so we only do :

USE <user database>;
DROP DATABASE ENCRYPTION KEY;

right?

and I can't find the URL about the SQL server restart after enabling and disabling TDE , any hints for that?

hi,

The test should be using restoring from backup.

this mean we store from the TDEed DB backup  and once it is restored we test using that copy ?

it is PROD DB and on deploy day we can't restore over it .

TDEDed ?

DB has TDE on.

I.e. update the Application settings, web .... that the SQL server is now at a new address.
Will it resume functioning. or ...

need to restore the key to new SQL server and restore TDE backup, this is what you meant ?

Maintain login record
Certificate and restore DB.

this only means once restored to new/targer SQL server by also restore the encryption key, the all infromatoin is there and application can works well?

now we only tested if we do not restore the encryption key the restore is not working. This is the key point we want to test but not also making it work after restoring the key too.

do you think it make sense to test if we detach the DB and reattach to another SQL server do not have the key and it MUST not work ?

The detach/approach under what scenario? 

if the TDE DB can be attached to the target SQL server if the encryption key is not restored to the target SQL server

Detach/attach you loose settings on the existing setup.

what setting will I lost? here a lot of SQL server migration case using detech and attach.

Restore from backup leaving it in restore state, can narrow the transition window to a shorter period of time comparatively.

I agree it may shorten the time as we can use compressed backup.

Attach reestablish logins and permissions....

we can simulate the case by sync all SQL login to the target server first right?

how.long will it take to transfer the mdb/ldb files.

not in compressed format, so longer.

but we just want to test if detech and attach the TDE DB to target SQL server will success or not !

1) without the encryption key the attach should not work.

2) with the encryption key restored to target SQL server attach should work.

etc.

Hi,

We have a concern here that how can we verify if, after SQL 2019 TDE is on, how long the process will take to encrypt all existing data of the user database.

I found this script to shows the overall process of TDE encryption:

SELECT encryption_state

FROM sys.dm_database_encryption_keys

WHERE database_id = DB_ID('YourDatabaseName');

on test platform we have TDE on for weeks but it seems encryption_state STILL 3 instead of 1,which mean still encrypting.

and I run this script to check:

SELECT 
d.name,
d.is_encrypted,
dek.encryption_state,
dek.percent_complete,
dek.key_algorithm,
dek.key_length
FROM
sys.databases as d
INNER JOIN sys.dm_database_encryption_keys AS dek
ON d.database_id = dek.database_id

Select allOpen in new window

and the result is 

precent complete is 0, so after tDE is on, nothing has been encrypted at all?

I read another article that I can run this:

SELECT 
    DB_NAME(database_id) AS DatabaseName, 
    encryption_state, 
    CASE encryption_state 
        WHEN 0 THEN 'No database encryption key present, no encryption'
        WHEN 1 THEN 'Unencrypted'
        WHEN 2 THEN 'Encryption in progress'
        WHEN 3 THEN 'Encrypted'
        WHEN 4 THEN 'Key change in progress'
    END AS encryption_state_desc
FROM sys.dm_database_encryption_keys;

Select allOpen in new window

and it say 

encryption_state = 3 is encrypted

Select allOpen in new window

is that right?

I found this one:

https://medium.com/codex/database-encryption-becomes-transparent-with-sql-server-tde-306f2ad8a026

This script is much better and it is simple enough and the desc already say 3 is encrypted

now we have enabled TDE on PROD last week and so far so good !

state 3 should be it for a complete encyption for all existing data buy just wonder why it change to state 3 right after we turn encryption ON.

THAT FAST?

It is a SQL 2019 box and I STILL believe SQL 2019 has a lot of bugs !! just like when you run whoisactive to see the query blockers in real time ! in SQL 2016 it is showing good but not SQL 2019!

arnold,

any SQL server you are running is SQL 2019?

do not use SQL 2019 man, buggy from time to time as other expertist know, use SQL 2017 with latest CU or SQL 2022

Any thoughts on running sql 2017 on Linux platform?

SQL server on linux only can work as AG read only scale out replica right?

Or you've shifted away from VM running sql.

why ask this ? we are using SQL server on Windows VM

not really, what makes you suggest SQL server on linux ?   on production linux still cost right?

one thing man, we really want to study Always encrypted -- column encryption in detail, any trust/vaild and easy to read on the whole topology about this on SQL server and people can read this 1-2 URL to understand how column encryption works in high level:

1) how is the difference between TDE and Always encrypted in terms of key management and topology design.

2) For always encrypt, how SQL server architect the column encryption, e.g. why sQL server always encryption works in this way.

I am not sure what you are asking about in question 2.

It means how SQL server 2019 

more diagram information like what is from : An overview of the column level SQL Server encryption (sqlshack.com)   is preferrable, tks.

people do not want to read much 

I am not sure what you are asking about in question 2.

why what works this way?

why SQL server column encryption work in this way

Also any URL to show how to migrate column encrypted enabled SQL server DB to a new SQL server?

also any comparsion between TDE vs column encryption architecture ? tks