powershell - 通过 Powershell 将 Azure 应用注册连接到 Azure SQL 数据库

Question

需要一个 Powershell 脚本在本地运行，该脚本将作为 Azure AD 应用注册登录到 Azure SQL 数据库。我能够获取 App Registration 的访问令牌，但是当我尝试使用它登录数据库时出现错误：

使用“0”参数调用“打开”的异常：“用户 'NT AUTHORITY\ANONYMOUS LOGON' 登录失败。”

我已经在目标数据库中创建了用户并应用了授权。

CREATE USER [App Reg Dave Test] FROM EXTERNAL PROVIDER;
ALTER ROLE [db_datareader] ADD MEMBER [App Reg Dave Test];
ALTER ROLE [db_datawriter] ADD MEMBER [App Reg Dave Test];  

我不确定是否有必要，但我还授予了 SQL 数据库 User_Impersonation 和 App_Impersonation 的 App 注册的 api 权限。这是我的 PS 代码示例。$connection.open() 命令失败。

#-- Intall ADAL.ps
# Import-Module ADAL.PS

#-----------------------------------
#--  Get Token for App Registration
#-----------------------------------

$tenantId = "448b9f7b-9e69-xxxx-xxxx-xxxxxxxxxxxx"
$authority = "https://login.microsoftonline.com/$tenantId/oauth2/v2.0/authorize"
$resourceUrl = "api://123a7216-7b20-yyyy-yyyy-yyyyyyyyyyyy"
$clientId = "123a7216-7b20-yyyy-yyyy-yyyyyyyyyyyy"
$secret = ConvertTo-SecureString -String "X87ZK@NPw=zzzzzzzzzzzzzzzzzzzx/-" -AsPlainText -Force
$response = Get-ADALToken -Authority $authority -Resource $resourceUrl -ClientId $clientId -ClientSecret $secret
$token = $response.AccessToken

#-----------------------------------
#--  Connect to DB
#-----------------------------------

$sqlServerUrl = "dave-sqls2.database.windows.net"
$database = "advworks"
$connectionString = "Server=tcp:$sqlServerUrl,1433;Initial Catalog=$database;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;"
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)

# Add AAD generated token to SQL connection token
$connection.AccessToken = $response.AccessToken

$connection  #-- Output connection object for debugging.

$query = "SELECT @@Servername, @@Version"
$command = New-Object -Type System.Data.SqlClient.SqlCommand($query, $connection)
$connection.Open()

$adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataSet) | Out-Null
$connection.Close()
$dataSet.Tables

标签： powershellazure-active-directoryazure-sql-databaseadal

Answer 1

根据您提供的 PowerShell 脚本，您使用了错误的resourceUrl. 请将资源 url 更新为https://database.windows.net/. 更多详情，请参阅博客。

详细步骤如下。1.创建服务主体

$SecureStringPassword = ConvertTo-SecureString -String "[Enter SPN Password]" -AsPlainText -Force
New-AzureRmADApplication -DisplayName "[Enter name for Application]" -HomePage "https://www.contoso.com/sqldb-spn" -IdentifierUris "https://www.contoso.com/sqldb-spn" -Password $SecureStringPassword -OutVariable app
New-AzureRmADServicePrincipal -ApplicationId $app.ApplicationId

2. 使用 Azure AD admin 通过 SSMS 登录 Azure SQL。然后运行以下命令

CREATE USER [your app name] FROM EXTERNAL PROVIDER;
ALTER ROLE [db_datareader] ADD MEMBER [App Reg Dave Test];
ALTER ROLE [db_datawriter] ADD MEMBER [App Reg Dave Test];

3. 获取访问令牌

$tenantId = "your tenant id"
$authority = "https://login.microsoftonline.com/$tenantId/oauth2/authorize"
$resourceUrl = "https://database.windows.net/"
$clientId = "your application id"
$secret=ConvertTo-SecureString "" -AsPlainText -Force
$response = Get-ADALToken -Authority $authority -Resource $resourceUrl -ClientId $clientId -ClientSecret $secret
$token = $response.AccessToken

4 连接 SQL

$sqlServerUrl = "<your server name>.database.windows.net"
$database = "master"
$connectionString = "Server=tcp:$sqlServerUrl,1433;Initial Catalog=$database;Connect Timeout=30"
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
$connection.AccessToken = $response.AccessToken
$connection.Open()

$query="SELECT name, database_id, create_date  FROM sys.databases ; "
$command = New-Object -TypeName System.Data.SqlClient.SqlCommand($query, $connection)   
$Result = $command.ExecuteScalar()
$Result
$connection.Close()