Encrypted Columns and Specifying the -XOD Flag in ddlgen

There are special considerations when using the ddlgen -XOD option with encrypted columns.

If you do not specify the -XOD flag in ddlgen, and you:

Did not specify a password when the encryption key was created – ddlgen generates DDL with no password.

Specified a password when the encryption key was first created – ddlgen generates the default password of ‘password’. This is similar to what ddlgen does for roles and login passwords, and its output looks similar to:

-------------------------------------------------------------------
-- DDL for EncryptedKey 'ssn_key'
-------------------------------------------------------------------
print 'ssn_key'

--The DDL is generated with a default password – 'password' as
--a password was specified when this key was created.

create encryption key SampleKeysDB.dbo.ssn_key for AES
with keylength 128 
passwd 'password'
init_vector random 
go

When you specify the-XOD flag in ddlgen, ddlgen generates DDL that includes a system encryption password (if it has been set and DDL is generated for a key encrypted with a system encryption password) and DDL for keys.

Use this syntax to generate a system encryption password:

ddlgen -Usa -P -Sserver -TEK -NsampleKeysdb.dbo.ek1 -XOD The output would look like:

-- System Encryption Password

use SampleKeysDB
go 

sp_encryption 'system_encr_passwd',
'0x8e050e3bb607225c60c7cb9f59124e99866ca22e677b2cdc9a4d09775850f4721',
NULL, 2, 0
go 

-----------------------------------------------------------------------
-- DDL for EncryptedKey 'ek1'
-----------------------------------------------------------------------

print '<<<<< CREATING EncryptedKey - "ek1" >>>>>'
go 

create encryption key SampleKeysDB.dbo.ek1 for AES
with keylength 128 
passwd 0x0000C7BC28C3020AC21401
init_vector NULL 
keyvalue 0xCE74DB1E028FF15D908CD066D380AB4AD3AA88284D6F7742DFFCADCAABE4100D01 
keystatus 32 
go

Note: When migrating keys from a source to a target server using ddlgen, set the system encryption password to NULL (if it exists) in the target server if you want to run the ddlgen output (from the source server) for encryption keys generated using “-XOD” parameter. Failure to do this results in errors when you try to execute the ddlgen output against the target server.