Monday, December 10, 2012

SQL Server Encryption

Encryption is the process of obfuscating data by the use of a key or password. This can make the data useless without the corresponding decryption key or password. Encryption does not solve access control problems. However, it enhances security by limiting data loss even if access controls are bypassed. For example, if the database host computer is misconfigured and a hacker obtains sensitive data, that stolen information might be useless if it is encrypted.

First of all run below T-SQL statment to get current value
select * from sys.dm_exec_connections
To find Encript_option column value ( It should be FALSE)

1. Open the SQL server Configuration Manager
2. SQL Server Network Configuration
   ( Protocols for MSSQLSERVER)  Right click to see propertise
3. You will see three tabs(Flags,Certificate,Advanced)
4. click Flags tab
Force encryption ( default is No)
 change to Yes


Then it is required to restart SQL Server service
Run the following  T-SQL statment
select * from sys.dm_exec_connections
To find encript_option column value ( It should be TRUE)

Important Note
Although encryption is a valuable tool to help ensure security, it should not be considered for all data or connections. When you are deciding whether to implement encryption, consider how users will access data. If users access data over a public network, data encryption might be required to increase security. However, if all access involves a secure intranet configuration, encryption might not be required. Any use of encryption should also include a maintenance strategy for passwords, keys, and certificates.
Source : http://technet.microsoft.com/en-us/library/bb510663.aspx