Second Invoke-SQLcmd fails login


This topic contains 5 replies, has 3 voices, and was last updated by Profile photo of Rory Kingan Rory Kingan 10 months ago.

  • Author
  • #27391
    Profile photo of Garth Maynard
    Garth Maynard

    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 ',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 ',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
    + FullyQualifiedErrorId : SqlExectionError,Microsoft.SqlServer.Management.

    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.

  • #27392
    Profile photo of Rob Simmers
    Rob Simmers

    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 = @{
    $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
  • #27452
    Profile photo of Garth Maynard
    Garth Maynard

    The commands I am running are these:

    $DbList = "Select * from Sys.Databases"

    $SqlStr = @"
    SELECT AS [TableName]
    , AS [IndexName]
    , AS [ColumnName]
    ,ty.Name 'Data type'
    ,col.max_length 'Max Length'
    sys.indexes ind
    sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id
    sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id
    sys.tables t ON ind.object_id = t.object_id
    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
    ips.avg_fragmentation_in_percent > 9.99
    ORDER BY,, ind.index_id, ic.index_column_id

    $Rebuild = @"
    PRINT('Rebuilding Indexes on ' + $F)
    Begin Try
    End Try
    Begin Catch
    PRINT('Cannot do rebuild with Online=On option, taking table ' + $F+' down for doing rebuild')
    End Catch

    $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.

  • #27455
    Profile photo of Rob Simmers
    Rob Simmers

    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.

  • #27493
    Profile photo of Garth Maynard
    Garth Maynard

    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.

  • #33393
    Profile photo of Rory Kingan
    Rory Kingan

    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 ',1433' -Database "master" -U $uid -Password $pwd `
    | foreach { $dbNames += $ }

    #loop through the list of DBs
    foreach($A in $dbNames){
    #run the query to get fragmented indexes
    $FragedTables = Invoke-Sqlcmd $SqlStr -ServerInstance ',1433' -Database $A -U $uid -Password $pwd

You must be logged in to reply to this topic.