powershell - 用于在 Azure Active Directory 中创建安全组以管理对 Azure SQL 的访问的 Powershell 脚本
问题描述
下面是在 Azure AD 中创建组然后将 SQL 角色分配给组的脚本。该脚本依赖于 ODBC 17 和 sqlcmd 实用程序版本 15，这使得在 Azure devops 管道中使用它有点困难。我想知道是否有人知道我可以使用的任何替代 powershell cmdlet 在脚本中针对 SQL 运行查询。我尝试了 invoke-sqlcmd 但在尝试使用 onmicrosoft 帐户进行身份验证时遇到了问题。当使用您使用 SQL 数据库创建的 sql admin 用户时，我可以运行命令来设置目标数据库的权限，但无法运行在主数据库中创建组（来自外部提供者）的 sql 查询。所以我有点卡住了。想法是将脚本添加到部署管道并让它自动创建 AAD 组，这将允许运营团队管理用户对数据库的访问。请帮助 :-) <# .SYNOPSIS 创建用于控制对 SQL Server 和数据库资源的访问的 AAD 组
.DESCRIPTION
Based on the SQL Server and Database supplied as a input the script will create the required AAD groups required for every Azure SQL Server
.INPUTS
sqlServerName: Name of the sql server (IAAS) or the sql server instance (PAAS)
sqlDatabaseName: Name of the database
custom_vars_envResourceGroupName : Resource group the sql server resides in
sqlAdminUser:
sqlAdminPassword
ProjectId
EnvironmentId
.OUTPUTS
Names of the AAD groups that was created
.NOTES
Script assumes that it is running in a session that is already connected to Azure RM TODO: Test least privilage
Script assumes that it is running in a session that is already connected to Azure RM with contributor RBAC rights on the resource group TODO: Test least privilage
If you are running this manually you need to add the sqlAdmin user into the Server admim group**** TODO: Still need to get the this automated to used the Azure DevOps Service Principle.
Dont try this with a microsoft account it needs a AAD account to work on the db authenication
If the qlAdminPassword is passed using clear text dont user "$" or "&" or "/" as special characters in the password.
.EXAMPLE
$sqlAdminPasswordnotsecure = "lhfuhwe48234#E"
$sqlAdminSecurePassword = ConvertTo-SecureString $sqlAdminPasswordnotsecure -AsPlainText -Force
#.\sqlAADGroupsCreator.ps1 `
-custom_vars_envResourceGroupName "<Resource Group>"
-sqlServerName "<Servername>"
-sqlAdminUser "<eg sqlAdminUser@companydirectory.onmicrosoft.com>"
-sqlAdminSecurePassword "<complex password>"
-sqlDatabaseName "<database name>"
-ProjectId "<Name of project>"
-EnvironmentId "<environment>"
#>
#region parameters
Param(
[Parameter(Mandatory = $True)][string]$custom_vars_envResourceGroupName,
[Parameter(Mandatory = $True)][string]$sqlServerName,
[Parameter(Mandatory = $True)][string]$sqlAdminUser,
[Parameter(Mandatory = $True)][securestring]$sqlAdminSecurePassword,
[Parameter(Mandatory = $True)][string]$sqlDatabaseName,
[Parameter(Mandatory = $True)][string]$ProjectId,
[Parameter(Mandatory = $True)][string]$EnvironmentId
)
$ErrorActionPreference = "Stop"
#endregion parameters
#region secureStringConversion
#Convert secure string back to plain text to be used in SQL connection
function Get-PlainText() {
[CmdletBinding()]
param
(
[parameter(Mandatory = $true)]
[System.Security.SecureString]$SecureString
)
BEGIN { }
PROCESS {
$bstr = [Runtime.InteropServices.Marshal]::SecureStringToBSTR($SecureString);
try {
return [Runtime.InteropServices.Marshal]::PtrToStringBSTR($bstr);
}
finally {
[Runtime.InteropServices.Marshal]::FreeBSTR($bstr);
}
}
END { }
}
#Call The function
$sqlAdminPassword = get-plaintext -SecureString $sqlAdminSecurePassword
#endregion secureStringConversion
#region manualParameter
<#Variables that can be used during testing or running manual
$custom_vars_envResourceGroupName = "MATA-APP-0-0-COMMON"
$sqlServerName = "<server name>"
$sqlAdminUser = "<SqlAdmin@educationtestgovtnz.onmicrosoft.com>"
$sqlAdminPassword = "<complex password>"
$sqlDatabaseName = "<database name>"
$EnvironmentId = "<environment variable"
$ProjectId ="<application name or abreviation>"
#>
#endregion manualParameter
#region aadGroupsCreation.
#Build up database server Url
$sqlServerURL = $sqlServerName + ".database.windows.net"
###Create AAD Groups based on resource group name
$sqlAdminGroupName = $sqlReadGroupName = $sqlDbContributorGroupName = $sqlDbReadGroupName = ""
$sqlAdminGroupName = "AL SQL " + ($ProjectId.ToUpper()) + " " + ($EnvironmentId.ToUpper()) + " Admin"
$sqlReadGroupName = "AL SQL " + ($ProjectId.ToUpper()) + " " + ($EnvironmentId.ToUpper()) + " Read"
$sqlDbContributorGroupName = "AL DB " + ($sqlServerName.ToUpper()) + " " + ($sqlDatabaseName.ToUpper()) + " Contributor"
$sqlDbReadGroupName = "AL DB " + ($sqlServerName.ToUpper()) + " " + ($sqlDatabaseName.ToUpper()) + " Read"
###Create the Groups in Azure AD if they dont exist then assign access roles to Resource Group
#SQL LEVEL ADMIN And DB OWNER#
#Create new group for Owner
if ($sqlAdminGroupName -ne ((Get-AzureRmADGroup -SearchString $sqlAdminGroupName).displayname)) {
#$slqAdminGroupDiscription = "Server Admin and database owner access to "+ $sqlServerName
New-AzureRmADGroup `
-DisplayName $sqlAdminGroupName `
-MailNickName "NotSet"
}
#Update group membership of sqlAdmin group
$groupUser = Get-AzureRmADGroupMember -GroupDisplayName $sqlAdminGroupName
if ($sqlAdminUser -ne ($groupUser.UserPrincipalName)) {
Add-AzureRmADGroupMember -MemberUserPrincipalName $sqlAdminUser -TargetGroupDisplayName $sqlAdminGroupName
}
#DB LEVEL CONTRIBUTOR###
#Create new group for Contributor
if ($sqlDbContributorGroupName -ne ((Get-AzureRmADGroup -SearchString $sqlDbContributorGroupName).displayname)) {
#$sqlContributorGroupDiscription = "Contributor access to "+ $sqlDatabase
New-AzureRmADGroup `
-DisplayName $sqlDbContributorGroupName `
-MailNickName "NotSet"
}
#SQL LEVEL READER###
#Create new group for ReadOnly
if ($sqlReadGroupName -ne ((Get-AzureRmADGroup -SearchString $sqlReadGroupName).displayname)) {
#$RGReadOnlygroupDiscription = "Read Only access to "+ $sqlDatabase
New-AzureRmADGroup `
-DisplayName $sqlReadGroupName `
-MailNickName "NotSet"
}
#DB LEVEL READER###
#Create new group for ReadOnly
if ($sqlDbReadGroupName -ne ((Get-AzureRmADGroup -SearchString $sqlDbReadGroupName).displayname)) {
#$RGReadOnlygroupDiscription = "Read Only access to "+ $sqlDatabase
New-AzureRmADGroup `
-DisplayName $sqlDbReadGroupName `
-MailNickName "NotSet"
}
#endregion aadGroupsCreation.
#region setDbPermissions
#Enable Azure AD Administrator on the SQL server (SQL / AAD Integration)
$sqlAdminGroupNameId = (Get-AzureRmADGroup -DisplayName $sqlAdminGroupName).Id
Set-AzureRmSqlServerActiveDirectoryAdministrator -ResourceGroupName $custom_vars_envResourceGroupName -ServerName $sqlServerName -DisplayName $sqlAdminGroupName -ObjectId $sqlAdminGroupNameID
#SQL Query to create the AAD groups in SQL master and map the appropriate roles to the AAD Groups
$createSQLUsersQuery = ""
$createSQLUsersQuery = @"
CREATE USER [$sqlDbContributorGroupName] FROM EXTERNAL PROVIDER;
CREATE USER [$sqlReadGroupName] FROM EXTERNAL PROVIDER;
CREATE USER [$sqlDbReadGroupName] FROM EXTERNAL PROVIDER;
"@
#Connect to SQL sever and configure the SQL Sever level logins
Sqlcmd -S $sqlServerURL -d master -U $sqlAdminUser -P $sqlAdminPassword -G -Q $createSQLUsersQuery
#SQL Query to create the AAD groups in targeted sqldatabase
$createSQLDBUsersQuery = ""
$createSQLDBUsersQuery = @"
CREATE USER [$sqlAdminGroupName] FROM EXTERNAL PROVIDER;
CREATE USER [$sqlDbContributorGroupName] FROM EXTERNAL PROVIDER;
CREATE USER [$sqlReadGroupName] FROM EXTERNAL PROVIDER;
CREATE USER [$sqlDbReadGroupName] FROM EXTERNAL PROVIDER;
"@
#Connect to SQL sever and configure the SQL database level logins
Sqlcmd -S $sqlServerURL -d $sqlDatabaseName -U $sqlAdminUser -P $sqlAdminPassword -G -Q $createSQLDBUsersQuery
#SQL Query to set the database level permissions in targeted sqldatabase
$setSQLDBPermissionsQuery = ""
$setSQLDBPermissionsQuery = @"
ALTER ROLE db_owner ADD MEMBER [$sqlAdminGroupName]
ALTER ROLE db_datareader ADD MEMBER [$sqlReadGroupName]
ALTER ROLE db_datawriter ADD MEMBER [$sqlDbContributorGroupName]
ALTER ROLE db_datareader ADD MEMBER [$sqlDbContributorGroupName]
ALTER ROLE db_datareader ADD MEMBER [$sqlDbReadGroupName]
"@
#Connect to SQL sever and configure the SQL database level permissions
Sqlcmd -S $sqlServerURL -d $sqlDatabaseName -U $sqlAdminUser -P $sqlAdminPassword -G -Q $setSQLDBPermissionsQuery
#endregion setDbPermissions
解决方案
我认为您的问题是您试图以 SQL 管理员而不是为您的 Azure SQL 数据库实例配置的 AD 管理员身份运行以下 T-SQL。
若要创建基于 Azure AD 的包含数据库用户（拥有数据库的服务器管理员除外），请以至少具有 ALTER ANY USER 权限的用户身份连接到具有 Azure AD 身份的数据库。然后使用以下 Transact-SQL 语法：
CREATE USER <Azure_AD_principal_name> FROM EXTERNAL PROVIDER;
请参阅此文档，因为它是为 Azure SQL 数据库（单实例/弹性池）、托管实例和数据仓库配置 Azure AD 的主要文档。
至于 PowerShell cmdlet，请注意AzureRM.sql（支持到 2020 年）已被弃用，而Az.sql是新模块。有关 Azure CLI（链接）的更多信息。
对于自动化场景，您需要从用户名和安全字符串创建凭据：
$passwd = ConvertTo-SecureString use a secure password here-AsPlainText -Force $pscredential = New-Object System.Management.Automation.PSCredential('服务主体名称/id', $passwd) Connect-AzAccount -ServicePrincipal -Credential $pscredential -TenantId $tenantId
$params = @{ 'Query' = 'CREATE USER FROM EXTERNAL PROVIDER' 'ConnectionString' = 'Data Source=MYSERVER;Initial Catalog=MyDatabase;Integrated Security=True' }
调用-Sqlcmd @params
推荐阅读
- python - 为什么 CSV 只用浮动数据写入最后一个数据？
- python - 如何根据熊猫python中的两列值进行分组和计数
- r - 为什么 max_n 没有记录在 base::print 文档中？
- c# - 我正在制作一个简单的登录服务来练习，如何将我的用户列表传递给我的创建用户方法
- command-line - PHP 脚本适用于浏览器，但不适用于 Windows Server 任务计划程序或 CMD/PS
- linux - 为什么我不能执行复制到容器中的二进制文件？
- html - 如何摆脱保证金
- react-big-calendar - 有没有办法通过拖放添加禁用 React Big Calendar 上特定视图的事件大小调整和拖动？
- webstorm - 在 WebStorm 中，有没有办法自动将语言注入到特定函数的参数中？
- gensim - word2vec 推荐系统 KeyError：“单词 '21883' 不在词汇表中”