How to import the backup encryption certificate into Cloud KMS

[ales_cervenka]

I have the following encryption certificate in my on-premise SQL Server:

CREATE CERTIFICATE [bkp_encryption_certificate] 
FROM BINARY = 0x111111AA111111A1A111111111111111AA111AAAA1A1A11A11A1AA111A1AA1111A11111A111111A11A11111A111111111111111A111111111111111111111A111111111A1111111111111A1A11111111111111111111111111111A11111111111111111A111A1111111111111111111111111A111A1111111111111111111111111A11111111111A111111111111111111111A111111111A1111111111111A1A11111111111111111111111111111A1111111111111111111111111A11111A111111A11A11111111111111111A111111111A1111111111AAA1A1A11A1AAA11111A111A1A1AA1111A1AA11AA1A111AA111A1A1A11AA1A1AA1AAAA1A1A11A111AAA11AAA11A11111A1A111AAA111111A11A1A1111111A1AA1AA111111A1A11111111AAA111111A1A11A11AA11111A1111AAA111A111111AA1AA1111AAA1A111A111111111A1AAA111111AA111111AAA1AA11AAAA1111111A11A111111111A111A1AAAAAA111A111111AA1A1AA1A1AAA11A111AA1111A11AA1A111AA111111A11A111111A1A1AA1A1A11111111AA111111111AA1111A1AA1111A1A1A11AAAA1111A1AAA11111111AA111111111A1111A11A11A1A1A1111A1A1A11AA1A111AA111AA11AAA1AA1AA1AA11111A11111AA1A1AA1111AA11A11AA11111111111111A11111A111111A11A11111A1111111111111111A1111AAA1A1111A1AAA1A11AA1111A11111111A11111AA111A111111A11A1A111A11AA11A111A1AAAAAAAAA1A1A1A1AA1111A11AA1AAAA1111A1AA111AA1A1111A11111A11A1AA11A1111A111111A1A1A1AAA1AAA11A1AAAA1A111AAAA1AA111A11AA11A1AA11A1111A11A11AA11A111AAAA111111AAA1AA1AA11A11A1A1111AAAA1A1AAAA1A111AA1111A1A111111AA1A1111A111A1AA11A11A111111111A111AAA1111AAA1A11A1111AA1111AA11111A111111A1A111AA1A111111111111AA1111111A111A1111AAAA111A1111A11A1111A111A11AA111A1111A111A11A1A11111AA11A11AA1A1111111A111A1A11AA1AA11111A11A11A11111AAAAA1A11 
WITH PRIVATE KEY ( 
BINARY = 
0x2BB2B2B2222222222222222222222222222222222222222222B2222BB2BB2222B2222BB222B2222B2222222222B222222B2BB2222B2BB2222222222B222BB2B22222B222B222BBBB2B2B22BB22B22BB22222BB222BBB222B2B2222B2222B2B222222B22B2222B2BB2222B2BBB22222222B2B2B2BBB22BB22BBB2B22B2B2222BBBBBBB22BB222B22222222B2BB2B2222BB2222222B2222222222B2BB22B2222222B2B22222B2BB22B22BB2BB222222222BB2BBB22BB22BB22BBB22B22BB2222222B22B22BBBBBB22B22B2222B222B2B2222222B2B2BB222222B2BB222B22B22B22222B2B22222BB2B22BBB2222BB22B2222B2B22B22BB22BB22222222B2BBB22BBBB2B2B2B2B2B2B2B222B2B2BB222BBB22B2BB222BB2BB2B222B2B2222BBB2222BBB2222BBB222B22B2B2222B2B2222222BB2BB22B2222B2B22B22BB22222B2B22222B2B22B2BB222222BBB22222222B222222222222B22BB22B22B22222BB2222222B22222B22222B22B2B2B222B2B2222222222BB222B222B222222B22B22B22BB22B2222B2BB2BB22B2B222B2B22222BB22B2B2222222B2BBBBB222BB22222222BB2222B2B22222BB2BBB2222B2BB2222BBBBB2B2222B2B22B22B22B222B2B2B2222BB222BBB22BB2222B2BBBBBB22222B2B22BB22222B222B2B2B2222BB22B2B2B2B2BB2222B2BBBBB22B22B2BBB22222BBB2222B222B2B22BBB2B22B22B2222B2BBB2222BBBB2BBBBBB2B22222222B222222B2BB22BB222222B2222B2222B2BBB2B2BB222B2B2B2222B22222B22B222B22B22B222B22B2222B22B2222222BBBB222B222222B22B2BBBBB2222B2B2B22222B222B2B22B2B222B222B2222BB2B2B222BBB2222BB2B222222B22BBB2222B22222222B222B2B2B2BB222BB2BB2B22BB22BBB22B2B2BBBB2BB22B22B22BBBBB2BB22222BBBBB2222B2222BB22B2B2B22BBB22B22222222BB222B222BB22222222222B222222222B2B2B22222B2B2B222222B222BB222BB2222BBB2222B222BB2222B2BB22BBB222B22BB22BBBB222BBBBBB2222B222BB2222B222B22BBBBB2BBBB222BB22222BB2B22B22B2B22B22B2BB2B222222B22BBBBB2B22B2B22B22222B2BB222222BBBBB2BB2B2BB2B2B222B2B2B22B2222B22B222B2222222B2B2B222222B2BB222222BB2B2222B2BB2222222B222B22BBB222B2222B22222B2B222222B2B2BB2BB2BB222BB22222222BB2BB222B22B2BBBB22B22222BBB2B2B2222B2B22BBB222B2BBB22BBBB2222B222B22BBBB2222222222BBBB2B2B222BBB2B2BB22BB2222222B2222B2B22B2BB2222BB2B222BBB22222B2BB22B22B22B2B2BB2BBB2222BB222BBBB22BB22B2B222BB2BBB2BBB222B2B222B222B2BBBB2BBBB222B2B22B2B22B2B2BBB2222BB22B2B2B2B2B2B2BB2BB2B2BB2B22222BBB2B22B2222222B2222B22BB2BBB22B22B2BB222BB222222B22222B22BBB222B222B22B2222B22222B2BB2B22222B2222B2B222B2BB22222222B2B2BB2B222BB2222B22B22B2B22222BB22B22B2B2B22B222B2B22B2B2B22B2B222BB22B2B2B2222B2B2B22B22B2B2B22B2B222B22222B22B2B222222B2B222B2B22B2BB2BB2B2B2B2B2B222BB22B22222222B2222B2BBB22222B2222B222222B22BB2BBB2BB222BBBBB22B2BB2222B2BB22B22B222B22B2222B22B22BB222BB22BB2222B2BBB222222222222222222BB2BB2B2 
, DECRYPTION BY PASSWORD = 'my-secret-password'
);

I would like to be able to import the certificate/key into Cloud KMS so that I can later import an encrypted BAK file created on my on-premise SQL Server into my Cloud MS SQL Server.

Can this be done? And how?

Thanks,

Ales

[lawrencenelson]

Hi @ales_cervenka,

Welcome to the Google Cloud Community!

For your scenario of importing an encrypted BAK file from an on-premise SQL Server to Cloud SQL, while directly importing encryption certificates/keys into Cloud KMS for this purpose might not be straightforward, an alternative approach would be using Cloud Storage. When you store your encrypted BAK file in Cloud Storage, it's automatically encrypted at rest using Google-managed encryption keys. This provides a seamless and secure way to handle your encrypted data without manually managing the encryption keys in Cloud KMS. Once your BAK file is in Cloud Storage, you can then import it into Cloud SQL. 

I hope this helps.

[ales_cervenka]

Thank you, @lawrencenelson , I was eventually able to import the encrypted BAK file using the Cloud storage and the gcloud CLI - for anyone interested in details, plase see https://www.googlecloudcommunity.com/gc/Databases/gcloud-sql-import-bak-fails-with-a-certificate-err... for the description of how I did it and what issues I ran into.

While I managed to do it using an approach similar to what you suggest, @lawrencenelson , I'd still like to know if the same could be accomplished using Cloud KMS.