Microsoft Fabric Data Pipeline Connection Error - Created Using Copy Assistant

Problem

I am getting this error when I created a connection using Data Pipeline's "copy assistant.

We were unable to connect to the data source using an encrypted connection. To use an unencrypted connection, uncheck "Use encrypted connection" and try again.

I scoured the internet for any related issues and saw the link below, but it did not really help:

https://community.fabric.microsoft.com/t5/Service/Unable-to-connect-to-the-data-source-using-an-encrypted/m-p/3654708

Solution

I noticed that when I use the "copy assistant" inside the Data Pipeline I get the error. But if I create the "connection" using "Manage connections and gateway" and use in Data Pipeline I don't get the error.

1. Create connection using Manage connections and gateway.

a. In Microsoft Fabric, click on the Settings icon and select "Manage connections and gateways".

b. In Manage Connections and Gateways, click the "+ New" to create a new connection.

c. In New connection windows provide the following information:

    - Gateway cluster name: select the correct gateway cluster name that will be used.

    - Connection name: enter the connection name. This is a free text field.

    - Connection type: select the connection type, example: SQL Server

    - Server: enter the on-premises server name that will be the data source.
    - Database: enter the database name that will be used in the server.

    - Authentication: Windows Authentication method will be used if you are using Microsoft Entra ID and your company's Active Directory is synchronized with Azure AD. 

     - Privacy level: Organizational

d. Once all the information were provided, click the "Create" button 

e. The newly created Connections will appear in the list of Connections.

 

2. Use newly created Connection in Data Pipeline

a. Inside the data pipeline, go to Copy Data --> Use copy assistant.

b. In Copy data window, select SQL Server database from the New sources.

c. In Connect to data source window, provide the Server Name and Database Name provided in the Connections in step #1. 

NOTE: The database name is case sensitive. 

d. If there is already a "Connection" for the Server and Database Name that was provided, the matching Connection will be displayed in the Connection credentials drop down list.Click the Next button when done.

e. The objects in the selected Connection will be loaded and displayed.

 

f. The on-premises database objects can now be used load data into Microsoft Fabric using data pipeline. You have successfully configured your data pipeline connections when you reach this screen.

The connections created within the Data Pipeline versus the one created in Manage Connections and Gateway have different connection encryption settings. There is no check box that can be unchecked during the Connections setting configuration. 

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.

Resolve-DNSName Slow In Returning Results For Remote IP

Problem

I need to get the host name of a server but I only have its IP address. I used Resolve-DnsName command with IP address and it took 6 seconds for the results to show up in the screen.

I also tried using the IP address of the server that I am using and it returned the values instantaneously. I am assuming it is quicker since it is getting the information locally. I am expecting that the speed to get the information would be similar for both.

I tried the command again on a 3rd server and the speed is the same with the first one.

I researched and tried additional parameter for Resolve-DnsName to help it run faster.

Solution 

1. Tried -QuickTimeout, but it still took 6 seconds to display the records.

 PowerShell

Resolve-DnsName 250.251.252.253 -QuickTimeout

2. Tried -NoRecursion, it still took 6 second for the results to return.

 PowerShell

Resolve-DnsName 250.251.252.253 -NoRecursion -QuickTimeout

3. Tried -DnsOnly, this command returned the values quickly.

PowerShell

Resolve-DnsName 250.251.252.253 -DnsOnly

Sometimes it just takes a few trial and error since the documentation did not specify that it will return the results quicker -DnsOnly parameter is used.

Get SQL Server TCP Connections from Operating System Level

Problem

Our systems (server) administrator who got local administrator rights to our SQL Server wanted to get the remote TCP connections on SQL Server instance.

This can be done by connecting to the instance and use the Dynamic Mangement View (DMV) called dm_exec_connections to query the information. This will also require us to give VIEW SERVER STATE permission to the user. We want to give "just enough" permission to the user that needs it and by providing VIEW SERVER STATE you give the user permission to all DMVs.

Solution

Knowing that the server admins already have local administrator rights to our servers, this solution can be achieved without giving them access to the database instance.

This solution can be achieved in 3 steps using PowerShell:

1. Get all the process that are running in the server using Get-Process and filter all the SQL Server related processes.

PowerShell

Get-Process -ComputerName $servername | Where-Object {$_.ProcessName -in ('smss','SQLAGENT','sqlceip','sqlservr','sqlwriter')}

2. Get all the TCP connection of the processes that were returned on step 1 using Get-NetTCPConnection. You need to relate the Id from Get-Process with OwningProcess from Get-NetTCPConnection. This will return the IP addresses of the remote TCP connections. 

PowerShell

Get-NetTCPConnection -OwningProcess "$SQLServerProcessID"

3. Get hostname of the remote TCP connections returned on step 2 by using Resolve-DnsName. 

PowerShell

Resolve-DnsName "$TCPRemoteAddress"

Now you can combine all these steps to create a function that will return SQL Server's remote TCP connection without accessing the database instance.

You can download the function Get-SQLServerTCPConnectionsOSLevel from github.

Format-Volume Bug or Feature?

Problem

We just recently added an additional node to our existing Availability Group cluster. 
Our team uses a PowerShell script to easily format multiple drives in a specific allocation unit size.

The issue happened when we formatted the drive on the new node. It is supposed to only format the drive on the server where it was ran. But for some reason, it also formatted the drive on the existing nodes.

This wiped out the drives on the existing nodes. The existing system went down and we have to restore everything from our backup.

We have to do a RCA (Root Cause Analysis) because it affected our production system.

Root Cause Analysis

We have to build a similar environment to simulate the issue.

To cut the story short, we were able to pin point the issue on Format-Volume command in PowerShell.

This particular issue only affects servers that are part of Windows cluster and have same drive letters on each node.

For SQL Server Always On:

• This does NOT affect Failover Cluster Instance (FCI) since it is using shared storage.
• This only affects the Availability Group configuration because each node have it's own storage and they can have same drive letters.

As a test we ran the Format-Volume to format the Z drive on node1 (of a 3 node cluster).

It formatted the Z drive on of all the nodes instead of just formatting the drive on node1.

Solution

Open a case with Microsoft to check if this is a bug or if there is a different method that we can do to run the Format-Volume only on the node where it is executed. (At the time of this writing, we have not heard back from Microsoft.)

As a precautionary measure for our team, we only use this feature on the following situation:

• Standalone servers
• Always On Failover Cluster Instance servers
• New server builds of Always On Availability Group

Get SQL Server Windows Cluster Information

Problem

I have already identified that there is a SQL Server installed on a server. Using PowerShell, I wanted to know if the server is using windows clustered WITHOUT connecting the database instance. 

This is an option that system administrators since they have admin rights on the Operating System but don't have access to the SQL Server instance.

I need to know the following:

1. I need to know if the server is clustered or not.

2. If the server is clustered, I need to know why type of clustering is configured on the server. 

    SQL Server have 2 types of Windows Clustering:

• Failover Cluster Instance
• Availability Group

    You can also combine Failover Cluster Instance with Availability Group.

    I also wanted to know if this type of configuration is configured on the server.

3. If the server is clustered, I also need to know if the server is active or passive node

Solution

One of the requirements to be able to get windows clustering information is to ensure that you have FailoverClusters PowerShell module installed on the machine that you are using to run the PowerShell commands that are specified below.

Let us step through the requirements:

1. How to check if the server is clustered or not?

    Using HKEY_LOCAL_MACHINE check if there is a "Cluster" entry in the registry

PowerShell

Get-ItemProperty -Path Registry::HKEY_LOCAL_MACHINE\Cluster}

    To check a server remotely you need to use Invoke-Command

PowerShell

Invoke-Command -Computer $servername -ScriptBlock {Get-ItemProperty -Path Registry::HKEY_LOCAL_MACHINE\Cluster}

    If no value is returned, the SQL Server is standalone.

    If the command returns a value, then SQL Server is clustered using windows clustering.

2. How to check what type of clustering is configured on the server?
    This is where the FailoverClusters module will come-in handy.
    We need to get/check the following:
    a. Check WMIObject MSCluster_ResourceGroup for root\mscluster namespace
    b. Get-Cluster information of the server
    c. Get-ClusterResource information of the server

    Check root\mscluster namespace

PowerShell

Get-WMIObject -Class MSCluster_ResourceGroup -Namespace root\mscluster}

    To check a server remotely you need to use Invoke-Command

PowerShell

Invoke-Command -Computer $servername -ScriptBlock {Get-WMIObject -Class MSCluster_ResourceGroup -Namespace root\mscluster}

    Get the cluster and cluster resource information of the server

PowerShell

Get-Cluster -Name $servername | Get-ClusterResource

    If both root\mscluster namespace and cluster resource does not return a value then the server is configured as a "single node" cluster.

    If both root\mscluster namespace and cluster resource returns a value, we need to figure out what type of SQL Server windows clustering is configured.

3. How to check if "Availability Group" is configured?
    Using the cluster resource information, check if there is a resource type called "SQL Server Availability Group".

PowerShell

Get-Cluster -Name $servername | Get-ClusterResource | Where-Object ResourceType -eq 'SQL Server Availability Group'

    If no results were returned then, the server is configured with "Failover Cluster Instance".

    If one result is returned, the server is configured with "Availability Group/Listener".
    If more than one result is returned, the server is configured with multiple "Availability Group/Listener".

4. How to check if "FCI with Availability Group" is configured?
    First you need to make sure that the server is configured with "Availability Group". 
    Using the cluster resource information, check if there is a resource type called "SQL Server Availability Group" or resource type called "Network Name" and resource name that starts with "SQL Network Name".

PowerShell

Get-Cluster -Name $servername | Get-ClusterResource | Where-Object { (($_.ResourceType -eq 'Network Name') -and ($_.Name -Like 'SQL Network Name *')) -or ($_.ResourceType -eq 'SQL Server Availability Group')  }

    If 2 different resource types are returned, the server is configured with "FCI with Availability Group".

    NOTE: Ensure that you filter the uniqueness of the returned resource type value.

5. How to check if the server is the active or passive node?
    Using the cluster resource information, get the OwnerNode for resource type called "SQL Server".

PowerShell

Get-Cluster -Name $servername | Get-ClusterResource | Where-Object ResourceType -eq 'SQL Server' | Select-Object OwnerNode

    If the returned OwnerNode value is the same as the $servername, then it is the "active" node, otherwise it is the passive node.

Now you can combine all of the commands above to create a function that will return the SQL Server Windows Cluster Information.

You can now download the Get-SQLServer-WindowsClusterInfo function from github.

Create External Data Source to Reference a Readable Secondary Replica of Always On Availability Group

Problem

We need to create an external table that uses and external data source. The external data source needs to query the (read_only) readable secondary node of an availability group.

Pretty simple, just follow the Microsoft document:

CREATE EXTERNAL DATA SOURCE (Transact-SQL) - SQL Server | Microsoft Docs

There are several requirements:

1. The external data source needs to be configured as AlwayOn Availability Group with Read Only Routing List configured.  Configure read-only routing for an availability group - SQL Server Always On | Microsoft Docs
2. CONNECTION_OPTIONS argument for External Data Source is only available on SQL Server 2019.
3. Declare the Application_Intent=ReadOnly on CONNECTION_OPTIONS
4. Run a query on external table and execute it multiple times. This will give you time to check where the session is being executed. 
5. Verify the connection on both primary and secondary server. The session should be running in the secondary server.

Solution

First Try:

1. Configured/created the external data source

CREATE EXTERNAL DATA SOURCE [XDSName]

WITH (LOCATION=n'sqlserver://AGListenerName',

CONNECTION_OPTIONS='ApplicationIntent=ReadOnly',

CREDENTIAL=[dbuser])

GO

2. Configured/created external data table

CREATE EXTERNAL TABLE [XTable] ([column1] INT not null...)
WITH (LOCATION=N'[dbname].[schemaname].[tablename]',

DATA_SOURCE=[XDSName])

GO

3. Run a query against the external data table and repeated it 1000 times

SELECT * FROM [XTable]

GO 1000

4. Verify both the primary and secondary server. The query should run in secondary server.

 

With my first test, the query ran in the primary server. 

After doing some investigation, I found out that the "default database" of the credential that I used is pointed to the "master" database.

I modified "default database" of the credential I used to point to database name that is being replicated by the Availability Group. I tested again by running a query on the external data table multiple time, this time the session is running on the secondary node.

But we are not allowed to change the "default database" of the user.

The "default database" was changed back to "master"

Second Try:

1. Configured/created the external data source, but this time the database needs to be declared.

CREATE EXTERNAL DATA SOURCE [XDSName]

WITH (LOCATION=n'sqlserver://AGListenerName',

CONNECTION_OPTIONS='ApplicationIntent=ReadOnly; Database=dbname',

CREDENTIAL=[dbuser])

GO

2. Configured/created external data table

3. Run a query against the external data table and repeated it 1000 times

4. Verify both the primary and secondary server. The query should run in secondary server.

The initial test failed because I used a wrong keyword "DatabaseName". 

A co-worker of mine gave me a sample of his external data source that he has configured and already working against a secondary server.

After comparing the 2 definition, I found out that the keyword should be "Database" and not "DatabaseName"

I tested again and it worked because the "database" keyword is defined in the CONNECTION_OPTIONS which override the "default database" of the credential.

Allowed connection string keywords that can be used on CONNECTION_OPTIONS

Using connection string keywords - SQL Server Native Client | Microsoft Docs

Since there is no example on Microsoft's document, I submitted a pull request and added an example G. Create external data source to reference a readable secondary replica of Always On Availability Group

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.