SQL SERVER – Unable to Get Listener Properties Using PowerShell – An Error Occurred Opening Resource

SQL
2 Comments

I was engaged with a client for an AlwaysOn project and they had some follow-up questions. I took some time to find the answers and encountered an interesting error. I am sharing them here so that others can get benefited. They informed me that they are not able to see and modify listener properties. Let us learn about this error related to opening resource.

Initially, I shared script to get the properties of the listener via T-SQL. As you can see below, we can use catalog views.

SELECT grp.name AS [AG Name],
lis.dns_name AS [Listener DNS Name],
lis.port AS [Listener Port]
FROM sys.availability_group_listeners lis
INNER JOIN sys.availability_groups grp
ON lis.group_id = grp.group_id
ORDER BY grp.name, lis.dns_name

Here is the output.

Solarwinds

SQL SERVER - Unable to Get Listener Properties Using PowerShell - An Error Occurred Opening Resource list-powershell-01

My client came back and told that networking team has asked to change RegisterAllProvidersIP setting. We are not able to use PowerShell and getting error “An error occurred opening resource”. We are not sure what wrong with the listener in the cluster.

Get-ClusterResource AGListener | Get-ClusterParameter
PS C:\> Get-ClusterResource AGListener | Get-ClusterParameter
Get-ClusterResource : An error occurred opening resource 'AGListener'.
At line:1 char:1
+ Get-ClusterResource AGListener | Get-ClusterParameter
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 + CategoryInfo : ObjectNotFound: (:) [Get-ClusterResource], ClusterCmdletException
 + FullyQualifiedErrorId : ClusterObjectNotFound,Microsoft.FailoverClusters.PowerShell.GetResourceCommand

If we look at cluster the value “AGListener” we are using seems correct, but still PowerShell thinks its incorrect. Here is the screenshot from cluster manager.

SQL SERVER - Unable to Get Listener Properties Using PowerShell - An Error Occurred Opening Resource list-powershell-02

I did some more searching and found that when we create Listener in through SSMS its naming convention like AGNAME_ListenerName. This is the reason that when we run the command Get-ClusterResource for the listener, we can’t see the properties. Here are the properties of the listener resource. (Right Click)

SQL SERVER - Unable to Get Listener Properties Using PowerShell - An Error Occurred Opening Resource list-powershell-03

WORKAROUND/SOLUTION

SQL SERVER - Unable to Get Listener Properties Using PowerShell - An Error Occurred Opening Resource list-powershell-04

Based on above explanation, we need to use the “name” as shown in properties and the command was working as expected.


Get-ClusterResource BO1AG_AGListener | Get-ClusterParameter

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , , , ,
Previous Post
SQL SERVER – Error: 8509 – Import of Microsoft Distributed Transaction Coordinator (MS DTC) transaction failed: 0x8004d00e(XACT_E_NOTRANSACTION)
Next Post
SQL SERVER – How to Reach Out to Cloud – Cloud Computing with PartitionDB

Related Posts

2 Comments. Leave new

  • alen teplitsky
    February 1, 2018 7:43 pm

    You can do something like this

    function Get-SQL-Clusters
    {
    param([string]$server)

    $servers = Get-Content -literalpath “C:\temp\sql_clusters.txt”

    if ($server -ne 1)
    {

    $files = ForEach ($box in $servers) {invoke-command -ComputerName $box {Get-ClusterResource| Get-ClusterParameter} |
    Where-Object {$_.Name -eq “Address”} |
    Format-Table PSComputerName, ClusterObject, State, Name, Value}
    }
    else
    {Write-Warning “‘$server’ is not a valid path.”}
    return $files
    }

    Reply

Leave a Reply

Menu