Connect to Microsoft SQL Server 2017

Connect Data Studio to a Microsoft SQL Server table or custom query.

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

In this article:

Supported versions

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

  • 2016
  • 2014
  • 2012
  • 2008 R2

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 Data 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 Data Studio.
  2. In the top left, click Create then select Data Source.
  3. Select the Microsoft SQL Server 2017 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: Data Studio can't connect to localhost. You must use a public hostname or IP address.
  5. Enable SSL (optional)

    Data 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. Data Studio uses this custom SQL as an inner select statement for each generated query to the database.
  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

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

Firewall access

To allow Data Studio to connect to and query databases that are behind a firewall, you will need to open access to specific IP addresses.  

Alert: The IP addresses currently used by Data Studio to connect to your databases will change in early 2023. To ensure that your reports continue to work properly after the change, you must open the IP addresses listed below.

Show the list of IP addresses

142.251.74.0/23 
2001:4860:4807::/48 (Optional, for platforms that support IPv6)
74.125.0.0/16

Limits of the Microsoft SQL Server 2017 connector

  • You can query a maximum of 150K rows using this connector.
  • Column headers (field names) must use ASCII characters only. Non-ASCII characters aren't supported.

License

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

Create a data source

Was this helpful?
How can we improve it?
Search
Clear search
Close search
Google apps
Main menu
Search Help Center
true
102097
false
false