Link to home
Start Free TrialLog in
Avatar of marrowyung
marrowyung

asked on

SQL server 2008 and after encryption method

hi,

We have having new year holiday here and before I do this so the reply time will be a bit slower, we will be off for 4 days starting from tomorrow.

I am now interesting on the topic of SQL server security. how many diff kind of encryption SQL server 2008 and later can offer, TDE is one of them and it is more on hardware level.

how many total diff number of encryption method SQL server have, pros and cons and the way to setting that up please.

wish you a good week.
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Transparent Data Encryption: It is the primary SQL Server encryption option. TDE enables you to encrypt an entire database. Backups for databases that use TDE are also encrypted. It protects the data at rest, which means that the database’s data and log files are encrypted using the AES and 3DES encryption algorithms. Know more about Transparent Data Encryption

Column-level Encryption: To use column level encryption (cell-level encryption), the schema must be changed to varbinary, then reconverted to the desired data type. This means the application must be changed to support the encryption-decryption operation; in addition, it can affect performance.

Encrypting File Systems: Windows Server supports Encrypting File Systems for encrypting data at the file and folder level. EFS uses industry-standard encryption algorithms including AES, SHA, ECC, and smart card–based encryption.

BitLocker: BitLocker works at the volume level, and it protects data when it's at rest by using the AES algorithm. BitLocker doesn't have the same performance concerns associated with EFS

Encrypting and Decrypting Data with the .NET Framework : The .NET Framework supports encryption using the System.Security.Cryptography namespace to perform symmetric or asymmetric encryption.  However, unlike TDE, encrypting data within the application requires that you specifically code the application to perform the encryption by calling the encryption and decryption methods.
@Daniel - You have referred the link that I had already mentioned and copied all the statements from there and pasted it here. Not good. Please don't do this.
Hi Marrowyung,
Just wanted to add to my first comment that out of these mostly people use Column Level encryption and TDE. I have also seen people using Bitlocker.

In case of column level encryption - you can encrypt a column e.g. salary in the employeeSalary table. Salary for each employee is confidential so even developer should not be able to check that out. So in these kinds of cases we use column level encryptions.  So Note this is only of columns not for the entire database. This kind of encryption saved data from internal users and developers.

In case of TDE, the encryption is on the data,log and backup files. Meaning at the Database level, it covers everything in the DB. So we enable the TDE on database level.  Example

ALTER DATABASE [YourDb]
SET ENCRYPTION ON;

Before executing the above statement, you must create master key, certificate and the encryption key. TDE has been handled by the background threads in SQL Server.  This kind of encryption saves us from any outside theft. If any one get access to data file or backup he cannot check out the valuable data.

Refer below for Pros and cons for each kind of encryptions-

BITLOCKER - https://prakashthakor.wordpress.com/2015/02/13/top-5-pros-and-cons-of-microsoft-bit-locker-drive-encryption/

TDE - http://www.sqlmatters.com/Articles/Pros%20and%20Cons%20of%20Transparent%20Data%20Encryption%20(TDE).aspx

BITLOCKER VS EFE - http://lockergnome.com/2012/04/25/bitlocker-vs-efs/

Hope it helps!
Avatar of marrowyung
marrowyung

ASKER

hi,

all these:

"3. Encrypting and Decrypting Data with the .NET Framework

For details - https://msdn.microsoft.com/en-us/library/ee435389.aspx

4. Encrypting File Systems

For details - https://technet.microsoft.com/en-us/library/cc700811.aspx
This is usually not referred as it make the system slow.

5. BitLocker

For details - https://technet.microsoft.com/en-us/library/cc732774(v=ws.11).aspx

If you want to understand anything in detail please let me know.

Refer more at - http://sqlmag.com/database-security/sql-server-encryption-options

Hope it helps!"

is on OS level, not for SQL server level right?  

which option you all use most ?
Hi Marrowyung,

EFS and Bit-locker - are OS level encryption. Other 3 are SQL Server Encryption. Most of the time people use ColumnLevel and TDE.

Hope it helps!

Thank you.
but does it impact the performance in anyway ?

"Most of the time people use ColumnLevel and TDE."

is it enough for compliance purpose ? this is enough for that?
but does it impact the performance in anyway ?

No these will not impact the performance. Yes I think they are enough for us to secure that data.

Hope it helps!
any link for me to setup the column level security ?
Hi,
Please find the microsoft url for the same - https://msdn.microsoft.com/en-IN/library/ms179331.aspx

Hope it helps!
hi,

tks. but you can use TDE and column encryption on the same user database, right?
dear all,

it seems that TDE only protect data at reast, which means the whole .mdf and .ldf files, this is what it means at rest ,right?

then column encryption is about data on the fly or moving?

so expected to be able to use together ? can we still use both when DB is used with replication/mirroring/always on /log shipping ?
Hi,

but you can use TDE and column encryption on the same user database, right?

Yes you can use.

it seems that TDE only protect data at reast, which means the whole .mdf and .ldf files, this is what it means at rest ,right?

Yes TDE helps in protecting ldf file, mdf file, ndf file(if any - secondary files), .bak files.

then column encryption is about data on the fly or moving?

No column encryption is not on the fly. When we save data we save that in encrypted format. End user will insert the data via stored procedure, the code written in sp will encrypt it and save that in the database. Now when the same user tries to open we will decrypt the value and show it to the end user.

so expected to be able to use together ? can we still use both when DB is used with replication/mirroring/always on /log shipping ?

Yes you can use them together. I think yes you can do that(not tried though).

Hope it helps!
"the code written in sp will encrypt it and save that in the database"

code needs to change to encrypt the data ?
code needs to change to encrypt the data ?

Yes we have to write the code to encrypt and decrypt logic once in the sp and you are done.
this is total out of my expectation ! code need to change ! I think SQL will handle it for us at the background.

it encrypt the whole thing right automatically....
hi,

I am reading this:

https://msdn.microsoft.com/en-in/library/ms179331(v=sql.120).aspx

column level audit only need to run script and then it will be setup ?

also in that link:

"encrypt a column of data using a simple symmetric encryption
"

is this means there are other method than 'simple symmetric encryption', what other encryption method column encryption can do ?
one thing, the TDE seems to me that, it only protect the database by not allowing people restore the DB to other HW box, right?

the whole process first use the DPAPI to encrypts the SQL server service master key, which then use to encrypt the database master key for the master database.

so without the same DPAPI, the same HW, the DB can't be restore on diff HW as the DPAPI is diff?
in that link:

https://msdn.microsoft.com/en-in/library/ms179331(v=sql.130).aspx

 'encrypt a column of data using a simple symmetric encryption' and 'encrypt a column of data using symmetric encryption that includes an authenticator'

looks like the same script, what is the diff ?
Hi,

one thing, the TDE seems to me that, it only protect the database by not allowing people restore the DB to other HW box, right?
>> Correct

looks like the same script, what is the diff ?
>> Both are same.

so without the same DPAPI, the same HW, the DB can't be restore on diff HW as the DPAPI is diff?
>> Correct.
">> Correct"

nothing else ?

">> Both are same."

then why show twice ?
Microsoft has many duplicate document. So thats fine. Any more questions?
it seems that there is an SQL encryption method called cell-level encryption since SQL 2015, right? is it column level ?
Yes it is same as column level. They just change the naming.
tks. give you score first, I am sorry I might come back later. hope not.
@marrowyung - I have answered all your question and you selected Daniel's copied answer (from the link I provided) as the solution.

May I know the reason for this ? I need an detailed explanation from your side.
sorry, I think I click the wrong one, I alreayd clear see that before I click, is you ! you are the man! why..
administrator, please give the mark to Pawan Kumar, the full mark
Thank you very much. marrowyung. Appreciate !
yeah, I cna't see why, I double check all the time ! this time is my bad, again , can' see why !
this time realy close correctly I think