Second Invoke-SQLcmd fails login

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.

It’s a little difficult to tell, but the queries you are running ($DbList and $SqlStr) aren’t in the post. If I had to guess, your initial query is returning all of the tables in a database and your are running another query against it and could be trying to connect to a system table and having a permission issue. One thing that does not look right is that you are not using the $A variable to build the next SQL commands in the for loop, so you’d be running the same command on each database

If you minimally just write output that tells you want database you are connecting to as well as the query, you’ll probably see the issue and need to adjust your SQL or $AzureDBNames queries before the loop:

$DBList = @"
    SELECT * 
    FROM information_schema.tables
"@

$sqlCmdParams = @{
    ServerInstance='tcp:xxxxxxxx.database.windows.net,1433'
    Database="master"
    U=$uid
    Password=$pwd
    Query=$DBList
}

$AzureDbs = Invoke-Sqlcmd @sqlCmdParams
$AzureDbsNames = $AzureDbs| SELECT name | ?{$_ -notmatch "master"}

#loop through the list of DBs
foreach($A in $AzureDbsNames){
$qry = @"
    Select *
    From $A
"@
    $sqlCmdParams.Set_Item("Query", $qry) 
    $sqlCmdParams.Set_Item("Database", $A) 

    #run the query to get fragmented indexes
    'Running command "{0}" on database {1}' -f $qry, $A
    $FragedTables = Invoke-Sqlcmd @sqlCmdParams
}

The commands I am running are these:

$DbList = “Select * from Sys.Databases”

$SqlStr = @"
SELECT
t.name AS [TableName]
,ind.name AS [IndexName]
,col.name AS [ColumnName]
,ty.Name ‘Data type’
,col.max_length ‘Max Length’
,ips.avg_fragmentation_in_percent
FROM
sys.indexes ind
INNER JOIN
sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id
INNER JOIN
sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id
INNER JOIN
sys.tables t ON ind.object_id = t.object_id
INNER JOIN
sys.types ty ON col.user_type_id = ty.user_type_id
INNER JOIN sys.dm_db_partition_stats ps
ON ps.object_id = ind.object_id
AND ps.index_id = ind.index_id
CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), ps.object_id, ps.index_id, null, ‘LIMITED’) ips
WHERE
ips.avg_fragmentation_in_percent > 9.99
ORDER BY
t.name, ind.name, ind.index_id, ic.index_column_id
"@

$Rebuild = @"
BEGIN
PRINT(‘Rebuilding Indexes on ’ + $F)
Begin Try
EXEC(‘ALTER INDEX ALL ON ’ + $F + ’ REBUILD with (ONLINE=ON)’)
End Try
Begin Catch
PRINT(‘Cannot do rebuild with Online=On option, taking table ’ + $F+’ down for doing rebuild’)
EXEC(‘ALTER INDEX ALL ON ’ + $F + ’ REBUILD’)
End Catch
FETCH NEXT FROM TableCursor INTO $F
END
"@

$DBSizeStr = “select sum(reserved_page_count) * 8.0 / 1024 as ‘size in MB’ from sys.dm_db_partition_stats”

This last query is not mentioned in the original code I posted, but is used in a foreach from the results of the second (fragmented indexes) query to test the change in size of the database.

So as you can see, I query for the list of all databases in my instance. Then I iterate through the databases, and then query for a list of indexes that are fragmented equal to or greater than 10% on each of the databases. All of the queries work fine in SQL Management Studio 2014. Also, if I comment out the first query call (return all databases), and assign a single database instance to $A and run the second query I get the expected results instead of the login failure. In that test, subsequent query attempts get the same access failure while using the same syntax and credentials. So it appears I am limited to a single login connection (within some unknown time limit). However the syntax of invoke-SQLcmd does not allow me to reuse the previously established connection.

Does anyone know of a way around this? One thing I have not tried, but was planning to attempt this morning, is to create 10 accounts in an array and iterate through them for the connections. This seems like a ridiculous work-around, but I cannot think of any other way to do it and Azure’s connections appear to be broken.

Note: anyone using the $Rebuild query above should know that they will not work on large fields (varchar(50) or anything defined as (max)). Search for other reindexing of Azure articles for more details.

The Invoke-SQLCmd documentation indicates that running a query should exit:

Run the specified query and exit

However, there are numerous bugs with Invoke-SQLCmd, so you may want to use manual code to make the connect, run your queries and close the connection. Take a look at answer #2 in this Stackoverflow thread at the Invoke-SQL function. You would be able to place your logic between the .Open() and .Close() to see if it works as expected.

That bug was closed, but the command is still a little buggy. It appears the issue I am having is that Invoke-SQLCmd assumes a trusted connection while Azure assumes the opposite. I am now using System.Data.SqlClient.SqlConnection with settings that include: “Trusted_Connection=False;Encrypt=True;Connection Timeout=30;” and it is working much better.

Thanks for the help.

I’ve had a similar problem before and found that Invoke-SqlCmd didn’t deal well with executing while enumerating the results of a previous Invoke-SqlCmd. In my case I processed the results into an array variable, then looped through it and executed Invoke-SqlCmd within the loop. Invoke-SqlCmd not working with Powershell’s pipelining is the problem. I’d change it to:

# I'd put the filtering on db name 'master' into the $DbList query instead of as a separate powershell filter.

can’t have multiple Invoke-SQLCmd in a pipeline, so get the dbnames first

$dbNames = @()
Invoke-Sqlcmd $DbList -ServerInstance ‘tcp:xxxxxxxx.database.windows.net,1433’ -Database “master” -U $uid -Password $pwd `
| foreach { $dbNames += $_.name }

#loop through the list of DBs
foreach($A in $dbNames){
#run the query to get fragmented indexes
$FragedTables = Invoke-Sqlcmd $SqlStr -ServerInstance ‘tcp:xxxxxxxx.database.windows.net,1433’ -Database $A -U $uid -Password $pwd
}