I want to write a script that:
- list all Azure Subscriptions --> and find all Azure SQL Servers
- list all Azure SQL Servers --> and find all Azure SQL Databases that are not
master
- 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....