使用Azure AD MFA使用Powershell连接到SQL Server

Question

使用Azure AD MFA使用Powershell连接到SQL Server

sql-serverpowershellauthenticationazure-active-directory

3

我正在尝试使用来自Azure AD的访问令牌连接到我的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数据工作室可能具有超能力，使用另一个帐户而不进行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);

同样的错误：

"连接错误：用户登录失败。

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

- Claudiordgz

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Claudiordgz · Answer 1

我找到了问题，我的问题比我想象的要小得多。

我在这个答案中找到了我的答案：使用PowerShell和ServicePrincipal为AD用户授予Azure SQL访问权限

发生的情况是这一行：

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

返回用双引号包装的访问令牌

$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令牌与SQL Server：

在Azure SQL上配置活动目录管理员
执行类似以下内容的命令以添加您的用户

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];

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

$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()