'Authenticate with Azure AD account while using Read-SqlTableData to read AzSQL tables

What other properties does one need to add to the ConnectionContext while connecting to AzSQL using an AzAD account?

$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') "<>.database.windows.net"
$srv.ConnectionContext.LoginSecure = $false
$srv.ConnectionContext.Authentication = [Microsoft.SqlServer.Management.Common.SqlConnectionInfo+AuthenticationMethod]::ActiveDirectoryPassword
$srv.ConnectionContext.Login = "<>@<>.onmicrosoft.com"
$srv.ConnectionContext.Password = "<>"
$srv.ConnectionContext.Connect()

Error : MethodInvocationException: Exception calling "Connect" with "0" argument(s): "Failed to connect to server <>.database.windows.net."

The ActiveDirectoryPassword method works fine from SSMS. I'm trying to run Read-SqlTableData instead of invoking a TSQL query.

Read-SqlTableData -InputObject $srv.Databases["TestDB"].Tables["TestTable"] -TopN 5

Expecting screenshot or screenclip GIF that the answer code is working for an AzAD userid on AzSQL using Read-SqlTableData.

Solution 1:^[1]

As discussed in person, this is just because the Read-SqlTableData has not been updated (yet) to support an -AccessToken parameter.

I've already had a few requests for other cmdlets as well... so I'll try to get it as soon as I can.

Solution 2:^[2]

Please make sure to login as administrator .The user must have azure ad admin role for server to connect.

And make sure the user has diretory readers role from azure portal IAM access control.

Also Ensure that you've assigned an AAD admin for the SQL Server. Try to create a service principal in azure ad if not already present and then give db_reader or db_writer or db_owner permissions granted .

CREATE USER [user or msi] FROM EXTERNAL PROVIDER;

ALTER ROLE [db_datareader] ADD MEMBER <Azure_AD_principal_name>  --Grant the user db_datareader permissions on that database
GO

or write permission if required

ALTER ROLE [db_datawriter] ADD MEMBER <Azure_AD_principal_name>;

Or owner permission if required

ALTER ROLE [db_owner] ADD MEMBER <Azure_AD_principal_name>

Or Create Azure AD users in azure sql DB using service principals - Azure SQL Database | Microsoft Docs

Please check below references:

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Matteo Taveggia
Solution 2

'Authenticate with Azure AD account while using Read-SqlTableData to read AzSQL tables

Solution 1:[1]

Solution 2:[2]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]