mssqllibR2DBC for SQL Server

Configuration

Option names follow Microsoft JDBC where this driver implements the same idea. URL query values arrive as strings. Boolean options are read with Boolean.parseBoolean, so only true is true.

Required URL options

TdsConnectionFactoryProvider.supports is true only when driver is mssql. create requires host, port, and database. There is no implicit port. A raw IP or a bracketed host is sent as LOGIN7 ServerName unchanged. A DNS name is reduced to its first label when the socket host is that same name.

OptionRequiredMeaning
driveryesmssql. sqlserver is rejected.
hostyesTCP host. On an Azure gateway login, LOGIN7 ServerName is the first DNS label. After a redirect it is the worker host.
portyesTCP port, commonly 1433.
databaseyesInitial catalog. A routing token that names a database replaces it for the next LOGIN7.
userSQL passwordRequired when the mode needs a password and no token source is configured.
passwordSQL passwordSame rule as user. Percent-encode reserved characters in a URL.
connectTimeoutnoThe login budget when loginTimeout is omitted. If both are set they must be equal. The factory throws loginTimeout and connectTimeout differ; set one login budget. Do not pass a raw URL string into the typed CONNECT_TIMEOUT option. The SPI stores URL values as strings.

Driver options

Constants are on org.mssqllib.r2dbc.mssql.TdsLibOptions. Authentication behavior is specified on the authentication page.

OptionDefaultMeaning
authentication SqlPassword Authentication or a JDBC name. Omitted, blank, and NotSpecified are SQL password.
accessToken unset Static token embedded in LOGIN7. Sensitive. Ignored when a callback is set. Do not put it in a URL. A known exp inside five minutes is rejected before connect.
accessTokenCallback unset AccessTokenCallback. Called after FEDAUTHINFO as (spn, stsUrl). Not expressible in a URL. Wins over the other token sources.
accessTokenCallbackClass unset Binary name of a public no-arg AccessTokenCallback. A URL may carry this name.
tokenProvider unset TokenProvider. Called as (stsUrl, spn) and must emit one token string. Not a URL keyword. Ignored when a callback or class is set.
entraClientId unset Application id for the built-in client-credentials callback. Used only when no callback, class, or token provider is set. Requires entraClientSecret.
entraClientSecret unset Secret paired with entraClientId. Sensitive. Both must be set or both absent in that fallback. An assertion callback is posted instead of this secret when one is configured.
trustServerCertificate false Install a trust manager that does not check the certificate. There is no ssl option.
trustStore unset Path of a trust store file, used when trustServerCertificate is false. Otherwise the JVM default context is used.
trustStorePassword unset Password for trustStore. The store type is KeyStore.getDefaultType().
materializeLobs false true buffers max and XML values and emits the row when every column is present. String and byte[] reads then succeed. See compatibility.
useWatermarks true Pause the socket when a Clob, Blob, or row queue hits its high watermark. false leaves the queue unbounded.
detectOrphanedExecutions true If the result subscriber is gone and another row arrives, send Attention. false drops those rows and lets the server finish.
streamCancellationLogLevel INFO SLF4J level name for the log line written when Attention is sent because the client cancelled. An unknown name falls back to INFO. Accepted names are the org.slf4j.event.Level constants, including ERROR, WARN, INFO, DEBUG, and TRACE.
loginTimeout 60 seconds Whole seconds, or an ISO-8601 duration such as PT2M. 0 also means 60 seconds. This is the whole login budget, including retries.
connectRetryCount 2 Extra attempts after the first failure. 0 disables retry. Range 0 to 255.
connectRetryInterval 10 seconds Wait before retries after the first. Range 1 to 60 seconds. The first retry does not wait.

TLS

PRELOGIN is sent with encryption off. The server selects the session:

Server answerTLS before LOGIN7TLS after LOGIN7
OFFyes, for logindropped before the login acknowledgement is read
ONyeskept
NOT_SUPPORTEDnono
REQUIREDyeskept. Azure SQL and Force Encryption.

trustServerCertificate=true builds a TLS 1.2 context that does not check the peer certificate. A trustStore path, used when that flag is false, is also a TLS 1.2 context loaded from that file. When neither is set, the driver uses SSLContext.getDefault(). The store type is KeyStore.getDefaultType(). A DNS host sets SNI. A numeric host does not. The redirect hop builds a new context so the gateway session is not resumed.

Login retry

Each attempt's socket timeout is the time left in the budget, capped at 30 seconds. The first retry is immediate. Later retries wait connectRetryInterval. Retry stops when the time left is less than or equal to that interval, or when connectRetryCount is exhausted. A longer JDBC-style profile (loginTimeout=120, count 5, interval 15) is available by setting those options. It is not the default.

Retried failures include the JDBC transient login errors, among them 40613, plus socket timeout, connection reset, end of stream, and broken pipe. A paused Azure SQL database accepts TCP and then returns 40613 after about 15 seconds. That is a retry, not a defect in the driver. Not retried: login denied, including 18456 and 40615, and SSLException. An expired token is not retried as the same JWT. A callback may try one new login.

When retries stop, 40613 is an R2dbcTransientResourceException. A bad password is an R2dbcPermissionDeniedException. FedAuth uses this same loop. Each attempt is a new socket, so a callback runs again.

Statements, cursors, batches

  • A statement that never calls bind is one SQL batch. add() with no values and no saved set returns the same statement. After a saved set, another add() or execute() with no new values throws IllegalStateException (The statement is parametrized and not all parameter values are provided). The driver does not count placeholders in the SQL, so binding @p0 and leaving @p1 unbound is still sent. Connection.createBatch() joins Batch.add statements into one SQL batch.
  • Bound parameters are sp_executesql. bind(0, value) is @p0. bind("id", value) is @id. bind(null) throws. Use bindNull(name, type). Any index and any name are accepted.
  • Parameters.out is an output parameter. A null output BIGINT is an 8-byte INTN. TINYINT, SMALLINT, INTEGER, and BIGINT keep distinct lengths.
  • Statement.add() batches parameter sets into one RPC. fetchSize applies only when there is a single execution.
  • fetchSize(0) is the default and sends the statement directly. A positive fetch size opens a server cursor. A negative fetch size throws.
  • returnGeneratedValues throws UnsupportedOperationException with the message The R2DBC-MSSQL driver does not support returnGeneratedValues. A null column-name array throws that same exception.
  • Result.map with a row-and-metadata function maps rows. Result.map with a function over Readable maps each row and each out-parameter segment, in order. Update counts and informational messages are skipped. An error fails the publisher. filter can keep an out-parameter segment, and that function still yields its value.

Transactions

  • A new connection has auto-commit on. isAutoCommit is true only when that flag is on and no transaction is open.
  • setAutoCommit sends IF @@TRANCOUNT > 0 COMMIT TRAN and then SET IMPLICIT_TRANSACTIONS OFF or ON. The same mode completes without a round-trip. The flag changes only after the batch succeeds. A closed connection fails with IllegalStateException before a write. When that change ends an isolation override that differs from the session level, the same batch restores the session level.
  • The session isolation level starts at read committed. setTransactionIsolationLevel sends SET TRANSACTION ISOLATION LEVEL and stores the level only after the batch succeeds. A null level fails with IllegalArgumentException. getTransactionIsolationLevel returns a transaction override while one is active, and the session level otherwise.
  • beginTransaction() sends a transaction-manager begin with isolation byte 0, which keeps the session level. It does not install an override.
  • beginTransaction(TransactionDefinition) sends read uncommitted, read committed, repeatable read, or serializable on that begin. Any other level, and a missing level, are byte 0. A supplied level becomes the override after the begin succeeds. A transaction name longer than 32 characters is shortened. A lock-wait attribute then sends SET LOCK_TIMEOUT for that transaction. Commit or rollback clears the override, sends the session isolation again when it differs, and restores the lock wait from before the begin. A non-zero isolation byte stays on the SQL Server session, which is why the driver sends the session level again.
  • commitTransaction and rollbackTransaction send the transaction-manager operation when a transaction is open.
  • createSavepoint rejects a null or empty name with IllegalArgumentException (Savepoint name must not be null or empty) before any write. Inside a transaction it sends only the savepoint and leaves auto-commit unchanged. With no transaction open it turns auto-commit off, begins a transaction, then sends the savepoint.
  • rollbackTransactionToSavepoint rejects a null or empty name the same way, and fails with IllegalStateException when no transaction is open.
  • releaseSavepoint rejects a null or empty name. Any other name completes with no server call. SQL Server has no RELEASE SAVEPOINT.
  • setLockWaitTimeout sends SET LOCK_TIMEOUT. Null fails on the call (lock wait timeout must not be null). A negative duration is -1 (wait forever). Zero is 0. Any other value is whole milliseconds, and a value above Integer.MAX_VALUE fails (lock wait timeout is too large). The session value updates only after the batch succeeds. The default is -1.
  • setStatementTimeout stores a client deadline. Null fails on the call (statement timeout must not be null). A negative duration fails (statement timeout must not be negative). Duration.ZERO means no deadline. The value is read when a statement or batch is subscribed, so a timeout set earlier in the same chain applies. If no response arrives in time, the driver cancels that exchange and the subscriber receives R2dbcTimeoutException (Statement timed out before the first response). The first response disarms the timer. A timer that fires after the next statement has started does not cancel that later statement.

Pools and closed sockets

A peer reset, a peer FIN, a closed channel, or a write on a closed socket marks the session closed before any later write. isClosed() is then true. validate(LOCAL) and validate(REMOTE) return false and do not run a query. postAllocate and preRelease fail so r2dbc-pool discards the connection. The next statement must not write on that socket. The next create() opens a new login.

On an open session, validate(LOCAL) is true and validate(REMOTE) runs SELECT 1. There is no keepalive in this driver.

Types

Encoders cover integers, strings, BigDecimal, boolean, float, date and time, binary, GUID, and streaming Clob and Blob. Decoders cover strings (including XML), numeric values (integers, floats, bit, decimal, money), date and time, binary, and GUID. A TDS type with no decoder throws UnsupportedOperationException.

tinyint requested as Integer or Short is the unsigned value 0 through 255. Requested as Byte, or with no class, it is a signed Java byte, so 255 is -1. uniqueidentifier binds and reads as java.util.UUID. SQL Server stores the first three UUID groups little-endian. A String request returns UUID.toString().

SQL Server SUM(int) and AVG(int) return int and can raise 8115 before a bigint output is assigned. Callers use CAST(… AS BIGINT) or COUNT_BIG(*).

Exceptions and column access

A server error token becomes an R2DBC exception. Numbers 208 and 2812 are R2dbcBadGrammarException (208 uses SQLSTATE 42S02). Numbers 547, 2601, and 2627 are R2dbcDataIntegrityViolationException (SQLSTATE 23000). Numbers 229 and 230 are R2dbcPermissionDeniedException. Number 1205 is R2dbcTransientResourceException. Severity 20 and above is R2dbcNonTransientResourceException. Severity 15, including a syntax error, is R2dbcBadGrammarException. Every other error is R2dbcException. SQLSTATE is HY000 except for the numbers named above.

A column index outside the row throws IndexOutOfBoundsException. An unknown column name throws NoSuchElementException (Column name '…' does not exist). A null name or a null type throws IllegalArgumentException. A column the network has not finished throws IllegalStateException (Cannot read column N before it has been fully received from the network.). Name lookup is case-insensitive and returns the first match.

Login failures stay on the login retry rules. 40613, after retries stop, is R2dbcTransientResourceException. A bad password is R2dbcPermissionDeniedException.