Connect to Synapse

info

For more information on how to set up security in Azure Synapse, see the official Synapse documentation.

Credentials

DirectQuery delegates the credentials handling to a Microsoft SQLServerDataSource.

To execute SQL in Azure Synapse, you need to have a SQL user in the database you will use. As described in Microsoft documentation, several authentication methods can be used.

For example, here is how you would build the data source with an SQL login:

final SQLServerDataSource dataSource = new SQLServerDataSource();
// Setup authentication
// authenticationMethod = "SqlPassword", "ActiveDirectoryPassword", ... depending on which login are used
dataSource.setAuthentication(authenticationMethod);
dataSource.setUser(USERNAME);
dataSource.setPassword(PASSWORD);
// Set up the database to connect to
dataSource.setServerName(SERVER_NAME);
dataSource.setDatabaseName(DATABASE_NAME);
// Setup connection security parameters
dataSource.setEncrypt(true);
dataSource.setHostNameInCertificate(HOST_NAME_IN_CERTIFICATE);
dataSource.setTrustServerCertificate(false);
dataSource.setSendTimeAsDatetime(false);
// Setup number of connection attempts (first tries may fail when pool needs to warm up)
dataSource.setConnectRetryCount(CONNECT_RETRY_ATTEMPTS);
dataSource.setConnectRetryInterval(CONNECT_RETRY_INTERVAL);

There are two other common ways to connect to Synapse:

• create a SQL user with an Azure Active Directory login.
• create a SQL user backed by a Managed Identity.

info

For more examples, here are the relevant Microsoft documentation pages:

• Data source sample
• Synapse connection strings

Permissions

The SQL user used by DirectQuery needs to have the permissions to run queries in Synapse.

To be able to run queries, the CONNECT permission is needed on the Synapse database to be used, then the SELECT permission is needed on the schemas or tables the user will use.