SQL Server as LakeXpress DB

1. Create the database

Run as a SQL Server administrator (for example with sqlcmd or SQL Server Management Studio):

CREATE DATABASE lakexpress_log;
GO
CREATE LOGIN lakexpress_admin WITH PASSWORD = 'Your_password1';
GO
USE lakexpress_log;
GO
CREATE USER lakexpress_admin FOR LOGIN lakexpress_admin;
ALTER ROLE db_owner ADD MEMBER lakexpress_admin;
GO

LakeXpress creates its tables and sequences in the dbo schema, which db_owner allows. The password must meet the server's password policy.

2. Add it to the credentials file

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

If the server uses a self-signed certificate (common on test servers and localhost), add "trust_server_certificate": true to info.

The connection parameters are the same as for a SQL Server source (Windows authentication, connection string): see SQL Server connection parameters.

3. Create the LakeXpress tables

./LakeXpress lxdb init -a credentials.json --lxdb_auth_id lxdb_mssql

Troubleshooting

User does not have permission to perform this action. (15247): The user can connect but is not a member of db_owner in lakexpress_log, so it cannot create the LakeXpress tables. Run the USE lakexpress_log and ALTER ROLE lines again.

Login failed or cannot open the database: See the SQL Server troubleshooting section.