SQL Server

SQL Server can be used as a source database (the database LakeXpress extracts data from) and as the LakeXpress DB (the internal database LakeXpress uses for configuration, sync tracking, and pipeline logging). Both roles use the same connection parameter format.

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	Must be "classic"
username	Yes	SQL Server username
password	Yes	SQL Server password
server	Yes	Hostname or IP
port	Yes	Port (default: 1433)
database	Yes	Database name

Required Permissions

Permissions differ depending on how you are using SQL Server:

-- When used as a source database (read-only access):
GRANT SELECT ON SCHEMA::dbo TO lakexpress_user;
GRANT VIEW DEFINITION ON SCHEMA::dbo TO lakexpress_user;

-- INFORMATION_SCHEMA access is granted by default

-- When used as the LakeXpress DB (LakeXpress manages its own tables here):
GRANT CONTROL ON DATABASE::lakexpress_log TO lakexpress_user;

Example: Source Database

{
  "mssql_prod": {
    "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: LakeXpress DB

{
  "lxdb_mssql": {
    "ds_type": "mssql",
    "auth_mode": "classic",
    "info": {
      "username": "$env{LX_LXDB_USER}",
      "password": "$env{LX_LXDB_PASSWORD}",
      "server": "mssql-prod.company.com",
      "port": 1433,
      "database": "lakexpress_log"
    }
  }
}

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