Run SQL query against Azure SQL database using PowerShell

user_5057152 0
Viewed 7405

My task is to retrieve the users list from our Azure SQL databases. We have 100s of Azure SQL database and I want to use PowerShell to make the task fast.

I am using connection string (Active Directory Integrated). I believe I am able to login to the SQL database using the connection string with PowerShell.

However, I am getting an error while running the SQL. Below is the code and exception. Could you please help me?

Code: 

try {
    $new = 'Server=tcp:dummy.database.windows.net,1433;Authentication="Active Directory Integrated";Initial Catalog=xtoiteuitbdbsqldb01;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;'
    $sql = "SELECT * FROM sys.database_principals"
  $temp_result_object = invoke-sqlcmd -ConnectionString $new -Query $sql
} catch {
  "error when running sql $sql"
  write-host "Exception type: $($_.Exception.GetType().FullName)" -ForegroundColor Red
    write-host "Exception message: $($_.Exception.Message)" -ForegroundColor Red
    write-host "Error: " $_.Exception -ForegroundColor Red   
}

Exception:

Exception type: ManagedBatchParser.ParserException
Exception message:
Error: ManagedBatchParser.ParserException

at ManagedBatchParser.Parser.Parse()
at Microsoft.SqlServer.Management.PowerShell.ExecutionProcessor.ExecuteTSql(String sqlCommand)

azure powershell sql-server
3 Answers

The below worked for me, Connect-AzAccount triggers interactive AAD auth via the browser

Example 11: Connect to Azure SQL Database (or Managed Instance) using an Access Token

Import-Module SQLServer
Import-Module Az.Accounts -MinimumVersion 2.2.0

# Note: the sample assumes that you or your DBA configured the server to accept connections using
#       that Service Principal and has granted it access to the database (in this example at least
#       the SELECT permission).

### Obtain the Access Token: this will bring up the login dialog
Connect-AzAccount
$access_token = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token

# Now that we have the token, we use it to connect to the database 'mydb' on server 'myserver'
Invoke-Sqlcmd -ServerInstance myserver.database.windows.net -Database mydb -AccessToken $access_token`
              -query 'select * from Table1'
user_52699810

You can use the .NET SqlClient provider in order to use either password or "Active Directory Integrated" authentication, e.g.:

# connect to database
$dbConn = New-Object System.Data.SqlClient.SqlConnection
$dbConn.ConnectionString = "Server=tcp:dummy.database.windows.net,1433;Database=DummyDB;Authentication=Active Directory Integrated;Encrypt=True;"
$dbConn.Open()

# construct command
$dbCmd = New-Object System.Data.SqlClient.SqlCommand
$dbCmd.Connection = $dbConn
$dbCmd.CommandText = "SELECT * FROM sys.database_principals"

# fetch all results
$dataset = New-Object System.Data.DataSet
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter
$adapter.SelectCommand = $dbCmd
$adapter.Fill($dataset)

# display results
$dataset.Tables | Format-Table
user_86269170

Try something like below and see if it helps: 

$Svr = "SVR"
$SvrPort = 10003
$Username = "ABC\sql"
$Password = "ABC#@!"

$Password = $Password | ConvertTo-SecureString -AsPlainText -Force
$Cred = New-Object System.Management.Automation.PSCredential -ArgumentList $Username,$Password
$Inst = $Svr + "," + $SvrPort

Get-PSSession

New-PSSession -ComputerName $Svr -Credential $Cred

Invoke-Sqlcmd -ServerInstance $Inst -Database "master" -Query "SELECT @@SERVERNAME AS SERVER_NAME"

Get-PSSession

Get-PSSession | Remove-PSSession

Invoke-Sqlcmd -ServerInstance $Inst -Database "master" -Query "SELECT @@SERVERNAME AS SERVER_NAME"

Get-PSSession

Also refer to below post for further reference:

Active Directory Password Connection Azure SQL Failure from Azure Automation

user_60496040
Related