SQL Server 2008 - Transparent Data Encryption

re: SQL Server 2008 - Transparent Data Encryption

Ashish — Mon, 27 Feb 2012 19:40:28 GMT

Is there any way to encrypt XML data within SQL Server 2008 R2 edition? Our client having such requirement so i am looking for help on that.

re: SQL Server 2008 - Transparent Data Encryption

Andrew.Fryer — Thu, 28 Jan 2010 17:49:20 GMT

I guess you mean so that only autorised people can see it or I would simply delete it!

Seriously since sql server 2005 there has been support for encrypting values in the database for example credit card number as there isn't a real need to encrypt the whole database. These values have to be unencrypted in the application that accesses them.

I would need to understand more about what you are trying to do or protect against to offe rmore help than this, so if you're in the UK perhaps we cna take this offline

Andrew

re: SQL Server 2008 - Transparent Data Encryption

Kishore — Thu, 28 Jan 2010 09:28:05 GMT

How can we hide the actual data in TDE, so that no one can see ?

re: SQL Server 2008 - Transparent Data Encryption

Jes — Wed, 13 Jan 2010 20:34:30 GMT

Andrew,

Can we setup a LOGIN on a database with EXECUTION permission on store procedures and this LOGINcan not view or see what store procedures are coded?

Thanks,

Jes

re: SQL Server 2008 - Transparent Data Encryption

Andrew.Fryer — Wed, 06 Jan 2010 12:53:57 GMT

Jes

I don't understand what you mean by "since Windows Authorization can do anything on database if they get in MSSQL server" for example I can create a login Jes using windows authorisation that only has very limited access to a given database.

If you mean that a winodws administrator has unlimited access then that's not the case either as I could remove them from the sysdmins role if I want to.

I would also add that this has nothing to do with transparent data encryption

Finally if you have a suggestion to improve any Microsoft product then you can go to http://connect.microsoft.com and put in your suggestion. at the very least you should get a reply fomr the product team

re: SQL Server 2008 - Transparent Data Encryption

Jes — Tue, 05 Jan 2010 17:53:52 GMT

Andrew,

Can we ask MSSQL to do some kind of updates to prevent viewing WITH ENCRYPTION store procedures and functions and etc. ( Decrypt ) ? Otherwise it is too dangers for database security since Windows Authorization can do anything on database if they get in MSSQL server?

Thx,

Jes

re: SQL Server 2008 - Transparent Data Encryption

Andrew.Fryer — Tue, 05 Jan 2010 07:55:55 GMT

Jes

Your utility which might well be useful is nothing to do with Transparent Data Encryption in SQL Server 2008. If you give your users full access to your databases then I agree they can install ssms and export the entire database. However that's what schemas are for and you can also use the audit functions in SQL Sevrer 2008 to watch and alert for the kinds of queries theat are requesting sensitive data in bulk.

re: SQL Server 2008 - Transparent Data Encryption

Jes — Mon, 04 Jan 2010 20:04:48 GMT

Andrew,

There are no security if you can't stop someone installing SSMS. People can create any SQL to get entire database scheme and export data by SSMS.

What about some body using tool as in http://www.devlib.net/decryptsql.htm?

Thanks,

Jes

re: SQL Server 2008 - Transparent Data Encryption

Andrew.Fryer — Mon, 04 Jan 2010 08:40:16 GMT

Satya

The data will be returned exactly as before, whether you run the query from an app, excel, or mamagement studio.

TDE only prpotects the database 'at rest' and doesn't affect the queries yoiu run against it when it is in it's usual location. so TDE kicks in when you detach/attach or backup/restore in that you'll need the key to unlock the database if you restore or attach to a new instance virtual machine or physical server.

Andrew

re: SQL Server 2008 - Transparent Data Encryption

Satya — Thu, 24 Dec 2009 23:54:42 GMT

Hi Andrew,

I read about SQL Server 2008 TDE and I got a basic question.

For example, if you have a table called users with userid, username and password as columns which returns the following data when you run a query against users tabel.

SELECT * FROM users

Userid Username Password

1 John John345

2 Paul Paul789

After you implement TDE for a specific database based on your steps described above, when you run the same query how does the data look like ?

Does the result data is in encrypted form or normal form as above ?

Thank you.

Regards,

Satya