azure 允许Active Directory组中的用户使用BICEP访问SQL数据库

rryofs0p 于 2023-08-07 发布在其他

关注(0)|答案(1)|浏览(101)

好了，我成功地在Azure中使用Bicep创建了一个SQL数据库，如下所示：

param appName string = 'core-database'
param environment string = 'dev'

param location string = resourceGroup().location

resource databaseServer 'Microsoft.Sql/servers@2022-02-01-preview' = {
  name: '${appName}-sqlserver-${environment}'
  location: location
  properties: {
    administrators: {
      administratorType: 'ActiveDirectory'
      azureADOnlyAuthentication: true
      principalType: 'Group'
      login: 'Developers'
      sid: 'my-groups-object-id-here'
      tenantId: subscription().tenantId
    }
  }
}

resource database 'Microsoft.Sql/servers/databases@2022-02-01-preview' = {
  parent: databaseServer
  name: '${appName}-sqldb-${environment}'
  location: location
  sku: {
    name: 'Basic'
    size: 'Basic'
    tier: 'Basic'
  }
}
 
resource databaseServerFirewall 'Microsoft.Sql/servers/firewallRules@2021-11-01-preview' = {
  name: 'Database server firewall'
  parent: databaseServer
  properties: {
    startIpAddress: '0.0.0.0'
    endIpAddress: '0.0.0.0'
  }
}

字符串
我的用户100%肯定在有问题的组中。
当我转到Azure中的查询编辑器并单击“继续为”时，我收到此错误：

Login failed for user '<token-identified principal>'. The server is not currently configured to accept this token.

型
没有很好的文档，我真的很想让Active Directory控制对这个数据库的访问。

Azure

来源：https://stackoverflow.com/questions/76701321/allow-users-in-an-active-directory-group-to-access-a-sql-database-using-bicep

1条答案

按热度按时间

eh57zj3b1#

我也遇到了类似的问题。要允许Active Directory组中的用户使用BICEP访问SQL数据库，您需要通过以下查询在Azure SQL数据库中为AAD组创建专用用户：

CREATE USER [Group_Name] FROM EXTERNAL PROVIDER;

字符串
要执行此操作，您需要使用Azure AD管理员帐户登录SQL。

因此，您需要使用Azure AD admin和以下模板部署SQL Server：

param appName string = 'core-database'
param environment string = 'dev2'

param location string = resourceGroup().location

resource databaseServer 'Microsoft.Sql/servers@2022-02-01-preview' = {
  name: '${appName}-sqlserver-${environment}'
  location: location
  properties: {
    administrators: {
      administratorType: 'ActiveDirectory'
      azureADOnlyAuthentication: true
      principalType: 'User'
      login: 'Azure AD admin'
      sid: 'Azure AD admin object id'
      tenantId: subscription().tenantId
    }
  }
}

resource database 'Microsoft.Sql/servers/databases@2022-02-01-preview' = {
  parent: databaseServer
  name: '${appName}-sqldb-${environment}'
  location: location
  sku: {
    name: 'Basic'
    size: 'Basic'
    tier: 'Basic'
  }
}

resource databaseServerFirewall 'Microsoft.Sql/servers/firewallRules@2021-11-01-preview' = {
  name: 'Database server firewall'
  parent: databaseServer
  properties: {
    startIpAddress: '0.0.0.0'
    endIpAddress: '0.0.0.0'
  }
}

型
然后使用Ad admin登录SQL数据库，在数据库中执行上述SQL查询。

的数据
然后您可以手动将AD Admin更改为group