SQL Server

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

Requirements

• Install the Microsoft ODBC driver for SQL Server before proceeding with the connection configuration.

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:

soda data-source test -ds ds_config.yml

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:

PATINDEX('%{pattern}%', {column expression} COLLATE SQL_Latin1_General_Cp1_CS_AS) > 0

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.

---

{% if (visitor.claims.plan === 'datasetStandard') %}

You are logged in to Soda and seeing the Dataset Standard license documentation. Learn more about Documentation access & licensing.

{% if (visitor.claims.plan === 'enterprise') %}

You are logged in to Soda and seeing the Team license documentation. Learn more about Documentation access & licensing.

{% if (visitor.claims.plan === 'enterpriseUserBased') %}

You are logged in to Soda and seeing the Enterprise license documentation. Learn more about Documentation access & licensing.

{% if !(visitor.claims.plan === 'enterprise' || visitor.claims.plan === 'enterpriseUserBased' || visitor.claims.plan === 'datasetStandard') %}

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

If you do have a Soda license, make sure to log in to Soda Cloud in this same browser.