After deciding to install SODA to make your quality check you have to connect the data source to SODA. We are going to see how to connect soda to Microsoft SQL Server. Remember that this is a tutorial in order to use, in an easy way the SODA quality check tool on your machine . You haven’t use it in production. Mainly because we are going to grant an user with admin privilege.

Prerequisites

• Having installed Microsoft SQL Server. I’m using the following set up
  • Server: SQL Server Developer Edition: Link -> Microsoft Documentation
  • Client: SQL Server Management Studio: Link -> Microsoft Documentation
  • Database: Adventure works Lightweight: Link -> Microsoft Documentation
• Having Installed soda-core:
  • SODA core for SQL server: Link -> SODA documentation
    • pip install soda-core-sqlserver

I’m currently logging to the database using: Windows Authentication. Besides that, I’m using as template in order to begin the following YAML file:

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: 

You can find it in the SODA documentation: Connect Soda to MS SQL Server

We are going to test the connection with the following command:

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

1 – Know what your Driver is

The first thing we have to know is what driver do we have. With this information we can fill the last requirement of the YAML. If you test the connection with that information you’ll receive the following error:

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

And

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

Following the Microsoft Documentation(How to check the ODBC SQL Server driver version) we can notice what to do:

To check the ODBC SQL Server driver version (32-bit ODBC):

1.1 – In Administative Tools, double-click Data Sources (ODBC).

1.2 – Click the Drivers tab. and 1.3 – Information for the Microsoft SQL Server entry is displayed in the Version column.

With that information now we can add the driver to the YAML. Now we have the driver as we can see:

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: 'ODBC Driver 17 for SQL Server'

2 – Fill the other values except username and password

As we are going to run the following test:

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

We notice some things:

• configuration.yml is the name of the file. All the properties have to be in a file with this name
• adventureworks is the name of the database given by me. So I’ve to change inside the YAML.
• Your host could be localhost
• Port could remain the same: ‘1433’
• The database I’m using is AdventureWorksLT2019
• The schema I’m using is SalesLT.Address

With that information we have the YAML file as we can see below:

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

3 – Create the user with an SQL server Authentication

At the end, if you want to connect create a quality check you have to create a new login. Create a Login on Microsoft Documentation and grant with privileges to see the database. If not the database will not be reacheable.

4 – Test your connection

Once you finish that you can fill the username and password and receive the test passed:

[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.