March 8, 2012 at 9:01 am
As Everyone of us know,SQL Server Enterprise does full DB level encryption and Standard does only Column level encryption. So, do we have any other way to protect the database using sql server standard edition. Please, let me know. Thank you
March 8, 2012 at 10:07 am
It all comes down to what you are protecting against. I would argue that rarely is whole DB encrpytion needed or even beneficial. We need to know more about what you are trying to protect against.
CEWII
March 8, 2012 at 11:14 am
I would like to protect whole 'SQL Server Database' for security reasons.
March 8, 2012 at 11:48 am
That isn't an answer. I'm sorry but it just isn't.
What are you trying to protect against?
Encrypting the disk protects the data at rest but if the database engine is running the only protection you have is what is provided by SQL.
So I ask again what are you trying to protect, until you have identified what you are protecting against you can't decide on your path.
Internal threats, external threats, threats accross the wire? Please define.
CEWII
March 8, 2012 at 12:41 pm
In order to protect against external threats. As you know it contains most of the PII data, I would like to apply some kind of encryption technique to the database. Usually SQL Server standard only support column level encryption, but my question is using the same standard edition, how can we protect the whole database against malicious threats(internal/external).
March 8, 2012 at 1:17 pm
DBA_SQL (3/8/2012)
In order to protect against external threats. As you know it contains most of the PII data, I would like to apply some kind of encryption technique to the database. Usually SQL Server standard only support column level encryption, but my question is using the same standard edition, how can we protect the whole database against malicious threats(internal/external).
There we go. First I generally view whole database encryption in the same view as nuking a city to kill a gnat. Overkill.
With that said you have basically a table or two with information that would be considered PII. Depending on your regulatory requirements can define what exactly is PII. I generally view CC, SSN, Bank information like account #/routing #, and perhaps drivers license # PII. If HIPAA is a requirement then we have to go a bit deeper.
Whole disk or database encryption ONLY protects data at rest. Whole disk encryption presents a clear-text database file if the machine is running. Whole database encrpytion (TDE) presents a clear-text view of the data if the database engine is running. I don't view this as much help in protecting the data. Mainly because if SQL is running you can see all the data in the clear without ANY extra effort.
When you go to column encryption you have a number of options, mainly you can do the encryption in the database or in the application. There are some limited cases where you can do it in both but I have not been real successful at that personally. There are benefits to each, doing it in the application means that the ability to read the data in clear-text is not possible with the database alone, therefore simply having a copy of the database or being able to execute a query against the database will not allow you to see the data in a readable format. But the application has to manage the keys and the encryption, I don't mean to say that you shouldn't do this just that there are trade-offs depending on what you are doing. When you do the encryption in the database you can do it with symmetric or asymetric encryption, usually symmetric because the processing costs of asymetric are substantially higher. You can encrypt the data in such a way that a password is required to decrypt it, or a password is required to unlock the key to decrypt it.
I like to use a little security through obscurity to further muddy the waters. I also like to break the password up to two parts, one part is stored in a sproc in a database the other is passed in by the application. As well as a key code that is hashed in the sproc opening the key. The key code is not stored in the sproc, only its hashed value, I also like to create extra unused keys to help obscure the real one. If you don't know the key code you are less likely to know which key is actually used, it isn't so much a method to prevent access but to make unauthorized decryption and research harder. Also I use the WITH ENCRYPTION statement, I realize that this can be readily decrypted but again, make it a little harder and require more work to get it done.
As you can guess I am very much for column level encryption because it mitigates the trade-offs between security and performance. It helps that it is supported in every version of SQL 2005 and above.
If you have concerns about interception over the wire then you can consider using forced encryption there. However, there are not many instances where I would go to this trouble. If you had data traversing the public internet and you were connecting to SQL directly over the internet I would probably consider it.
If you have specific requirements around HIPAA or PCI we can look at those.
CEWII
March 8, 2012 at 3:12 pm
Elliott Whitlow (3/8/2012)
DBA_SQL (3/8/2012)
In order to protect against external threats. As you know it contains most of the PII data, I would like to apply some kind of encryption technique to the database. Usually SQL Server standard only support column level encryption, but my question is using the same standard edition, how can we protect the whole database against malicious threats(internal/external).There we go. First I generally view whole database encryption in the same view as nuking a city to kill a gnat. Overkill.
With that said you have basically a table or two with information that would be considered PII. Depending on your regulatory requirements can define what exactly is PII. I generally view CC, SSN, Bank information like account #/routing #, and perhaps drivers license # PII. If HIPAA is a requirement then we have to go a bit deeper.
Whole disk or database encryption ONLY protects data at rest. Whole disk encryption presents a clear-text database file if the machine is running. Whole database encrpytion (TDE) presents a clear-text view of the data if the database engine is running. I don't view this as much help in protecting the data. Mainly because if SQL is running you can see all the data in the clear without ANY extra effort.
When you go to column encryption you have a number of options, mainly you can do the encryption in the database or in the application. There are some limited cases where you can do it in both but I have not been real successful at that personally. There are benefits to each, doing it in the application means that the ability to read the data in clear-text is not possible with the database alone, therefore simply having a copy of the database or being able to execute a query against the database will not allow you to see the data in a readable format. But the application has to manage the keys and the encryption, I don't mean to say that you shouldn't do this just that there are trade-offs depending on what you are doing. When you do the encryption in the database you can do it with symmetric or asymetric encryption, usually symmetric because the processing costs of asymetric are substantially higher. You can encrypt the data in such a way that a password is required to decrypt it, or a password is required to unlock the key to decrypt it.
I like to use a little security through obscurity to further muddy the waters. I also like to break the password up to two parts, one part is stored in a sproc in a database the other is passed in by the application. As well as a key code that is hashed in the sproc opening the key. The key code is not stored in the sproc, only its hashed value, I also like to create extra unused keys to help obscure the real one. If you don't know the key code you are less likely to know which key is actually used, it isn't so much a method to prevent access but to make unauthorized decryption and research harder. Also I use the WITH ENCRYPTION statement, I realize that this can be readily decrypted but again, make it a little harder and require more work to get it done.
As you can guess I am very much for column level encryption because it mitigates the trade-offs between security and performance. It helps that it is supported in every version of SQL 2005 and above.
If you have concerns about interception over the wire then you can consider using forced encryption there. However, there are not many instances where I would go to this trouble. If you had data traversing the public internet and you were connecting to SQL directly over the internet I would probably consider it.
If you have specific requirements around HIPAA or PCI we can look at those.
CEWII
TDE will protect the backup files as well. When restoring you have to have the proper credentials or the restore will fail. Also, if someone is able to copy the mdf/ndf/ldf files off the server, TDE will again protect the database if those files are copied to another server.
This is one reason I never got around to implementing TDE at a previous employer. Not enough time in the day to establish the necessary restore routines to ensure that production databases could be restored in other environments (dev/test/upgrade) if needed.
March 8, 2012 at 4:26 pm
Thank you Elliot. You got me a very good Information. Even I set the password using symmetric key method. But, you have got a good point of breaking up the password to two parts, one part is stored in a sproc in a database the other is passed in by the application. Do, you have any script to divide password in such a way. Please, let me know. Thank you.
March 9, 2012 at 9:57 am
I do have code but unfortuantely I can't share it, I don't own the rights to it.
But really the concept is part 1 + part 2 = password
CEWII
March 9, 2012 at 9:58 am
Also, I sometimes create views on the data with built in decryption functions on those columns. If you call the view without opening the key then those fields are NULL, if you open the key and then call them in the same session they return the decrypted data..
CEWII
March 9, 2012 at 11:00 am
Understood. Thank you for your information
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply