How to fetch Azure AD group and use its object ID as SQL Server admin SID?

Question

Pulumi · Accepted Answer

In order to fetch Azure AD group and use its object ID as SQL Server admin SID, below is a Pulumi program in TypeScript:

```typescript
import * as pulumi from "@pulumi/pulumi";
import * as azure from "@pulumi/azure-native";
import * as azuread from "@pulumi/azuread";

// provisioning an Azure AD group
const adGroup = new azuread.Group("myGroup", {
    displayName: "My Group",
    //additional parameters
});

// creating Azure SQL Server
const sqlServer = new azure.sql.Server("mySqlServer", {
    resourceGroupName: "resourceGroupName",
    serverName: "myservername",
    administratorLogin: "sqladmin",
    administratorLoginPassword: "Enyf!dsjk$459745",
    location: "East US",
    version: "12.0",
});

// assigning the Azure AD Group to the Azure SQL Server Admin
const serverADAdmin = new azure.sql.ServerAzureADAdministrator("adAdmin", {
    administratorType: "ActiveDirectory",
    login: adGroup.displayName, // using the display name of the AD group as login
    sid: adGroup.id, // using the Object ID of the group as SID
    serverName: sqlServer.name,
    resourceGroupName: "resourceGroupName",
});
```

This program first creates an Azure AD group. Then an Azure SQL Server is created. Finally, the created AD group is assigned as Azure AD admin for the SQL Server.

The [`azuread.Group`](https://www.pulumi.com/registry/packages/azuread/api-docs/group/) resource is used to create an Azure AD group. The `displayName` property is the display name for the group.

The [`azure.native.sql.Server`](https://www.pulumi.com/registry/packages/azure-native/api-docs/sql/server/) resource is used to create an Azure SQL Server. It requires details like the resource group it needs to be created on, the server name, location and version of the server.

Lastly, [`azure-native.sql.ServerAzureADAdministrator`](https://www.pulumi.com/registry/packages/azure-native/api-docs/sql/serverazureadadministrator/) is used to assign the Azure AD group as Azure AD admin to the SQL Server. It uses properties like `administratorType`, `login`, `sid` (which is the AD group Object ID), `serverName`, and `resourceGroupName`.