Setting up a SQL Server account for data upload using an extractor

Introduction

In order to set up a destination connection to the SQL Server database for uploading data to Process Mining on-premises (Automation Suite), a database user is needed with the correct access permissions for a process app.

Authentication method

When using SQL Server as the destination connection in your extraction tool, the following authentication methods are available.

Authentication method	Description
SQL Server Authentication	A SQL Server user in the target database.
Windows Authentication	A windows user that has access to the SQL database.

Refer to the official Microsoft documentation for more information on authentication methods.

Both SQL Server Authentication and Windows Authentication are supported authentication methods for loading data for Process Mining process apps using an extractor. However, using Windows Authentication will require additional actions. See Windows Authentication below..

Database user

The database user needs to have access permissions to write to the app schema of the corresponding Process Mining process app in the SQL Server database. When a new Process Mining process app is created, the app schema is provided in the Upload data using extractor step.

Access can be set up in the following ways:

• One user with access to all process app schemas
• One or more users with access to specific process app schemas

One user with access to all process app schemas

A database user is set up for loading data into the Process Mining SQL Server database. This database user is granted access permissions to access all Process Mining app schemas, and must have permissions to add or delete tables in these app schemas. This way, this user can be used when loading data for all Process Mining process apps. When creating a new process app in Process Mining, no additional access permissions have to be set for this database user.

Below is an overview of the required permissions for the database user on the Process Mining AutomationSuite_ProcessMining_Warehouse database.

Permission	To grant
db_datareader	read access on all tables in the database.
db_datawriter	write access on all tables in the database.
db_ddladmin	update access on all tables in the database.

Note: As an alternative, you can choose to add the user to all the app schemas individually if you want to limit the user permissions.

Setting up the SQL user to upload the data

Each process app that makes use of the option to upload data via the extractor, needs a dedicated SQL Server user with specific access to the schema associated with the process app.

Follow these steps.

Step	Action	Syntax
1	Create a login. Note: The login needs to be created in the master database.	CREATE LOGIN <SQL_login_name> WITH PASSWORD = '<Password>'
2	Create a database user for the login. Note: This user needs to be created in the AutomationSuite_ProcessMining_Warehousedatabase.	CREATE USER <User_name> FOR LOGIN <SQL_login_name>
3	Assign the user to the correct database role.	SP_ADDROLEMEMBER <Role>, <User_name>
4	Set the default schema for the created user. Attention: This step is only required for loading data using Theobald Xtract Universal.	ALTER USER <User_name> WITH DEFAULT_SCHEMA = <Schema>

One or more users with access to specific process app schemas

One or more database users are set up for loading data into the Process Mining SQL Server database. Each database user is granted access to write into the specific app schema of a one or more Process Mining process apps. When a new Process Mining process app is created, automatically a database Role is generated in SQL Server for accessing the corresponding app schema. The database user who is allowed to upload data for the new process app, must get assigned the app-specific role, to write in the correct app schema for the process app.

Windows authentication for CData Sync

When using Windows Authentication, CData Sync uses the Windows user with which the CData Sync instance is started as the database user. This implies that a CData Sync instance can make use of only one Windows user account. If multiple Windows users are required, each Windows user needs to have their own different CData installation, in order to run CData Sync with a different Windows user.