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
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

With auth_mode: "connection_string", only the connection_string field is used; username, password, server, port, database, encrypt, and trust_server_certificate are ignored.

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". "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;"
    }
  }
}

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 Overview - Credentials file format, security best practices