question

mgmjtech-5961 avatar image
0 Votes"
mgmjtech-5961 asked Yufeishao-0810 answered

SQL server database encryption step by step

I have a SQL 2016 database server and would like to implement database encryption. Can someone please point me or have the process on how to do this from the start to the end?

1 how to create master key and certificate
2 run the SQL encryption query

please include other steps that I missed.

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

What are your requirements for the encryption? There are three ways (at least) to implement encryption in SQL Server, and the aim for these methods are different.

Do you want to protect data at rest - that is, prevent that someone walks away with data file and reads the data elsewhere?

Do you want prevent the DBA from reading sensitive data?

Something else?

If you don't know you want to achieve, you cannot achieve it.

0 Votes 0 ·
Yufeishao-0810 avatar image
0 Votes"
Yufeishao-0810 answered

Hi @mgmjtech-5961,

SQL Server provides the following mechanisms for encryption, you can choose different encryption algorithm to meet your needs:
Asymmetric keys
Symmetric keys
Certificates
Transparent Data Encryption(TDE)
Encrypt a Column of Data:https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/encrypt-a-column-of-data?view=sql-server-ver15
Always Encrypted: https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-ver15
Transact-SQL functions:
create certificate and master key with T-SQL, you can refer to:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-certificate-transact-sql?view=sql-server-ver15
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-master-key-transact-sql?view=sql-server-ver15

According to your question, I guess you want to use TDE.
TDE protects data at rest, which is the data and log files and does real-time I/O encryption and decryption of data and log files. TDE encrypts an entire database using that symmetric key called the database encryption key. The database encryption key is protected by other keys or certificates which are protected either by the database master key or by an asymmetric key stored in an EKM module.
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver15

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.