More Encryption Syntax Changes in SQL Server 2005
Microsoft's Tom Rizzo recently blogged about a change to the syntax in OPEN SYMMETRIC KEY for the April CTP of SQL Server 2005 that didn't make it into BOL in time. Rather than using just the PASSWORD clause, you need to use DECRYPTION BY PASSWORD. So rather that this, which worked in the last CTP:
OPEN SYMMETRIC KEY User2SymmetricKeyPwd
USING PASSWORD = 'AGreatPassword'
You need to do this:
OPEN SYMMETRIC KEY User2SymmetricKeyPwd
DECRYPTION BY PASSWORD = 'AGreatPassword'
But it doesn't affect just OPEN SYMMETRIC KEY. Any place you open a symmectic key the syntax seems to apply. So to explicitly open a database master key you have to use this syntax:
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'WeZ#6hv*XHq#akAEaqcr7%CUP3aQ'
The DUMP and LOAD syntax has also changed for saving keys to files. For example, to backup the service master key, you used to dump it:
DUMP SERVICE MASTER KEY TO FILE = 'c:\servicemasterkey.dat'
PASSWORD = 'S3@fBZir2D^P$x5P&tNr^uR!@wGW'
Now you back it up, as well as use the ENCRYPTION BY syntax:
BACKUP MASTER KEY TO FILE = 'c:\EncryptionDBMasterKey.dat'
ENCRYPTION BY PASSWORD = '#JmyC^ibO%u^gN%F#QxW#$T*n9Dp'
Rather than LOADing the key, now you must restore it, and specify both the decryption password and the encryption password used in the database (which can be the same as I show here):
RESTORE MASTER KEY FROM FILE = 'c:\EncryptionDBMasterKey.dat'
DECRYPTION BY PASSWORD = '#JmyC^ibO%u^gN%F#QxW#$T*n9Dp'
ENCRYPTION BY PASSWORD = '#JmyC^ibO%u^gN%F#QxW#$T*n9Dp'
I'm up to my armpits in SQL Server 2005 encryption this week, so I'll post other changes I find. These other changes are in BOL, so at least you can discover them.