1

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.

enter image description here

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

2 Answers 2

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.

7
  • I'm getting the same error while connecting with the first and the second method with the username specified in the email address format. MethodInvocationException: Exception calling "Connect" with "0" argument(s): "Failed to connect to server <>.database.windows.net." See the screenshot. I couldn't use the 3rd method since I'm not logged in as the same AzAD user. Are you able to use the first two methods to connect to AzSQL with an AzAD account? Apr 6, 2022 at 0:17
  • 1
    @AyanMullick - I have updated my answer based on testing today. Apr 6, 2022 at 21:04
  • Thank you for the update. I am able to set the AuthenticationMethod in $srv. However, I'm getting the same error using the first two methods as before. Failed to connect to server See screenshot Apr 6, 2022 at 21:47
  • 1
    @AyanMullick then your issue is likely nothing to do with PowerShell and most likely be related to how you have configured your Azure SQL Database. Have you setup a Active Directory Admin on your instance? Did you create your logins with EXTERNAL PROVIDER? Apr 6, 2022 at 21:54
  • 1
    You need to create your logins with the EXTERNAL PROVIDER option, otherwise you cannot use AZ AD to authenticate your users. Apr 6, 2022 at 22:17
0

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();
}
3
  • This method of connection, using a connection string, works if you want to run TSQL commands using Invoke-Sqlcmd. However, the method in the question is the only way to connect if one wants to run native PowerShell queries. The SMO object is the only way to work with Read-SQLTableData. Apr 4, 2022 at 12:53
  • The connection string formation is the issue I see. See the Authentication attribute. The question asked is showing how one might login using purely a sql login. The example I am showing above shows the difference, plus the link I provided shows many other connection strings.
    – BAllen
    Apr 5, 2022 at 20:20
  • The connection string creation process you mentioned works fine. It creates the connection, and one is able to use it with Invoke-Sqlcmd but not with Read-SqlTableData Apr 5, 2022 at 22:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.