How the script enables TDE on SQL Server

The script is a convenient way to perform end-to-end steps required to configure TDE on a user database.

  1. Create the master key, if required.

    TDE requires that a master key be created in the [master] database. Each database server can only have one master key that is shared amongst all user databases. The password must be provided in the script in this step. If the master key does not already exist, it will be created with the provided password.

  2. Open the master key.

    The master key must be open to perform the subsequent steps. This step ensures the master key is open before continuing. In cases where a master key is already present on the database server this step verifies that the password entered matches the password used to initially create the master key.

  3. Create the 'AppScan' certificate.

    A certificate is created to be used by all AppScan® Enterprise databases on this database server. The name of the certificate is 'APPSCAN_ENT_CERT'.

    Once the certificate is created, it is immediately backed up. This step generates two files: AppScanEntCert.bak and AppScanEntCert.pvk. The file are stored with the database .mdf file in the relevant location:
    • (SQL 2014) C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA
    • (SQL 2012) C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA
    • (SQL 2008) C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA
    • (SQL 2008 R2) C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA
  4. Associate the AppScan certificate with the AppScan Enterprise database.

    This step creates an encryption key on the database based on the certificate created in Step 3.

  5. Enable encryption.

    This step enables encryption on the AppScan Enterprise database.

  6. Test and display results

    A message is printed to the 'Messages' view in SQL Management Studio indicating if the previous steps were successful, and displays the percentage of TDE completion.