SQL Server

This page covers SQL Server as a source database, the database LakeXpress exports data from. To use SQL Server as the LakeXpress DB, see SQL Server as LakeXpress DB.

note

LakeXpress uses the ODBC driver for SQL Server connections. If ODBC drivers are not installed on the host machine, LakeXpress automatically falls back to PyMSSQL.

Connection Parameters

Parameter	Required	Description
ds_type	Yes	Must be "mssql"
auth_mode	Yes	"classic" (username/password), "integrated" (Windows authentication), or "connection_string" (supply a full ODBC connection string)
username	Classic only	SQL Server username
password	Classic only	SQL Server password
server	Classic/integrated	Hostname or IP (host is also accepted)
port	No	Port (default: 1433)
database	Classic/integrated	Database name
encrypt	No	Enable connection encryption (default: true)
trust_server_certificate	No	Trust the server's TLS certificate without validation (default: false). Set to true for self-signed certificates, common on test servers and localhost
application_intent	No	"ReadWrite" (default) or "ReadOnly". "ReadOnly" lets an Always On listener with read-only routing send both LakeXpress and the export to a readable secondary. See Availability Groups
use_adbc	No	Export through FastBCP's ADBC driver (default: true). false uses FastBCP's native driver. Set at the same level as ds_type, not inside info. See ADBC Export
fastbcp_connection_string	No	Connection string passed to FastBCP as is, in the format of its SqlClient (.NET) driver. Overrides the one LakeXpress builds; use it for options that do not convert (see Connection String Mode)

With auth_mode: "connection_string", only the connection_string field is used; username, password, server, port, database, encrypt, trust_server_certificate and application_intent are ignored. Set ApplicationIntent in the string instead.

The export uses the same TLS settings as LakeXpress's own connection: encrypt and trust_server_certificate (or Encrypt and TrustServerCertificate in the connection string) also apply to FastBCP. With the defaults, the export verifies the server certificate too.

Availability Groups

LakeXpress's own connection (discovery and the incremental MAX(column) query) and the export use the same application_intent, so they read the same replica. With the default "ReadWrite", both read the primary.

With "ReadOnly", both are routed to a readable secondary. The incremental upper bound then comes from the secondary, so it never gets ahead of the rows the export can see. If the listener routes connections across several secondaries, LakeXpress and the export may land on different ones; add an --incremental_safety_lag larger than the replica lag, or point server at a single secondary.

If you set fastbcp_connection_string, give it the same ApplicationIntent as LakeXpress's connection.

With auth_mode: "integrated", Windows integrated authentication is used and username/password are omitted.

note

The pymssql fallback driver (used automatically when no ODBC driver is installed) only supports auth_mode: "classic" and application_intent: "ReadWrite". "integrated" and "connection_string" require the ODBC driver.

Required Permissions

LakeXpress only needs read access to the schemas it exports:

GRANT SELECT ON SCHEMA::dbo TO lakexpress_user;
GRANT VIEW DEFINITION ON SCHEMA::dbo TO lakexpress_user;

INFORMATION_SCHEMA access is granted by default.

Example: Source Database

{
  "source_mssql": {
    "ds_type": "mssql",
    "auth_mode": "classic",
    "info": {
      "username": "$env{LX_MSSQL_USER}",
      "password": "$env{LX_MSSQL_PASSWORD}",
      "server": "mssql-prod.company.com",
      "port": 1433,
      "database": "SalesDB"
    }
  }
}

Example: Integrated Authentication

{
  "mssql_integrated": {
    "ds_type": "mssql",
    "auth_mode": "integrated",
    "info": {
      "server": "mssql-prod.company.com",
      "port": 1433,
      "database": "SalesDB"
    }
  }
}

Example: Connection String Mode

{
  "mssql_cs": {
    "ds_type": "mssql",
    "auth_mode": "connection_string",
    "info": {
      "connection_string": "DRIVER={ODBC Driver 18 for SQL Server};SERVER=mssql-prod.company.com,1433;DATABASE=SalesDB;UID=$env{LX_MSSQL_USER};PWD=$env{LX_MSSQL_PASSWORD};Encrypt=yes;TrustServerCertificate=yes;"
    }
  }
}

LakeXpress uses this ODBC string for its own connection and passes it to FastBCP without DRIVER, which FastBCP's SqlClient driver does not accept. A DSN= string cannot be passed to FastBCP: set fastbcp_connection_string to a SqlClient connection string (Server=...;Database=...;User Id=...;Password=...).

ADBC Export

By default, FastBCP reads the source through the arpe.io ADBC driver (ArrowTDS, connection type adbc_mssql). Set "use_adbc": false to use FastBCP's native driver instead, for example to work around a driver issue. The setting only changes the export: LakeXpress still discovers schemas and tables with its own driver, and all auth_mode values work as before. The run log shows the path once per run, e.g. Source extraction: adbc_mssql (ADBC).

{
  "sql_server_native": {
    "ds_type": "mssql",
    "auth_mode": "classic",
    "use_adbc": false,
    "info": {
      "username": "$env{LX_MSSQL_USER}",
      "password": "$env{LX_MSSQL_PASSWORD}",
      "server": "mssql-prod.company.com",
      "port": 1433,
      "database": "SalesDB"
    }
  }
}

The Parquet files written through ADBC differ from the native ones:

• datetime2 keeps microseconds (the native driver writes milliseconds).
• datetimeoffset is a UTC timestamp (the native driver writes text such as 1/1/1970 12:00:00 AM +00:00).

The ADBC driver needs its own license. It is included in the license file that arpe.io sends for LakeXpress, and LakeXpress passes it to FastBCP. If your license file has no ADBC block, provide the driver license with ARPEIO_ADBC_LICENCE (license text), ARPEIO_ADBC_LICENCE_FILE (path), or arpeio.adbc.license_file=<path> in info.fastbcp_connection_string (the connection string LakeXpress builds from connection_string does not carry it). Without any of these, the sync stops before the export with exit code 13 (LICENSE_ERROR). See License Information.

ADBC drivers ship with FastBCP for linux-x64 and win-x64. See ADBC Extraction for the other sources.

Troubleshooting

Login failed for user: Confirm SQL Server authentication is enabled (not Windows-only), verify user exists with correct permissions, check password and IP restrictions.

Cannot open database requested by the login: Verify database name, check user access, confirm database is online: SELECT state_desc FROM sys.databases WHERE name='YourDB'

See Also

• Database Connections OverviewCredentials file format, security best practices