How to create a Google service account and its JSON key

Querona authenticates to Google data sources — Google Sheets and Google BigQuery — as a service account: a Google identity that belongs to an application rather than to a person, so a connection keeps working with nobody signed in.

A service account proves who it is with a JSON key you download once from the Google Cloud console. That file is what Querona stores in a DATABASE SCOPED CREDENTIAL. This page covers the path through Google’s console that a Querona connection needs; Google documents the full procedure, its alternatives (gcloud, the APIs) and its organization-policy constraints in Create and delete service account keys.

Important

The JSON key is a long-lived credential: anyone holding the file can act as the service account until the key is deleted. Treat it like a password — see Handling the key below.

Before you start

You need a Google Cloud project and enough rights in it to enable APIs and to create service accounts and keys — typically the Service Account Admin, Service Account Key Admin and Service Usage Admin roles, or Owner. These are the rights you need to follow this page; they are not granted to the service account. If your organization restricts key creation, ask whoever administers the project to perform the steps below and hand you the resulting file.

Step 1. Choose or create a Google Cloud project

Sign in to the Google Cloud console and pick a project from the project selector in the header bar, or create one.

Note its project id — Querona uses it as the connection’s data-source name, where it serves as a quota and attribution label.

Important

A project has three different identifiers, shown together, and Querona needs the ID:

• Project name — the display label, editable, not unique. Not what you want.

• Project ID — globally unique, lowercase, fixed for the life of the project (for example my-project-123456). This is the value to use.

• Project number — an all-digits identifier. Also not what you want.

Three places show it, in increasing order of reliability:

• The project selector dialog in the console header lists every project with its ID beside the name.

• The console dashboard for a selected project shows a Project info card carrying all three values.

• The JSON key you download in step 4 contains it outright, as the project_id field — and the service account’s own email address embeds it too, as <name>@<project-id>.iam.gserviceaccount.com. If you already have the key, read the id from there and skip the console entirely; it cannot disagree with the account you are authenticating as.

Step 2. Enable the API for the source you are connecting to

APIs are enabled per project, on the project that owns the service account — the one you selected in step 1 — not on whoever owns the data. Enable only the API for the source you actually connect to; if you connect to both from the same project, enable both.

Go to APIs & Services → Library and search for the API by the identifier below rather than by its display name (the Library lists several similarly named Google APIs), then click ENABLE. The right-hand column links straight to each API’s page:

Data source	API to enable	Identifier — links to its console page
Google Sheets	Google Sheets API	sheets.googleapis.com
Google BigQuery	BigQuery API	bigquery.googleapis.com

Note

A console link opens whichever project you last had selected. Check the project picker in the blue header bar before enabling anything — enabling an API on the wrong project is the quietest way to get a permission error later.

That is the whole list — neither source needs a second API:

• Google Sheets does not require the Google Drive API. Querona reads a spreadsheet by its id, and both tab enumeration and cell reads work with the Sheets API alone. Drive would only be needed to list spreadsheets — something Querona does not do, since you list the spreadsheets yourself on the connection.

• BigQuery does not require the BigQuery Storage API. Querona reads through the standard BigQuery API only.

An API left disabled fails every query later with a permission error that names the API and offers a link to enable it — recoverable, but easier to get right here than to diagnose against a live connection.

Step 3. Create the service account

1. Go to APIs & Services → Credentials.

2. Select Create credentials ‣ Service account.

3. Enter a service account name and service account ID, then click DONE.

You are returned to the Credentials page, where the new account appears with an email address of the form <name>@<project-id>.iam.gserviceaccount.com. Copy that address — it is the identity you grant access to in step 5.

The same accounts are also listed under IAM & Admin → Service accounts, which is the view Google’s own documentation uses. Either page reaches the key management in the next step.

Step 4. Create and download the JSON key

1. On the Credentials page (or Service accounts), click the service account you just created.

2. Open the KEYS tab.

3. Select ADD KEY ‣ Create new key.

4. Choose key type JSON and click CREATE.

The browser downloads the key file immediately. This is the only time Google offers it — there is no way to download the same key again, only to create another one.

The file looks like this, abbreviated:

{
  "type": "service_account",
  "project_id": "your-project-id",
  "private_key_id": "...",
  "private_key": "-----BEGIN PRIVATE KEY-----\n...\n-----END PRIVATE KEY-----\n",
  "client_email": "your-account@your-project-id.iam.gserviceaccount.com",
  "client_id": "...",
  "token_uri": "https://oauth2.googleapis.com/token"
}

Two fields matter to you: client_email is the address to share data with, and the file as a whole — every character, braces included — is what Querona stores as the credential secret.

Step 5. Grant the service account access to the data

A brand-new service account can reach nothing. How you grant access depends on the source, and the two sources work through entirely different permission systems — this is the step people most often get wrong.

Google Sheets — grant no IAM roles at all. A spreadsheet is a Google Workspace asset, not a Cloud resource, and Google is explicit that “Identity and Access Management (IAM) roles configured in the Google Cloud Console don’t grant access to Google Workspace assets (such as Sheets or Gmail)”. Adding project roles such as Viewer, Editor or Owner to the service account gives it no access to any spreadsheet — it only widens what that account can do elsewhere in the project.

Access comes from sharing the file, exactly as with a person: open the spreadsheet in your browser, click Share, paste the client_email address and give it Viewer. The service account cannot own or create spreadsheets of its own, so an existing spreadsheet always has to be shared with it. Forgetting this is the single most common cause of connection failures, and it surfaces as an HTTP 403.

BigQuery — grant IAM roles. BigQuery is a Cloud resource, so here the roles do the work. The three needed for read access are listed under Google BigQuery.

Source	IAM roles on the project	How access is actually granted
Google Sheets	None	Share each spreadsheet with the service account’s address (Viewer)
BigQuery	Three read roles	The IAM roles themselves — see the BigQuery page

Step 6. Store the key in Querona

Create a DATABASE SCOPED CREDENTIAL inside the virtual database that owns the connection, with the service account’s email as the identity and the entire JSON file as the secret. Credentials are managed in SQL only — there is no GUI equivalent:

USE [your_database];
CREATE DATABASE SCOPED CREDENTIAL google_cred
    WITH IDENTITY = '<client_email from the JSON>',
         SECRET   = '<the entire JSON key file, as one string>';

See CREATE DATABASE SCOPED CREDENTIAL for the full syntax and rules.

Then name that credential in the connection’s provider configuration. The per-source pages show the exact ordering, which matters — the credential lives inside the database, so it can only be created after the database exists and must exist before metadata import.

Note

A credential is optional. When a connection names none, Querona falls back to Application Default Credentials — the ambient Google credentials of the machine running the engine, for example the GOOGLE_APPLICATION_CREDENTIALS environment variable or, on a Google Cloud VM, the instance’s own attached service account. That avoids handling a key file at all, at the cost of every connection on that engine sharing one identity.

Handling the key

• Never commit it to source control, and never paste it into a shared script or ticket. Anyone with the file can act as the service account.

• Rotate it on a schedule your organization defines: create a new key, update the credential in Querona, then delete the old key from the service account’s KEYS tab in the Google Cloud console — Service accounts. Keys do not expire on their own.

• Delete keys you stop using. A key stays valid until it is explicitly deleted, including keys downloaded by people who have since left.

• Grant the least access that works — Viewer on one spreadsheet beats broad project-level roles.

Once the key is stored, Querona protects it: the secret is never held or shown in clear text, it is redacted wherever statement text is logged or traced, and sys.database_scoped_credentials exposes the identity metadata only. The exposure that remains is the downloaded file itself and whoever can create objects in the owning database.

Google’s own guidance on key lifetime, rotation cadence and organization-policy controls is in Best practices for managing service account keys.

See also

• Google Sheets

• Google BigQuery