How can one authenticate with AzAD while using Read-SqlTableData to read AzSQL tables

Question

How should one specify the User ID while creating the $sqlcc object when using Read-SqlTableData to read a table in AzSQL with an Azure account?

$sqlcc = new-object ('System.Data.SqlClient.SqlConnection') "Data Source= <>.database.windows.net;User ID=<>@<>.onmicrosoft.com;Password=<>"
$sc    = new-object ('Microsoft.SqlServer.Management.Common.ServerConnection') $sqlcc
$srv   = new-object ('Microsoft.SqlServer.Management.Smo.Server') $sc
$db    = $srv.Databases["TestDB"]                 
$table = $db.Tables["TestTable"]                
 
Read-SqlTableData -TopN 10 -InputObject $table

The code above is working for SQL ids but not for Azure ids. Specifying the User ID like <>@<>.onmicrosoft.com or <User Objectid>@<Tenantid> causes the $srv object to be blank.

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

Answer 1

You need to make sure you set your connection context before performing any other action. From the documentation for ServerConnection, and AuthenticationMethod:

Authentication The authentication type of the connection

LoginSecure
If set to true, Windows integrate security is used and Login and Password are ignored. If not set, Sql Server Authentication is used. The authentication type of the connection

When choosing an authentication method, you must either choose ActiveDirectoryIntegrated or ActiveDirectoryPassword. ActiveDirectoryIntegrated will pick up the account executing the PowerShell and ActiveDirectoryPassword will allow you to specify the exact account, as in the examples below. If you use ActiveDirectoryIntegrated there is no need to include the lines that specify the Login and Password:

Example:

$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 = "***"
$srv.ConnectionContext.Password = "***"
$srv.ConnectionContext.Connect()

$db    = $srv.Databases["TestDB"]                 
$table = $db.Tables["TestTable"]                

Read-SqlTableData -TopN 10 -InputObject $table

It is highly recommended to use a credential object though, especially if you want to execute as the user who is running the PowerShell (beneficial for schedule tasks etc...):

$cred = Get-Credential
$srv.ConnectionContext.LoginSecure = $false
$srv.ConnectionContext.Authentication = [Microsoft.SqlServer.Management.Common.SqlConnectionInfo+AuthenticationMethod]::ActiveDirectoryPassword
$srv.ConnectionContext.Login = $cred.username
$srv.ConnectionContext.Password = $cred.GetNetworkCredential().Password
$srv.ConnectionContext.Connect()

Also note, in the examples above I access the ConnectionContext directly from the Server object. I do this to remind myself that I am setting the context, you can also set the same properties directly from the ServerConnection like the example below:

$cred = Get-Credential
$sc.LoginSecure = $false
$sc.Authentication = [Microsoft.SqlServer.Management.Common.SqlConnectionInfo+AuthenticationMethod]::ActiveDirectoryPassword
$sc.Login = $cred.username
$sc.Password = $cred.GetNetworkCredential().Password

Whether you choose to do it on the ConnectionContext or the ServerConnection is a matter of style, they are both actually the same instance of the same object.

CAVEAT : I cannot actually test the above end-to-end as in my company we use multi-factor-authentication and so the above will not work with this enabled. However, this method will connect with SQL Server and AZ AD to verify that I need MFA.

Answer 2

I would visit https://learn.microsoft.com/en-us/sql/connect/ado-net/sql/azure-active-directory-authentication as there are a lot of different methods to connect depending on your circumstances.

But this might be a good example for you to follow. Ignore that it’s in c#. Pay special attention to the connectionString. Note the “Authentication” attribute. There are many other examples in the link above with other attributes to try:

// Use your own server, database, user ID, and password.
string ConnectionString = @"Server=demo.database.windows.net; Authentication=Active Directory Password; Database=testdb; User [email protected]; Password=***";

using (SqlConnection conn = new SqlConnection(ConnectionString)) {
conn.Open();
}