使用PowerShell和ServicePrincipal的AD用户的Azure SQL授予访问权限

问题描述

我尝试为Azure SQL Server创建一个用户.我想使用AzureAD用户.

I try to create a User for an Azure SQL Server. I want to use the AzureAD users.

这是代码:

$accessToken = $(az account get-access-token --resource https://database.windows.net/ --query accessToken -o tsv --subscription ${data.azurerm_client_config.current.subscription_id})
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = "Data Source = ${azurerm_sql_server.server.fully_qualified_domain_name}; Initial Catalog = ${azurerm_sql_database.sqldb.name}"
$sqlConnection.AccessToken = $accessToken
$sqlConnection.Open()
$sqlCmd = New-Object System.Data.SqlClient.SqlCommand
$sqlCmd.Connection = $sqlConnection
$sqlCmd.CommandText = "CREATE USER [someone.name_domain.com#EXT#@rootdomain.onmicrosoft.com] FROM EXTERNAL PROVIDER"
$sqlCmd.ExecuteNonQuery()

用于生成令牌的azure命令行已使用服务主体进行了身份验证.使用AAD组将服务主体定义为azurerm_sql_active_directory_administrator.我收到以下错误

The azure commandline which is used to generate the token is authenticated using a service principal. The service principal is defined as the azurerm_sql_active_directory_administrator using an AAD-Group. I get the following Error

MethodInvocationException:
Line |
  11 |  $sqlCmd.ExecuteNonQuery()
     |  ~~~~~~~~~~~~~~~~~~~~~~~~~
     | Exception calling "ExecuteNonQuery" with "0" argument(s): "Principal 'someone.name_domain.com#EXT#@rootdomain.onmicrosoft.com' could not be resolved. Error message: ''"

如果我将SqlCommand更改为:SELECT * FROM sys.database_principals.我可以查询结果.因此，我认为身份验证本身不是问题.另一方面，当用我的活动目录用户(属于同一AAD组的一部分)而不是服务主体登录到Azure cli时，CREATE USER ...命令起作用并创建了该用户.

If I change the SqlCommand to: SELECT * FROM sys.database_principals. I can query the results. Therefore I assume the Authentication itself is not the Problem.On the other hand, when logged into the azure cli with my active directory user (which is part of the same AAD-Group) instead of the service principal, the CREATE USER ... Command works and the user is created.

我想以自动化方式运行代码，因此我需要它与我的服务负责人一起工作.有什么可以做的才能完成这项工作吗?

I want to run the code in automation therefore I need it to work with my service principal. Is there anything that can be done to make this work?

推荐答案

Azure SQL数据库暂时不允许服务主体创建用户.

Azure SQL database doesn't allow service principal to create user for now.

但是其他人发现了一些解决方法来解决此问题:

But others have found some workaround to solve the issue:

Microsoft MSFT确认它是正确的，并且官方教程即将发布:

Microsoft MSFT confirm that it's correct and the official tutorial will come soon:

1. 向Azure SQL逻辑服务器分配服务器身份

1. Assign a server identity to the Azure SQL logical server

Set-AzSqlServer -ResourceGroupName-ServerName< Servername> -AssignIdentity

Set-AzSqlServer -ResourceGroupName  -ServerName <Server name> -AssignIdentity

授予服务器身份的目录读取器权限.

Grant the Directory Readers permissions to the server identity.

请参考此博客:

1. 通过服务负责人从外部提供商创建用户
2. https://github.com/MicrosoftDocs/sql-docs/issues/4959

1. CREATE USER FROM EXTERNAL PROVIDER by Service Principal
2. https://github.com/MicrosoftDocs/sql-docs/issues/4959

希望这会有所帮助.

这篇关于使用PowerShell和ServicePrincipal的AD用户的Azure SQL授予访问权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！