SQL Server cell level encryption. Backup/Restore scenario. Lost DMK password scenario

----------------------------Steps to achieve cell level encryption---------------------------------------

--CREATE MASTER KEY ENCRYPTION BY
--PASSWORD = 'myPass'
--GO

--CREATE CERTIFICATE EncDemoCert
--   WITH SUBJECT = 'Enc Demo SSN Encryption';
--GO

--CREATE SYMMETRIC KEY EncDemoSSNKey
--    WITH ALGORITHM = DES
--    ENCRYPTION BY CERTIFICATE EncDemoCert;
--GO

--Alter table Customer add EncSSN varbinary(500) null


--OPEN SYMMETRIC KEY EncDemoSSNKey
--DECRYPTION BY CERTIFICATE EncDemoCert;
--UPDATE Customer
--SET [EncSSN] = EncryptByKey(Key_GUID('EncDemoSSNKey'), convert(varchar,SSN))
--CLOSE SYMMETRIC KEY EncDemoSSNKey

                           
select * from Customer


--OPEN SYMMETRIC KEY EncDemoSSNKey
--DECRYPTION BY CERTIFICATE EncDemoCert;
--select *
--, CONVERT(VARCHAR, DecryptByKey(EncSSN)) as test
--from Customer
--CLOSE SYMMETRIC KEY EncDemoSSNKey

--------------------------------------Backup Restore scenario---------------------------------------

Lets say the database where encryption is done needs to backed-up and restored. In a full-backup scenario, the DMK and symmetric keys are also automatically backed-up and restored in the target server.

However to use it the DMK has be regenerated using the following queries - 

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'myPass'; 

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'myPass_New';

CLOSE MASTER KEY; 

----------Backup Restore scenario - Source DB server DMK password lost-----------------

If the source server DMK password (myPass in this case) is lost, then a new password can be added to its database master key using the following query in the SOURCE server- 

ALTER MASTER KEY 

    ADD ENCRYPTION BY PASSWORD = 'myPass_New2'.

After the above query, take the backup of the database, restore it in the target server use the queries mentioned above in the "Backup Restore scenario" 

-----------------------------------------Side Note------------------------------------------------------

You have some data in SQL Server encrypted with cert > key > DMK. (all at DB level, not server level)
If you run the following 2 commands, SQL Server will not allow -
--DROP CERTIFICATE EncDemoCert
--DROP MASTER KEY

However if you run the following query
--DROP SYMMETRIC KEY EncDemoKey
the symm key will be deleted permanently and all the encrypted data can NEVER be recovered.

So it is always advisable to backup the certificate and keys to a physical file.

(https://msdn.microsoft.com/en-IN/library/ms178578.aspx)

WCF Security Notes

SYMMETRIC BINDING (WHEN ONLY SERVER HAS THE X509 CERT)

In case of derived key scenario –

2 keys are generated from symmetric key (derived keys). 1 key is used for encryption, while the other is used for signing. The client encrypt both derived keys.

      ASYMMETRIC BINDING ( BOTH CLIENT AND SERVER HAVE X509 CERT)

Although Derived keys can be used for Asymm binding as well, it will be of no use as Encryption and Signing are already happening with different keys.

If you want to use Asymmetric binding, you can ONLY do it via code. Only configuration will not suffice.

First create a custom binding inheriting from Binding class. Then add a BindingExtension and use that bindingextension in the endpoint binding configuration.

WCF by default uses SYMMETRIC KEY with DERIVED KEYS on. You cannot switch to make DERIVED KEYS off UNLESS you make a ‘custom binding’.

                                                     Best of both worlds – 

If negotiateServiceCredential=”true”, then the public key is given to the Client in the beginning of the transaction, however note that IT IS NOT INTEROPERABLE.

To make it interoperable, public key has to given to client by out-of-band mechanism.

Since client has the public key part of the certificate, it can verify the service (by the checking the trusted CA store). Client authenticates the server by using the <identity> element inside <client> element.

Note – Even if the ProtectionLevel of a operation is set to None, its body will be in clear text BUT its message header will have an encrypted section as client credentials are being passed.

Even if client has its own pvk key for authentication to service, WCF defaults to Symmetric binding with DERIVED keys. If you want to override this behavior (i.e use asymmetric binding), you have programmatically create a custom binding and use it client and service end.