Connection
connection_information, SQL Server credentials, ODBC driver selection and the state database path for the DLH.io SQL Server Agent.
Top-level keys
| Key | Default | Description |
|---|---|---|
agent_version | (template version) | The template version your file was written against. --config-check uses it to tell you which keys were added since. Update it when you adopt a new template. |
state_db_path | dlh_agent_state.duckdb | Location of the DuckDB state database that stores sync pointers, schema metadata and run history. Relative paths resolve from the working directory; use an absolute path on hosts that run the agent from several tasks. See State store. |
connection_information
connection_information:
connection_type: sql_server
server_name_or_ip: 'sql01.corp.local'
server_port: 1433
database_names: [SalesDb, HrDb]
# odbc_driver: 'ODBC Driver 18 for SQL Server'| Key | Default | Description |
|---|---|---|
connection_type | sql_server | Source type. sql_server is the supported value for this agent. |
server_name_or_ip | required | Hostname, FQDN or IP address of the SQL Server instance. For a named instance use the host and set server_port to the instance's TCP port (the agent always connects over TCP with tcp:<host>,<port>, so SQL Browser and named pipes are not needed). |
server_port | 1433 | TCP port, 1 to 65535. Replaces the deprecated port key, which is still accepted. |
database_names | required | Every database the agent should connect to. Each database also needs an entry under data_retrieval.databases. |
odbc_driver | auto-detect | Force a specific ODBC driver name. When omitted the agent prefers ODBC Driver 18 for SQL Server and falls back to Driver 17. |
The connection uses TLS with TrustServerCertificate=yes, so self-signed certificates on the SQL Server instance are accepted without additional configuration.
Hosted SQL Server
Any SQL Server reachable over TCP from the agent host works, including instances on other VMs, in other data centers over VPN, or hosted offerings that expose a SQL Server TCP endpoint. Point server_name_or_ip and server_port at the endpoint and make sure the login can see the Change Tracking or CDC system objects of each database when you use ct or cdc sync modes.
SQL Server credentials
Credentials are kept in a separate file, dlh_sql_server_credentials.yaml, so that the main configuration file can be shared or checked into a private repository without secrets.
credentials:
encryption: no
credentials_file: dlh_sql_server_credentials.sec
username: dlh_agent_reader
password: '<PASSWORD>'| Key | Description |
|---|---|
encryption | no the first time you enter the password. The agent sets it to yes after encrypting. |
credentials_file | Where the encrypted password is written. Defaults to ./dlh_sql_server_credentials.sec when empty. |
username | SQL Server login. SQL authentication is used; see the permissions below. |
password | Plaintext on the first run, <encrypted> afterwards. |
On the first run the password is encrypted into credentials_file, the YAML is rewritten with <encrypted> and encryption: yes, and the encryption key is stored in dlh_agent_secret.key unless DLH_AGENT_SECRET_KEY is set. If the .sec file is later missing or cannot be decrypted, an interactive run offers to re-enter the credentials; a scheduled run fails with a clear message and a notification. See Security.
Recommended login permissions
USE [SalesDb];
CREATE USER [dlh_agent_reader] FOR LOGIN [dlh_agent_reader];
ALTER ROLE db_datareader ADD MEMBER [dlh_agent_reader];
GRANT VIEW CHANGE TRACKING ON SCHEMA::dbo TO [dlh_agent_reader];
GRANT VIEW DEFINITION TO [dlh_agent_reader];For CDC add the login to the capture instance's gating role if you set one when enabling CDC on the table (@role_name), or leave @role_name = NULL to allow any db_datareader. The agent never writes to the source database.
ODBC driver
The agent talks to SQL Server through Microsoft's ODBC Driver for SQL Server. The installer does not bundle it, so install it once per host:
.\dlh_agent_sql_server.exe --install-driver # prints download instructions
.\dlh_agent_sql_server.exe --install-driver --auto # downloads and installs Driver 18 silentlyVerify with --diagnose, whose SYSTEM section lists the installed drivers and which one the agent will use. Driver 18 and Driver 17 are both supported; older drivers (13, 11, "SQL Server") are not.
Related settings
- Per-database
sync_modeandschema_refresh_mode, plus the tables and views to extract, are described in Data retrieval. - Enabling Change Tracking or CDC on the source is covered in Getting started.