Connecting to SQL Server using Powershell with Azure AD MFA

Question

I am trying to connect to my Azure SQL instance using an Access Token from Azure AD. I was following this tutorial over here: https://medium.com/microsoftazure/deploying-a-dacpac-to-azure-with-azure-pipelines-and-managed-identity-89703d405e00

But something is not working right in the approach.

The first thing was to make sure my user was setup in the database via:

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

Which is the same process from this answer: https://stackoverflow.com/a/62161471/1963929

And then I tested both SQL Server Management Studio and Azure Data Studio, both worked perfectly.

But when I try exactly the same thing in Powershell it does not work, all I get is the dread Login failed for user '<token-identified principal>'.

Here’s what I tried

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

The error that I receive when connecting to my db is the following

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

Then I thought “maybe I’m using the wrong settings” so I tried the using a client id that my app is using to connect to the same db. And this time I tested multiple scopes: none, .default, and 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

So I thought maybe Azure Data Studio has superpowers, and used another account in there without doing CREATE USER and I got the right error

And this error proves me that CREATE USER is necessary, but it does not explain why I can't do this via Powershell.

I also tried Node and Tedious like the following:

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

Same error:

"ConnectionError: Login failed for user ''.

Does anyone know what I'm doing wrong?

Answer 1

I found the issue, and my problem is more minuscule than I thought.

I found my answer in this answer: Azure SQL Grant Access for AD User using PowerShell and ServicePrincipal

What's happening is this line:

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

returns an Access Token wrapped in Double Quotes

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

That -o tsv at the end will trim the double quotes from the output.

On TediousJS the problem was that I was doing

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

What I needed to be doing is:

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

So the steps you need to do to use the Azure CLI token with SQL Server are the following:

1. Configure an Active Directory Admin on Azure SQL
2. Execute something like the following to add your user

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

3. Confirm you can connect with Azure AD with SSMS or Azure Data Studio
4. Try the following:

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