Azure Synapse Source

Overview

The Azure Synapse Source connects Sourcesible to an Azure Synapse Analytics workspace, enabling your dedicated SQL pools or serverless SQL endpoints to serve as a foundation for customer data unification, audience segmentation, and Reverse ETL activation. This source falls under the Warehouse category in Sourcesible and supports direct connectivity to publicly accessible Synapse endpoints.

Before you begin, ensure your Azure Synapse workspace has a SQL pool or serverless endpoint that is network-accessible from Sourcesible's egress IP, and that you have the server name, database name, and SQL authentication credentials or an Azure Service Principal ready.

Azure Synapse Credential & Permission Setup

Before configuring the connection in Sourcesible, complete the following steps in the Azure portal.

Create a Dedicated SQL User (Password Auth)

1. Connect to your Synapse dedicated SQL pool or serverless endpoint using an admin account (e.g., via Azure Synapse Studio or a SQL client).
2. Create a dedicated login at the server level:

CREATE LOGIN sourcesible_user WITH PASSWORD = 'your_strong_password';

3. Switch to the target database and create a user mapped to that login:

USE MyDemoDB;

CREATE USER sourcesible_user FOR LOGIN sourcesible_user;

1. Grant the user read access to the schemas Sourcesible needs to query:

GRANT SELECT ON SCHEMA::dbo TO sourcesible_user;

Register a Service Principal (Microsoft Service Principal with Client Secret Auth)

If your organization enforces Azure Active Directory (AAD) authentication and prohibits SQL password logins, use the Microsoft Service Principal with Client Secret option instead.

1. In the Azure Portal, navigate to Azure Active Directory → App registrations.
2. Click New registration and provide a name (e.g., Sourcesible-CDP).
3. Under Certificates & secrets, click New client secret, set an expiry, and copy the generated Client Secret Value immediately — it will not be shown again.
4. Note the Application (client) ID and Directory (tenant) ID from the app's Overview page.
5. In your Synapse workspace, grant the service principal access to the target database:

USE MyDemoDB;

CREATE USER [Sourcesible-CDP] FROM EXTERNAL PROVIDER;

EXEC sp_addrolemember 'db_datareader', [Sourcesible-CDP];

---

### 2.3 Configure Azure Firewall to Allow Sourcesible

1. In the Azure Portal, navigate to your Synapse workspace.

2. Under **Networking**, select **Firewall rules**.

3. Click **Add client IP** or add a new rule with Sourcesible's egress IP:

```

Rule name: Sourcesible

Start IP: 34.1.205.137

End IP: 34.1.205.137

```

4. Click **Save**.

## 3. Connection Configuration (Inside Sourcesible)

### 3.1 Navigate to Data Sources

1. In the left navigation, under **Connections**, click **Data Sources**.

2. Click **Add Data Sources** in the top-right corner.

### 3.2 Select Azure Synapse as Your Source

3. On the **Sources** catalog page, locate the **Warehouse** section.

4. Click the **Azure Synapse** tile.

5. On the Azure Synapse detail page, review the **Set up requirements** and **Overview** sections, which list: Azure Synapse server name, Database name, SQL authentication credentials, Read access to selected tables or views, and Network access configured (if firewall or VNet is enabled).

6. Click **Add Source →** in the top-right corner to open the setup form.

### 3.3 Name Your Source

7. In the **Source Name** field, enter a descriptive name to identify this connection within Sourcesible (e.g., `Azure Synapse - Production` or `Synapse DW`). The field accepts up to 50 characters and can be changed later under data source settings.

### 3.4 Choose an Authentication Method

8. In the **Choose an authentication method** section, select one of the two options:

- **Password** — authenticate using a SQL database username and password

- **Microsoft Service Principal with Client Secret** — authenticate using an Azure AD application identity

The fields shown in the **Configure your Azure Synapse source** section below will vary depending on which method you select. See [Section 4](#4-choose-configuration-options) for a full comparison.

### 3.5 Configure Your Azure Synapse Source

9. In the **Host** field, enter the hostname or IP address of your Azure Synapse instance. For most workspaces, this follows the format:

```

<workspace-name>.sql.azuresynapse.net

```

10. In the **Port** field, enter the port number. The default is `1433`. Verify your actual port in the Azure Portal if your workspace uses a non-standard configuration.

11. In the **Database** field, enter the name of the specific database Sourcesible will query (e.g., `MyDemoDB`). This is the database where Sourcesible will execute its queries.

### 3.6 Provide Credentials

**If using Password authentication:**

12. In the **User** field, enter the SQL login username. This can be your personal Azure Synapse login or the dedicated service account created in Section 2.1. At minimum, this user must have read access to the data you wish to sync.

13. In the **Password** field, enter the password for the user specified above.

**If using Microsoft Service Principal with Client Secret:**

12. In the **Client ID** field, enter the **Application (client) ID** of the registered Azure AD app.

13. In the **Tenant ID** field, enter the **Directory (tenant) ID** from the app registration.

14. In the **Client Secret** field, enter the client secret value generated in Section 2.2.

### 3.7 Trust Server Certificate (Optional)

15. If your Synapse instance uses a self-signed TLS certificate (i.e., one not issued by a public certificate authority), select **Always trust certificate** under the **Trust server certificate?** section. This forces Sourcesible to accept the certificate without validation.

### 3.8 Review the IP Allowlist Reminder

16. Review the inline callout at the bottom of the form. It confirms the IP Sourcesible will use to connect to your source. Ensure this IP has already been added to your Azure Synapse firewall rules (see Section 2.3) before proceeding.

### 3.9 Test and Save

17. Click **Test Connection** to validate all credentials and network access.

18. Once the test passes, click **Save** to finalize the source connection.

---

## 4. Choose Configuration Options

Sourcesible offers two authentication methods for Azure Synapse, selected directly in the setup form under **Choose an authentication method**.

| Option | Credentials Required | Best For |

|---|---|---|

| **Password** | SQL username + password | Teams using SQL authentication; dedicated pool with SQL logins; fastest setup path |

| **Microsoft Service Principal with Client Secret** | Client ID + Tenant ID + Client Secret | Organizations enforcing Azure AD / AAD authentication; environments that prohibit SQL logins |

⭐ **Recommendation:** If your organization enforces Azure Active Directory authentication as a policy, or if your Synapse workspace has SQL authentication disabled, use **Microsoft Service Principal with Client Secret**. For all other cases, **Password** is the simpler and faster option.

---

## 5. Test Your Connection

After completing the form, click **Test Connection** to validate the setup. During the test, Sourcesible checks:

- That the **Host** endpoint is reachable over the network on the configured port

- That the **Port** (`1433` by default) is open and accepting TCP connections from Sourcesible's egress IP

- That the **Database** name is valid and exists on the Synapse instance

- That the provided **credentials** (SQL user/password or Service Principal) authenticate successfully

- That the authenticated identity has at minimum SELECT access on the target database

ℹ️ **Info:** The **Test Connection** button and **Save** button are separate actions. You can click **Save** without running the test, but Sourcesible strongly recommends testing first to catch configuration errors before saving.

---

## 6. Next Steps

Once your Azure Synapse source is active, you can begin pulling data into Sourcesible to power unification and activation workflows:

- [Create a Dataset](link) — Define which tables and columns from Synapse to include as a structured dataset

- [Configure a Dataset Model](link) — Map your Synapse tables to Sourcesible's identity model for customer unification

- [Build Audiences](link) — Use Synapse data as a segmentation source in the Audiences module

- [Set Up Computed Fields](link) — Define derived traits from Synapse data to enrich customer profiles

- [Configure Reverse ETL Sync](link) — Push unified customer data back to Synapse or activate it to Destinations via Sync

---

## 7. Tips and Troubleshooting

### Connection Test Times Out

**Symptom:** Clicking **Test Connection** hangs and returns a timeout or network error.

**Cause:** Sourcesible's egress IP is not allowlisted in the Azure Synapse firewall, or the workspace endpoint is not publicly accessible.

**Fix:**

1. In the Azure Portal, navigate to your Synapse workspace → **Networking → Firewall rules**.

2. Add a rule for Sourcesible's egress IP shown in the form:

```

Start IP: 34.1.205.137

End IP: 34.1.205.137

```

3. Click **Save** in the Azure Portal and retry the test in Sourcesible.

---

### Authentication Fails: Login Failed for User

**Symptom:** The connection test returns a login failure error immediately.

**Cause:** The username or password is incorrect, the user does not exist in the target database, or the database context is wrong.

**Fix:**

1. Verify the credentials by connecting directly to Synapse using a SQL client:

```

Server: <workspace>.sql.azuresynapse.net,1433

Database: MyDemoDB

User: sourcesible_user

1. Confirm the user was created in the correct database context, not just at the server login level:

sql

USE MyDemoDB;

SELECT name FROM sys.database_principals WHERE name = 'sourcesible_user';

1. If the query returns no rows, re-run the CREATE USER statement from Section 2.1.

Service Principal Login Fails

Symptom: Using Microsoft Service Principal with Client Secret results in an authentication error.

Cause: The Client ID, Tenant ID, or Client Secret value is incorrect; the secret has expired; or the service principal has not been added as a database user.

Fix:

1. Confirm the Client ID and Tenant ID match exactly what is shown in the app registration's Overview page in Azure AD.
2. Check whether the client secret has expired in Azure AD → App registrations → [your app] → Certificates & secrets.
3. If expired, generate a new secret and update the Client Secret field in Sourcesible.
4. Confirm the service principal exists as a database user in the target database:

USE MyDemoDB;

SELECT name FROM sys.database_principals WHERE name = '[Sourcesible-CDP]';

1. If not found, recreate the external user and grant access as shown in Section 2.2.

Permission Denied on Table or Schema

Symptom: The connection test passes, but data sync fails with a permission error on a specific schema or table.

Cause: The SQL user or service principal does not have SELECT access on the target schema.

Fix: Run the following as a workspace administrator, replacing dbo with the affected schema:

USE MyDemoDB;

GRANT SELECT ON SCHEMA::dbo TO sourcesible_user;

For service principal users:

USE MyDemoDB;

GRANT SELECT ON SCHEMA::dbo TO [Sourcesible-CDP];

---

### TLS / Certificate Validation Error

**Symptom:** The connection test fails with a TLS or SSL certificate error.

**Cause:** Your Synapse instance uses a self-signed certificate that Sourcesible cannot validate through the standard certificate chain.

**Fix:** In the setup form, scroll to the **Trust server certificate?** section and select **Always trust certificate**, then re-run the test. Only apply this setting if you are certain your endpoint uses a self-signed certificate.

---

### Host Field: On-Demand vs. Dedicated Pool Endpoint Format

**Symptom:** The connection test fails with "host not found" even though the workspace is running.

**Cause:** Using the wrong endpoint for the pool type. On-demand (serverless) and dedicated pool endpoints have different hostnames.

**Fix:** Confirm which pool type you are connecting to and use the correct host format:

```

# Dedicated SQL Pool

<workspace-name>.sql.azuresynapse.net

# Serverless SQL (On-Demand)

<workspace-name>-ondemand.sql.azuresynapse.net