Microsoft SQL Server connections

Part of aner.db, the native database module. The module reference covers shared credentials, connection lifetime, result conversion and all four connectors.

aner.db.mssql uses the native ODBC API and Microsoft ODBC Driver 18 for SQL Server. It reuses a TCP session, prepares statements and binds Unicode parameters. Results use the shared database API and convert to bounded tables and tensors through aner.data.

Requirements

Use a compatible installed Aner executable with optional ODBC support, an ODBC manager and the separately installed Microsoft driver registered as ODBC Driver 18 for SQL Server. db.available("mssql") reports support in the executable; it does not mean the vendor driver is installed or a server reachable. Aner, the manager and driver need compatible CPU architectures.

Microsoft provides macOS installation instructions, Linux installation instructions and Windows downloads. On macOS use Microsoft's supported unixODBC manager.

Private credentials and connection

Store the password using the hidden terminal prompt:

aner secrets set demo-mssql-password --store system
aner secrets status demo-mssql-password --store system

Use secrets.stored("demo-mssql-password") as the password reference. For a private injected environment use secrets.env("ANER_MSSQL_PASSWORD"). Linux system storage is unavailable. The explicit POSIX file fallback uses --store file with secrets.file and stores plaintext with owner only permissions. See Credentials for each store's boundaries.

All settings in this example are fictional:

import aner.db;
import aner.db.mssql;
import aner.secrets;

let connection: DBConnection = mssql.connect(
    host: "sql.example.invalid", port: 1433, database: "example_science",
    user: "example_reader", password: secrets.stored("demo-mssql-password"),
    sslmode: "verify-full", timeout_seconds: 10);
let result = db.query(connection,
    "SELECT CAST(? AS bigint) AS sample_id, CAST(? AS nvarchar(200)) AS note",
    List("123", "A Unicode note: café"), max_rows: 10, max_bytes: 1048576);
print(db.integer(result, 0, 0));
print(db.text(result, 0, 1));
db.close(connection);

The factory requires import aner.db.mssql and a SecretRef. Configure one hostname or IP address with a separate TCP port, never a raw connection string, DSN or named instance string. Ports accept 1 through 65535 and connection timeouts 1 through 300 seconds, defaulting to 10. Every statement uses a native 30 second query timeout. Database, username and password values are escaped independently; temporary authentication buffers owned by Aner are wiped after connection setup.

The current factory exposes SQL authentication. Windows integrated authentication and Microsoft Entra tokens are not exposed. Connection copies share one session and operations serialize; use separate sessions for independent concurrent workers.

Verified TLS

Install the issuing CA in the client's trust store and choose a hostname matching the certificate. Select a weaker mode only deliberately for the appropriate private development environment. Aner does not downgrade TLS automatically. See Microsoft's connection attributes.

Parameters, types and batches

SQL Server uses positional ? markers bound as native Unicode values. Supply String or null parameters in marker order, with casts when SQL type inference is ambiguous. Identifiers cannot be parameters. SQL Server accepts at most 2100 parameters.

Long Unicode and binary values are read in bounded chunks. Query defaults are 100000 rows and 67108864 retained bytes; overflow returns no partial result. The budget excludes native driver buffers, allocator capacity and conversion buffers. Use projection and TOP or OFFSET ... FETCH to reduce server work. Decimal and binary columns remain strings in DataTable; tensor conversion needs complete numeric features.

ODBC accepts T SQL batches. db.query requires exactly one result and rejects additional results, including row count results. Earlier statements may already have side effects when a batch is rejected. Prefer one statement, or use an explicit transaction. db.execute consumes command results, adds reported affected row counts and rejects tabular results. SET NOCOUNT ON can make the count zero. Do not send the administration tool's GO separator to ODBC.

let started = db.execute(connection, "BEGIN TRANSACTION");
let changed = db.execute(connection,
    "UPDATE aner_demo.samples SET label = CAST(? AS int) WHERE sample_id = CAST(? AS bigint)",
    List("2", "123"));
let rolled_back = db.execute(connection, "ROLLBACK TRANSACTION");

Run this fragment before closing an active session. Use COMMIT TRANSACTION to keep a change; follow your transaction policy on failure. Diagnostics expose SQLSTATE instead of private native messages. Closing invalidates every alias, while completed results remain readable.

Four class demonstration

Run db_mock_mssql.sql as one batch in an administration tool with permission to create the dedicated demo schema. It creates 400 deterministic samples with three numeric features and four labels. Repeated setup preserves existing rows and adds missing IDs. Adapt the complete Aner source privately, then check and run it:

aner check examples/db_mssql.aner
aner run examples/db_mssql.aner

Checking opens no connection. Running reads the prepared demo tables through your authenticated role. Native local tests cover settings, escaping, Unicode and typed result handling. Live compatibility still depends on the installed vendor driver, server and authentication configuration.