search

microsoft / SQLServerPSModule

This repo is the home of SQL Server PowerShell Module development.
MIT License
45 stars 1 forks source link

Make Read-SQLTableData AzureAD access token aware #1

Closed Ayanmullick closed 1 year ago

Ayanmullick commented 2 years ago

Currently, one cannot use an Azure context to read tables from an Azure SQL database. Using Microsoft.Data.SqlClient.SqlConnection doesn't work with SMO.

$sqlcc = new-object ('Microsoft.Data.SqlClient.SqlConnection') "Server=tcp:<ServerName>.database.windows.net,1433;Initial Catalog=<DbName>;Persist Security Info=False;User ID=<User>@<TenantName>.onmicrosoft.com;Password=<>;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False"
$sc = new-object ('Microsoft.SqlServer.Management.Common.ServerConnection') $sqlcc
$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $sc
$srv.ConnectionContext.Authentication = [Microsoft.SqlServer.Management.Common.SqlConnectionInfo+AuthenticationMethod]::ActiveDirectoryPassword
Read-SqlTableData -InputObject $srv.Databases["<DbName>"].Tables["<TableName>"] -TopN 5|ft

If Read-SQLTableData was AzureAD access token aware, one could read tables from AzSQLdb natively using PowerShell.

Matteo-T commented 2 years ago

Thanks @Ayanmullick - it's a good suggestion. I had a few other similar requests to sprinkle the "-AccessToken" parameter all over (when applicable), so your request here aligns well with that.

Matteo-T commented 1 year ago

This is going to be fixed in v22 GA, which I expect to roll out in the next few days (or hours).