Create a Microsoft SQL Server Data Source

Follow these steps to connect to a Microsoft SQL Server and create a Microsoft SQL Server data source:

  1. Configure User Access Rights

  2. Create a Microsoft SQL Server Data Source

Refer to Additional Information for additional performance tips and information about Microsoft SQL Server feature support.

Configure User Access Rights

We recommend that you create a dedicated user and user role for integration with the GoodData platform.

Steps:

  1. Create a user role and grant the following access rights to it:

    GRANT SELECT ON SCHEMA::{schema_name} TO {role_name} WITH GRANT OPTION;
    

    If you intend to enable pre-aggregation caching, additional usage rights have to be granted for your pre-aggregation schema cache_schema_name:

    GRANT CREATE TABLE TO {role_name};
    GRANT ALTER, SELECT, INSERT ON SCHEMA::{cache_schema_name} TO {role_name} WITH GRANT OPTION;
    
  2. Create a user and grant it with the user role:

     ALTER ROLE {role_name} ADD MEMBER {user_name};
    

Create a Microsoft SQL Server Data Source

Once you have configured your Microsoft SQL Server user’s access rights, you can proceed to create a Microsoft SQL Server data source that you can then connect to.

UI
API

Steps:

  1. On the home page switch to Data sources.

    data sources tab
  2. Click Connect data.

    connect data
  3. Select Microsoft SQL Server.

    select data source type
  4. Name your data source and fill in your Microsoft SQL Server credentials and click Connect:

    db cretentials
  5. Input your schema name and click Save:

    db cretentials

    Your data source is created!

    db creation complete

Steps:

  1. Create a Microsoft SQL data source with the following API call:

    curl $HOST_URL/api/v1/entities/dataSources \
      -H "Content-Type: application/vnd.gooddata.api+json" \
      -H "Accept: application/vnd.gooddata.api+json" \
      -H "Authorization: Bearer $API_TOKEN" \
      -X POST \
      -d '{
        "data": {
        "type": "dataSource",
        "id": "<unique_id_for_the_data_source>",
        "attributes": {
          "name": "<data_source_display_name>",
          "url": "jdbc:sqlserver://<MSSQL_HOST>:5432",
          "schema": "<MSSQL_SCHEMA>",
          "type": "MSSQL",
          "username": "<MSSQL_USER>",
          "password": "<MSSQL_PASSWORD>"
        }
        }
      }' | jq .
    
  2. To confirm that the data source has been created, ensure the server returns the following response:

    {
      "data": {
        "type": "dataSource",
        "id": "<unique_id_for_the_data_source>",
        "attributes": {
          "name": "<data_source_display_name>",
          "url": "jdbc:sqlserver://<MSSQL_HOST>:5432",
          "schema": "<MSSQL_SCHEMA>",
          "type": "MSSQL",
          "username": "<MSSQL_USER>"
        }
      },
      "links": {
        "self": "$HOST_URL/api/v1/entities/dataSources/<unique_id_for_the_data_source>"
      }
    }
    

Additional Information

Ensure you understand the following limitations and recommended practice.

Data Source Details

  • The JDBC URL must be in the following format:

    jdbc:sqlserver://<host>:<port>

  • Basic authentication is supported. Specify user and password.

  • GoodData uses up-to-date drivers.

  • The following database versions are supported:

    • 13.0 (SQL Server 2016)
    • 14.0 (SQL Server 2017)
    • 15.0 (SQL Server 2019)
    • 16.0 (SQL Server 2022)

Unsupported Features

GoodData does not support the following features:

  • Functions GREATEST, LEAST with more than two arguments in Microsoft SQL server before 2022 (version < 16.x)

  • Aggregation functions: CORREL, COVAR and SLOPE

Known Issues

  • SQL Commands and Filtering

    Redshift, MSSQL, AzureSQL, and SynapseSQL databases remove trailing spaces in WHERE and HAVING SQL commands, potentially affecting dashboard and workspace data filter functionality. For example, the command SELECT * FROM table WHERE column = 'value ' would return both 'value' and 'value ' (with a trailing space). Other databases retain trailing spaces in values.

Performance Tips

If your database holds a large amount of data, consider the following practices:

  • Denormalize the relational data model of your database. This helps avoid large JOIN operations. Because Microsoft SQL is a columnar database, queries read only the required columns and each column is compressed separately.

  • Index the columns that are most frequently used for JOIN and aggregation operations. Those columns may be mapped to attributes, labels, primary and foreign keys.

Query Timeout

Query timeout is configurable per application instance. It is a parameter of the sql-executor service, default value is 160 seconds.

Query timeout is closely related to the ACK timeout. Proper configuration of the system requires that ACK timeout is longer than query timeout. Default ACK timeout value is 170 seconds.

Permitted parameters

  • AADSecurePrincipalId
  • AADSecurePrincipalSecret
  • accessToken
  • applicationIntent
  • applicationName
  • authentication
  • authenticationScheme
  • columnEncryptionSetting
  • connectRetryCount
  • connectRetryInterval
  • database
  • databaseName
  • delayLoadingLobs
  • disableStatementPooling
  • domainName
  • domain
  • enablePrepareOnFirstPreparedStatementCall
  • encrypt
  • hostNameInCertificate
  • instanceName
  • keyVaultProviderClientId
  • keyVaultProviderClientKey
  • lastUpdateCount
  • msiClientId
  • multiSubnetFailover
  • packetSize
  • port
  • portNumber
  • realm
  • replication
  • selectMethod
  • sendStringParametersAsUnicode
  • sendTemporalDataTypesAsStringForBulkCopy
  • sendTimeAsDatetime
  • server
  • serverName
  • serverNameAsACE
  • serverPreparedStatementDiscardThreshold
  • serverSpn
  • sslProtocol
  • TransparentNetworkIPResolution
  • trustServerCertificate
  • useFmtOnly
  • workstationID
  • xopenStates