Configuring SQL Server workspace data connections

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. Note that if you plan to use NTLM for authentication, then the username must be in the format <domain name>\<username>. Otherwise, you only provide the username.

5. For Password, you can either specify the password manually or, if secrets managers are available, you can select a secret name from a secrets manager.

6. To test the connection to the source data, click Test Source Connection. If the connection test is successful, then Structural also tests and reports on the connection speed.

Connection security settings

For the source connection, you can also:

• Use SSL/TLS to encrypt authentication.

• Trust the server certificate.

• Use a connection tunneling option (SSH or Tailscale).

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. Note that if Structural is unable to connect using NTLM, it attempts to connect using Kerberos.

Enabling MultiSubnetFailover for the source database

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

Enabling cross-database references

By default, Structural only uses objects from the source database. It does not refer to linked servers or to objects that reference other databases.

To enable Structural to use those cross-database objects, toggle Enable Cross-Database References to the on position.

Note that even when cross-database references are enabled:

• It does not grant access to linked servers that are blocked.

• For linked servers that are not blocked, the source or destination database user must be granted the required permissions on those servers.

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 Password, you can either specify the password manually or, if secrets managers are available, you can select a secret name from a secrets manager.

3. To test the connection to the intermediate database, click Test Intermediate Connection. If the connection test is successful, then Structural also tests and reports on the connection speed.

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. Note that if you plan to use NTLM for authentication, then the username must be in the format <domain name>/<username>. Otherwise, you only provide the username.

5. For Password, you can either specify the password manually or, if secrets managers are available, you can select a secret name from a secrets manager.

6. To test the connection to the source data, click Test Intermediate Connection. If the connection test is successful, then Structural also tests and reports on the connection speed.

Connection security settings

For the intermediate database connection, you can also:

• Use SSL/TLS to encrypt authentication.

• Trust the server certificate.

• Use a connection tunneling option (SSH or Tailscale).

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. Note that if Structural is unable to connect using NTLM, it attempts to connect using Kerberos.

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 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 Password, you can either specify the password manually or, if secrets managers are available, you can select a secret name from a secrets manager.

3. To test the connection to the destination database, click Test Destination Connection. If the connection test is successful, then Structural also tests and reports on the connection speed.

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. Note that if you plan to use NTLM for authentication, then the username must be in the format <domain name>/<username>. Otherwise, you only provide the username.

5. For Password, you can either specify the password manually or, if secrets managers are available, you can select a secret name from a secrets manager.

6. To test the connection to the destination data, click Test Destination Connection. If the connection test is successful, then Structural also tests and reports on the connection speed.

Connection security settings

For the destination connection, you can also:

• Use SSL/TLS to encrypt authentication.

• Trust the server certificate.

• Use a connection tunneling option (SSH or Tailscale).

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. Note that if Structural is unable to connect using NTLM, it attempts to connect using Kerberos.

Enabling MultiSubnetFailover for the destination database

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