# Configuring SQL Server workspace data connections

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

## Connecting to the source database <a href="#sql-server-source-data-connection" id="sql-server-source-data-connection"></a>

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

### Providing the source database connection details <a href="#sql-server-data-connection-source-details" id="sql-server-data-connection-source-details"></a>

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](https://docs.tonic.ai/app/workspace/managing-workspaces/workspace-configuration-settings/secrets-manager/selecting-a-secrets-manager-secret).
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.

### Ensuring encryption of source database authentication <a href="#sql-server-source-auth-encrypt" id="sql-server-source-auth-encrypt"></a>

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 <a href="#sql-server-source-kerberos-ntlm-auth" id="sql-server-source-kerberos-ntlm-auth"></a>

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.

### Trusting the server certificate <a href="#sql-server-source-trust-cert" id="sql-server-source-trust-cert"></a>

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

### Connecting through an SSH bastion <a href="#sql-server-source-ssh-bastion" id="sql-server-source-ssh-bastion"></a>

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.\
   \
   If secrets managers are available, you can instead [select a secret name from a secrets manager](https://docs.tonic.ai/app/workspace/managing-workspaces/workspace-configuration-settings/secrets-manager/selecting-a-secrets-manager-secret).
6. If you do use a private key, then in the **SSH Private Key** field, provide the private key.\
   \
   If secrets managers are available, you can instead [select a secret name from a secrets manager](https://docs.tonic.ai/app/workspace/managing-workspaces/workspace-configuration-settings/secrets-manager/selecting-a-secrets-manager-secret).\
   \
   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 <a href="#sql-server-source-db-multisubnetfailover" id="sql-server-source-db-multisubnetfailover"></a>

To enable [MultiSubnetFailover](https://learn.microsoft.com/en-us/sql/relational-databases/native-client/features/sql-server-native-client-support-for-high-availability-disaster-recovery?view=sql-server-ver16#connecting-with-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](https://docs.tonic.ai/app/setting-up-your-database/sql-server-before-create-workspace#linked-servers-block-all).
* For linked servers that are not blocked, the source or destination database user must be [granted the required permissions on those servers](https://docs.tonic.ai/app/setting-up-your-database/sql-server-before-create-workspace#linked-server-grant-permissions).

## Connecting to the intermediate database for upsert <a href="#sqlserver-data-connection-intermediate" id="sqlserver-data-connection-intermediate"></a>

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](https://docs.tonic.ai/app/workspace/managing-workspaces/workspace-configuration-settings/workspace-config-upsert) 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 <a href="#sqlserver-data-connection-intermediate-copy-source" id="sqlserver-data-connection-intermediate-copy-source"></a>

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](https://docs.tonic.ai/app/workspace/managing-workspaces/workspace-configuration-settings/secrets-manager/selecting-a-secrets-manager-secret).
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 <a href="#sqlserver-data-connection-intermediate-connection-details" id="sqlserver-data-connection-intermediate-connection-details"></a>

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](https://docs.tonic.ai/app/workspace/managing-workspaces/workspace-configuration-settings/secrets-manager/selecting-a-secrets-manager-secret).
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.

### Ensuring encryption of intermediate database authentication <a href="#sql-server-intermediate-auth-encrypt" id="sql-server-intermediate-auth-encrypt"></a>

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 <a href="#sql-server-intermediate-auth-kerberos-ntlm" id="sql-server-intermediate-auth-kerberos-ntlm"></a>

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.

### Trusting the server certificate <a href="#sql-server-intermediate-trust-cert" id="sql-server-intermediate-trust-cert"></a>

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

### Connecting through an SSH bastion <a href="#sql-server-intermediate-ssh-bastion" id="sql-server-intermediate-ssh-bastion"></a>

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.\
   \
   If secrets managers are available, you can instead [select a secret name from a secrets manager](https://docs.tonic.ai/app/workspace/managing-workspaces/workspace-configuration-settings/secrets-manager/selecting-a-secrets-manager-secret).
6. If you do use a private key, then in the **SSH Private Key** field, provide the private key.\
   \
   If secrets managers are available, you can instead [select a secret name from a secrets manager](https://docs.tonic.ai/app/workspace/managing-workspaces/workspace-configuration-settings/secrets-manager/selecting-a-secrets-manager-secret).\
   \
   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 <a href="#sql-server-intermediate-db-multisubnetfailover" id="sql-server-intermediate-db-multisubnetfailover"></a>

To enable [MultiSubnetFailover](https://learn.microsoft.com/en-us/sql/relational-databases/native-client/features/sql-server-native-client-support-for-high-availability-disaster-recovery?view=sql-server-ver16#connecting-with-multisubnetfailover) for the intermediate database, toggle **Enable MultiSubnetFailover** to the on position.

## Connecting to the destination database <a href="#sql-server-destination-connection" id="sql-server-destination-connection"></a>

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 [workspace-config-write-to-container-artifacts](https://docs.tonic.ai/app/workspace/managing-workspaces/workspace-configuration-settings/workspace-config-write-to-container-artifacts "mention").

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 <a href="#sql-server-data-connection-destination-copy-source" id="sql-server-data-connection-destination-copy-source"></a>

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](https://docs.tonic.ai/app/workspace/managing-workspaces/workspace-configuration-settings/secrets-manager/selecting-a-secrets-manager-secret).
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 <a href="#sql-server-data-connection-destination-connection-details" id="sql-server-data-connection-destination-connection-details"></a>

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](https://docs.tonic.ai/app/workspace/managing-workspaces/workspace-configuration-settings/secrets-manager/selecting-a-secrets-manager-secret).
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.

### Ensuring encryption of the destination database authentication <a href="#sql-server-destination-encrypt-auth" id="sql-server-destination-encrypt-auth"></a>

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 <a href="#sql-server-destination-auth-kerberos-ntlm" id="sql-server-destination-auth-kerberos-ntlm"></a>

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.

### Trusting the server certificate <a href="#sql-server-destination-trust-cert" id="sql-server-destination-trust-cert"></a>

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

### Connecting through an SSH bastion <a href="#sql-server-destination-ssh-bastion" id="sql-server-destination-ssh-bastion"></a>

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.\
   \
   If secrets managers are available, you can instead [select a secret name from a secrets manager](https://docs.tonic.ai/app/workspace/managing-workspaces/workspace-configuration-settings/secrets-manager/selecting-a-secrets-manager-secret).
6. If you do use a private key, then in the **SSH Private Key** field, provide the private key.\
   \
   If secrets managers are available, you can instead [select a secret name from a secrets manager](https://docs.tonic.ai/app/workspace/managing-workspaces/workspace-configuration-settings/secrets-manager/selecting-a-secrets-manager-secret).\
   \
   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 <a href="#sql-server-destination-db-multisubnetfailover" id="sql-server-destination-db-multisubnetfailover"></a>

To enable [MultiSubnetFailover](https://learn.microsoft.com/en-us/sql/relational-databases/native-client/features/sql-server-native-client-support-for-high-availability-disaster-recovery?view=sql-server-ver16#connecting-with-multisubnetfailover) for the destination database, toggle **Enable MultiSubnetFailover** to the on position.