Connect to Google BigQuery database from SSLS (SQL Server Linked Server) using the DataDirect Google BigQuery ODBC Driver

Introduction

Progress DataDirect’s Google BigQuery ODBC Driver offers a high-performing, secure and reliable connectivity solution, for ODBC applications to access Google BigQuery data. If you want to connect Google BigQuery data from SSLS, Progress DataDirect has you covered. In this tutorial, you will learn how to configure the DataDirect Google BigQuery driver within SSMS (SQL Server Management Studio). These steps can also be used to configure any ODBC connection.  

 

SQL Server Management Studio is an ideal environment for managing the infrastructure of the SQL Server database and SQL Server itself. With SSMS, you can easily connect to your SQL Server instance, configure, and monitor database objects, as well as execute queries. Besides this, there is a possibility to create an ODBC connection to external data from SSMS.

 

Thus, you can work with other databases and clouds used by your applications. 

In this article, we will describe a step-by-step procedure for establishing a connection to Google BigQuery from SSMS using our ODBC Driver for Google BigQuery.  

 

Prerequisites

This tutorial assumes that you have ODBC drivers installed and a data source configured. Ensure that SSMS and ODBC Driver architecture matches. For example, if you have a 64-Bit MS SSMS installed then you need to have the 64-Bit version of Progress DataDirect ODBC drivers installed. Most likely, your SSMS would be a 32-bit version and you would need a 32-bit driver. If you have not yet done these steps, please follow these instructions:    

 

  1. Download and install the Progress DataDirect Google BigQuery ODBC driver for your database using the 15-day trial software. In this tutorial, we are connecting to Google BigQuery.  
  2. Configure a data source for your database. See the DataDirect documentation for assistance with setting up your data source. The sample below uses a data source. 

 

Configure Connection to Google BigQuery ODBC Driver Using the ODBC Administrator

1. After the DataDirect Google BigQuery ODBC driver has been installed, open ODBC Administrator to configure the connection and click on “Add.” 

Graphical user interface

Description automatically generated 

 

 

2. Choose the “DataDirect X.X Google BigQuery” driver and click on “Finish.”   

 

Graphical user interface, text, application

Description automatically generated   

 

3. You should now see the ODBC Google BigQuery Driver Setup pop-up window launched.   

 

           

 

4. Add your Data Source Name, project, dataset, and other credentials, and click on “Apply” and then “Test Connect.”  

 

       Graphical user interface, table

Description automatically generated   

 

 

5. A pop-up window will confirm that the connection is established.   

 

                       

 

Configure the DataDirect ODBC Google BigQuery Driver with Microsoft SQL Server Management Studio

1. Launch SQL Server Management Studio.  

 

2. Navigate to Server Objects > Linked Servers > Providers > MSDASQL and confirm that the “Allow inprocess” and “Non transacted updates” options are enabled.  

Graphical user interface, text, application

Description automatically generated 

 

3. In the object explorer, navigate to Server Objects > Linked Servers. Right-click and select New Linked Server.” 

Graphical user interface, application

Description automatically generated 
 

 

4. Fill in all the details: 

 

a) Name your linked server. 

b) Check the Server type as "Other data source."

c) Select the Provider as Microsoft OLE DB Provider for ODBC Drivers from the drop-down menu.  

d) Enter the Product name as DataDirect.  

e) Enter the Data Source name created in the ODBC configuration process.  

 

Graphical user interface, application

Description automatically generated 

 

 

5. Click on the Security page and select “Be made without using a security context”. 

 

Graphical user interface, text, application

Description automatically generated 

 

6. Click on the Server Options tab and ensure that they match the options shown in the following image: