> For the complete documentation index, see [llms.txt](https://docs.teleskope.ai/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.teleskope.ai/connectors/clouds/azure/azure-sql-managed-identity.md).

# Azure SQL — Managed Identity

Configure Azure SQL scanning using a User Assigned Managed Identity (UAMI) instead of a SQL login and password. Use this path when your security policy prohibits stored credentials, or when Teleskope runs in your Azure subscription with Workload Identity. For the standard SQL login setup, see [Azure SQL](/connectors/clouds/azure/azure-sql.md).

## Requirements

* Teleskope scanning services run in your Azure subscription with Workload Identity enabled and a UAMI attached
* Microsoft Entra authentication is enabled on each Azure SQL server (an Entra admin is set on the server)
* You have `az` CLI access to the resource group containing the UAMI, and admin access to the SQL servers
* The UAMI has the `Reader` role on each subscription containing the servers, so Teleskope can discover them. See [Azure: Workload Identity](/setup/deployment/deployments/azure/workload-identity.md)
* No SQL login is stored in Teleskope for these servers. A stored username and password take precedence over Entra authentication

{% hint style="info" %}
This page applies to Azure SQL Database (`*.database.windows.net`). SQL Server running on Azure VMs requires instance-level Entra configuration and is not covered here.
{% endhint %}

## Identify the managed identity

Database users must be bound to the exact identity the Teleskope pods run as. Look it up by its client ID (provided by Teleskope, visible in the pod environment as `AZURE_CLIENT_ID`):

```bash
az identity list -g <resource-group> \
  --query "[?clientId=='<client-id>'].{name:name, principalId:principalId}" -o table
```

Record the identity `name` and `principalId`. If a statement below fails with "Principal not found in the directory", add `WITH OBJECT_ID = '<principalId>'` to it:

* In `master`, create the login by object ID, then continue with the rest of the `master` step:

  ```sql
  CREATE LOGIN [<identity-name>] FROM EXTERNAL PROVIDER WITH OBJECT_ID = '<principalId>';
  ```
* In each user database, create the user by object ID:

  ```sql
  CREATE USER [<identity-name>] FROM EXTERNAL PROVIDER WITH OBJECT_ID = '<principalId>';
  ```

Never run `CREATE USER ... FROM EXTERNAL PROVIDER` in `master`.

## Permissions

{% stepper %}
{% step %}

#### Let the identity enumerate databases from `master`

Teleskope's first connection to each server lands in `master` and lists databases from `sys.databases`. Without a server-level grant, that list contains only `master`, and no user databases are crawled. `VIEW ANY DATABASE` is a server-level permission, so it needs an Entra login; a database user in `master` cannot hold it.

{% tabs %}
{% tab title="Azure SQL Database" %}
Azure SQL Database grants `VIEW ANY DATABASE` through the `##MS_DefinitionReader##` server role:

```sql
-- in master
CREATE LOGIN [<identity-name>] FROM EXTERNAL PROVIDER;
CREATE USER [<identity-name>] FROM LOGIN [<identity-name>];
ALTER SERVER ROLE ##MS_DefinitionReader## ADD MEMBER [<identity-name>];
```

{% endtab %}

{% tab title="SQL Managed Instance" %}

```sql
-- in master
CREATE LOGIN [<identity-name>] FROM EXTERNAL PROVIDER;
GRANT VIEW ANY DATABASE TO [<identity-name>];
```

{% endtab %}
{% endtabs %}

Do not add `db_datareader` in `master`; no table data is read there.

If you followed an earlier version of this page, `master` already has a contained user for the identity, created `FROM EXTERNAL PROVIDER` without a login. Run `DROP USER [<identity-name>];` in `master` first, then run the statements above, so the user is bound to the new login.
{% endstep %}

{% step %}

#### Grant read access in each database

For each database to be scanned:

{% tabs %}
{% tab title="Azure SQL Database" %}
Azure SQL Database does not support cross-database `USE`, so the grants run per database. To avoid manual repetition, loop over the server's databases from a client with `az` CLI and `sqlcmd`, authenticated as an Entra admin of the server (`-G` uses Entra authentication):

```bash
SERVER='<server-name>'            # without .database.windows.net
RG='<resource-group>'
IDENTITY='<identity-name>'

# once per server: master login, user, and enumeration role
sqlcmd -S "$SERVER.database.windows.net" -d master -G -Q "
  IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = '$IDENTITY')
    CREATE LOGIN [$IDENTITY] FROM EXTERNAL PROVIDER;
  IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = '$IDENTITY')
    CREATE USER [$IDENTITY] FROM LOGIN [$IDENTITY];
  ALTER SERVER ROLE ##MS_DefinitionReader## ADD MEMBER [$IDENTITY];"

# every database on the server: read grants
for DB in $(az sql db list --server "$SERVER" -g "$RG" --query "[?name!='master'].name" -o tsv); do
  sqlcmd -S "$SERVER.database.windows.net" -d "$DB" -G -Q "
    IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = '$IDENTITY')
      CREATE USER [$IDENTITY] FROM EXTERNAL PROVIDER;
    GRANT SELECT TO [$IDENTITY];
    GRANT VIEW DATABASE STATE TO [$IDENTITY];"
done
```

The script is idempotent and safe to re-run; wrap it in an outer loop over `az sql server list` to cover every server. To grant a single database manually, run just the inner statements connected to that database:

```sql
CREATE USER [<identity-name>] FROM EXTERNAL PROVIDER;
GRANT SELECT TO [<identity-name>];
GRANT VIEW DATABASE STATE TO [<identity-name>];
```

{% endtab %}

{% tab title="SQL Managed Instance" %}
Managed Instance supports cross-database scripting, so the grants can be applied to all non-system databases in one pass:

```sql
DECLARE @sql NVARCHAR(MAX);
SET @sql = '';

-- Generate the dynamic SQL for each database
SELECT @sql +=
    'USE [' + name + '];
    CREATE USER [<identity-name>] FROM EXTERNAL PROVIDER;
    GRANT SELECT TO [<identity-name>];
    GRANT VIEW DATABASE STATE TO [<identity-name>];' + CHAR(13)
FROM sys.databases
WHERE state = 0 AND name NOT IN ('tempdb', 'model', 'msdb'); -- Exclude system databases

-- Execute the generated SQL
EXEC sp_executesql @sql;
```

{% endtab %}
{% endtabs %}

These are the same read grants as the [standard setup](/connectors/clouds/azure/azure-sql.md): `SELECT` for table contents, `VIEW DATABASE STATE` for row-count statistics.
{% endstep %}

{% step %}

#### Verify the identity binding

Confirm the database user is bound to the identity Teleskope runs as. In any database where the user was created:

```sql
SELECT name, CONVERT(uniqueidentifier, sid) AS client_id
FROM sys.database_principals
WHERE name = '<identity-name>';
```

`client_id` must equal the UAMI client ID from the identification step. A different value means the user is bound to another principal; drop it and recreate with `WITH OBJECT_ID`.
{% endstep %}

{% step %}

#### Validate on one server, then roll out

Apply the `master` and per-database grants on a single server, trigger a scan, and confirm its databases and tables appear in the Teleskope catalog. Repeat for the remaining servers.
{% endstep %}
{% endstepper %}