SQL Server error 47106

Problem

I was trying to configure Read-Scale Always On Availability Group in our test server using the steps from https://www.mssqltips.com/sqlservertip/6905/sql-server-read-scale-always-on-availability-groups/ but I encountered an error "SQL Server error 47106".

Below is the error that I got:

TITLE: Microsoft SQL Server Management Studio

------------------------------
Failed to join the instance 'TESTSQL17A' to the availability group 'TESTSQL17RS'. (Microsoft.SqlServer.Management.HadrModel)
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.100.47021.0&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot join availability group 'TESTSQL17RS'. Download configuration timeout. Please check primary configuration, network connectivity and firewall setup, then retry the operation.
Failed to join local availability replica to availability group 'TESTSQL17RS'.  The operation encountered SQL Server error 47106 and has been rolled back.  Check the SQL Server error log for more details.  When the cause of the error has been resolved, retry the ALTER AVAILABILITY GROUP JOIN command. (Microsoft SQL Server, Error: 47106)
For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-47106-database-engine-error
------------------------------
BUTTONS:
OK
------------------------------

Solution

I scoured the internet for any related issues and saw a post from  https://www.voltol.com/2020/02/sql-error-47106-when-trying-to-add.html

Reading through the article above helped me in the right direction. The port used by read-scale is being blocked by a firewall.

Below is the link of required firewall rules to access SQL Server: 

Configure the Windows Firewall to allow SQL Server access - SQL Server | Microsoft Learn

During the course of the investigation, I performed the following tasks:

1. Checked the port being used for Read-Scale Availability Group. Availability group uses port 5022 or 7022 for database mirroring.

SQL

SELECT name, protocol_desc, port, state_desc
FROM sys.tcp_endpoints
WHERE type_desc = 'DATABASE_MIRRORING'

2. Using PowerShell command Test-NetConnection, I tested the port being used by SQL Server for database mirroring on both servers. I included port 1433 since it is the default TCP port of SQL Server instance.

PowerShell

clear
Test-NetConnection -ComputerName TESTSQL17A -Port 1433
Test-NetConnection -ComputerName TESTSQL17A -Port 5022

Test-NetConnection -ComputerName TESTSQL17B -Port 1433
Test-NetConnection -ComputerName TESTSQL17B -Port 5022

The test from TESTSQL17B to TESTSQL17A failed on port 5022, but the connection using port 1433 was successful.

3. To ensure that the ports are open, I manually added the ports 1433 and 5022 in the firewall rules of both servers.

PowerShell

clear
New-NetFirewallRule -DisplayName "SQLServer default instance" -Direction Inbound -LocalPort 1433 -Protocol TCP -Action Allow
New-NetFirewallRule -DisplayName "SQLServer Database Mirroring" -Direction Inbound -LocalPort 5022 -Protocol TCP -Action Allow

New-NetFirewallRule -DisplayName "SQLServer default instance" -Direction Outbound -LocalPort 1433 -Protocol TCP -Action Allow
New-NetFirewallRule -DisplayName "SQLServer Database Mirroring" -Direction Outbound -LocalPort 5022 -Protocol TCP -Action Allow

The test to connect using port 5022 still failed even though both outbound and inbound firewall rules were already been configured.

4. Contacted systems administrators to check the ports and firewall rules. The server admin confirmed that ports and firewall rule settings are correct. 

I recall getting an email regarding "Zero Trust Network Access" software being tested by our security team.

5. Contacted our security team and asked someone to check the servers that I am using for my testing.

The security admin confirmed that one of the server is included in their "Zero Trust Network Access" testing. They opened/verified that port 5022 are open on both servers.

The Test-NetConnection testing on port 5022 was successful after the change was implemented.

6. Read-scale Always On configuration was successful after the changes were made.

Some solutions might require working with other teams in your company. Awareness of events happening around you is also important.

SQL Server Agent Job - $RawUI.CursorPosition Error

Problem

I had this issue a long time ago, but did not have time to write about this.

I am trying to run some PowerShell script to cleanup old backup files in the database server using SQL Server Agent job.

But when I tried running the job, I got this error below.

Executed as user: <sql server agent account>. A job step received an error at line 3 in a PowerShell script. The corresponding line is '$RawUI.CursorPosition = @{X=0;Y=0}  '. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Exception setting "CursorPosition": "A command that prompts the user failed because the host program or the command type does not support user interaction. Try a host program that supports user interaction, such as the Windows PowerShell Console or Windows PowerShell ISE, and remove prompt-related commands from command types that do not support user interaction, such as Windows PowerShell workflows."  '.  Process Exit Code -1.  The step failed.

Solution

Let's walk through my initial troubleshooting steps:

1. I read the first 2 sentences and it seems to be obvious that the error is in line 3.

    Line 3 of my code just gets the current date and puts it in a variable:

    

    $currdate = Get-Date

    It seems straight forward, I ran it in Powershell and it runs fine.

    I isolated the code by adding Write-Output to display the value right after getting the date.

Write-Output "Current Date: $currdate"

    I also deleted the rest of my code after the Write-Output since the error is just in line 3.

    

2. I ran the job again and I still got the same error.

    Now I tried to read and understand the error and read the rest of the error message.

    I tried to understand what each sentence.

Executed as user: <sql server agent account>. A job step received an error at line 3 in a PowerShell script.

The corresponding line is '$RawUI.CursorPosition = @{X=0;Y=0}  '. 

Correct the script and reschedule the job. 

The error information returned by PowerShell is: 'Exception setting "CursorPosition": "A command that prompts the user failed because the host program or the command type does not support user interaction. Try a host program that supports user interaction, such as the Windows PowerShell Console or Windows PowerShell ISE, and remove prompt-related commands from command types that do not support user interaction, such as Windows PowerShell workflows."  '.  Process Exit Code -1.  

The step failed.    

    Looking at the code, there is no command that seems to be prompting a user and waiting for user interaction.

    The only command that seems to be interacting with the PowerShell Console or ISE is Write-Output which I added in step 1.

3. I tried running the job again with 3 the job again and I still got the same error on line 3.

    I decided to just remove the Get-Date code, just for the heck of it.

4. I ran the SQL Server Agent job with just "clear" on the code. 

clear

    I still got the same error:

Executed as user: <sql server agent account>. A job step received an error at line 3 in a PowerShell script. The corresponding line is '$RawUI.CursorPosition = @{X=0;Y=0}  '. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Exception setting "CursorPosition": "A command that prompts the user failed because the host program or the command type does not support user interaction. Try a host program that supports user interaction, such as the Windows PowerShell Console or Windows PowerShell ISE, and remove prompt-related commands from command types that do not support user interaction, such as Windows PowerShell workflows."  '.  Process Exit Code -1.  The step failed.

    There is no line 3 in the code but it is still telling me that the error is in line 3.

    

    This was just frustrating because the error message is not giving the right information.

    The "clear" command is not prompting a user, but it is somewhat interacting with the console and ISE.

    The command "clear" is and alias for "Clear-Host". Another alias for "Clear-Host is "cls". 

5. I removed the "clear" command and tested again the Get-Date and Write-Output code, and it ran without error.

$currdate = Get-Date

Write-Output "Current Date: $currdate" 

6. I added back the rest of my code to complete the SQL Agent job that will clean old backup files.

Lesson's learned: Don't forget to remove the "Clear-Host", "clear" or "cls" when running your PowerShell commands in SQL Server Agent job.