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.