Error raised When I Connecting to azure sql database with AAD Authentication using Powershell

Question

When I Connecting to azure sql  database with AAD Authentication using Powershell, Error raised as below. code and error as below . but i can login sql database with  the aad account using ssms. thanks for you help .

 

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

$conn.ConnectionString ="Server=tcp:sql-hase-hermes-dev-02.database.windows.net,1433;Initial Catalog=sql-db-hase-hermes-dev-02;Persist Security Info=False;User ID=******@noexternalmail.hsbc.com;Password=xxxxxxxx MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication='Active Directory Password';";

$conn.Open()

 Exception calling "Open" with "0" argument(s): "One or more errors occurred."

At line:1 char:1

• $conn.Open()

• 
      + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
  
      + FullyQualifiedErrorId : AggregateException
  
  

Answer 1

Hello Brian D H ZHANG

Try following approach:

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

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

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

1. Confirm you can connect with Azure AD with SSMS or Azure Data Studio
2. Use the following code:

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

https://stackoverflow.com/questions/66381928/connecting-to-sql-server-using-powershell-with-azure-ad-mfa