Azure SQL Database Setup, Authentication and User Permissions

Azure SQL Database Setup, Authentication and User Permissions#

This document provides a comprehensive guide to setting up and managing Azure SQL Database with a focus on using Entra ID authentication. It covers the essential steps for creating an Azure SQL Database, configuring Azure SQL Server security controls such as enabling Entra ID-only authentication and restricting access to selected networks, mapping Entra ID users to specific Azure SQL DB roles like db_owner, db_datawriter, and db_datareader, and verifying if corporate email users exist in the database while granting appropriate permissions to them.

If the Azure SQL Database is already set up, you can skip directly to Section 3 to manage user DB permissions.

1. Creating an Azure SQL Database#

To create an Azure SQL Database, follow the steps in Azure SQL DB Creation Guide.

During the creation of the Azure SQL DB, you can either create a new Azure SQL Server or select an existing one.

2. Configuring Azure SQL Server security controls#

Once the Azure SQL DB is created, the associated Azure SQL Server needs to be configured. Security controls must be applied as follows to ensure Microsoft security best practices:

a. Entra ID Only Authentication#

As per best security practices, it is recommended to use Entra ID Only Authentication for Azure SQL DB, rather than username/password-based authentication.

You can find detailed steps in the official documentation here.

  • Steps:

    1. Go to your Azure SQL Server instance -> Settings -> Microsoft Entra ID.

    2. Check the box that says “Support only Microsoft Entra authentication for this server.”

    3. Hit Save

b. Selected Networks#

For enhanced security, it is recommended to restrict access to selected public IP addresses.

  • Steps:

    1. Go to Azure SQL Server instance -> Security -> Networking.

    2. Enable Selected Networks and create firewall rules with the public IP address of the user who will connect to the database.

Alternatively, you can use your organization’s VPN CIDR IP ranges so that users can connect through VPN and access the database seamlessly.

3. Azure SQL DB Roles and Entra ID Authentication#

In PyRIT, we currently support Entra ID Only Authentication for connecting to Azure SQL Database.

Even when users authenticate via Entra ID, they must be explicitly mapped to specific database roles to perform database operations.

Key Database Roles:#

  1. db_owner: Provides full control over the Azure SQL Database. Users with this role can create, modify, and delete database objects, manage security, and grant/revoke permissions to other users.

  2. db_datawriter: Allows users to insert, update, and delete data but does not permit modifying the database schema or managing users.

  3. db_datareader: Grants read-only access to all tables and views in the database.

Mapping Entra ID Users to Database Roles#

To grant users access to the database, you must map them to the appropriate role. Developers/maintainers should be assigned the db_owner role, while operators can be assigned the db_datawriter role.

Example: Mapping Entra ID Users#

-- Create a database user for the Entra ID user
CREATE USER [user@domain.com] FROM EXTERNAL PROVIDER;

-- Map the user to the db_datawriter role
ALTER ROLE db_datawriter ADD MEMBER [user@domain.com];

user@domain.com could be corporate email address, such as abc@microsoft.com, which is linked to Entra ID.

4. Checking If a Corporate Email Address Exists#

To verify if a specific corporate email address (Entra ID user) exists in the database, you can run the following query from the Query Editor:

SELECT u.name AS UserName, u.type_desc AS UserType, r.name AS RoleName
FROM sys.database_principals AS u
LEFT JOIN sys.database_role_members AS rm ON rm.member_principal_id = u.principal_id
LEFT JOIN sys.database_principals AS r ON r.principal_id = rm.role_principal_id
WHERE u.type NOT IN('R', 'G')  -- Exclude roles and groups
ORDER BY UserName, RoleName;

  • UserName: The user’s email address.

  • UserType: Type of user (e.g., EXTERNAL_USER for Entra ID users).

  • RoleName: The role assigned to the user in the database.

Example Output:#

UserName

UserType

RoleName

operator1@microsoft.com

EXTERNAL_USER

db_datawriter

dev1@microsoft.com

EXTERNAL_USER

db_owner

5. Granting Permissions to a New User#

  1. Determine whether the user needs the db_owner or db_datawriter role.

    • db_owner is recommended for developers and maintainers.

    • db_datawriter is recommended for operators interacting with the database.

  2. Run the following commands from the query editor:

-- Create a database user for the Entra ID user
CREATE USER [user@domain.com] FROM EXTERNAL PROVIDER;

-- Map the user to the required role
ALTER ROLE db_datawriter ADD MEMBER [user@domain.com];

  1. Verify the permissions by running the above Checking If a Corporate Email Address Exists query again in the Query Editor.

6. Configure Local Environment#

Connecting PyRIT to an Azure SQL Server database requires ODBC, PyODBC and Microsoft’s ODBC Driver for SQL Server to be installed in your local environment. Consult PyODBC’s documentation for detailed instruction on.

7. Testing#

After assigning the right permissions, the user can test executing the azure sql demo code by connecting through Azure.