Skip to main content

Microsoft SQL Server

info

Datafold utilizes ODBC Driver 18 for SQL Server (see compatibility chart here).

Please contact support@datafold.com regarding legacy versions of SQL Server.

info

Column-level Lineage is not currently supported for Microsoft SQL Server.

Steps to complete:

  1. Run SQL Script for Permissions
  2. Configure your data source in Datafold

Run SQL Script

To connect to Microsoft SQL Server, create a user with read-only access to all tables you wish to diff. Include read and write access to a Datafold-specific temp schema:

/* Select the database that will contain the temp schema */
USE DatabaseName;

/* Datafold utilizes a temporary dataset to materialize scratch work and keep data processing in the your warehouse. */
CREATE SCHEMA datafold_tmp;

/* Create the Datafold user */
CREATE LOGIN DatafoldUser WITH PASSWORD = 'SOMESECUREPASSWORD';
CREATE USER DatafoldUser FOR LOGIN DatafoldUser;

/* Grant read access to diff tables */
GRANT SELECT ON SCHEMA::YourSchema TO DatafoldUser;

/* Grant read + write access to datafold_tmp schema */
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::datafold_tmp TO DatafoldUser;

Configure in Datafold

Field NameDescription
Connection nameA name given to the data source within Datafold
HostThe hostname for your SQL Server instance
PortSQL Server connection port; default value is 1433
UsernameThe user created in our SQL script - DatafoldUser
PasswordThe password created in our SQL script
DatabaseThe name of the SQL Server database you want to connect to
Dataset for temporary tablesThe schema created in our SQL script, in database.schema format - DatabaseName.datafold_tmp

Click Create. Your data source is ready!