Fabric mirror SQL Server 2022 on prem via Service Principal

[Iaco96]

Hi everyone,
I have a question regarding a connection from Fabric to an on-prem SQL Server using a service principal.
Specifically, I need to mirror a database that resides on an on-prem SQL Server 2022 instance, and I want to authenticate using a service principal (Azure Entra ID authentication).

I have a gateway installed to allow Fabric to connect, but when I create the connection I get the following error:

“A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)”

I’ve tried following several guides, including adding the environment variable PBI_SQL_TRUSTED_SERVERS with the value set to the server name I need to connect to, but nothing seems to work.

Has anyone faced the same issue or can provide some guidance?

[wardy912 · Accepted solution]

Hi @Iaco96 

 This is AI generated and unverified but I hope it helps!

This error typically occurs because the SSL certificate presented by your on-prem SQL Server isn’t trusted by the gateway or the Fabric service when using Azure Entra ID authentication. Let’s break down the key points and possible solutions:

---

✅ Why This Happens

• When connecting via Azure Entra ID (service principal), the connection uses TLS/SSL for encryption.
• If the SQL Server instance uses a self-signed certificate or one issued by an internal CA that isn’t trusted by the gateway machine, the SSL handshake fails.
• The environment variable PBI_SQL_TRUSTED_SERVERS only bypasses name mismatch checks, not certificate trust issues.

---

🔍 Steps to Fix It

1. Ensure SQL Server Supports Azure AD Authentication

• SQL Server 2022 supports Azure AD authentication, but you must:
  • Enable Azure Active Directory authentication on the instance.
  • Configure the service principal in Azure AD and grant it access to the database.
  • Use ODBC driver 18 or later (Fabric uses this internally).

2. Fix the Certificate Trust Issue

You have two main options:

Option A: Install a Trusted Certificate

• Use a certificate from a trusted CA (public or internal) on your SQL Server.
• Ensure the gateway machine trusts the issuing CA (add it to the Windows Trusted Root Certification Authorities store).

Option B: Disable Certificate Validation (Not Recommended for Production)

• Add the connection string parameter:

  Encrypt=True;TrustServerCertificate=True;

• This tells the client to ignore certificate trust validation.
• In Fabric, you can set this in the Advanced settings of the connection.

3. Verify Gateway Configuration

• Make sure the gateway is updated to the latest version.
• Restart the gateway after any certificate or environment variable changes.

4. Double-Check Service Principal Permissions

• In SQL Server, create a contained user mapped to the service principal

CREATE USER [<service-principal-name>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<service-principal-name>];
​

• Ensure the service principal has the correct roles for mirroring.

---

✅ Recommended Approach

• Use TrustServerCertificate=True temporarily to confirm connectivity.
• Then replace the self-signed certificate with one from a trusted CA for production.

--------------------------------

I hope this helps, please give kudos and mark as solved if it does!

Connect with me on LinkedIn.

Subscribe to my YouTube channel for Fabric/Power Platform related content!

[v-saisrao-msft]

Hi @Iaco96,

Checking in to see if your issue has been resolved. let us know if you still need any assistance.

Thank you.

[svenchio]

Hi @Iaco96  I think I would like to start with some simple questions to understand where you are in a series of steps requiered to stablish  a Mirrored SQL Server;

#1. It all starts with a Gateway running and updated to the lastet version,  from your issue description you mentioned a gateway being present, but, to the lastest update? 

#2. Next is to create a connection to the on-prem sql via the gateway, have you created this? Is it here where you get the error trying to create the connection? or are you skipping the test of the connection? using a service principal works at connection level? have you tried other auth methods (e.g. Basic or Windows), they worked?  

#3. On the target on-prem sql server, have you successfully created the service principal using FROM EXTERNAL PROVIDER? Has it been successfull? That is, is your on-prem sql server hosted and able to sync to the Azure Entra ID where the service-principal exists? 

CREATE USER [<service-principal-name>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<service-principal-name>];

Mirroring has many different moving part, hence, fail points to consider and it's important to check one component at a time to get this figured it out! Before I suggest anything, more information is needed for properly guide you towards the resolution of your issue.  

Start with the questions above and ping back  ... all the very best. 

[Iaco96]

Hi @svenchio 

here the details you requested:

1. Gateway version
   The gateway is running the version: 3000.278.5. Is this the last version according to you?

2. Connection creation
   I created the connection during the setup of the SQL Server database mirror, following the same steps you described and selecting Service Principal as the authentication method.
   At that point, I get a permission-related error.
   I also tested using Basic authentication, and in that case it works correctly.
   This suggests the issue is specifically related to using an AD identity such as the Service Principal.

3. Service Principal on SQL Server
   On the master database, the login for the Service Principal has been created.
   On the target database, the corresponding user has been created correctly and assigned the sysadmin role.

[v-saisrao-msft]

Hi @Iaco96,

Have you had a chance to review the solution we shared by @svenchio? If the issue persists, feel free to reply so we can help further.

Thank you.

[svenchio · Accepted solution]

@Iaco96  the most current verion is 3000.294.7 as show on the first pic I sent, and if you were able to create the Service Principal on SQL Server with the ... 

EXTERNAL PROVIDER

 confirms your SQL instance been able to communicate with Azure Entra ID, thus, I agree with you: 

This suggests the issue is specifically related to using an AD identity such as the Service Principal.

Perhaps is not supported "[...] you cannot directly connect to an on-premises SQL Server via the On-premises Data Gateway using a Microsoft Entra (Azure AD) service principal — the gateway does not natively support service principal authentication for on-premises SQL connections" 

I would suggest you to reach out MSFT via a support case for confirmation.

Hope it helps, wish you best of lucks 

[wardy912 · Accepted solution]

Hi @Iaco96 

 This is AI generated and unverified but I hope it helps!

This error typically occurs because the SSL certificate presented by your on-prem SQL Server isn’t trusted by the gateway or the Fabric service when using Azure Entra ID authentication. Let’s break down the key points and possible solutions:

---

✅ Why This Happens

• When connecting via Azure Entra ID (service principal), the connection uses TLS/SSL for encryption.
• If the SQL Server instance uses a self-signed certificate or one issued by an internal CA that isn’t trusted by the gateway machine, the SSL handshake fails.
• The environment variable PBI_SQL_TRUSTED_SERVERS only bypasses name mismatch checks, not certificate trust issues.

---

🔍 Steps to Fix It

1. Ensure SQL Server Supports Azure AD Authentication

• SQL Server 2022 supports Azure AD authentication, but you must:
  • Enable Azure Active Directory authentication on the instance.
  • Configure the service principal in Azure AD and grant it access to the database.
  • Use ODBC driver 18 or later (Fabric uses this internally).

2. Fix the Certificate Trust Issue

You have two main options:

Option A: Install a Trusted Certificate

• Use a certificate from a trusted CA (public or internal) on your SQL Server.
• Ensure the gateway machine trusts the issuing CA (add it to the Windows Trusted Root Certification Authorities store).

Option B: Disable Certificate Validation (Not Recommended for Production)

• Add the connection string parameter:

  Encrypt=True;TrustServerCertificate=True;

• This tells the client to ignore certificate trust validation.
• In Fabric, you can set this in the Advanced settings of the connection.

3. Verify Gateway Configuration

• Make sure the gateway is updated to the latest version.
• Restart the gateway after any certificate or environment variable changes.

4. Double-Check Service Principal Permissions

• In SQL Server, create a contained user mapped to the service principal

CREATE USER [<service-principal-name>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<service-principal-name>];
​

• Ensure the service principal has the correct roles for mirroring.

---

✅ Recommended Approach

• Use TrustServerCertificate=True temporarily to confirm connectivity.
• Then replace the self-signed certificate with one from a trusted CA for production.

--------------------------------

I hope this helps, please give kudos and mark as solved if it does!

Connect with me on LinkedIn.

Subscribe to my YouTube channel for Fabric/Power Platform related content!

[Iaco96]

Hi @wardy912,

thank you for your response. However, for option B regarding the issue fix, there is no Advanced Setting available for the Fabric connection