powershellazure-active-directoryazure-sql-databasedatabase-connectionsmo
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
An AccessToken parameter has been added to the cmdlet in version 22.0.59 of the SqlServer Module.
Read-SqlTableData -AccessToken (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token `
-ServerInstance <>.database.windows.net -SchemaName dbo -DatabaseName <> -TableName <> -TopN 3 |FT
Output
- What is the fastest\best way to get file names from a folder using PowerShell?
- Collections and the Powershell Pipeline
- How to fix/speed up powershell script?
- Extracting specific data from a string with regex and Powershell
- Encryption PowerShell script generated by SQL Server Management Studio executes but doesn't work
- How do I assign values to variables in PowerShell ThreadJobs?
- Running 7-Zip from within a Powershell script
- Powershell extract custom line numbers using Get-Content
- Read individual key presses in PowerShell
- Set-PnPListItem Column '{my column name}' does not exist. It may have been deleted by another user
- Security Warning when running scripts - Unblock-File not unblocking file
- What is an equivalent of *Nix 'cut' command in Powershell?
- PowerShell equivalent to grep -f
- Multiple foreground colors in PowerShell in one command
- Pipe complete array-objects instead of array items one at a time?
- How to return a parameterized code block in PowerShell
- Writing a secret from AzureKeyVault in a Azure DevOps Pipeline to a file with powershell
- Change the default terminal in Visual Studio Code
- Syntax error when executing Invoke-DDBQuery to fetch dynamodb record using Powershell
- Is it possible to open a Windows Explorer window from PowerShell?
- The mock in my pester test keeps calling Connect-AzAccount
- qwinsta /server:somesrv equivalent in Powershell?
- Shortcut to pull up all file explorer windows that are open
- Convert Unix time with PowerShell
- How to extract a substring from EDL line, between 2 sequence of characters
- Querying nested JSON files in Powershell
- How to create string of foreach results
- Powershell get timezone with DST
- How to replace all Active Directory account photos?
- Variable in WPF Binding with PropertyChanged