0

I want to write a script that:

  1. list all Azure Subscriptions --> and find all Azure SQL Servers
  2. list all Azure SQL Servers --> and find all Azure SQL Databases that are not master
  3. list all Azure SQL Databases --> and run the query SELECT @@VERSION

My beautiful code is this:

# Connect to Azure
$azureAccount = Connect-AzAccount 
# Get Azure Access Token (we will use this to query the databasees)
$azureToken = Get-AzAccessToken -ResourceUrl https://database.windows.net
# List all subscriptions
Get-Azsubscription | Format-table
# Iterate into subscriptoins and print names
$GetSubscription = Get-Azsubscription 
    foreach ($gs in $GetSubscription) {
        Select-Azsubscription -Subscription "$gs" | Out-Null
        Write-Host "Let's browse into Azure Sunscription: " -NoNewline
        Write-Host (Get-AzContext).Subscription.Name -ForegroundColor green
        # Fins all Azure SQL Server
        $AzSqlServer = Get-AzSqlServer 
        if($AzSqlServer){
            Foreach ($server in $AzSqlServer){
                # Find all Azure SQl Databases
                $SQLDatabase = Get-AzSqlDatabase -ServerName $server.ServerName -ResourceGroupName $server.ResourceGroupName | Where-Object DatabaseName -NE "master" 
                Foreach ($database in $SQLDatabase){
                    # For each database run a query trough DBATools 
                    $server = Connect-DbaInstance -SqlInstance $database.ServerName -Database $database.DatabaseName -AccessToken $azureToken
                    Invoke-DbaQuery -SqlInstance $server -Query "SELECT @@VERSION" | Format-Table -AutoSize
                }
            }
            
        }   
        else {
            Write-Host "No Servers here" -ForegroundColor yellow
            }
    }

And for now it returns me an error:

Error connecting to [testing-db]: The network path was not found
At C:\Users\FrancescoMantovani\Documents\WindowsPowerShell\Modules\dbatools\1.1.129\allcommands.ps1:148341 char:9
+         throw $records[0]
+         ~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ConnectionError: (bqm-eus-9r2cust-prd-catalog-mssql-srvr:String) [], Exception
    + FullyQualifiedErrorId : dbatools_Connect-DbaInstance

And I know exactly where the problem is: in the parameter -SqlInstance I'm passing testing-db instead of testing-db.database.windows.net.

But I don't know where can I find such information.

Sure thing it should be inside Get-AzSqlDatabase or Get-AzSqlServer but I cannot find it.

I believe this is what DBATools needs to work.

EDIT:

After I changed -SqlInstance $database.ServerName to -SqlInstance "$($database.ServerName).database.windows.net" I now receive a new error:

Error connecting to [testing-db.database.windows.net]: Login failed for user '<token-identified
principal>'. The server is not currently configured to accept this token.
At C:\Users\FrancescoMantovani\Documents\WindowsPowerShell\Modules\dbatools\1.1.129\allcommands.ps1:148341 char:9
+         throw $records[0]
+         ~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ConnectionError: (bqm-eus-9r2cust...ase.windows.net:String) [], Exception
    + FullyQualifiedErrorId : dbatools_Connect-DbaInstance

So strange....

Francesco Mantovani
  • 10,216
  • 13
  • 73
  • 113
  • 3
    Sounds like you just need to change `-SqlInstance $database.ServerName` to `-SqlInstance "$($database.ServerName).database.windows.net"`? – Mathias R. Jessen Sep 06 '22 at 14:51
  • Thank you @MathiasR.Jessen, changing the code has changed the error. Now the error is with the token. Not sure if you are a DBATools expert. Thank you for your help anyway – Francesco Mantovani Sep 06 '22 at 19:58

0 Answers0