Microsoft SQL Server

Connect Foundry to Microsoft SQL Server to read and sync data between SQL Server databases and Foundry.

Supported capabilities

Setup

1. Open the Data Connection application and select + New Source in the upper right corner of the screen.
2. Select MS SQL Server from the available connector types.
3. Choose to use a direct connection over the Internet or to connect through an agent runtime.
4. Follow the additional configuration prompts to continue the setup of your connector using the information in the sections below.

Learn more about setting up a connector in Foundry.

Authentication

You can authenticate with SQL Server in the following ways:

1. Username and password: Provide a username and password. We recommend the use of service credentials rather than individual user credentials.
2. Active Directory Msi*: This option will use the authentication=ActiveDirectoryMSI JDBC setting. An msiClientId may optionally be provided.
3. Active Directory Password*: This option will use the authentication=ActiveDirectoryPassword JDBC setting. A username and password for an Active Directory user must be provided to use this setting.
4. Active Directory Service Principal*: This option will use the authentication=ActiveDirectoryServicePrincipal JDBC setting. A principal ID (sometimes referred to as an application or client ID) must be specified, along with a secret for that principal ID.

For more information on these authentication modes, see the official documentation ↗. For all authentication options, ensure that the provided user and role has the necessary privileges on the target database, as well as permission to read from or write to the target table(s).

* Note that Azure Active Directory is now called Microsoft Entra ID ↗; however, the JDBC options on the SQL Server driver published by Microsoft retain the original names referring to Active Directory.

Networking

The Microsoft SQL Server connector requires network access to the SQL Server instance that you wish to connect to. SQL Server connections will normally use a hostname to connect on port 1433.

To enable direct connections out of Foundry to SQL Server, the appropriate egress policies must be added when setting up the source in the Data Connection application.

For SQL Server instances hosted on a cloud service like Azure SQL or AWS RDS, you must add an egress policy for the hostname retrieved from your cloud provider’s console.

• To find the hostname for your Azure SQL instance, navigate to the Settings > Properties page in the Azure portal, and look for the Server Name field. An example hostname-based egress policy for Azure SQL: <your-database-name>.database.windows.net (port 1433)
• To find the resolved IP, you can run nslookup <your-database-name>.database.windows.net from the command line. The final result will be the IP address that this hostname resolves to in Azure. The following is an example IPv4-based egress policy for Azure SQL: x.x.x.x (port 1433). Azure SQL does load balancing across multiple hosts, so you may need to run the nslookup command several times and add all of the resolved IP addresses.

If you are connecting with an agent runtime, you must ensure that the agent host has firewalls open to the host names, IP addresses, and ports required to connect to your MS SQL Server database.

Connection details

Change data capture

The Microsoft SQL Server source supports change data capture syncs.

To enable change data capture for Microsoft SQL Server, you must run a command like the one below to enable CDC on the database.

USE <database>
GO
EXEC sys.sp_cdc_enable_db
GO

Then, run another command on each table that should be recording changelogs:

EXEC sys.sp_cdc_enable_table
    @source_schema = N'<schema>'
  , @source_name = N'<table_name>'
  , @role_name = NULL
  , @capture_instance = NULL
  , @supports_net_changes = 0
  , @filegroup_name = N'PRIMARY';
GO

Once change data capture is enabled for the table(s) you wish to sync to Foundry, you can navigate to the Overview page and select + Create CDC sync to start creating a new change data capture sync.

For more information on these commands and using change data capture (CDC) with Microsoft SQL Server, see the official documentation ↗ for the version of SQL Server in use.

Change Data Capture permissioning

To successfully read CDC data, you will need to ensure you have provided sufficient permissions to the database user.

• You can verify your permissions with the following query: SELECT HAS_PERMS_BY_NAME('cdc', 'SCHEMA', 'EXECUTE') AS HasExecutePermission;. The result will return 1 if True and 0 if False.
• You can grant missing permissions by running the following query within the source system itself: GRANT EXECUTE ON SCHEMA::cdc TO <USER>;