ddlgen

ddlgen supports generation of DDL statements for encryption keys. The syntax is:

ddlgen -Usa -P -Sserver -TEK -Ndb_name.owner.key_name

where:

• EK – is the encrypted key type

• db_name.owner.key_name – is the fully qualified name for the encrypted key.

The type EK, used for encryption key, generates the DDL to create an encryption key and to grant permissions on it. ddlgen generates encrypted column information and a grant decrypt statement, along with the table definition.

See the Adaptive Server Enterprise Utility Guide for the complete ddlgen syntax. See the Replication Server Administration Guide for examples of using ddlgen with replicated databases.

To generate DDL for an encryption key “ssn_key” in a database called “SampleKeysDB,” the syntax is:

ddlgen -Usa -P -Sserver -TEK -NSampleKeysDB.dbo.ssn_key

If “ssn_key” was created as:

create encryption key ssn_key

ddlgen generates this output:

--------------------------------------------------
-- DDL for EncryptedKey 'ssn_key'
--------------------------------------------------
print 'ssn_key'
go
use SampleKeysDB
go
IF EXISTS (SELECT 1 FROM sysobjects o, sysusers u WHERE o.uid=u.uid AND o.name = 'ssn_key' AND u.name = 'dbo' AND o.type = 'EK')
	drop encryption key SampleKeysDB.dbo.ssn_key
IF (@@error != 0)
BEGIN
	PRINT "Error CREATING EncryptedKey 'ssn_key'"
	SELECT syb_quit()
END
go
create encryption key SampleKeysDB.dbo.ssn_key for AES
with keylength 128 
init_vector random 
go 

This example generates DDL for all encryption keys in a database accounts on a machine named “HARBOR” using port 1955:

ddlgen -Uroy -Proy123 -SHARBOR:1955 -TEK
-Naccounts.dbo.%

Alternatively, you use the -D option to specify the database name:

This is the output:

	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 has an option -XOD which generates the create encryption key that specifies the key’s encrypted value as represented in sysencryptkeys. Use the -XOD to synchronize encryption keys across servers for data movement.

When a user specifies -XOD, 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.

For the following syntax and output, the system encryption password has been set in sampleKeysdb, and ek1 has been created with encryption by the system encryption password. The ddlgen command below generates syntax to set the system encryption password using an encrypted version of the original setting in sampleKeysdb. It then creates syntax to create ek1 using the encrypted value of ek1 as stored in sysencryptkeys in sampleKeysdb.

ddlgen -Usa -P -Sserver -TEK -NsampleKeysdb.dbo.ek1 -XOD

The output for the command is:

-- 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 

IF EXISTS (SELECT 1 FROM sysobjects o, sysusers u WHERE o.uid=u.uid	AND o.name = 'ek1' AND u.name = 'dbo'	AND 'o.type = 'EK')		drop encryption key sampleKeysdb.dbo.ek1
go
if (@@error != 0)
BEGIN
	PRINT "Error CREATING EncryptedKey 'ek1'"
	SELECT syb_quit()
END
go
create encryption key SampleKeysDB.dbo.ek1 for AES
with keylength 128 
passwd 0x0000C7BC28C3020AC21401
init_vector NULL 
keyvalue 0xCE74DB1E028FF15D908CD066D380AB4AD3AA88284D6F7742DFFCADCAABE4100D01 
keystatus 32 
go 

If you do not specify the -XOD option, and the key to be migrated has been created in the source database using the with passwd clause, ddlgen generates a create encryption key command with password as its explicit password.This is similar to what ddlgen does for roles and login passwords, and its output looks similar to the following:

-----------------------------------------------------------------------
-- DDL for EncryptedKey 'ssn_key'
-----------------------------------------------------------------------
print '<<<< CREATING EncryptedKey - "ssn_key" >>>>>'
go
use SampleKeysDB
go
IF EXISTS (SELECT 1 FROM sysobjects o, sysusers u WHERE o.uid=u.uid
	AND o.name = 'ssn_key' AND u.name = 'dbo' AND o.type = 'EK'
		drop encryption key SampleKeysDB.dbo.ssn_key
IF (@@error !=0)
BEGIN
	PRINT "Error CREATING EncryptedKey 'k1'"
	SELECT syb_quit()
END
go

-- 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