sql-server - 通过 Azure AD MFA 使用 Powershell 连接到 SQL Server

Question

我正在尝试使用来自 Azure AD 的访问 token 连接到我的 Azure SQL 实例。我在这里关注本教程:https://medium.com/microsoftazure/deploying-a-dacpac-to-azure-with-azure-pipelines-and-managed-identity-89703d405e00

但该方法有些问题。

第一件事是确保我的用户是通过以下方式在数据库中设置的:

CREATE USER [myemail@email.com] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA=[dbo]

这与此答案的过程相同:https://stackoverflow.com/a/62161471/1963929

然后我测试了 SQL Server Management Studio 和 Azure Data Studio，两者都运行良好。

但是当我在 Powershell 中尝试完全相同的东西时它不起作用，我得到的只是恐惧 Login failed for user '<token-identified principal>'.

这是我尝试过的

$conn = New-Object System.Data.SqlClient.SQLConnection

$conn.ConnectionString = "Server=tcp:azure-sql.database.windows.net,1433;Initial Catalog=default;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30"

$conn.AccessToken = $(az account get-access-token --resource=https://database.windows.net/ --query accessToken)

$conn.Open()

连接到我的数据库时收到的错误如下

MethodInvocationException: Exception calling "Open" with "0" argument(s): "Login failed for user ''."

然后我想“也许我使用了错误的设置”所以我尝试使用我的应用程序用来连接到同一个数据库的客户端 ID。这次我测试了多个范围:无，.default , 和 user_impersonation .

$clientid = "azure-data-studio-client-id"
$request = Invoke-RestMethod -Method GET -Uri "https://login.microsoftonline.com/organizations/oauth2/v2.0/devicecode" -Body @{client_id=$clientid; scope="https://database.windows.net/user_impersonation"} -ContentType "application/x-www-form-urlencoded"
$request.message


$tokens = Invoke-RestMethod -Method POST -Uri "https://login.microsoftonline.com/organizations/oauth2/v2.0/token" -Body @{client_id=$clientid; grant_type="urn:ietf:params:oauth:grant-type:device_code"; code = $request.device_code} -ContentType "application/x-www-form-urlencoded"
$accesstoken = $tokens.access_token

所以我想也许 Azure Data Studio 有超能力，并且在没有做 CREATE USER 的情况下使用了另一个帐户我得到了正确的错误

这个错误向我证明 CREATE USER是必要的，但它没有解释为什么我不能通过 Powershell 执行此操作。

我也像下面这样尝试了 Node 和 Tedious:

      const dbConfig = {
        authentication: {
          type: "azure-active-directory-access-token",
          options: {
            token: token
          }
        },
        server: getDatabasePerEnvironment(environment),
        database: databaseName,
        options: {
          trustServerCertificate: false,
          encrypt: true,
          port: 1433
        }
      };
      const connection = new tedious.Connection(dbConfig);

同样的错误:

"ConnectionError: Login failed for user ''.

有人知道我做错了什么吗？

Answer 1

我发现了问题，而且我的问题比我想象的要小。

我在这个答案中找到了我的答案:Azure SQL Grant Access for AD User using PowerShell and ServicePrincipal

这一行发生了什么:

$conn.AccessToken = $(az account get-access-token --resource=https://database.windows.net/--query accessToken)

返回一个用双引号括起来的访问 token

$conn.AccessToken = $(az account get-access-token --subscription $subscription --resource https://database.windows.net --query accessToken -o tsv)

末尾的 -o tsv 将从输出中删除双引号。

在 TediousJS 上，问题是我在做

const tokenPayload = JSON.parse(execSync("az account get-access-token").toString());

我需要做的是:

const tokenPayload = execSync(
    "az account get-access-token  --subscription YOUR-SUBSCRIPTION --resource https://database.windows.net  --query accessToken -o tsv"
  ).toString();

因此，将 Azure CLI token 与 SQL Server 结合使用需要执行的步骤如下:

1. 在 Azure SQL 上配置 Active Directory 管理员
2. 执行类似下面的操作来添加您的用户

CREATE USER [youremail@mail.com] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA=[dbo]
ALTER ROLE db_datareader ADD MEMBER [youremail@mail.com];
ALTER ROLE db_datawriter ADD MEMBER [youremail@mail.com];
ALTER ROLE db_ddladmin ADD MEMBER [youremail@mail.com];

1. 确认您可以使用 SSMS 或 Azure Data Studio 连接到 Azure AD
2. 尝试以下操作:

$conn = New-Object System.Data.SqlClient.SQLConnection 
$conn.ConnectionString = "Server=yourserver.database.windows.net;Initial Catalog=Subledger;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30"

$conn.AccessToken = $(az account get-access-token --subscription YOUR-SUBSCRIPTION --resource https://database.windows.net --query accessToken -o tsv)

$conn.Open()