The driver supports service account authentication. A service account is a type of Google account that represents an application instead of an individual end user. Unlike a user account, a service account allows your application to authenticate and communicate to Google APIs without direct human intervention. This is useful for applications that need to access their own data, not the user's data. For a successful service account authentication, you need:
  • Private key file or Private key
    • The private key file is a .json or .p12 file that contains the key required to authenticate API calls. You can download it from the Google Cloud Platform (GCP) Console.
    • The private key is contained in the private key file downloaded from the GCP Console.
  • Service account's email address: A unique email address that is provisioned while creating a service account.

To know more about service account authentication, refer to the Google documentation.

To configure the driver to use service account authentication, set the following connection options:

  • Set the Authentication Method option to serviceaccount.
  • Set the Service Account Email option to specify your service account's email address.
  • Set either the Service Account Key Content option or the Service Account Private Key option
    • The Service Account Key Content option specifies the private key required to authenticate to Google BigQuery. Use this option if you do not want to persist the private key file in your environment.

      The value of the Service Account Key Content option should be protected for security reasons. The option has been intentionally excluded from the Windows Setup dialog. In addition, the value should not be hardcoded in an ODBC data source. Rather, it may be specified in the application with the SQLConnect ODBC API, in a connection string as with SQLDriverConnect and SQLBrowseConnect, or through the Logon dialog prompt. (If your operating system supports the Logon dialog, the prompt appears when the value has not been otherwise specified.)

    • The Service Account Private Key option specifies the full path to the .json or .p12 file that contains the private key. The driver extracts the private key value from the specified file and uses it to authenticate the user to the database. Use this option if it is preferable to persist the private key file.
  • Optionally, set the JWT Audience option to specify the JWT audience claim associated with your service account. The default value is https://accounts.google.com/o/oauth2/token.
  • Optionally, set the Token URI option to specify the endpoint for retrieving access tokens. The default value is https://accounts.google.com/o/oauth2/token.

The following examples show how to connect to a Google BigQuery instance using service account authentication.

Service Account Key Content option in a connection string:

DRIVER=DataDirect 8.0 Google BigQuery;AuthenticationMethod=serviceaccount;
Project=myproject;Dataset=mydataset;ServiceAccountEmail=abc123@iam.gserviceaccount.com;
ServiceAccountKeyContent=NJJXZexIHJFGYBgkqhkiG9w0BAQnWRwiHANpf3MC1pVRqhtTE5tSpxZeQnICG
4zp087Eidn4qc66udg8KAHknyqFdj7b\n+MgxMFPAvJ59cylHFaHA4pGmeGfVqzYub6LEs9aN/751jmZqcuAYp
5nXRF1EvJPN\nsDuJGLvuuDBZW0iux0liEHmcQVBBKwIx8t+EQxePGTiLsBoCdzOUsi4UWWv\nASqfdP/kSX+N;);

Service Account Key Content and the odbc.ini file:

Note: For security reasons, the value of the Service Account Key Content option should not be specified in the odbc.ini file.
Driver=ODBCHOME/lib/xxgbq28.yy
AuthenticationMethod=serviceaccount
Project=myproject
Dataset=mydataset
ServiceAccountEmail=abc123@iam.gserviceaccount.com

Service Account Private Key option in a connection string:

DRIVER=DataDirect 8.0 Google BigQuery;AuthenticationMethod=serviceaccount;
Project=myproject;Dataset=mydataset;ServiceAccountEmail=abc123@iam.gserviceaccount.com;
ServiceAccountPrivateKey=abc123.json;);

Service Account Private Key in the odbc.ini file:

Driver=ODBCHOME/lib/xxgbq28.yy
AuthenticationMethod=serviceaccount
Project=myproject
Dataset=mydataset
ServiceAccountEmail=abc123@iam.gserviceaccount.com
ServiceAccountPrivateKey=abc123.json