Service Principal for semantic model authentication from SQL Analytics Endpoint

[amaaiia]

I have some tables in a Lakehouse and I want to use these tables as a data source for my PBI report. The semantic model connects to these tables using my personal account, but I have registered an APP to be able to use Service Principal as an authentication method.

The app is included in a security group X, and I have given access to the workspace where the Lakahouse is located to this security group, so the app has access to this data.

I try to create the connection in the following way:

The server is the SQL connection string of the SQL Analytics Endpoint associated to my Lakehouse.

As you can see, I get that error.

What additional configuration do I have to apply to the app?

[GilbertQ · Accepted solution]

Hi @amaaiia 

As mentioned, I don't think service principles are currently supported

[GilbertQ]

Hi @amaaiia 

That applies to the Warehouse from the Blog Post. And the way that he is connecting is using a .Net App which unfortunately will not work in the Power BI Service.

[amaaiia]

Hi @GilbertQ ,

Mmm he is also connecting to the Lakehouse SQL Endpoint through SQL Server Management Studio using Service Principal. This is exactly what I'm trying to do. Well, it doesnt' matter if I create the connection from SQL Server Management Studio (I've also tried it) or from Fabric. At the end, what I'm trying to create is a connection to a SQL Analytics Endpoint string with a Service Principal authentication. But I can't.

Just check the post:

1. He gives SP acces to the Lakehouse (in my case, I have given access SP to the whole workspace with Contributor role):

2. Create the connection thorugh SQL Server Management Studio, indicating Server as SQL Endpoint and Authentication as SP:

Regards.

[GilbertQ · Accepted solution]

Hi @amaaiia 

As mentioned, I don't think service principles are currently supported

[amaaiia · Accepted solution]

I confirm that IT IS possible, both to connect to SQL Endpoint through Management Studio, and to create a connection to a SQL Endpoint by authenticating with Service Principal.

Management Studio:

1. I Copy SQL Enpoint Stirng from Fabric and paste it as Server Name. The I enter service principal values:

2. Connect and:

Success.

New connection in Fabric:

1. From connections pane, I create the new connection with Servive Principal authentication:

2. I have one report accessing data in direct lake mode from a semantic model:

3. In semantic model settings, I change the data source connection as the new serv_princ_conn:

5. I can refresh the semantic model and I can see the data in the report.

So yes, it seems Service Principal is working.

[GilbertQ]

Hi @amaaiia 

As it stands today, it is not currently possible to use service principles to connect to the sequel endpoint I have read that this will change in the future, but for now you would need to use your personal account to connect.

[amaaiia]

Hi @GilbertQ 

So, is this post wrong? https://datacrafters.io/blogs/authenticating-using-service-principal-in-microsoft-fabric-sql-endpoin...