Use transparent data encryption (TDE)

MySQL | PostgreSQL | SQL Server

This page shows you how to use transparent data encryption (TDE) .

The following sections show you how to manage TDE certificates.

Before you begin

To import or export TDE certificates, you must have the cloudsql.instances.manageEncryption Identity and Access Management permission, available in the cloudsql.admin role. For more information, see the Cloud SQL roles .

GRANT  
VIEW  
ANY  
DEFINITION  
TO  
other_login  
AS  
CustomerDbRootRole

Import an external TDE certificate

Console

You can't import a TDE certificate using Google Cloud console.

gcloud

Use the following gcloud sql import to import a certificate from a Cloud Storage bucket:

  
gcloud  
sql  
import  
tde  
 INSTANCE_NAME 
  
 \ 
  
--certificate  
 CERTIFICATE_NAME 
  
 \ 
  
--cert-path = 
gs:// BUCKET_NAME 
/ CERTIFICATE_NAME 
  
 \ 
  
--pvk-path = 
gs:// BUCKET_NAME 
/ KEY_NAME 
  
 \ 
  
--prompt-for-pvk-password  
 \ 
  
--pvk-password  
 CERTIFICATE_PASSWORD

Make the following replacements:

INSTANCE_NAME : the name of the Cloud SQL instance to which you want to import the TDE certificate.
CERTIFICATE_NAME : the name of the encryption certificate you want to import.
BUCKET_NAME : the name of the Cloud Storage bucket where the TDE certificate you want to import is located.
KEY_NAME : the name of the encryption key associated with the Cloud Storage bucket.
CERTIFICATE_PASSWORD : the certificate password.

REST v1

To import a TDE certificate, use the instances.import method and specify the following properties:

Before using any of the request data, make the following replacements:

PROJECT_ID : the name of the project where the encryption certificate is to be imported.
INSTANCE_NAME : the name of the Cloud SQL instance to which you want to import the encryption certificate.
TDE_CERTIFICATE_NAME : the name of the encryption certificate you want to use after it has been imported.
CERTIFICATE_NAME : the name of the encryption certificate you want to import.
BUCKET_NAME : the name of the Cloud Storage bucket where the encryption certificate you want to import is located.
KEY_NAME : the name of the encryption key associated with the Cloud Storage bucket.
CERTIFICATE_PASSWORD : the certificate password.

HTTP method and URL:

POST https://sqladmin.googleapis.com/v1/projects/ PROJECT_NAME 
/instances/ INSTANCE_NAME 
/import

Request JSON body:

{
  "importContext":
    {
        "fileType": "TDE",
        "tdeImportOptions":
          {
            "name": " TDE_CERTIFICATE_NAME 
",
            "certificatePath": "gs:// BUCKET_NAME 
/ CERTIFICATE_NAME 
",
            "privateKeyPath": "gs:// BUCKET_NAME 
/ KEY_NAME 
",
            "privateKeyPassword": " CERTIFICATE_PASSWORD 
"
          }
    }
}

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Note: The following command assumes that you have logged in to the gcloud CLI with your user account by running gcloud init or gcloud auth login , or by using Cloud Shell , which automatically logs you into the gcloud CLI . You can check the currently active account by running gcloud auth list .

Save the request body in a file named request.json , and execute the following command:

curl -X POST \
-H "Authorization: Bearer $(gcloud auth print-access-token)" \
-H "Content-Type: application/json; charset=utf-8" \
-d @request.json \
"https://sqladmin.googleapis.com/v1/projects/ PROJECT_NAME 
/instances/ INSTANCE_NAME 
/import"

PowerShell (Windows)

Note: The following command assumes that you have logged in to the gcloud CLI with your user account by running gcloud init or gcloud auth login . You can check the currently active account by running gcloud auth list .

Save the request body in a file named request.json , and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
-Method POST `
-Headers $headers `
-ContentType: "application/json; charset=utf-8" `
-InFile request.json `
-Uri "https://sqladmin.googleapis.com/v1/projects/ PROJECT_NAME 
/instances/ INSTANCE_NAME 
/import" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

Response

{
  "kind": "sql#operation",
  "targetLink": "https://sqladmin.googleapis.com/v1/projects/ PROJECT_NAME 
/instances/ DESTINATION_INSTANCE_NAME 
",
  "status": "PENDING",
  "user": "user@example.com",
  "insertTime": "2020-01-21T22:43:37.981Z",
  "operationType": "UPDATE",
  "name": " OPERATION_ID 
",
  "targetId": " INSTANCE_NAME 
",
  "selfLink": "https://sqladmin.googleapis.com/v1/projects/ PROJECT_NAME 
/operations/ OPERATION_ID 
",
  "targetProject": " PROJECT_NAME 
"
}

REST v1beta4

To import a TDE certificate, use the instances.import method and specify the following properties:

Before using any of the request data, make the following replacements:

PROJECT_ID : the name of the project where the encryption certificate is to be imported.
INSTANCE_NAME : the name of the Cloud SQL instance to which you want to import the encryption certificate.
TDE_CERTIFICATE_NAME : the name of the encryption certificate you want to use after it has been imported.
CERTIFICATE_NAME : the name of the encryption certificate you want to import.
BUCKET_NAME : the name of the Cloud Storage bucket where the encryption certificate you want to import is located.
KEY_NAME : the name of the encryption key associated with the Cloud Storage bucket.
CERTIFICATE_PASSWORD : the certificate password.

HTTP method and URL:

POST https://sqladmin.googleapis.com/v1beta4/projects/ PROJECT_NAME 
/instances/ INSTANCE_NAME 
/import

Request JSON body:

{
  "importContext":
    {
        "fileType": "TDE",
        "tdeImportOptions":
          {
            "name": " TDE_CERTIFICATE_NAME 
",
            "certificatePath": "gs:// BUCKET_NAME 
/ CERTIFICATE_NAME 
",
            "privateKeyPath": "gs:// BUCKET_NAME 
/ KEY_NAME 
",
            "privateKeyPassword": " CERTIFICATE_PASSWORD 
"
          }
    }
}

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Save the request body in a file named request.json , and execute the following command:

curl -X POST \
-H "Authorization: Bearer $(gcloud auth print-access-token)" \
-H "Content-Type: application/json; charset=utf-8" \
-d @request.json \
"https://sqladmin.googleapis.com/v1beta4/projects/ PROJECT_NAME 
/instances/ INSTANCE_NAME 
/import"

PowerShell (Windows)

Save the request body in a file named request.json , and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
-Method POST `
-Headers $headers `
-ContentType: "application/json; charset=utf-8" `
-InFile request.json `
-Uri "https://sqladmin.googleapis.com/v1beta4/projects/ PROJECT_NAME 
/instances/ INSTANCE_NAME 
/import" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

Response

{
  "kind": "sql#operation",
  "targetLink": "https://sqladmin.googleapis.com/v1beta4/projects/ PROJECT_NAME 
/instances/ DESTINATION_INSTANCE_NAME 
",
  "status": "PENDING",
  "user": "user@example.com",
  "insertTime": "2020-01-21T22:43:37.981Z",
  "operationType": "UPDATE",
  "name": " OPERATION_ID 
",
  "targetId": " INSTANCE_NAME 
",
  "selfLink": "https://sqladmin.googleapis.com/v1beta4/projects/ PROJECT_NAME 
/operations/ OPERATION_ID 
",
  "targetProject": " PROJECT_NAME 
"
}

Each imported TDE certificate is assigned a unique name in the following format:

  
gcloud_tde_user_ CERTIFICATE_NAME 
_ GENERATED_GUID

If point-in-time recovery (PITR) is enabled on the instance, it may take several minutes before the imported TDE certificate is available.

Rotate TDE certificates

You can rotate an imported TDE certificate with the msdb.dbo.gcloudsql_rotate_tde_certificate stored procedure. The procedure must be invoked by the sqlserver user, or by users to which the sqlserver user has given permission, using a call similar to the following:
```
  
EXEC  
msdb.dbo.gcloudsql_rotate_tde_certificate  
 TDE_CERTIFICATE_NAME 
  
```

Delete TDE certificates

You can import up to ten user TDE certificates. If you need to import more, delete any unnecessary TDE certificates using the msdb.dbo.gcloudsql_drop_tde_user_certificate stored procedure. The procedure must be invoked by the sqlserver user, or by users to which the sqlserver user has given permission, using a call similar to the following:
```
  
EXEC  
gcloudsql_drop_tde_user_certificate  
 TDE_CERTIFICATE_NAME 
  
```
You can't delete a certificate managed by Cloud SQL.
You can't delete a certificate while it is in use.

Export a TDE certificate

You must specify the full TDE certificate name—including the prefix and the suffix—to export. You can look up the full TDE encryption certificate name querying the name column from the sys.certificates view:

  
SELECT  
name  
FROM  
sys.certificates

Console

You can't export a TDE certificate using Google Cloud console.

gcloud

Use the following gcloud sql export to export a TDE certificate to a Cloud Storage bucket:

  
gcloud  
sql  
 export 
  
tde  
 INSTANCE_NAME 
  
 \ 
  
--certificate  
 CERTIFICATE_NAME 
  
 \ 
  
--cert-path = 
gs:// BUCKET_NAME 
/ CERTIFICATE_NAME 
  
 \ 
  
--pvk-path = 
gs:// BUCKET_NAME 
/ KEY_NAME 
  
 \ 
  
--prompt-for-pvk-password  
 \ 
  
--pvk-password  
 CERTIFICATE_PASSWORD

Make the following replacements:

INSTANCE_NAME : the name of the Cloud SQL instance from which you want to export the TDE certificate.
CERTIFICATE_NAME : the name of the encryption certificate you want to export.
BUCKET_NAME : the name of the Cloud Storage bucket where the TDE certificate you want to export is located.
KEY_NAME : the name of the encryption key associated with the Cloud Storage bucket.
CERTIFICATE_PASSWORD : the certificate password.

REST v1

To export a TDE certificate, use the instances.export method and specify the following properties:

Before using any of the request data, make the following replacements:

PROJECT_ID : the name of the project from which the encryption certificate is to be exported.
INSTANCE_NAME : the name of the Cloud SQL instance from which you want to export the encryption certificate.
TDE_CERTIFICATE_NAME : the name of the encryption certificate you want to use after it has been exported.
CERTIFICATE_NAME : the name of the encryption certificate you want to export.
BUCKET_NAME : the name of the Cloud Storage bucket where the encryption certificate you want to export is located.
KEY_NAME : the name of the encryption key associated with the Cloud Storage bucket.
CERTIFICATE_PASSWORD : the certificate password.

HTTP method and URL:

POST https://sqladmin.googleapis.com/v1/projects/ PROJECT_NAME 
/instances/ INSTANCE_NAME 
/export

Request JSON body:

{
  "exportContext":
    {
      "fileType": "TDE",
      "tdeExportOptions":
        {
          "name": " TDE_CERTIFICATE_NAME 
",
          "certificatePath": "gs:// BUCKET_NAME 
/ CERTIFICATE_NAME 
",
          "privateKeyPath": "gs:// BUCKET_NAME 
/ KEY_NAME 
",
          "privateKeyPassword": " CERTIFICATE_PASSWORD 
"
        }
    }
}

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Save the request body in a file named request.json , and execute the following command:

curl -X POST \
-H "Authorization: Bearer $(gcloud auth print-access-token)" \
-H "Content-Type: application/json; charset=utf-8" \
-d @request.json \
"https://sqladmin.googleapis.com/v1/projects/ PROJECT_NAME 
/instances/ INSTANCE_NAME 
/export"

PowerShell (Windows)

Save the request body in a file named request.json , and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
-Method POST `
-Headers $headers `
-ContentType: "application/json; charset=utf-8" `
-InFile request.json `
-Uri "https://sqladmin.googleapis.com/v1/projects/ PROJECT_NAME 
/instances/ INSTANCE_NAME 
/export" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

Response

{
  "kind": "sql#operation",
  "targetLink": "https://sqladmin.googleapis.com/v1/projects/ PROJECT_NAME 
/instances/ DESTINATION_INSTANCE_NAME 
",
  "status": "PENDING",
  "user": "user@example.com",
  "insertTime": "2020-01-21T22:43:37.981Z",
  "operationType": "UPDATE",
  "name": " OPERATION_ID 
",
  "targetId": " INSTANCE_NAME 
",
  "selfLink": "https://sqladmin.googleapis.com/v1/projects/ PROJECT_NAME 
/operations/ OPERATION_ID 
",
  "targetProject": " PROJECT_NAME 
"
}

REST v1beta4

To export a TDE certificate, use the instances.export method and specify the following properties:

Before using any of the request data, make the following replacements:

PROJECT_ID : the name of the project from which the encryption certificate is to be exported.
INSTANCE_NAME : the name of the Cloud SQL instance from which you want to export the encryption certificate.
TDE_CERTIFICATE_NAME : the name of the encryption certificate you want to use after it has been exported.
CERTIFICATE_NAME : the name of the encryption certificate you want to export.
BUCKET_NAME : the name of the Cloud Storage bucket where the encryption certificate you want to export is located.
KEY_NAME : the name of the encryption key associated with the Cloud Storage bucket.
CERTIFICATE_PASSWORD : the certificate password.

HTTP method and URL:

POST https://sqladmin.googleapis.com/v1beta4/projects/ PROJECT_NAME 
/instances/ INSTANCE_NAME 
/export

Request JSON body:

{
  "exportContext":
    {
      "fileType": "TDE",
      "tdeExportOptions":
        {
          "name": " TDE_CERTIFICATE_NAME 
",
          "certificatePath": "gs:// BUCKET_NAME 
/ CERTIFICATE_NAME 
",
          "privateKeyPath": "gs:// BUCKET_NAME 
/ KEY_NAME 
",
          "privateKeyPassword": " CERTIFICATE_PASSWORD 
"
        }
    }
}

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Save the request body in a file named request.json , and execute the following command:

curl -X POST \
-H "Authorization: Bearer $(gcloud auth print-access-token)" \
-H "Content-Type: application/json; charset=utf-8" \
-d @request.json \
"https://sqladmin.googleapis.com/v1beta4/projects/ PROJECT_NAME 
/instances/ INSTANCE_NAME 
/export"

PowerShell (Windows)

Save the request body in a file named request.json , and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
-Method POST `
-Headers $headers `
-ContentType: "application/json; charset=utf-8" `
-InFile request.json `
-Uri "https://sqladmin.googleapis.com/v1beta4/projects/ PROJECT_NAME 
/instances/ INSTANCE_NAME 
/export" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

Response

{
  "kind": "sql#operation",
  "targetLink": "https://sqladmin.googleapis.com/v1beta4/projects/ PROJECT_NAME 
/instances/ DESTINATION_INSTANCE_NAME 
",
  "status": "PENDING",
  "user": "user@example.com",
  "insertTime": "2020-01-21T22:43:37.981Z",
  "operationType": "UPDATE",
  "name": " OPERATION_ID 
",
  "targetId": " INSTANCE_NAME 
",
  "selfLink": "https://sqladmin.googleapis.com/v1beta4/projects/ PROJECT_NAME 
/operations/ OPERATION_ID 
",
  "targetProject": " PROJECT_NAME 
"
}

Import a TDE-encrypted backup

When importing a TDE-encrypted backup, if the matching TDE certificate is already on the source instance, then you can import the encrypted backup directly and it won't be decrypted. You don't need to provide the certificate again.

If the certificate isn't on the instance, then use the --keep-encrypted flag when importing. The flag provides the following benefits:

Keeps the TDE certificate associated with the instance.
Prevents the backup from being decrypted during import.

If you don't use the --keep-encrypted flag, the imported backup will be decrypted, and the TDE certificate won't be saved on the server.

For more information, see the following resources:

Export a TDE-encrypted backup

You can export a TDE-encrypted backup the same way you export an unencrypted one. The certificate used to encrypt the backup is not included in the export. You must export it separately.

For more information, see the following resources:

Encrypt and decrypt a database

You can encrypt and decrypt your instance in much the same way you would using SQL Server.

Initially, only user root logins, such as sqlserver can access TDE certificates. To grant access to other users, use the following command or similar:

  
GRANT  
VIEW  
ANY  
DEFINITION  
TO  
 CERTIFICATE_NAME 
  
AS  
CustomerDbRootRole

Alternatively, you can grant access to a specific certificate and database user.

You can encrypt a database using the following command or similar:

  
CREATE  
DATABASE  
ENCRYPTION  
KEY  
WITH  
 ALGORITHM 
  
 = 
  
AES_256  
ENCRYPTION  
BY  
SERVER  
CERTIFICATE  
 CERTIFICATE_NAME 
  
ALTER  
DATABASE  
 DATABASE_NAME 
  
SET  
ENCRYPTION  
ON

You can decrypt a database using the following command or similar:

  
ALTER  
DATABASE  
 DATABASE_NAME 
  
SET  
ENCRYPTION  
OFF

What's next

About TDE

Use transparent data encryption (TDE) Stay organized with collections Save and categorize content based on your preferences.

Before you begin

Import an external TDE certificate

Console

gcloud

REST v1

curl (Linux, macOS, or Cloud Shell)

PowerShell (Windows)

Response

REST v1beta4

curl (Linux, macOS, or Cloud Shell)

PowerShell (Windows)

Response

Rotate TDE certificates

Delete TDE certificates

Export a TDE certificate

Console

gcloud

REST v1

curl (Linux, macOS, or Cloud Shell)

PowerShell (Windows)

Response

REST v1beta4

curl (Linux, macOS, or Cloud Shell)

PowerShell (Windows)

Response

Import a TDE-encrypted backup

Export a TDE-encrypted backup

Encrypt and decrypt a database

What's next

Use transparent data encryption (TDE)