January 16, 2014 at 10:22 pm
New Born DBA (1/16/2014)
There are maybe more than 1000 articles on tde and how to implement it but I haven't seen even 1 article that talks about "Kind of threats tde can't protect your data from are listed below".
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 17, 2014 at 6:45 am
Perry and Shaw, thanks for the replies, this has been enlightening! Yesterday I didn't know much about encrypting and 12 hours later I know enough to know where I should implement TDE and where I shouldn't . Thank you so much for taking some time out to resolve this issue.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
January 17, 2014 at 8:56 am
you're welcome, thanks for posting back
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 17, 2014 at 1:09 pm
Good discussion - I apparently missed it.
I would note that with TDE or other encryption technologies, if you're trying to accomplish a goal beyond circling yes on "Encryption: Yes/No", you need to know, as mentioned above, the threats or rules you must follow, and you should know the algorithms and key sizes used.
SQL Server CREATE CERTIFICATE, if you let SQL Server create the cert, creates RSA 1024 bit keys, which the U.S. agency NIST has rated in http://csrc.nist.gov/publications/nistpubs/800-131A/sp800-131A.pdf as Disallowed (the worst rating) for digital signature generation, key agreement and key transport after 2013. RSA keys of 2048 bits and above are Acceptable (the best rating) with no end date stated.
In order to have a SQL Server certificate with >=2048 bit RSA keys, you'll need to generate the key outside of SQL Server and then use the FILE parameter of CREATE CERTIFICATE to import it.
Don't forget to use very strong passwords throughout.
If you do use TDE, make sure it's finished on all the databases you need it to be set up on:
SELECT sd.name,
CASE
WHEN sddek.encryption_state = 0 THEN 'No database encryption key present, no encryption'
WHEN sddek.encryption_state = 1 THEN 'Unencrypted'
WHEN sddek.encryption_state = 2 THEN 'Encryption in progress'
WHEN sddek.encryption_state = 3 THEN 'Encrypted'
WHEN sddek.encryption_state = 4 THEN 'Key change in progress'
WHEN sddek.encryption_state = 5 THEN 'Decryption in progress'
WHEN sddek.encryption_state = 6 THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)'
ELSE CONVERT(VARCHAR(10),sddek.encryption_state)
END AS encryption_state_description,
sddek.*, sc.* from sys.dm_database_encryption_keys sddek
INNER JOIN sys.databases sd
ON sd.database_id = sddek.database_id
LEFT OUTER JOIN sys.certificates sc
ON sc.thumbprint = sddek.encryptor_thumbprint
January 17, 2014 at 1:56 pm
You would usually purchase certs from a recognised vendor rather than self generating them 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 5 posts - 31 through 34 (of 34 total)
You must be logged in to reply to this topic. Login to reply