Powershell - Connecting to SQL Server using Connect-AzAccount (With MFA)-CodePudding

We have a SQL Server set up (created by me, with my account as the admin account), and using Management Studio, I can access the database just fine. I'm now trying to achieve the same thing through Powershell.I have created my own user using:

CREATE USER [<my ccount>] FROM EXTERNAL PROVIDER

I start by connecting using MFA (and signing in using the pop-up window/MFA):

Connect-AzAccount -SubscriptionId $subscriptionID

This works fine, and returns the expected values of the subscription specified, and my Azure AD Login as account. I can access my KeyVault and pull secrets from there.

I now would like to connect to my SQL Server using the credentials I'm already signed in with within the PowerShell session, and that's where I get stuck.

I tried a ConnectionString including Authentication=Active Directory Integrated, but that throws an error Exception calling "Open" with "0" argument(s): "One or more errors occurred.".

I then tried using a connection string like this: Server=tcp:<server>.database.windows.net,1433;Initial Catalog=<database>;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30, and adding an AccessToken using SqlConn.AccessToken = $(Get-AzAccessToken -ResourceUrl "Https://database.windows.net/").

However, when I then try to Open the connection, I get the error: "Login failed for user '<token-identified principal>'."

Googling led to several SO articles, but none of those use Connect-AzAccount, but the (what I believe to be outdated) Az Account (I.e. Connecting to SQL Server using Powershell with Azure AD MFA).

Other examples I found all add UID and Password, which should not be needed considering I authenticated already.

Is there a way to use the existing Connect-AzAccount session to authenticate against my Azure SQL Server, and if so, what format should the ConnectionString have? I have a feeling I'm very close to a solution, but just cannot seem to actually achieve the opening of the connection.

Thanks in advance.

CodePudding user response：

After reading more and more, I finally stumbled across this thread. @golfalot's answer was exactly what I needed.

So the proper code to initialize the connection is:

# Sign in to Azure
Connect-AzAccount -SubscriptionID $subscriptionID

$connectionString = "Server=<server>.database.windows.net;Initial Catalog=<database>;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30"

$accessToken = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token

$sqlConn = New-Object System.Data.SqlClient.SqlConnection
$sqlConn.ConnectionString = $connectionString
$sqlConn.AccessToken = $accessToken
$sqlConn.Open()