Soda Core with SQL Server: connection, Windows auth, SSL errors and checks

Soda is an open-source tool for running data quality checks against a database. This guide covers getting Soda Core talking to Microsoft SQL Server on a local machine, the errors you hit along the way, and a first real check. It is a local setup for learning the tool, not a production configuration: one of the options below grants a user admin rights.

Prerequisites

• Server: SQL Server Developer Edition (download)
• Client: SQL Server Management Studio (download)
• Database: AdventureWorks Lightweight (install guide)
• Soda Core for SQL Server (installation docs):

pip install soda-core-sqlserver

Soda’s earlier package, soda-sql, is still on PyPI; everything below uses soda-core-sqlserver.

The configuration file

Start from the template in Connect Soda to MS SQL Server:

data_source my_datasource_name:
  type: sqlserver
  host: host
  port: '1433'
  username: xxx
  password: ...
  database: 
  schema: dbo
  trusted_connection: false
  encrypt: false 
  trust_server_certificate: false
  driver: 

Save it as configuration.yml and test the connection:

soda test-connection -d adventureworks -c configuration.yml -V

• -c configuration.yml is the file name; every property must be in this file.
• -d adventureworks is the data source name. It must match the name after data_source in the YAML.

Set the ODBC driver

With driver: left empty, the connection test fails with:

soda.common.exceptions.DataSourceConnectionError: Encountered a problem while trying to connect to sqlserver: can only concatenate str (not "NoneType") to str 

followed by:

AttributeError: 'NoneType' object has no attribute 'has_valid_connection'

Find the installed driver name as described in Microsoft’s How to check the ODBC SQL Server driver version:

1. In Administrative Tools, double-click Data Sources (ODBC).
2. Open the Drivers tab.
3. The Microsoft SQL Server entry and its version are listed there.

Put that name in the YAML:

  driver: 'ODBC Driver 17 for SQL Server'

Fill in host, database and schema

For a local AdventureWorks install:

• host: localhost
• port: '1433' (the default)
• database: AdventureWorksLT2019
• schema: SalesLT

data_source adventureworks:
  type: sqlserver
  host: localhost
  port: '1433'
  username: 
  password: 
  database: AdventureWorksLT2019
  schema: SalesLT
  trusted_connection: false
  encrypt: false 
  trust_server_certificate: false
  driver: 'ODBC Driver 17 for SQL Server'

Option 1: SQL Server authentication

If you normally log in with Windows Authentication, Soda needs either a SQL login or a trusted connection (option 2). For a SQL login, create a login and grant it privileges on the database; without them the database is not reachable. Then fill in username and password. A passing test looks like this:

[15:43:45] Soda Core 3.0.22
[15:43:45] Reading configuration file "configuration.yml"
Successfully connected to 'adventureworks'.
[15:43:45] Query adventureworks.test-connection:
SELECT 1
Connection 'adventureworks' is valid.

Option 2: Windows authentication, no password

The Soda docs describe trusted_connection as:

The default value is false. Set to true if using Active Directory authentication.

With trusted_connection: true, Soda connects as your Windows user and you don’t need a separate login. username and password are ignored, but the keys stay in the file:

data_source adventureworks:
  type: sqlserver
  host: localhost
  port: '1433'
  username: notmatterwhatyouwrite
  password: notmatterwhatyouwrite
  database: AdventureWorksLT2019
  schema: SalesLT
  trusted_connection: true
  trust_server_certificate: false
  driver: 'ODBC Driver 17 for SQL Server'

Run the same test; it reports a valid connection:

soda test-connection -d adventureworks -c configuration.yml -V

SSL: CERTIFICATE_VERIFY_FAILED when running Soda

Soda can fail with an SSL error that has nothing to do with SQL Server:

requests.exceptions.SSLError: HTTPSConnectionPool(host='collect.soda.io', port=443): Max retries exceeded with url: /v1/traces (Caused by SSLError(SSLCertVerificationError(1, '[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: self signed certificate in certificate chain (_ssl.c:1129)')))

The host is collect.soda.io and the path is /v1/traces: this is Soda sending its anonymous usage statistics, which is on by default (Soda usage statistics). The machine’s network setup won’t let that outbound HTTPS call verify the certificate chain (“self signed certificate in certificate chain”).

The fix is to opt out of usage statistics. Add this to ~/.soda/config.yml or .soda/config.yml:

send_anonymous_usage_stats: false

Your checks never needed that call, so they run normally afterwards.

A first check: count distinct values

Data quality checks usually target six dimensions: completeness, accuracy, consistency, validity, uniqueness and integrity. A simple completeness check is to confirm that a category column contains every expected value.

The example uses MySQL’s world sample database, not AdventureWorks. Its country.continent column should contain exactly 7 values: North America, Asia, Africa, Europe, South America, Oceania, Antarctica. In plain SQL:

SELECT COUNT(DISTINCT continent) AS cont FROM country; 

and the check passes when the result is 7.

SodaCL has no built-in count-distinct metric in Metrics and Checks. Two ways to express it in checks.yml are a failed rows check and a user-defined check:

checks for country:
  # FAILED ROW CHECKS -> https://docs.soda.io/soda-cl/failed-rows-checks.html
  - failed rows:
      fail query: |
          WITH continent AS (
            SELECT COUNT(DISTINCT continent) AS quantity FROM country
          )
 
          SELECT * FROM continent WHERE quantity != 7
  # USER DEFINED CHECKS -> https://docs.soda.io/soda-cl/user-defined.html
  - continents = 7:
      continents query: |
        SELECT COUNT(DISTINCT continent) AS quantity FROM country

The failed rows check fails if the query returns any row. The user-defined check computes a metric named continents and compares it to 7. The file is also on GitHub.

Gotchas

• Empty driver produces the can only concatenate str (not "NoneType") to str error, not a “driver not found” message.
• -d must match the YAML. The name passed to soda test-connection -d is the data_source name, not the SQL Server database name.
• schema is a schema, not a table. Use SalesLT, not SalesLT.Address.
• SSL errors from collect.soda.io come from usage telemetry. Set send_anonymous_usage_stats: false instead of disabling certificate checks.

Related

• AWS Glue and Chargebee: diagnosing CERTIFICATE_VERIFY_FAILED after TLS chain updates is the same Python error in a different pipeline.
• Inserting data from a CSV file into SQL Server using PowerShell with Windows Authentication uses Windows auth from a different client.
• Building a data quality dashboard on top of Tuva’s DQI mart covers data quality reporting once checks exist.