locked
Regenerate Database Encryption Key with Server Certificate fails RRS feed

  • General discussion

  • I can't regenerate a database encryption key after upgrading to Sql Server 2016.  The syntax I am using was used in Sql 2014 with no issue.

    ALTER DATABASE ENCRYPTION KEY  
    REGENERATE WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER CERTIFICATE CertName;

    This fails with a incorrect syntax near 'Encryption'.  The failure says it is the encryption in this line, ENCRYPTION BY SERVER CERTIFICATE .

     
    Monday, October 3, 2016 4:01 PM

All replies

  • I don't know about the change, but I notice that the syntax diagram in Books Online look like this:

    -- Syntax for SQL Server 
    ALTER DATABASE ENCRYPTION KEY        REGENERATE WITH ALGORITHM = { AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY }     |     ENCRYPTION BY SERVER       {          CERTIFICATE Encryptor_Name |          ASYMMETRIC KEY Encryptor_Name      }  [ ; ]

    That is, there is a | before ENCRYPTION BY SERVER.

    That is, you either say:

    ALTER DATABASE ENCRYPTION KEY  
    REGENERATE WITH ALGORITHM = AES_256

    or

    ALTER DATABASE ENCRYPTION KEY  
    ENCRYPTION BY SERVER CERTIFICATE CertName;

    Monday, October 3, 2016 9:46 PM
  • That would be a change in the syntax from 2014 to 2016.  When you create a certificate, it does not say what the algorithm is for the certificate, so in 2014 and below where the certificate change has been working for years, the syntax is 

    ALTER DATABASE ENCRYPTION KEY  
    REGENERATE WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER CERTIFICATE CertName;

    Hmm, wondering if the rules changed and how does it know what algorithm to use if I just do 

    ALTER DATABASE ENCRYPTION KEY  
    ENCRYPTION BY SERVER CERTIFICATE CertName;

    Wednesday, October 5, 2016 3:55 PM