# 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](https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-azure-ad-only-authentication?view=azuresql&tabs=azure-cli).

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](https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-azure-ad-only-authentication?view=azuresql&tabs=azure-cli).

- **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

```sql
-- 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:

```sql
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:

```sql
-- 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];
```

3. 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 the **Microsoft ODBC Driver 18 for SQL Server**, which on macOS and Linux depends on **unixODBC**. The `pyodbc` Python package is already installed as a PyRIT dependency.

Install the driver for your OS:

::::{tab-set}

:::{tab-item} Windows
Download and install the [Microsoft ODBC Driver 18 for SQL Server](https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server) MSI from Microsoft.
:::

:::{tab-item} macOS
Install via [Homebrew](https://brew.sh):

```bash
brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew install msodbcsql18 mssql-tools18
```
:::

:::{tab-item} Linux (Ubuntu / Debian)
```bash
# Install unixODBC
sudo apt-get update
sudo apt-get install -y unixodbc unixodbc-dev

# Add the Microsoft package repository
curl -sSL -O https://packages.microsoft.com/config/ubuntu/$(grep VERSION_ID /etc/os-release | cut -d '"' -f 2)/packages-microsoft-prod.deb
sudo dpkg -i packages-microsoft-prod.deb
rm packages-microsoft-prod.deb

# Install the driver
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql18
```

For other Linux distributions (RHEL, SUSE, Alpine), see [Microsoft's installation guide](https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server).
:::

::::

See [PyODBC's documentation](https://github.com/mkleehammer/pyodbc/wiki) for further details.

## 7. Testing

After assigning the right permissions, the user can test executing the [azure sql demo code](../../code/memory/6_azure_sql_memory.ipynb) by connecting through Azure.