Skip to content
For the complete documentation index, see llms.txt. This page is also available as Markdown.

SQL Server

Access configuration details to connect Soda to a Microsoft SQL Server data source.

Requirements

Connection configuration reference

Install the following package:

pip install soda-sqlserver

Data source YAML

Create the config file:

soda data-source create -f ds_config.yml

The data source configuration YAML should look like the following:

ds_config.yml
type: sqlserver
name: my_sqlserver
connection:
  host: ${env.SQLSERVER_HOST}
  port: 1433
  database: ${env.SQLSERVER_DB}
  user: ${env.SQLSERVER_USER}                            # SEE NOTE
  password: ${env.SQLSERVER_PW}                          # SEE NOTE
  authentication: sql  # activedirectoryserviceprincipal | activedirectoryinteractive | activedirectorypassword 
  # optional
  client_id: ${env.SQLSERVER_SERVICE_CLIENT_ID}          # SEE NOTE
  client_secret: ${env.SQLSERVER_SERVICE_CLIENT_SECRET}  # SEE NOTE
  driver: ODBC Driver 18 for SQL Server
  trusted_connection: false
  encrypt: false
  trust_server_certificate: false
  connection_parameters:
    application_intent: 'ReadOnly'

Note: depending on the authentication method that is used, user and password may not be required (e.g. activedirectoryserviceprincipal requires client_id and client_secret).

Connection test

Test the data source connection:


Required permissions

Create a dedicated login and user for Soda:

db_datareader role: This built-in role grants SELECT on all tables and views in the database, which is the simplest way to grant Soda read access. Combine it with VIEW DEFINITION on schemas for full schema introspection.

Row-level security


Regex patterns in SQL Server

SQL Server and related databases do not offer universal regex support. When using regex-based checks in these environments, Soda adapts pattern-matching queries.

Instead of regex functions, Soda translates patterns into SQL queries using the PATINDEX function.

How Soda translates regex patterns

If the user specifies a regex pattern which matches a column expression, it will be expanded in SQL as:

Key considerations

  • Collation

A collation (a rule set used for determining string matches) must be specified.

Soda uses the SQL_Latin1_General_Cp1_CS_AS, which is case-sensitive and accent-sensitive, so a and A are not equal, and accented characters (such as é) are treated distinctly.

  • Pattern handling

Regex patterns are wrapped in %…%, so any substring match within the column value will return true.

Example: ^abc becomes %abc%, which will match anywhere in the string.

  • Character ranges In SQL Server and related databases, character range expansion for alpha characters is handled differently than in most regex engines.

    • Soda auto-expands the most common ranges for you:

      • [a-z] → [abcdefghijklmnopqrstuvwxyz]

      • [A-Z] → [ABCDEFGHIJKLMNOPQRSTUVWXYZ]

    • Other ranges are not expanded automatically and you may need to manually expand the full set when defining your pattern.

      • If you need [0-9], [α-ω], or similar, you may have to manually expand them.

      • Example: instead of [0-9], write [0123456789].

  • connection_parameters

You can define key-value pairs that will be passed directly to the connection string. For example:

application_intent: 'ReadOnly' → application_intent=ReadOnly in the final connection string.

Troubleshoot

Problem: Metrics Monitoring Scan fails with error:

Solution: Ensure the user login name used to connect to Soda (e.g. soda_user ) has VIEW permissions on sys.dm_db_index_usage_stats (which is one of the built-in SQL Server Dynamic Management Views DMVs, prefixed with sys.dm_*).

For example, run this SQL query (e.g. as sysadmin) to GRANT the necessary permissions to the user:


You are not logged in to Soda and are viewing the default public documentation. Learn more about Licensing & documentation access.

Last updated

Was this helpful?