gcloud sql import bak fails with a certificate error

[ales_cervenka]

I am trying to import an enrcypted BAK file into my MS SQL cloud instance.

This is the query I am running in the cloud shell and the error message I am getting after few seconds:

ales_cervenka@cloudshell:~ (my-gcp-project)$ gcloud beta sql import bak my-mssql01 gs://my-database-dumps/MyTestDB.bak --database=MyTestAdventure --cert-path=gs://my-database-dumps/test_cert_for_encryption.cert --pvk-path=gs://my-database-dumps/test_pvk_for_encryption.pvk --prompt-for-pvk-password
Private Key Password:
Data from [gs://my-database-dumps/MyTestDB.bak] will be imported to [my-mssql01].

Do you want to continue (Y/n)? Y

Importing data into Cloud SQL instance...failed.
ERROR: (gcloud.beta.sql.import.bak) [ERROR_SQL_SERVER_EXTERNAL_WARNING] Failed to create certificate from certificate file, private key file, and password with error mssql: The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.
ales_cervenka@cloudshell:~ (my-gcp-project)$

The import process generates the following entries in the log file:

2024-02-09 11:29:06.304 ImportBak: Starting importing bak gs://my-database-dumps/MyTestDB.bak
2024-02-09 11:29:06.305 ImportBak: completed. Job started asynchronously.
2024-02-09 11:29:06.387 GCSDownload: Download from URL=gs://my-database-dumps/MyTestDB.bak started
2024-02-09 11:29:07.472 writeFileWithPrepend: estimated percentage completed: 5%
2024-02-09 11:29:08.163 writeFileWithPrepend: estimated percentage completed: 10%
2024-02-09 11:29:08.733 writeFileWithPrepend: estimated percentage completed: 15%
2024-02-09 11:29:08.734 writeFileWithPrepend: estimated percentage completed: 20%
2024-02-09 11:29:09.198 writeFileWithPrepend: estimated percentage completed: 25%
2024-02-09 11:29:09.733 writeFileWithPrepend: estimated percentage completed: 30%
2024-02-09 11:29:11.012 writeFileWithPrepend: estimated percentage completed: 40%
2024-02-09 11:29:11.012 writeFileWithPrepend: estimated percentage completed: 35%
2024-02-09 11:29:11.312 writeFileWithPrepend: estimated percentage completed: 45%
2024-02-09 11:29:11.551 writeFileWithPrepend: estimated percentage completed: 50%
2024-02-09 11:29:11.884 writeFileWithPrepend: estimated percentage completed: 60%
2024-02-09 11:29:11.884 writeFileWithPrepend: estimated percentage completed: 55%
2024-02-09 11:29:12.209 writeFileWithPrepend: estimated percentage completed: 65%
2024-02-09 11:29:12.487 writeFileWithPrepend: estimated percentage completed: 70%
2024-02-09 11:29:13.126 writeFileWithPrepend: estimated percentage completed: 75%
2024-02-09 11:29:13.127 writeFileWithPrepend: estimated percentage completed: 80%
2024-02-09 11:29:13.419 writeFileWithPrepend: estimated percentage completed: 85%
2024-02-09 11:29:13.856 writeFileWithPrepend: estimated percentage completed: 90%
2024-02-09 11:29:14.084 writeFileWithPrepend: estimated percentage completed: 100%
2024-02-09 11:29:14.085 GCSDownload: Download from URL=gs://my-database-dumps/MyTestDB.bak completed
2024-02-09 11:29:14.121 GCSDownload: Download from URL=gs://my-database-dumps/test_cert_for_encryption.cert started
2024-02-09 11:29:14.121 EncryptedImportBak: Beginning importing encrypted bak gs://my-database-dumps/MyTestDB.bak
2024-02-09 11:29:14.286 GCSDownload: Download from URL=gs://my-database-dumps/test_pvk_for_encryption.pvk started
2024-02-09 11:29:14.286 GCSDownload: Download from URL=gs://my-database-dumps/test_cert_for_encryption.cert completed
2024-02-09 11:29:14.286 writeFileWithPrepend: estimated percentage completed: 100%
2024-02-09 11:29:14.453 writeFileWithPrepend: estimated percentage completed: 100%
2024-02-09 11:29:14.454 GCSDownload: Download from URL=gs://my-database-dumps/test_pvk_for_encryption.pvk completed
2024-02-09 11:29:14.474 EncryptedImportBak: Creating master key for /var/opt/mssql/importexport/aa1fc222-34b1-117a-8acc-7b08d12d4fa3.bak
2024-02-09 11:29:14.492 EncryptedImportBak: creating certificate from provided files for /var/opt/mssql/importexport/aa1fc222-34b1-117a-8acc-7b08d12d4fa3.bak
2024-02-09 11:29:14.507 EncryptedImportBak: Failed importing encrypted bak gs://my-database-dumps/MyTestDB.bak

Have you ever run into this issue? What may be causing this?

Thanks, 
Ales

[ms4446]

Yes, this is a relatively common issue when dealing with encrypted .BAK imports to Google Cloud SQL for Microsoft SQL Server. 

ERROR: (gcloud.beta.sql.import.bak) [ERROR_SQL_SERVER_EXTERNAL_WARNING] Failed to create certificate from certificate file, private key file, and password with error mssql: The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.

This indicates a problem with SQL Server's attempt to utilize the provided certificate and private key for decrypting the .BAK file.

Potential Causes and Fixes

• Incorrect or Corrupted Files: Your certificate (.cert) or private key (.pvk) files might be damaged, incorrectly formatted, or generated incorrectly.
• Incorrect Password: If your private key is encrypted, ensure the password you're using during import is accurate.
• Permissions Issues: The Google Cloud SQL service account requires access to the Cloud Storage bucket and its contents (BAK, certificate, key).
• Mismatching Certificate and Key: The certificate and private key must be the corresponding pair that was used to encrypt the BAK file.
• SQL Server Compatibility: Double-check that your Google Cloud SQL for SQL Server instance is compatible with the SQL Server version where the BAK file was created.

Troubleshooting Steps

1. Double-Check Certificate and Key: Test the certificate and key files on the original machine by decrypting something simple. Inspect the files for proper formatting.
2. Verify Google Cloud Storage Permissions: Ensure the Cloud SQL service account has at least the "Storage Object Viewer" role for the bucket where your files are stored.
3. Password Accuracy: If you cannot recover the private key password, you'll likely need to re-encrypt the BAK file.
4. Re-create the Certificate and Key (If Necessary): Re-encrypt the .BAK file with a new, carefully managed certificate and key pair. Store the password securely.

Additional Tips

• Best Practices:
  • Employ a secure key management solution (hardware security module, cloud-based KMS)
  • Consider using a trusted Certificate Authority (CA) for issuing certificates.
  • Maintain clear documentation about certificates, passwords, and backup processes.
• Sample gcloud Command:

  gcloud beta sql import bak <instance_name> gs://<bucket_name>/<bak_file.bak> \
      --database=<database_name> \
      --cert-path=gs://<bucket_name>/<certificate_file.cert> \
      --pvk-path=gs://<bucket_name>/<private_key_file.pvk>
  

• Official Documentation: Refer to the Google Cloud documentation for more details: https://cloud.google.com/sql/docs/sqlserver/import-export/import-export-bak

[ales_cervenka]

Thank you, @ms4446 . I am leaning towards the "Incorrect or Corrupted Files" as the potential cause of my import issues.

On the SQL Server, my encryption certificate looks similar to this hypothetical one:

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

How do I correctly export the cert and pvk file? Once exported, what should I be checking for in the files to make sure their format is correct?

[ms4446 · Accepted solution]

To correctly export the certificate and private key (PVK) file from SQL Server and ensure their format is correct for importing into Google Cloud SQL for Microsoft SQL Server, follow these steps:

Exporting the Certificate and Private Key

1. Export the Certificate: Use the BACKUP CERTIFICATE Transact-SQL command to export the certificate to a file. This command allows you to back up the certificate to a file in the file system, which you can then upload to Google Cloud Storage.

   BACKUP CERTIFICATE [bkp_encryption_certificate]
   TO FILE = 'C:\Path\To\Your\certificate_name.cert'
   WITH PRIVATE KEY (
       FILE = 'C:\Path\To\Your\private_key_name.pvk',
       ENCRYPTION BY PASSWORD = 'your-pvk-password'
   );

   Replace C:\Path\To\Your\certificate_name.cert and C:\Path\To\Your\private_key_name.pvk with your desired paths and filenames for the certificate and private key files, respectively. Also, replace your-pvk-password with a strong, secure password.

2. Upload the Files to Google Cloud Storage: After exporting the certificate and private key, upload them to a Google Cloud Storage bucket that your Google Cloud SQL instance can access.

Verifying the Certificate and Private Key Format

Once you have exported and uploaded the certificate and private key, you should verify their format to ensure they are correct and compatible with SQL Server requirements for encryption and decryption:

1. Certificate File (.cert):

   • The certificate file should be in binary format.

   • You can open the .cert file with a text editor to check if it looks like a binary file (it should not be readable text).

2. Private Key File (.pvk):

   • The private key file is also typically in a binary format, encrypted with the password you provided during the export.

   • Similar to the certificate file, opening the .pvk file with a text editor should show non-readable, binary content.

3. File Integrity:

   • Ensure that the files were not corrupted during transfer. You can do this by comparing the file sizes before and after transfer or by using checksums (if available).

   • Ensure there was no alteration of the files during the upload process to Google Cloud Storage.

4. Permissions and Accessibility:

   • Verify that the Google Cloud SQL service account has the necessary permissions to access the files in the Google Cloud Storage bucket.

   • Ensure the paths specified in the gcloud command accurately reflect the location and names of the uploaded certificate and private key files.

[ales_cervenka]

Thank you, @ms4446 , the BACKUP CERTIFICATE command was exactly what I needed to know and what helped resolve my issues with the restore!