Creating Read-Only Accounts in Azure SQL with and without Failover Groups

When working with Azure SQL Database, there are scenarios where you need to provide users with read-only access. This ensures they can query data without the risk of modifying or deleting it. The process is straightforward if you are using a single Azure SQL server, but it changes slightly when a failover group is involved. Let’s walk through both cases and highlight the best practices.

Scenario 1: Single Azure SQL Server

• Create a Login on the Master Database

CREATE LOGIN <loginname> WITH PASSWORD = 'Nam@012';        

• Create a User in the Target Database and Grant Permissions

CREATE USER <username> FROM LOGIN <loginname>;
EXEC sp_addrolemember 'db_datareader', '<username>';        

At this point, the user has read-only access to the selected database.

Scenario 2: Azure SQL with Failover Group

When a failover group is configured, you have a primary server and a secondary server. Creating a read-only account requires ensuring that the login and user exist on both servers to maintain access after a failover. You can achieve this in two ways.

Option A: Manual SID Synchronization

• Create a Login on the Primary Master Database

CREATE LOGIN <loginname> WITH PASSWORD = 'Nam@012';        

• Create a User in the Target Database and Assign Permissions

CREATE USER <username> FROM LOGIN <loginname>;
EXEC sp_addrolemember 'db_datareader', '<username>';        

• Get the SID for the User on the Primary Database

SELECT [sid] FROM sysusers WHERE [name] = '<username>';        

• Create the Login on the Secondary Master Database with the Same SID

CREATE LOGIN <loginname> WITH PASSWORD = 'Nam@012', SID = <SID from primary>;        

This ensures login consistency across the failover group.

Option B: Use the Read-Only Listener Endpoint

Instead of syncing logins manually, you can use the read-only listener endpoint of the failover group. Once you create the login and user on the primary server (steps 1 and 2 above), simply connect to the read-only replica by adding this to your connection string:

ApplicationIntent=ReadOnly

Azure SQL automatically routes the connection to a readable secondary database. This approach is simpler, more reliable, and requires less management.

Benefits of Read-Only Accounts

• Improved Performance: Offload reporting and analytics queries to a secondary replica, reducing load on the primary database.
• Enhanced Scalability: Gain extra compute capacity for read-heavy workloads without additional cost in Premium and Business Critical tiers.
• Cost Efficiency: Eliminate the need for dedicated reporting servers.
• High Availability: Read-only replicas stay in sync, ensuring continuous availability for analytics even during failovers.

Security and Management Best Practices

• Principle of Least Privilege: Use db_datareader to restrict users to SELECT queries only.
• Use Fixed Roles: Rely on built-in roles instead of assigning granular permissions object by object.
• Centralized Identity: Prefer Microsoft Entra (formerly Azure AD) authentication over SQL logins for better security and easier user management.
• Auditing and Monitoring: Leverage Azure SQL’s built-in auditing to track read-only user activities, even on replicas.

Creating read-only accounts in Azure SQL is straightforward for single servers and manageable with failover groups. You can either sync logins manually using SIDs or take advantage of the failover group’s read-only listener endpoint for an easier and more cloud-native approach.

References:

https://learn.microsoft.com/en-us/azure/azure-sql/database/auto-failover-group-sql-db?view=azuresql-db&tabs=azure-powershell

https://learn.microsoft.com/en-us/azure/azure-sql/database/read-scale-out?view=azuresql-mi