Authentication Service Account (Using *.json OR *.p12 key file)

Description

Service accounts are accounts that do not represent a human user. They provide a way to manage authentication and authorization when a human is not directly involved, such as when an application needs to access Google Cloud resources. Service accounts are managed by IAM. [API reference]

Instructions

Follow these steps on how to create Service Account to authenticate and access BigQuery API in SSIS package or ODBC data source:

Step-1: Create project

This step is optional, if you already have a project in Google Cloud and can use it. However, if you don't, proceed with these simple steps to create one:

1. First of all, go to Google API Console.

2. Then click Select a project button and then click NEW PROJECT button:

   

3. Name your project and click CREATE button:

   

4. Wait until the project is created:

   
5. Done! Let's proceed to the next step.

Step-2: Enable Google Cloud APIs

In this step we will enable BigQuery API and Cloud Resource Manager API:

1. Select your project on the top bar:

   

2. Then click the "hamburger" icon on the top left and access APIs & Services:

   

3. Now let's enable several APIs by clicking ENABLE APIS AND SERVICES button:

   

4. In the search bar search for bigquery api and then locate and select BigQuery API:

   

5. If BigQuery API is not enabled, enable it:

   

6. Then repeat the step and enable Cloud Resource Manager API as well:

   
7. Done! Let's proceed to the next step and create a service account.

Step-3: Create Service Account

Use the steps below to create a Service Account in Google Cloud:

1. First of all, go to IAM & Admin in Google Cloud console:

   

2. Once you do that, click Service Accounts on the left side and click CREATE SERVICE ACCOUNT button:

   

3. Then name your service account and click CREATE AND CONTINUE button:

   

4. Continue by clicking Select a role dropdown and start granting service account BigQuery Admin and Project Viewer roles:

   

5. Find BigQuery group on the left and then click on BigQuery Admin role on the right:

   

6. Then click ADD ANOTHER ROLE button, find Project group and select Viewer role:

   

7. Finish adding roles by clicking CONTINUE button:

   
   You can always add or modify permissions later in IAM & Admin.

8. Finally, in the last step, just click button DONE:

   

9. Done! We are ready to add a Key to this service account in the next step.

Step-4: Add Key to Service Account

We are ready to add a Key (JSON or P12 key file) to the created Service Account:

1. In Service Accounts open newly created service account:

   

2. Next, copy email address of your service account for the later step:

   

3. Continue by selecting KEYS tab, then press ADD KEY dropdown, and click Create new key menu item:

   

4. Finally, select JSON (Engine v19+) or P12 option and hit CREATE button:

   
5. Key file downloads into your machine. We have all the data needed for authentication, let's proceed to the last step!

Step-5: Configure connection

1. Now go to SSIS package or ODBC data source and configure these fields in Service Account authentication configuration:

   • In the Service Account Email field paste the service account Email address value you copied in the previous step.
   • In the Service Account Private Key Path (i.e. *.json OR *.p12) field use downloaded certificate's file path.
2. Done! Now you are ready to use Google BigQuery Connector!

Parameters