Synapse: Read-Only Login

This guide walks you through creating a dedicated read-only login for Azure Synapse Analytics. Choose the authentication method that matches your environment:

• SQL authentication — the default. Works unless your Synapse workspace has SQL authentication disabled.
• Entra authentication — use this when SQL authentication is disabled and your workspace is in Entra-only mode.

The process also differs slightly between dedicated and serverless SQL pools.

SQL Authentication

Dedicated SQL Pool

Connect to the master database of your Synapse workspace as an admin, then run:

-- Create a server-level login (run on master database)
CREATE LOGIN freshguard_reader
  WITH PASSWORD = 'your-strong-password-here';

Switch to your dedicated SQL pool and create the user:

-- Switch to the dedicated SQL pool
USE analytics_pool;

-- Create a database user mapped to the login
CREATE USER freshguard_reader FOR LOGIN freshguard_reader;

-- Grant read-only access
EXEC sp_addrolemember 'db_datareader', 'freshguard_reader';

Serverless SQL Pool

For serverless SQL pools, the process is similar but uses the built-in serverless endpoint:

-- Connect to the serverless SQL endpoint's master database
CREATE LOGIN freshguard_reader
  WITH PASSWORD = 'your-strong-password-here';

-- Switch to the target database
USE analytics_db;

-- Create a database user
CREATE USER freshguard_reader FOR LOGIN freshguard_reader;

-- Grant read-only access
ALTER ROLE db_datareader ADD MEMBER freshguard_reader;

Entra Authentication (Entra-only environments)

If your Synapse workspace has SQL authentication disabled, create a contained database user mapped to an Entra service principal instead.

Step 1 — Create an Entra app registration

In the Azure Portal, go to Microsoft Entra ID → App registrations → New registration:

• Name: freshguard-reader (or any descriptive name)
• Supported account types: Single tenant

After creating the registration, note the Application (client) ID and Directory (tenant) ID — you will need these when adding the source in FreshGuard.

Then go to Certificates & secrets → New client secret, create a secret, and copy its Value immediately (it is only shown once).

Step 2 — Create the contained database user

Dedicated SQL Pool

Connect to your dedicated SQL pool (not master) as an Entra admin:

-- Create a contained user mapped to the Entra app registration
-- Use the display name of your app registration
CREATE USER [freshguard-reader] FROM EXTERNAL PROVIDER;

-- Grant read-only access
EXEC sp_addrolemember 'db_datareader', 'freshguard-reader';

Serverless SQL Pool

Connect to your serverless database as an Entra admin:

-- Create a contained user mapped to the Entra app registration
CREATE USER [freshguard-reader] FROM EXTERNAL PROVIDER;

-- Grant read-only access
ALTER ROLE db_datareader ADD MEMBER [freshguard-reader];

Step 3 — Multiple pools or databases

Repeat the CREATE USER and role assignment in each SQL pool or database you want to monitor. There is no server-level login to share — each pool or database gets its own contained user.

Connecting to FreshGuard

When adding the source in FreshGuard, select Entra Service Principal as the authentication type and enter:

• Tenant ID: Directory (tenant) ID from the app registration
• Client ID: Application (client) ID from the app registration
• Client Secret: The secret value you copied in Step 1

Schema-Level Access

To restrict access to specific schemas:

-- Grant SELECT on specific schemas instead of db_datareader
GRANT SELECT ON SCHEMA::dbo TO freshguard_reader;
GRANT SELECT ON SCHEMA::analytics TO freshguard_reader;

Verify Permissions

Connect as the new login and confirm it can read but not write:

-- This should work
SELECT COUNT(*) FROM dbo.your_table;

-- This should fail with "INSERT permission denied"
INSERT INTO dbo.your_table (id) VALUES (0);

Firewall Setup

Add FreshGuard’s outgoing IP addresses to your Synapse workspace firewall:

1. In your FreshGuard dashboard, go to Settings → Network to find FreshGuard’s current outgoing IPs.
2. In the Azure Portal, navigate to your Synapse workspace.
3. Go to Networking (under Security).
4. Add a firewall rule for each IP address.