> For the complete documentation index, see [llms.txt](https://docs.soda.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.soda.io/soda-documentation/reference/data-source-reference-for-soda-core/synapse.md). # Synapse Access configuration details to connect Soda to an Azure Synapse Analytics data source. #### Requirements Install the [Microsoft ODBC driver for SQL Server](https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server) before you configure the connection. Soda uses ODBC Driver 18 for SQL Server unless you set a different `driver`. ## Connection configuration reference Install the following package: ```bash pip install soda-synapse ``` ### **Data source YAML** Create a file named `ds_config.yml`. The following example uses SQL authentication: {% code title="ds\_config.yml" %} ```yaml type: synapse name: my_synapse connection: host: .sql.azuresynapse.net port: 1433 database: ${env.SYNAPSE_DB} authentication: sql user: ${env.SYNAPSE_USER} password: ${env.SYNAPSE_PW} ``` {% endcode %} Set `host` to the endpoint of the pool you want to scan. For a dedicated SQL pool in a Synapse workspace, use `.sql.azuresynapse.net`. For a serverless SQL pool, use `-ondemand.sql.azuresynapse.net`. For a standalone dedicated SQL pool (formerly SQL DW), use `.database.windows.net`. Set `database` to the dedicated pool name or to the serverless database name. ### **Authentication** The `authentication` value determines which credential fields are required. | `authentication` | Required fields | Notes | | --------------------------------- | ---------------------------- | ------------------------------------------------------------------------------ | | `sql` | `user`, `password` | SQL login. | | `activedirectoryserviceprincipal` | `client_id`, `client_secret` | Microsoft Entra service principal. Use this for scheduled scans. | | `activedirectorypassword` | `user`, `password` | Microsoft Entra user. Microsoft has deprecated this method in its SQL drivers. | | `activedirectoryinteractive` | `user` | Opens a sign-in prompt. Works with the Windows ODBC driver only. | Always set `authentication`. If you leave it out, Soda infers the method from the fields you provide: `user` with `password` resolves to `sql`, and `user` alone resolves to `activedirectoryinteractive`. Other Entra methods, such as managed identity, aren't supported. The following example uses a service principal: {% code title="ds\_config.yml" %} ```yaml type: synapse name: my_synapse connection: host: .sql.azuresynapse.net port: 1433 database: ${env.SYNAPSE_DB} authentication: activedirectoryserviceprincipal client_id: ${env.SYNAPSE_SERVICE_CLIENT_ID} client_secret: ${env.SYNAPSE_SERVICE_CLIENT_SECRET} ``` {% endcode %} {% hint style="info" %} **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`). {% endhint %} #### **Optional fields** | Field | Default | Description | | -------------------------- | ------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `driver` | `ODBC Driver 18 for SQL Server` | Name of the installed ODBC driver. | | `encrypt` | `false` | When `true`, Soda adds `Encrypt=YES` to the connection string. When `false`, Soda adds nothing and the driver's default applies. ODBC Driver 18 encrypts connections by default. | | `trust_server_certificate` | `false` | When `true`, Soda adds `TrustServerCertificate=YES`. The connection stays encrypted, but the driver skips server certificate validation. | | `trusted_connection` | `false` | When `true`, Soda adds `Trusted_Connection=YES`, which uses Windows integrated authentication (Kerberos on Linux and macOS). Not typically used with Synapse. | | `connection_max_retries` | `0` | Sets the driver's `ConnectRetryCount`, the number of attempts to restore a broken idle connection. Soda always sends this value, so the default of `0` turns off reconnection. | | `login_timeout` | `0` | Login timeout in seconds. `0` leaves the driver's default in place. | | `connection_parameters` | None | Map of extra ODBC connection string keywords, appended as written. | *** #### **Connection test** Test the data source connection by passing the path to your configuration file: ```bash soda data-source test -ds ds_config.yml ``` *** ## Required permissions Soda needs read access to the data it scans and access to schema metadata. The statements differ between dedicated and serverless SQL pools. Run them as an administrator. ### **Dedicated SQL pool** Create a login in the `master` database: ```sql CREATE LOGIN soda_login WITH PASSWORD = ''; ``` Connect directly to the dedicated SQL pool database, then create the user and grant access. Dedicated SQL pools don't support the `USE` statement, so you must open a new connection to the pool database. ```sql CREATE USER soda_user FOR LOGIN soda_login; EXEC sp_addrolemember 'db_datareader', 'soda_user'; GRANT VIEW DEFINITION ON SCHEMA :: TO soda_user; ``` ### **Serverless SQL pool** Create a login in the `master` database, then create the user in the target database: ```sql CREATE LOGIN soda_login WITH PASSWORD = ''; USE ; CREATE USER soda_user FOR LOGIN soda_login; ALTER ROLE db_datareader ADD MEMBER soda_user; GRANT VIEW DEFINITION ON SCHEMA :: TO soda_user; ``` If Soda scans external tables or views whose external data source uses a database scoped credential, grant `REFERENCES` on that credential: ```sql GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL :: TO soda_user; ``` If Soda scans views built on `OPENROWSET`, the user also needs permission to run bulk operations, whether or not the data source uses a credential: ```sql GRANT ADMINISTER DATABASE BULK OPERATIONS TO soda_user; ``` If the underlying storage is accessed with the caller's own Microsoft Entra identity rather than a credential, that identity also needs read access on the storage account. **Microsoft Entra ID authentication** If you connect with an Entra ID user or service principal, don't create a login. Instead, create the database user from Entra ID, then run the same role and grant statements for your pool type: ```sql CREATE USER [] FROM EXTERNAL PROVIDER; ``` {% hint style="warning" %} #### **Row-level security** If your data source uses row-level security policies (e.g. Row Access Policies in Snowflake, Row-Level Security in PostgreSQL or SQL Server, Row Filters in Databricks Unity Catalog, or equivalent mechanisms in other data sources), **ensure that the Soda connection** user or role **has been granted the appropriate policy exemptions or access**. Row-level security policies do not cause connection errors or scan failures, but **they silently filter the rows visible to Soda**. This can produce unexpected results. For example, `dataset_rows_tested: 0` even when the dataset clearly contains rows. {% endhint %} *** ## Limitations & edge cases ### Regex patterns in Synapse Synapse 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: ```sql PATINDEX('%{pattern}%', {column expression} COLLATE SQL_Latin1_General_Cp1_CS_AS) > 0 ``` #### **Substring matching** 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. #### **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. #### **Character ranges** In SQL Server and related databases, like Synapse, 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]`. ### Storage structure Soda uses a **Heap** table storage structure in Synapse. Unlike indexed structures, **Heap stores rows without predefined order or compression**, which is required for compatibility with certain data types used in the warehouse (VARCHAR(MAX), TEXT, and XML) without character limits. As a side effect, **tables in the diagnostics warehouse may consume more storage** than equivalent tables using columnar compression. This is expected behavior and a known trade-off of the current architecture.
*** {% hint style="info" %} You are **not logged in to Soda** and are viewing the default public documentation. Learn more about [Licensing & documentation access](/soda-documentation/reference/documentation-access-and-licensing.md). {% endhint %} --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation by asking a question. Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters: ``` GET https://docs.soda.io/soda-documentation/reference/data-source-reference-for-soda-core/synapse.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.