Dataform error "Admin requires that you specify encryption key for queries"

[lawlesspattern]

Good day, today a policy was applied to make mandatory the use of KMS keys for everything including queries. I have succesfully managed to apply kms key to my dataflow jobs queries in apache beam thanks to the .withKmsKey(String kmsKey) function, but for dataform I have not been able to solve this error, let me share all what I have tried first:

Tried without success:

1) Creating from 0 the dataform repo using the same kms key that is used at the bigquery datasets that sqlx script is trying to query.

2) Tried to give the Service account used at my dataform workflows the encrypt/decrypt role to the key that is used at the bigquery dataset we are trying to query.

3) Finally in the documentation I found the flag to setup the kms_key_name at the sqlx scrypt config block, but this did also not work, here is the documentation I found about this:

* dataform core general doc: https://cloud.google.com/dataform/docs/reference/dataform-core-reference then in the IBigQueryOptions:

https://cloud.google.com/dataform/docs/reference/dataform-core-reference#bigquery, then inside this there is the additionalOptions parameter that contains the "kms_key_name" option: 

https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#table_option_...

But this also did not work, I always get this error message:

"Your administrator requires that you specify an encryption key for queries in project project-id. See https://cloud.google.com/bigquery/docs/customer-managed-encryption#services_constraint for more info."

So right now I am not sure what else should we try? Please help

[ms4446]

The core issue is that a new policy in your project mandates the use of CMEK for all BigQuery operations, including the queries Dataform executes. While you've successfully handled this in Dataflow, Dataform requires a slightly different configuration.

Troubleshooting Steps

Verify KMS Key Permissions:

• Service Account: Ensure the service account used by your Dataform workflows has the following roles on the KMS key:
  • roles/cloudkms.cryptoKeyEncrypterDecrypter
  • roles/bigquery.dataOwner (or a custom role with equivalent BigQuery permissions)
• Project Level: Double-check that the KMS key is enabled for BigQuery encryption at the project level. Go to the BigQuery settings in the Google Cloud Console and verify this.

Dataform Configuration (SQLX files):

• kms_key_name: Confirm the correct usage of the kms_key_name option within your SQLX file configuration blocks:

config {
  type: "table",
  ...other options...
  additionalOptions: {
    kms_key_name: "projects/your-project-id/locations/your-key-location/keyRings/your-key-ring/cryptoKeys/your-key-name"
  }
}

• Replace Placeholders: Make absolutely sure to replace your-project-id, your-key-location, your-key-ring, and your-key-name with the actual values for your KMS key.

Dataform Compilation and Deployment:

• Recompile: After making configuration changes, recompile your Dataform project.
• Redeploy: If you are using Dataform's Git-based deployment, commit your changes and redeploy to ensure the updated configurations are picked up.

Alternative Syntax (Optional):

• Environment Variable: If directly specifying the KMS key in the SQLX file doesn't work, try setting an environment variable in your Dataform execution environment:

export DATAFORM_BIGQUERY_KMS_KEY_NAME="projects/your-project-id/locations/your-key-location/keyRings/your-key-ring/cryptoKeys/your-key-name"

Important Considerations:

• Key Location: Ensure your KMS key is in the same location (region) as your BigQuery dataset.
• Dataform Version: Use the latest version of Dataform to benefit from any bug fixes or improvements related to KMS integration.
• Custom IAM Roles: If you're using custom IAM roles, verify they grant the necessary permissions for KMS key usage with BigQuery.
• Error Logs: Examine Dataform logs for more specific error messages that might provide additional clues.

Example:

config {
  type: "table",
  ...
  additionalOptions: {
    kms_key_name: "projects/your-project-id/locations/us-central1/keyRings/your-key-ring/cryptoKeys/your-key"
  }
}

SELECT * FROM `your-project-id.your-dataset.your-table`

If none of these steps resolve the issue, consider reaching out to Google Cloud support with details of your configuration and the error message you are encountering. They might be able to provide more specific guidance based on your project's setup.

[lawlesspattern]

Hi @ms4446 thanks for your reply, I tried what you mention but no success, only thing I could not try is the

export DATAFORM_BIGQUERY_KMS_KEY_NAME="projects/your-project-id/locations/your-key-location/keyRings/your-key-ring/cryptoKeys/your-key-name"

Because I could not do this in the js block of dataform, export was not allowed.

But reading again the documentation I think I found the reason there is no way to fix this: https://cloud.google.com/dataform/docs/cmek#restrictions

There is a restriction there that says policies are not supported, and what I understand is that if we setup policies like the one causing this issue, it wont be supported by dataform? Resulting in no way to fix this

[AndreaSu]

This may be coming too late, but I had the exact same issue today and we managed to solve it by adding the encryption key with the following syntax:

config {
 type: "table",
 schema: "stage",
 description: "Table with two integer columns c1 and c2",
 bigquery: {
   additionalOptions: {
     kms_key_name: "\"projects/PROJECT-ID/locations/REGION/keyRings/KEY-RING/cryptoKeys/KEY\""
   }
 }
}

SELECT
1 AS c1,
3 AS c2

Note the escaped double quotes around the key path - otherwise it will not work.