Forum Discussion
Unable to setup mirroring with on-prem SQL 2025 instance
Hi,
Just getting started with Fabric and SQL 2025. Have tried to setup mirroring with the AdventureWorksLT database, but get the following error:
Unable to grant required permission to the source server. PowerBI user with prefix undefined not found
Have checked all the permissions that I can, and everything looks fine. The tenant settings are look correct, with the SQL Server's Service Principal able to use the create connections and pipelines, and able to call the public APIs.
I've been through the Tutorial a number of times, and can't see what I'm doing wrong.
Thanks in advance.
9 Replies
- deborshi_nag
Super User
Hi Recce
Given you’ve enabled both new toggles, typical reasons why you still have the problem could be:- The toggles are scoped to a group that doesn’t include the service principal you’re using for the connection (or the internal “Power BI” app identity that Fabric uses during grant). Make sure the same SP/app is in the same security group as configured in both toggles. Can you check the service principle is included in the security group that you specified?
- You’re creating the mirror as a Workspace Contributor (not Member/Admin), so the automatic grant step can’t complete. Switch your role to Member (or Admin) and retry. Are you a workspace admin/member?
Let me know what you find!
Hi deborshi_nag,
- Created a new security group for this, and the "system-assigned managed identity" / Service Principal of the SQL Server is the only menber. And that SG is assigned to both options.
- Regarding the Workspace, I am the Admin, and while the Service Principal has been "promopted" to Admin (from Contributor)
- deborshi_nag
Can you do this and check again please?
Admin portal -> Tenant settings -> Developer settings and enable Allow service principals to use Power BI APIs
ssritharHi @Recee
From your screenshots:
You have correctly enabled:
Service principals can create workspaces, connections, and pipelines
Service principals can call Fabric public APIs
OneLake access is enabled
These tenant settings are necessary, but not sufficient on their own.
Likely Causes and Fixes
1. Service Principal Not Assigned Proper Roles on SQL Server.Even if Fabric is configured, SQL Server 2025 must explicitly grant access to the service principal. Fix:In SQL Server 2025 (in Azure or on-prem with Entra integration), ensure the service principal used by Fabric is added as a login with appropriate database role
2. Missing Permission to Mirror Fix:
The database you are mirroring allows external identities (Entra/AAD).
The service principal has at least CONNECT, VIEW DEFINITION, and SELECT permissions on required schemas/tables.
3. Power BI Fabric is Expecting a Service Principal That Was Not Configured Correctly often happens when the Fabric workspace or mirroring setup doesn’t know which service principal to use. Fix
When setting up mirroring in Fabric:
Ensure you select “Use a service principal” and provide the client ID explicitly.
Ensure this SP is granted API permissions to Fabric and has tenant-wide access, or is included in the correct security group defined in tenant setting
Go to Power BI Admin Portal > Tenant Settings:
Reconfirm that the service principal or security group is added in both:
"Service principals can create workspaces, connections, and deployment pipelines"
"Service principals can call Fabric public APIs"
If this post helps, then please appreciate giving a Kudos or accepting as a Solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!Hi ssrithar
- Currently not using the Service Prinipal to connect through to on prem SQL Server, rather am currently using a SQL Login. This is our first Arc-Connected SQL Server, and I've not use the SP to connect through the Data Gateway before, trying to remove all variables from this process at the moment.
I can see the SP when I query sys.dm_server_managed_identities. - The SQL Login has the correct permissions in database we are trying to mirror, and using Profiler I can see the last T-SQL call the setup makes is sys.fn_has_change_feed_permissions, which returns a run when executed as that login
- The SP is setup in the security group, which is assigned to the 2 tenant settings.
I can try switching the connection to the SQL Server to use a SP instead of the SQL Login.
- ssrithar
Hi Recce
Given your setup and error context, the core issue is that SQL Login authentication is not supported for mirroring in Microsoft Fabric. Even if the SQL login works locally and passes low-level permission checks (like sys.fn_has_change_feed_permissions), it lacks an Azure Entra identity, which Fabric requires to validate and assign internal security roles for mirroring.
Since you’ve already:
Confirmed that the Service Principal (SP) appears in sys.dm_server_managed_identities
Added the SP to the correct security groups in Fabric tenant settings
Verified SQL login access
You're 90% of the way there — you just need to use the SP to authenticate the connection in Fabric.
Step 1 : Configure SP Access in SQL Server (Arc-enabled)
Run the following once on your Arc-connected SQL Server:
-- Replace <client-id> with the actual SP's Application (client) ID
CREATE USER [<client-id>] FROM EXTERNAL PROVIDER;-- Assign minimal required permissions
ALTER ROLE db_datareader ADD MEMBER [<client-id>];
GRANT VIEW DATABASE STATE TO [<client-id>];
GRANT VIEW DEFINITION TO [<client-id>];
GRANT SELECT ON SCHEMA::[dbo] TO [<client-id>]; -- Or specific schema if different-- Confirm with:
SELECT name, type_desc FROM sys.database_principals WHERE name = '<client-id>';Ensure change tracking is enabled on the database and the target tables.
Step 2: Update Fabric Connection to Use SP
In Microsoft Fabric, when setting up mirroring:
Choose “Service Principal” as the authentication method
Provide:
Tenant ID
Client ID
Client Secret
Fabric will validate the SP identity and permissions using the Data Gateway
Step 3: Validate Data Gateway is Configured Correctly
Ensure the on-prem Data Gateway (Enterprise mode) is:
Online and registered to the same tenant as Fabric
Has the Arc-connected SQL Server added as a data source
Configured to support AAD authentication
You can check this in the Power BI Service > Manage Gateways.
Step 4: Retry Mirroring Setup in Fabric
Now, retry the mirroring setup:
Select the gateway and data source
Authenticate using the SP
Select your AdventureWorksLT database
If everything is configured, Fabric will now detect and validate permissions correctly
Even though the SQL login "works" at the SQL layer, Fabric can't grant required permissions to a login it can't identify in Entra ID, which is what causes the error:
"PowerBI user with prefix undefined not found"
If this post helps, then please appreciate giving a Kudos or accepting as a Solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
- Currently not using the Service Prinipal to connect through to on prem SQL Server, rather am currently using a SQL Login. This is our first Arc-Connected SQL Server, and I've not use the SP to connect through the Data Gateway before, trying to remove all variables from this process at the moment.
- v-veshwara-msft
Community Support
Hi Recce ,
Just checking in to see if you query is resolved and if any responses were helpful.
Otherwise, feel free to reach out for further assistance.Thank you.
