ddlgen supports generation of DDL statements for encrypted keys. To specify a key, use:
db_Name.owner.keyName
The new type EK, for encryption key, is for generating the DDL to create an encryption key and to grant permissions on it. ddlgen generates encrypted column information and a grant decrypt statement, with the DDL of a table.
This example generates DDL for all encrypted keys in a database “accounts” on a machine named “HARBOR” using port 1955:
ddlgen -Uroy -Proy123 -SHARBOR:1955 -TEK -Naccounts.dbo.%
Alternatively, you can specify the database name with the -D option:
ddlgen -Uroy -Proy134 -SHARBOR:1955 -TEK -Ndbo.% -Daccounts ----------------------------------------------------------------------------- -- DDL for EncryptedKey 'ssn_key' ----------------------------------------------------------------------------- print 'ssn_key' create encryption key accounts.dbo.ssn_key for AES with keylength 128 init vector random go ----------------------------------------------------------------------------- -- DDL for EncryptedKey 'ek1' ----------------------------------------------------------------------------- print 'ek1' create encryption key accounts.dbo.ek1 as default for AES with keylength 192 init vector NULL go use accounts go grant select on accounts.dbo.ek1 to acctmgr_role go
ddlgen also has an extended option to generate the create encryption key that specifies the key’s encrypted value as represented in sysencryptkeys. The option is -XOD and can be used if you must synchronize encryption keys across servers for data movement. For example, to make cc_key on server “PACIFIC” available on server “ATLANTIC”, execute ddlgen using -XOD on “PACIFIC” as follows:
ddlgen -Sfred -Pget2work -SPACIFIC:8532 -TEK -Nsales.dbo.cc_key -XOD
ddlgen output is:
----------------------------------------------------------------------------- -- DDL for EncryptedKey 'cc_key' ----------------------------------------------------------------------------- print 'cc_key' create encryption key sales.dbo.cc_key for AES with keylength 128 passwd 0x0000E1D8235FEBEB118901 init_vector NULL keyvalue 0xF772B99CE547D2932A12E0A83F2114848BD93F38016C068D720DDEBAC4DF8AA001 keystatus 32 go
Next, change the create encryption key command generated by ddlgen to specify the target database on “ATLANTIC,” and run the command on the target server. cc_key is now available on server “ATLANTIC” to decrypt data that is moved in ciphertext form from “PACIFIC” to “ATLANTIC.”
See the Adaptive Server Enterprise Utility Guide for more information about ddlgen syntax options, and see the Replication Server Administration Guide for examples of using ddlgen with replicated databases.
