Connect to Microsoft SQL Server

The Microsoft SQL Server connector lets you create data sources based on Microsoft SQL Server. You can then visualize that data in your Looker Studio reports and explorations.

In this article:

• Supported versions
• How to connect to Microsoft SQL Server
• Notes
• Limits
• License
• Related resources

Supported versions

The Microsoft SQL Server connector uses the V7.2 JDBC driver. This connector was tested on MS SQL Server, and is compatible with the following earlier versions:

• 2017
• 2016
• 2014
• 2012

Check the MS SQL Server version compatibility document to see if your database version is compatible with the V7.2 JDBC driver.

How to connect to Microsoft SQL Server

A Looker Studio data source can connect to a single Microsoft SQL Server database table, or to a custom query you provide.

To connect

1. Sign in to Looker Studio.
2. On the Looker Studio home page, in the top left, click Create and then select Data Source.
3. Select the Microsoft SQL Server connector.
4. Set up the connection to your database:

   Connect with a hostname or IP address,

   1. On the left, select BASIC.
   2. Enter the connection details:
      1. Hostname or IP address
      2. Port (Optional)
      3. Database
      4. Username
      5. Password

   Connect with a JDBC URL

   1. On the left, select JDBC URL.
   2. Enter the connection details:
      1. JDBC URL

         Example
         
         jdbc:sqlserver://<hostname or IP address>[:<port>];databaseName=<database>
         
         Learn more about building the connection URL (opens in the Microsoft documentation site).

      2. Username
      3. Password

   If you use the JDBC hostname option, you can make sure it's accessible with public DNS lookup tools such as Whois.
   Note: Looker Studio can't connect to localhost. You must use a public hostname or IP address.

5. Enable SSL (optional)

   Looker Studio supports secure (encrypted) connections to the server using the TLS (Transport Layer Security) protocol. TLS is also referred to as SSL (Secure Sockets Layer). To enable a secure connection, check Enable SSL, then provide your SSL configuration files.

6. Click AUTHENTICATE.
7. Select a table from the list or enter a custom query.

   Select the CUSTOM QUERY option to provide a SQL query instead of connecting to a single table. Looker Studio uses this custom SQL as an inner select statement for each generated query to the database.

   Custom SQL queries may only have a single statement.

   For example, the following will not work because it has multiple SQL statements:

   DECLARE cost_per_tb_in_dollar FLOAT64 DEFAULT 4.2;

   SELECT total_bytes_billed / (1024 * 1024))* cost_per_tb_in_dollar)/(1024*1024))) FROM billing-table;

   This is also true when exploring from BigQuery: your query result set may only be based on a single statement.

   Note: Queries in Looker Studio may timeout after 3 to 5 minutes. If your custom queries time out, try the following approaches to resolve the issue:

   • Simplify the query so that it runs faster.
   • Run the query in your database and store the results in a separate table. Then connect to that table in your data source.
8. Click CONNECT.

In a moment, the data source fields list page appears. In the upper right, click CREATE REPORT or EXPLORE to start visualizing your data.

Notes

Data types

Looker Studio maps your database's native data types to a unified set of data types. If Looker Studio encounters a column in your table or query of an unsupported type, it will not create a field for that column.

Firewall and database access

To allow Looker Studio to connect to and query databases that are behind a firewall, you will need to open access to one of the IP addresses shown below. You may also need to configure your database to accept traffic from these addresses. Consult the documentation for your specific database for instructions on how to do this.

IP addresses

• 142.251.74.0/23
  

• 2001:4860:4807::/48 (Optional, for platforms that support IPv6)

Limits of the Microsoft SQL Server connector

• You can query a maximum of 150 K rows per query using this connector. If you exceed this limit, the data will be truncated.
• Column headers (field names) must use ASCII characters only. Non-ASCII characters aren't supported.

License

The Microsoft SQL Server connector was developed using Microsoft JDBC Driver for SQL Server, which is distributed under the MIT license.

Related resources

Create a data source