Create SQL DW Connections

This section describes how to create connections to Microsoft SQL Datawarehouse (DW).

Limitations

• Microsoft SQL DW connections are available only if you have deployed the Designer Cloud Powered by Trifacta platform onto Azure.

• SSL connections to SQL DW are required.

  NOTE: In this release, this connection cannot be created through the CLI or APIs. Please create connections of this type through the application.

Pre-requisites

• If you haven't done so already, you must create and deploy an encryption key file for the Alteryx node to be shared by all relational connections. For more information, see Create Encryption Key File.

Connection Types

The Designer Cloud Powered by Trifacta platform supports two types of connections to an Azure SQL DW data warehouse:

Azure SQL DW permissions

• Read-Only connection: The authenticating DB user must have read permissions to any SQL DW databases, schemas and tables to which the user should have access.

• Read-Write connection: In addition to the above, the authenticating DB user must have the following permissions:

  CREATE TABLE**
  ALTER ANY SCHEMA
  ALTER ANY EXTERNAL DATA SOURCE
  ALTER ANY EXTERNAL FILE FORMAT

  
  

  • The authenticating DB user must also have read access to the external data source.

Azure SQL DW External Data Source Name

When specifying a SQL DW Read-Write connection, you can provide an External Data Source Name value as part of the connection definition. The External Data Source enables publishing and support for large-scale data ingestion. 

If the External Data Source is not provided:

• The connection is read-only.
• The native ingestion of the Designer Cloud Powered by Trifacta platform is used.

Requirements:

• The external data source must be created by the database admin on the default database defined in the SQL DW connection. For more information, see https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-data-source-transact-sql?view=azure-sqldw-latest&tabs=dedicated.
• The External Data Source must point to the same storage location as the base storage layer for the Designer Cloud Powered by Trifacta platform. For example, if the base storage layer is WASB, the External Datasource must point to the same storage account defined in Alteryx configuration. If this configuration is incorrect, then publishing and ingestion of data fail.
• For more information on privileges required for the authenticating DB user, see https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-table-transact-sql.

Configure

To create this connection:

• Read-only connection: See Import Data Page.
• Read-write connection: See Connections Page. 
• For additional details on creating a relational connection, see Enable Relational Connections.

Please create a connection of this type in the appropriate page and modify the following properties with the listed values:

testsql.database.windows.net

Use

For more information on locating data, see Database Browser.

For more information, see Using SQL DW.

Data Conversion

For more information on how values are converted during input and output with this database, see SQL DW Data Type Conversions.