Tonic Structural supports SQL Server versions 2014 and higher.

Enabling SQL Auth login

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

To use Structural, 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.

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.

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

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

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;

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;

SQL Server is a relational database management system developed by Microsoft.

The SQL Server data connector can be used to connect to your SQL Server data.

You can also use the SQL Server data connector to connect to an Azure SQL database.

During workspace creation, under Connection Type, click SQL Server.

Connecting to the source database

In the Source Settings section, provide the details about the source database.

Providing the source database connection details

To provide the connection details for the source database:

1. In the Server field, provide the server where the database is located.

2. In the Database field, provide the name of the database.

3. In the Port field, provide the port to use to connect to the database.

4. In the Username field, provide the username for the account to use to connect to the database.

5. For the user password, you can either specify the password manually, or you can select a secret name from a secrets manager. The selected secret must store a password. The secrets manager option only displays if at least one secrets manager is configured. For information about configuring the available secrets managers, go to Configuring secrets managers for database connections. To enter the password manually:

   1. Click Provide Password.

   2. In the password field, enter the password.

   To use a secret name from a secrets manager:

   1. Click Use Secrets Manager.

   2. From the secrets manager dropdown list, select the secrets manager. Structural connects to the secrets manager and retrieves a list of available secret names.

   3. From the secret name dropdown list, select the secret name.

6. To test the connection to the source data, click Test Source Connection.

Ensuring encryption of source database authentication

The Enable SSL/TLS setting indicates whether to encrypt source database authentication.

By default, the toggle is in the on position. We strongly recommend that you do not turn off this setting.

Using Kerberos or NTLM authentication

To use Kerberos or NTLM authentication:

1. Toggle Integrated Security to the on position.

2. For Kerberos authentication, in the Kerberos Domain field, provide the Kerberos domain. For NTLM authentication, leave the field empty.

Trusting the server certificate

To indicate that Tonic Structural should trust the server certificate, toggle Trust Server Certificate to the on position.

Connecting through an SSH bastion

For additional security, to connect through an SSH bastion :

1. Toggle Enable SSH Tunnel to the on position.

2. In the SSH Host field, provide the host for the SSH bastion.

3. In the SSH Port field, provide the port for the SSH bastion.

4. In the SSH User field, provide the name of the user to use to connect to the SSH bastion.

5. If you do not use a private key, then in the SSH Passphrase field, provide the passphrase to use for authentication.

6. If you do use a private key, then in the SSH Private Key field, provide the private key. If the private key uses a passphrase, then in the SSH Passphrase field, provide the passphrase for the private key.

Enabling MultiSubnetFailover for the source database

To enable MultiSubnetFailover for the source database, toggle Enable MultiSubnetFailover to the on position.

Blocking data generation on all schema changes

By default, data generation is not blocked for schema changes that do not conflict with your workspace configuration.

To block data generation when there are any schema changes, regardless of whether they conflict with your workspace configuration, toggle Block data generation on schema changes to the on position.

Connecting to the intermediate database for upsert

SQL Server supports the upsert process. When you enable upsert for the workspace, the data generation process initially writes the transformed data to an intermediate database.

After the initial data generation is complete, the upsert job writes new records to the destination database, and updates existing records in the destination database. It does not touch any other records that are in the destination database.

In the Upsert section, when you enable upsert, you are prompted to configure the upsert processing and to provide the connection information for the intermediate database.

If the intermediate database is in the same location as the source database, then you can copy the connection and authentication details from the source database.

Copying the connection and authentication details from the source database

To copy the connection and authentication details from the source database:

1. Click Copy Settings from Source.

2. For the user password, you can either specify the password manually, or you can select a secret name from a secrets manager. The selected secret must store a password. The secrets manager option only displays if at least one secrets manager is configured. For information about configuring the available secrets managers, go to Configuring secrets managers for database connections. To enter the password manually:

   1. Click Provide Password.

   2. In the password field, enter the password.

   To use a secret name from a secrets manager:

   1. Click Use Secrets Manager.

   2. From the secrets manager dropdown list, select the secrets manager. Structural connects to the secrets manager and retrieves a list of available secret names.

   3. From the secret name dropdown list, select the secret name.

3. To test the connection to the intermediate database, click Test Intermediate Connection.

Providing the intermediate database connection details

To provide the connection details for the intermediate database:

1. In the Server field, provide the server where the database is located.

2. In the Database field, provide the name of the database.

3. In the Port field, provide the port to use to connect to the database.

4. In the Username field, provide the username for the account to use to connect to the database.

5. For the user password, you can either specify the password manually, or you can select a secret name from a secrets manager. The selected secret must store a password. The secrets manager option only displays if at least one secrets manager is configured. For information about configuring the available secrets managers, go to Configuring secrets managers for database connections. To enter the password manually:

   1. Click Provide Password.

   2. In the password field, enter the password.

   To use a secret name from a secrets manager:

   1. Click Use Secrets Manager.

   2. From the secrets manager dropdown list, select the secrets manager. Structural connects to the secrets manager and retrieves a list of available secret names.

   3. From the secret name dropdown list, select the secret name.

6. To test the connection to the source data, click Test Intermediate Connection.

Ensuring encryption of intermediate database authentication

The Enable SSL/TLS setting indicates whether to encrypt intermediate database authentication.

By default, the toggle is in the on position. We strongly recommend that you do not turn off this setting.

Using Kerberos or NTLM authentication

To use Kerberos or NTLM authentication:

1. Toggle Integrated Security to the on position.

2. For Kerberos authentication, in the Kerberos Domain field, provide the Kerberos domain. For NTLM authentication, leave the field empty.

Trusting the server certificate

To indicate that Structural should trust the server certificate, toggle Trust Server Certificate to the on position.

Connecting through an SSH bastion

For additional security, to connect through an SSH bastion:

1. Toggle Enable SSH Tunnel to the on position.

2. In the SSH Host field, provide the host for the SSH bastion.

3. In the SSH Port field, provide the port for the SSH bastion.

4. In the SSH User field, provide the name of the user to use to connect to the SSH bastion.

5. If you do not use a private key, then in the SSH Passphrase field, provide the passphrase to use for authentication.

6. If you do use a private key, then in the SSH Private Key field, provide the private key. If the private key uses a passphrase, then in the SSH Passphrase field, provide the passphrase for the private key.

Enabling MultiSubnetFailover for the intermediate database

To enable MultiSubnetFailover for the intermediate database, toggle Enable MultiSubnetFailover to the on position.

Connecting to the destination database

For a SQL Server workspace, you can write the destination data to either:

• A destination database server.

• A Tonic Ephemeral snapshot. For more information, go to Writing output to Tonic Ephemeral.

• A container repository, for more information, go to Writing output to a container repository.

Under Destination Settings, to write the transformed data to a destination database on a database server, click Database Server.

Copying source connection and authentication details

To copy the connection and authentication details from the source database:

1. Click Copy Settings from Source.

2. For the user password, you can either specify the password manually, or you can select a secret name from a secrets manager. The selected secret must store a password. The secrets manager option only displays if at least one secrets manager is configured. For information about configuring the available secrets managers, go to Configuring secrets managers for database connections. To enter the password manually:

   1. Click Provide Password.

   2. In the password field, enter the password.

   To use a secret name from a secrets manager:

   1. Click Use Secrets Manager.

   2. From the secrets manager dropdown list, select the secrets manager. Structural connects to the secrets manager and retrieves a list of available secret names.

   3. From the secret name dropdown list, select the secret name.

3. To test the connection to the destination database, click Test Destination Connection.

Providing destination database connection details

If you do not copy the source connection details, then to configure the connection to the destination database:

1. In the Server field, provide the server where the database is located.

2. In the Database field, provide the name of the database.

3. In the Port field, provide the port to use to connect to the database.

4. In the Username field, provide the username for the account to use to connect to the database.

5. For the user password, you can either specify the password manually, or you can select a secret name from a secrets manager. The selected secret must store a password. The secrets manager option only displays if at least one secrets manager is configured. For information about configuring the available secrets managers, go to Configuring secrets managers for database connections. To enter the password manually:

   1. Click Provide Password.

   2. In the password field, enter the password.

   To use a secret name from a secrets manager:

   1. Click Use Secrets Manager.

   2. From the secrets manager dropdown list, select the secrets manager. Structural connects to the secrets manager and retrieves a list of available secret names.

   3. From the secret name dropdown list, select the secret name.

6. To test the connection to the destination data, click Test Destination Connection.

Ensuring encryption of the destination database authentication

The Enable SSL/TLS setting indicates whether to encrypt destination database authentication.

By default, the toggle is in the on position. We strongly recommend that you do not turn off this setting.

Using Kerberos or NTLM authentication

To use Kerberos or NTLM authentication:

1. Toggle Integrated Security to the on position.

2. For Kerberos authentication, in the Kerberos Domain field, provide the name of the Kerberos domain. For NTLM authentication, leave the field empty.

Trusting the server certificate

To indicate that Structural should trust the server certificate, toggle Trust Server Certificate to the on position.

Connecting through an SSH bastion

For additional security, to connect to an SSH bastion:

1. Toggle Enable SSH Tunnel to the on position.

2. In the SSH Host field, provide the host for the SSH bastion.

3. In the SSH Port field, provide the port for the SSH bastion.

4. In the SSH User field, provide the name of the user to use to connect to the SSH bastion.

5. If you do not use a private key, then in the SSH Passphrase field, provide the passphrase to use for authentication.

6. If you do use a private key, then in the SSH Private Key field, provide the private key. If the private key uses a passphrase, then in the SSH Passphrase field, provide the passphrase for the private key.

Enabling MultiSubnetFailover for the destination database

To enable MultiSubnetFailover for the destination database, toggle Enable MultiSubnetFailover to the on position.