Azure Synapse

Sync custom events from Azure Synapse to OneSignal to trigger automated Journeys and personalized messaging campaigns based on user behavior.

Overview

The OneSignal + Azure Synapse integration enables syncing of custom events from your Azure Synapse Analytics workspace to OneSignal to trigger automated messaging campaigns and Journeys based on user behavior.

Azure Synapse Analytics is Microsoft’s cloud-based analytics service that combines data integration, data warehousing, and analytics.

---

Requirements

• Access to Custom Events (currently in beta)
• Updated Account Plan (not available on free apps).

Azure Synapse

• Azure Synapse workspace with SQL pool access
• Database user with appropriate permissions
• Event tables containing structured behavioral data
• Firewall access for OneSignal IP addresses

---

Setup

1. Create dedicated login for OneSignal

   Create a dedicated login and user account with a strong, unique password:

   USE <your-database>;
   
   -- Create census login with the ability to sign in with a password
   CREATE LOGIN CENSUS WITH PASSWORD = '<strong-unique-password>';
   
   -- Create user for the login
   CREATE USER CENSUS FOR LOGIN CENSUS;
   
   -- Give the census user the ability to connect to database
   GRANT CONNECT TO CENSUS;
   

   Replace <your-database> with your actual database name containing event data.

2. Grant read permissions

   Provide read-only access to your event data:

   -- Give the census user the ability to read all tables
   EXEC sp_addrolemember 'db_datareader', CENSUS;
   
   -- Grant census user ability to read schema and data
   -- Run this for each schema you intend OneSignal to access
   GRANT SELECT, VIEW DEFINITION ON SCHEMA::<your-schema> TO CENSUS;
   

   Replace <your-schema> with your actual schema name containing event data. Repeat this command for each schema you want OneSignal to access.

3. Configure firewall access

   Configure Azure Synapse firewall to allow OneSignal IP addresses.

   Use the Windows Azure Management Portal or run sp_set_firewall_rule on the primary database:

   -- Example: Add firewall rule for OneSignal IP range
   EXEC sp_set_firewall_rule
       N'OneSignal Access',
       'ONESIGNAL_IP_START',
       'ONESIGNAL_IP_END';
   

   Contact OneSignal support for the current IP address ranges for your region.

4. Connect to OneSignal

   In OneSignal, go to Data > Integrations and click Add Integration.

   Select Azure Synapse and provide the following connection details:

   • Host: Your Synapse SQL endpoint hostname
   • Port: 1433 (default)
   • Database: Your database name
   • Username: CENSUS
   • Password: The password from Step 1

---

Event data mapping

Map your to OneSignal’s custom events format:

OneSignal Field	Description	Required
name	event_name	Event identifier	Yes
external_id	user_id	User identifier	Yes
timestamp	event_timestamp	When event occurred	No
properties	event_data	No

Example Event Table Schema

-- Example Azure Synapse event table
CREATE TABLE analytics.user_events (
    event_id BIGINT IDENTITY(1,1) PRIMARY KEY,
    event_name NVARCHAR(100) NOT NULL,
    user_id NVARCHAR(255) NOT NULL,
    event_timestamp DATETIME2 DEFAULT GETUTCDATE(),
    event_data NVARCHAR(MAX),
    session_id NVARCHAR(255),
    device_type NVARCHAR(50)
)
WITH (DISTRIBUTION = HASH(user_id), CLUSTERED COLUMNSTORE INDEX);

SQL Query Mode

Write custom SQL queries to transform your event data:

-- Example: Recent high-value events
SELECT
    event_name,
    user_id,
    event_timestamp,
    event_data
FROM analytics.user_events
WHERE event_timestamp >= DATEADD(day, -7, GETUTCDATE())
    AND JSON_VALUE(event_data, '$.value') > 100
ORDER BY event_timestamp DESC;

---

Azure-Specific Features

Distributed Architecture

• Events distributed by user_id for optimal query performance
• Clustered columnstore indexes for analytics workloads
• Massively parallel processing (MPP) for large-scale event data

Integration with Azure Ecosystem

• Connect to Azure Data Factory for automated event pipelines
• Integrate with Azure Event Hubs for real-time event streaming
• Leverage Azure Active Directory for authentication

---

Advanced Network Configuration

OneSignal can successfully connect to Azure Synapse instances that are using advanced networking controls including region constraints, IP address allow lists, or SSH Tunneling.

For more information about configuring network access, contact your Azure Synapse administrator or OneSignal support.

---

Limitations

• Based on SQL Server JDBC driver connection protocol
• Requires explicit firewall rules for OneSignal IP addresses
• Complex queries may impact SQL pool performance and costs
• JSON operations require careful indexing for optimal performance

---

FAQ

Can I connect to multiple Azure Synapse schemas?

Yes, you can grant the CENSUS user access to multiple schemas by running the GRANT SELECT, VIEW DEFINITION ON SCHEMA::<schema> statement for each schema containing event data.

How do I configure firewall access for OneSignal?

Use the Azure Management Portal or sp_set_firewall_rule to add OneSignal’s IP addresses. Contact OneSignal support for the current IP ranges.

What’s the difference between Azure Synapse and SQL Server integration?

Azure Synapse uses the same SQL Server JDBC driver but includes distributed architecture features and requires specific firewall configuration for cloud access.