I am connecting to Azure SQL with the intent of reindexing fragmented indexes. The relevant code is as follows:
#grab the list of all databases
$AzureDbs = Invoke-Sqlcmd $DbList -ServerInstance ‘tcp:xxxxxxxx.database.windows.net,1433’ -Database “master” -U $uid -Password $pwd
$AzureDbsNames = $AzureDbs| SELECT name | ?{$_ -notmatch “master”}
#loop through the list of DBs
foreach($A in $AzureDbsNames){
#run the query to get fragmented indexes
$FragedTables = Invoke-Sqlcmd $SqlStr -ServerInstance ‘tcp:xxxxxxxx.database.windows.net,1433’ -Database $A -U $uid -Password $pwd
}
The first invoke-sqlcmd returns the expected data.
The second invoke-sqlcmd (passed to $FragedTables) gets the error:
Invoke-Sqlcmd : Login failed for user ‘xxxxxxxx’.
This session has been assigned a tracing ID of
‘01d4f613-d2dc-4572-b830-6a32b7d99412’. Provide this tracing ID to customer
support when you need assistance.
At C:\DevCode\Repos\AzureIndexMaitenance.ps1:122 char:18
-
$FragedTables = Invoke-Sqlcmd $SqlStr -ServerInstance
'tcp:xxxxxxxx.database …
+ CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlExcept
ion
+ FullyQualifiedErrorId : SqlExectionError,Microsoft.SqlServer.Management.
PowerShell.GetScriptCommand
The login account has full access to all of the databases. When I copied out the failed snippet and ran it by itself, it worked perfectly.
What do I need to do to get the second (and following) command(s) to connect (login)? it appears that a first connection is successful, but subsequent connects consistently fail.