Microsoft SQL Server - Datafold

Steps to complete:

Run SQL script and create schema for Datafold

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 your warehouse. */
CREATE SCHEMA datafold_tmp;

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

/* Allow the user to create views */
GRANT CREATE VIEW TO DatafoldUser;

/* Allow the user to create tables */
GRANT CREATE TABLE TO DatafoldUser;

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

/* Grant read + write access to datafold_tmp schema */
GRANT CONTROL ON SCHEMA::datafold_tmp TO DatafoldUser;

Configure in Datafold

Field Name	Description
Connection name	A name given to the data connection within Datafold
Host	The hostname for your SQL Server instance
Port	SQL Server connection port; default value is 1433
Username	The user created in our SQL script, named DatafoldUser
Password	The password created in our SQL script
Database	The name of the SQL Server database you want to connect to
Schema path for temporary tables	The schema created in our SQL script, in database.schema format: DatabaseName.datafold_tmp in our script above.
Require encryption	Encrypt the connection. On by default.
Trust server certificate	Accept the server’s certificate without validation. On by default.
Always On Availability Group	Enable only when connecting to an Always On Availability Group listener. Requires encryption.

Click Save. Your data connection is ready!

⌘I