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
- VS code terminal can't find AWS SAM even though windows terminal can
- How to combine multiple conditions in a PowerShell if-statement
- Can I change Read-Host so it doesn't always end in a colon?
- How to call native XPath functions from Select-Xml?
- How to get all piped in objects at once when using Begin/Process/End blocks?
- Calling a method returned from another method in Powershell
- Powershell -- how to minimize the console the script is running in while sitting in a loop?
- Automation Account Script to delete Stale Devices
- Powershell version of cut -d is very slow on large files, am I missing the fast way to do it?
- how to use "if" statements inside pipeline
- Error When Connecting to Azure from PowerShell
- Execute powershell in x64 from MSBuild
- Powershell 5 and 7 processes double quotes in different way when running Cygwin
- SQL Package extract command is failing in PowerShell script
- Office 364 Audit logs: Search-UnifiedAuditLog is not recognized as the name of a cmdlet
- Empty path name is not legal - DTEXEC with Powershell
- Cannot remove PowerShell environment variables
- Is automating App Registration on Azure possible through ARM Template on the User's tenant?
- Powershell error "value can not in the type System Management Automation LanguagePrimitives+InternalPSCustomObject converted"
- Diskspace Report HTML Text Color Output
- Issue with Azure Automation Account and Key Vault Certificate Retrieval
- How to display current virtual environtment in python in oh-my-posh?
- How do I get a list of Service Principals in Azure via the CLI
- Handling special characters in password string
- What is the right way to check a switch parameter
- How to make ($Line_1`n$Line_2) work in a CMD script?
- .ps1 script reported as "Missing closing '}'" in .bat file script
- APPAP308E - Invalid process path. Full path is required
- How do I verify that a SMB server requires signing, preferably with Powershell?
- Import-Excel: Expand imported Excel columns to become individual row entries