March 30, 2010 at 1:06 am
Dear Experts,
One of our clients has the following requirement.
Data in database should be displayed only through front end application.I mean, none should be able to see the proper data even if they can login into the database
and open the tables. (Data should be in encrypted format..).
How can I achieve it (in SQL 2005/2008).? Please guide.
Thanks in advcance.
March 30, 2010 at 3:57 am
Will 'transparent data encryption' in SQL-20008 help i this case..?
Please guide
Thanks.
March 30, 2010 at 7:56 am
The only solution that I am aware of is transperant data encryption in MSSQL2008. This solution performs real-time i/o encryption and decryption of data and log files. The msdn link below covers most everything you need to know about TDE.
http://msdn.microsoft.com/en-us/library/bb934049.aspx
costa
MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends
March 30, 2010 at 8:18 am
SQL really does not have anything that will do what you are asking but you could probably get close. As mentioned there is TDE but that is just meant to prevent someone from walking off with the physical database files or backups and using them on another server. The entire database is encrypted on the disk, however any login with rights to that db can login and view the decrypted data using any method they choose. Another option is cell level encryption. This will encrypt the data in a particular column and is useful for encrypting certain data within the database. Here the data in those columns will not be decrypted until they are actually used. The problem with cell level encryption though is that it does require code and schema changes to implement.
This is a nice technical paper that describes what is available in SQL 2008 and how each option can be used alone or together.
March 30, 2010 at 8:30 am
dang; i followed the fine article cos_ta393 posted; at the very end, i found out only enterprise or developer supports TDE; my 2008 Standard doesn't support that feature.
That's something to keep in mind...Enterprise version is required
Lowell
April 1, 2010 at 5:26 am
Is there anything like that in Oracle.? Anyone aware.?
Just curious....comparing both..
When I told some of my friends that now I have become an SQL Server DBA (certified as well), they suggest me to be an ORACLE DBA...I don't understand why..!!!
😀
April 28, 2010 at 1:41 am
Yes, Oracle has TDE via thier Advanced Security Option.
It is implemented similiar to SQL Server, but it also includes encrypting data across the network.
FYI... I don't know why but our Oracle DBA's are paid more than our SQL Server DBA's.
April 29, 2010 at 4:15 am
Many of the prior replies noted Transparent Data Encryption as an option. It was also noted that TDE encrypts the physical data files and not the data itself. The intent of this feature is to prevent someone from stealing your backup files or .mdf/.ldf files and restoring them on another server.
Your other option is cell-level encryption, which can be accomplished by individually encrypting each column; but your data types for your columns will need to be varbinary. Also note that for each read you will have to decrypt this data which adds overhead to your database.
I would recommend asking more about this requirement that you client has put upon you. It may be a misinterpretation of a regulation or a fear-based requirement. It may also be simply a miscommunication of what they actually want... avoiding the "I got what I asked for, not what I wanted" scenario.
April 29, 2010 at 5:52 am
I agree with thoughts that this could be a misinterpretation of the situation. If an entire database needs encrypting, my thought is that NO ONE should have access to it. All logins should be DENIED.
Or, is this a situation of Production being restored down to Dev and trying to protect the data from developers? If so, a data-scrubbing tool would be more appropriate than encryption. Or scripting out the DB & Tables, then creating faux-data for development / testing.
In my POV (and perhaps mine alone), Encryption should ideally only occur on HIPAA or PPI related data. The whole encrypt-decrypt routine is too processor heavy to implement for every record across the board.
April 30, 2010 at 12:46 am
I think that we can rule out TDE in your case, as it will make data visible to someone who has login permissions. Encrypting columns will work but there could be a performance hit (and I've always found it a pain to put together).
Can the application encrypt the data? there are a lot of security assemblies available...and this seems to fit your requirements.
April 30, 2010 at 12:56 am
Many of the prior replies noted Transparent Data Encryption as an option. It was also noted that TDE encrypts the physical data files and not the data itself. The intent of this feature is to prevent someone from stealing your backup files or .mdf/.ldf files and restoring them on another server.
... This is what exactly my client's requirement.
"to prevent someone from stealing your backup files or .mdf/.ldf files and restoring them on another server."
Thank you all.
April 30, 2010 at 4:29 am
Joy Smith San (4/30/2010)
Many of the prior replies noted Transparent Data Encryption as an option. It was also noted that TDE encrypts the physical data files and not the data itself. The intent of this feature is to prevent someone from stealing your backup files or .mdf/.ldf files and restoring them on another server.
... This is what exactly my client's requirement.
"to prevent someone from stealing your backup files or .mdf/.ldf files and restoring them on another server."
I guess our confusion came from the part where you said no one with proper logins should be able to see the data in the database too. Which makes it more than just TDE / backup encryption.
FYI, .mdf & .ldf files can't be read while SQL Services are still going. So if someone "stole" the drive without logging into SQL or the Server first and stopping the services, then those files should be completely unreadable.
Note use of phrase "SHOULD be".
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply