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.