SQL Server Encryption Notes

ENCRYPTION AT REST (ON DISK) - Transparent Data Encryption or TDE

  • Data and Log files encrypted
    • But memory paged out of the operating system wont be
  • No changes for developers (ie. code remains unchanged)
  • Provides security if SQL Access Controls are bypassed. Eg. Shut down SQL Server and take database files off the server. Stolen backup files
  • Can encrypt single fields or entire database (entire database probably safest)
  • Performance hit estimated at 3-5% CPU - needs to be tested but should be acceptable
  • The database backup are automatically encrypted when database encryption is turned on 

  • Data not encrypted in memory
  • No added protection for access via SQL (ie. TSQL or stored proc. Access Control Lists lock down this access)
  • Need a maintenance strategy for passwords, keys, certificates
    • Lose one of these and the database may not be recoverable
  • More complex database refresh procedure
  • TDE does not increase the size of the data (but negates compression - on average we will need 3x the current disk space, compression ratio typically around 35% of original size)
  • Automatically encrypts the tempdb database
    • I.e. Performance hit for all databases as all tempDB operations are now encrypted
  • Having enough memory is important for performance as data in memory is not encrypted. Retrieving data from disk will have the encryption overhead
  • Other features such as full text search and filestream are not encrypted - we dont use these at the moment.
Other Details 
  • Authenticators option - can force the same user that encrypts the data to be the only person that can decrypt the data
  • Encryption Algorithms - DES, Triple DES, TRIPLE_DES_3KEY, RC2, RC4, 128-bit RC4, DESX, 128-bit AES, 192-bit AES, and 256-bit AES.
  • Asymmetric encryption offers stronger encryption than symmetric - but also very slow  
    • Symmetric is recommended for data
    • Asymmetric is used to protect symmetric keys
  • Can use existing UAT infrastructure for testing as encryption is turned on per database

  • SSL 128bit
  • Server setting - can force encryption or make it mandatory
    • Recommend forcing encryption
  • Change in connection string
    • Eg. Driver=SQLServer;Server=ServerNameHere;UID=UserIdHere;PWD=PasswordHere;Network=DBNETLIB.DLL;Encrypt=YES
    • Eg. Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=dbNameHere;Data Source=ServerNameHere;Use Encryption for Data=True

Also look at:


Popular posts from this blog

Monitoring Raspberry Pi Performance Using Cacti

Using NAGIOS to Check the Physical Memory Available on a Windows Host

Error 2738 Occurs When Installing An MSI Package