Connecting to SQL Server using Powershell with Azure AD MFA

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