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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@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!
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!
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 ?
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.
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.
ASKER
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?
"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!
No these will not impact the performance. Yes I think they are enough for us to secure that data.
Hope it helps!
ASKER
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!
Please find the microsoft url for the same - https://msdn.microsoft.com/en-IN/library/ms179331.aspx
Hope it helps!
ASKER
hi,
tks. but you can use TDE and column encryption on the same user database, right?
tks. but you can use TDE and column encryption on the same user database, right?
ASKER
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/alwa ys on /log shipping ?
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/alwa
Hi,
Yes you can use.
Yes TDE helps in protecting ldf file, mdf file, ndf file(if any - secondary files), .bak files.
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.
Yes you can use them together. I think yes you can do that(not tried though).
Hope it helps!
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!
ASKER
"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 ?
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.
ASKER
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....
it encrypt the whole thing right automatically....
ASKER
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 ?
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 ?
ASKER
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?
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?
ASKER
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 ?
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.
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.
ASKER
">> Correct"
nothing else ?
">> Both are same."
then why show twice ?
nothing else ?
">> Both are same."
then why show twice ?
Microsoft has many duplicate document. So thats fine. Any more questions?
ASKER
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.
ASKER
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.
May I know the reason for this ? I need an detailed explanation from your side.
ASKER
sorry, I think I click the wrong one, I alreayd clear see that before I click, is you ! you are the man! why..
ASKER
administrator, please give the mark to Pawan Kumar, the full mark
Thank you very much. marrowyung. Appreciate !
ASKER
yeah, I cna't see why, I double check all the time ! this time is my bad, again , can' see why !
ASKER
this time realy close correctly I think
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.Cryptograp