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.
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.
Step 1
Step 2
On the Looker Studio home page, in the top left, click Create, and then select Data Source.
Step 3
Select the Microsoft SQL Server connector.
Step 4
Set up the connection to your database using a hostname or IP address, or a JDBC URL.
Connect using a hostname or IP address
In the connection, select BASIC . Enter the connection details:
- Hostname or IP address
- Port (Optional)
- Database
- Username
- Password
Connect using a JDBC URL
In the connection, select JDBC URL . Enter the connection details:
- JDBC URL
Example
jdbc:sqlserver://HOSTNAME[:PORT];databaseName=DATABASE
- Username
- Password
If you use the JDBC hostname option, you can make sure it's accessible with public DNS lookup tools such as Whois.
To connect using an IPv6 literal address, enclose the address in square brackets. For example: jdbc: sqlserver ://[2001:db8:1:1:1:1:1:1]/your_db
Step 5
Enable SSL
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.
This setting is not required by Looker Studio but may be required for your database configuration. Check with your database vendor for details.
Step 6
Click AUTHENTICATE.
Step 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.
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;
Queries in Looker Studio may time out after three to five 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. Connect the new table in your data source.
Step 8
Click CONNECT.
In a moment, the data source fields list page appears. Click CREATE REPORT to start visualizing your data.
Notes
Data types
Looker Studio maps your database's built-in 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 won't 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 following IP addresses. 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/232001: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.
Troubleshooting
See the Looker Studio troubleshooting guide for a list of common error messages and steps to resolve them.
License
The Microsoft SQL Server connector was developed using Microsoft JDBC Driver for SQL Server, which is distributed under the MIT license.