Connect to Microsoft SQL Server
Credentials
DirectQuery delegates the credentials handling to a Microsoft
SQLServerDataSource
.
To execute SQL in Microsoft SQL Server, 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();
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();
info
For more examples, here are the relevant Microsoft documentation pages:
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.