Encryption in SQL2005

I found this nice article on Encryption in SQL2005.

http://www.databasejournal.com/features/mssql/article.php/3461471 - Authentication
http://www.databasejournal.com/features/mssql/article.php/3481751 - Authorization

http://www.databasejournal.com/features/mssql/article.php/3483931 - Encryption

http://www.databasejournal.com/features/mssql/article.php/3488046 - Security

http://www.sqlservercentral.com/articles/SQL+Server+2005+-+Security/3058/

As a matter of fact we already have password hashing capability in SQL2000 itself, the PWDENCRYPT function is available to create a one-way hash.

In 2005 there are the EncryptByKey, EncryptByCert, EncryptByAsmKey and EncryptByPassPhrase methods that can be used for encrypting data.

For passwords the encryptByCert / EncryptByAsmKey or EncryptByPassPhrase can used which provide enough security.

EncryptByKey is symmetric key encryption which is usually combined with Certificates to encrypt the Symmetric key used for encypting large amounts data.

At the root level is the Service Master key which is automatically created during SQL2005 installation.

Then we have a Database Master Key which needs to be created using DDL statements for each Database on the SQL Server Instance. The database master key is created using the service master key along with an optional password.

• create master key encryption by password = 'SecretPassword' ----this creates the DB master key
• create certificate rbg_cert with subject = 'cert for rbg' ----creates a certificate using the DB Master key
• create certificate rbg_cert encryption by password = 'secret' with subject = 'cert for rohit' ----- this creates a certificate using password as the Private Key instead of the DB master key.
• create user rbg for certificate rbg_cert ----- creates the PROXY user
• create login rbg_login from certificate rbg_cert --- creates a SQL login -- note logins are created in master DB, thus a rbg_cert should exist in master DB before you can run this command

Important system tables are

1. sys.symmetric_keys - stores master and symmetric keys
2. sys.databases --- list of all db's in the server
3. sys.certificates -- lists certificates in a particular DB
4. sysusers --- lists users in a particular DB, syslogins --- all logins
   

Then Certificates and Asymmetric keys are created using the database Master key and then we use the Encryption functions to encrypt and decrypt data/passwords. The EncryptByKey and EncryptByPassPhrase are symmetric encryption mechanisms which do not involve using the Database master key