Quick start
Host, port, and database are required. SQL password login also requires a user and a password. .block() is only for a short program. A reactive application returns the publisher.
SQL username and password
Omitting authentication, leaving it blank, or setting NotSpecified selects SqlPassword. Percent-encode reserved characters in the user or password. trustServerCertificate=true skips TLS certificate checks. Leave it false when the JVM trust store, or trustStore, already trusts the server.
r2dbc:mssql://user:password@localhost:1433/database?trustServerCertificate=true
String url = "r2dbc:mssql://user:password@localhost:1433/database"
+ "?trustServerCertificate=true";
ConnectionFactory factory = ConnectionFactories.get(url);
Integer value = Mono.usingWhen(
Mono.from(factory.create()),
connection -> Flux.from(connection.createStatement("SELECT 1").execute())
.flatMap(result -> result.map((row, metadata) -> row.get(0, Integer.class)))
.single(),
Connection::close)
.block();
Imports: io.r2dbc.spi.Connection, ConnectionFactories, ConnectionFactory, reactor.core.publisher.Flux, and Mono.
The same login in code
PORT is an int. DRIVER, HOST, DATABASE, USER, and PASSWORD are the constants on io.r2dbc.spi.ConnectionFactoryOptions.
ConnectionFactoryOptions options = ConnectionFactoryOptions.builder()
.option(ConnectionFactoryOptions.DRIVER, "mssql")
.option(ConnectionFactoryOptions.HOST, "localhost")
.option(ConnectionFactoryOptions.PORT, 1433)
.option(ConnectionFactoryOptions.DATABASE, "database")
.option(ConnectionFactoryOptions.USER, "user")
.option(ConnectionFactoryOptions.PASSWORD, "password")
.option(TdsLibOptions.TRUST_SERVER_CERTIFICATE, true)
.build();
ConnectionFactory factory = ConnectionFactories.get(options);
TdsLibOptions is org.mssqllib.r2dbc.mssql.TdsLibOptions.
A parameter
A statement with no parameters is a SQL batch. A bound parameter is sent as sp_executesql. bind("id", value) and bind("@id", value) both send @id. bind(0, value) sends @p0. The SQL text has to use that name. null is bindNull, not bind.
Integer id = Mono.usingWhen(
Mono.from(factory.create()),
connection -> Flux.from(
connection.createStatement("SELECT @id AS id")
.bind("id", 7)
.execute())
.flatMap(result -> result.map((row, metadata) -> row.get("id", Integer.class)))
.single(),
Connection::close)
.block();
Entra client credentials
Set authentication to ActiveDirectoryServicePrincipal and pass entraClientId with entraClientSecret. User and password are not required. Do not put an access token in the URL. The authority and the scope come from the server after login, not from a tenant option.
ConnectionFactoryOptions options = ConnectionFactoryOptions.builder()
.option(ConnectionFactoryOptions.DRIVER, "mssql")
.option(ConnectionFactoryOptions.HOST, "host.database.windows.net")
.option(ConnectionFactoryOptions.PORT, 1433)
.option(ConnectionFactoryOptions.DATABASE, "database")
.option(TdsLibOptions.AUTHENTICATION, "ActiveDirectoryServicePrincipal")
.option(TdsLibOptions.ENTRA_CLIENT_ID, clientId)
.option(TdsLibOptions.ENTRA_CLIENT_SECRET, clientSecret)
.option(TdsLibOptions.TRUST_SERVER_CERTIFICATE, true)
.build();
ConnectionFactory factory = ConnectionFactories.get(options);
The query is the same createStatement call as the SQL login. Token acquisition, callbacks, and what the parsed authentication names do not do are on the authentication page. Spring wiring is on the Spring Boot page.
Each ConnectionFactory.create() opens a new TDS session. Pool connections in the application, or in Spring, rather than calling create per query. A paused Azure SQL database can accept TCP and then return error 40613. The default login budget retries that. See login retry.