Before you create a SQL Server workspace

Enabling SQL Auth login

Out of the box, Tonic Structural supports SQL Auth login.

You can also configure Structural to support Kerberos and NTLM. For information on how to connect Kerberos to SQL Server, go to the Microsoft documentation.

To use SQL Auth for Structural database connections, your SQL Server Admin must enable SQL Auth login.

Creating the source database user

The following is an example of how to create a new user, called tonic, and grant the necessary permissions.

For the source database, we recommend that you use a backup of your production database instead of a direct connection to your production environment.

The following statements create a new database user called tonic that has the SELECT, VIEW DATABASE STATE, and VIEW ANY DEFINITION grants.

USE [<Database being masked>];
GO
CREATE LOGIN tonic
    WITH PASSWORD = '<Password Goes Here>';  
GO
CREATE USER tonic FOR LOGIN tonic;  
GO
GRANT SELECT TO tonic;
GO
GRANT VIEW DATABASE STATE TO tonic;
USE [master]
GO
GRANT VIEW ANY DEFINITION TO tonic;
--On Azure SQL Server
GRANT VIEW DEFINITION TO tonic;
GO

Creating the destination database

Creating the database

Structural does not create the destination database during data generation. Instead, you must set up the destination database before you generate data.

The destination database must resemble the source database as much as possible. We strongly recommend that you run the CREATE DATABASE and ALTER DATABASE statements exactly as they were written for the source database. For example, if your source database contains filegroups, then for Structural to run successfully, you must also create filegroups on the destination database.

Creation of the destination database is a one-time step. After the destination database is created, Structural can successfully update it, including schema-level objects, between generation runs.

Creating a database user with the required permissions

The destination database requires a higher level of permissions than the source database.

For ease of use, we recommend that you create a user with the db_owner role.

USE [<Database being masked>];
GO
CREATE LOGIN tonic
    WITH PASSWORD = '<Password Goes Here>';  
GO
CREATE USER tonic FOR LOGIN tonic;  
GO
EXEC sp_addrolemember 'db_owner', 'tonic'; 
GO    

Using a Simple recovery model

To maximize performance, we suggest that you run your destination database with a Simple recovery model. This reduces transactional logging, and improves the performance of writes to the destination database during generation.

To set this in SQL Server Management Studio:

1. Right-click the destination database.

2. From the context menu, select Properties.

3. Select the Options sub-menu.

4. From the Recovery Model drop-down, select Simple.

Configuring whether Structural creates the destination database schema

By default, during each data generation job, Structural creates the database schema for the destination database tables, then populates the database tables based on the workspace configuration.

If you prefer to manage the destination database schema yourself, set the environment setting TONIC_SQL_SERVER_SKIP_CREATE_DB to true. You can configure TONIC_SQL_SERVER_SKIP_CREATE_DB from the Environment Settings tab on Structural Settings.

When TONIC_SQL_SERVER_SKIP_CREATE_DB is true, then Structural does not create the destination database schema. Before you run data generation, you must create the destination database with the full schema.

Note that you can override this setting in individual workspaces. For more information, go to Advanced workspace overrides.

Note also that if you create the destination schema, then Structural ignores the workspace configuration to enable or disable cross-database references.

During data generation, Structural deletes the data from the destination database tables, except in the following cases:

• Tables that use Preserve Destination or Incremental mode.

• Upsert data generation.

It then populates the tables with the new destination data.

For a diagram of the data generation process when you manage the destination schema, go to Data process - User-managed destination schema.

Integrating with CLR

If you use common language runtime (CLR) assembly objects, then to allow Structural to work with those objects, you must enable CLR on the destination database server.

To do this, a user with server-level permission runs the following query. Do not use the Structural user, which should not have these permissions.

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
GO

Managing access to linked servers

If you use linked servers, and your database does not require access to those linked servers, then to reduce the Structural query workload, you can deny permission to those linked servers.

You set up the restrictions separately for the source and destination databases.

For linked servers that you do use in the source or destination data, you must grant the database users access to those servers.

You must also enable cross-database references in the workspace configuration.

Blocking access to all linked servers

To block access for all linked servers for the source or destination database, run the following SQL script:

DECLARE @UserName NVARCHAR(50) = '<your user name>';
USE [<source or destination database name>];

-- Add the user to the 'db_owner' role
EXEC sp_addrolemember 'db_owner', @UserName;

-- Use `sp_denylogin` to deny access to all linked servers
DECLARE @SQL NVARCHAR(MAX) = '';

SELECT @SQL = @SQL + 'EXEC sp_denylogin ''' + name + '\' + @UserName + '''; '
FROM sys.servers
WHERE is_linked = 1;

EXEC sp_executesql @SQL;

Note that if you block access to all linked servers, then the workspace setting to enable cross-database references does not grant access to them.

Blocking access to specific linked servers

To block access for specific linked servers for the source or destination database, for each linked server to block, run the following SQL script:

DECLARE @UserName NVARCHAR(50) = '<your user name>';
USE [<source or destination database name];

EXEC sp_denylogin '<linked server name>\' + @UserName;

Note that even if you enable cross-database references in the workspace, only the non-blocked linked servers are available.

Required permissions for connected linked servers

For each linked server that is connected to a source or destination database, you must grant the corresponding database user the following permissions:

• GRANT SELECT

• GRANT VIEW DATABASE STATE

• GRANT VIEW ANY DEFINITION

You must also enable cross-database references in the workspace.