mssqllibR2DBC for SQL Server

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.

URL
r2dbc:mssql://user:password@localhost:1433/database?trustServerCertificate=true
SELECT 1
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
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.

Named parameter
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.

Service principal
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.