Connect to Microsoft SQL Server - Atoti Hub

Credentials

Atoti DirectQuery delegates the credentials handling to a Microsoft SQLServerDataSource. Executing SQL in Microsoft SQL Server requires a SQL user in the target database. As described in the Microsoft documentation, several authentication methods can be used. The following example builds the data source with an SQL login:

final SQLServerDataSource dataSource = new SQLServerDataSource();
dataSource.setUser(username);
dataSource.setPassword(password);
// Set up the database to connect to
dataSource.setPortNumber(port);
dataSource.setDatabaseName(DATABASE_NAME);
// Setup connection security parameters
dataSource.setEncrypt("true");
dataSource.setTrustServerCertificate(true);
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);
final MsSqlClientSettings clientSettings =
    MsSqlClientSettings.builder().dataSource(dataSource).build();

More examples are available in the Microsoft documentation:

Data source sample

Permissions

The SQL user used by DirectQuery needs to have the permissions to run queries in Microsoft SQL Server. To be able to run queries, the CONNECT permission is needed on the Microsoft SQL Server to be used, then the SELECT permission is needed on the schemas or tables the user will use.