Azure SQL: Read-Only Login

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

• SQL authentication — the default. Works unless your organisation has disabled SQL logins at the server level.
• Entra authentication — use this when SQL authentication is disabled and your Azure SQL server is in Entra-only mode.

SQL Authentication

Connect to the master database on your Azure SQL server as the server admin, then run:

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

Now switch to the target database and create the user:

-- Switch to the target database
USE analytics;

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

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

Each Azure SQL database requires its own user — repeat the CREATE USER and ALTER ROLE commands for each database you want to monitor.

Entra Authentication (Entra-only environments)

If your Azure SQL server has SQL authentication disabled (Entra-only mode), you need to 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

Connect to your target database (not master) as an Entra admin — this can be the Entra admin set on the Azure SQL server, or any Entra user with ALTER ANY USER permission in the database:

-- 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
ALTER ROLE db_datareader ADD MEMBER [freshguard-reader];

Step 3 — Multiple databases

Repeat the CREATE USER and ALTER ROLE commands in each database you want to monitor. There is no server-level login to share across databases — each database gets its own contained user.

USE staging;
CREATE USER [freshguard-reader] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [freshguard-reader];

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

Remember to add FreshGuard’s outgoing IP addresses to your Azure SQL 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 SQL server.
3. Go to Networking > Firewall rules.
4. Add a rule for each IP address.