Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
emartinezh
Helper I
Helper I

Problem connecting sql analytics endpoint to visual studio


Hello good morning and thank you in advance for your effort helping people.

Since fabric appeared, in my company we're moving all our resources to the fabric environment, we're very excited with this new tool :).

But today we've this problem trying to connect from a visual studio code with Microsoft.Data.SqlClient sdk to get data from a sql analytics endpoint.

We've created a new user in the microsoft entra ID for this but now it's giving us this error.


Microsoft.Data.SqlClient.SqlException (0x80131904): ClientSecretCredential authentication failed: AADSTS700016: Application with identifier 'SVC-XXXX-FAB-DB' was not found in the directory 'XXXX-XXXXX'. This can happen if the application has not been installed by the administrator of the tenant or consented to by any user in the tenant. You may have sent your authentication request to the wrong tenant. Trace ID: 7b444cac-e9f0-438e-bc83-35472d762300 Correlation ID: 550d3038-eec3-47df-930b-efdc554f9451 Timestamp: 2024-04-15 08:22:43Z
---> Azure.Identity.AuthenticationFailedException: ClientSecretCredential authentication failed: AADSTS700016: Application with identifier 'SVC-XXXX-FAB-DB' was not found in the directory 'XXXX-XXXXX'. This can happen if the application has not been installed by the administrator of the tenant or consented to by any user in the tenant. You may have sent your authentication request to the wrong tenant. Trace ID: 7b444cac-e9f0-438e-bc83-35472d762300 Correlation ID: 550d3038-eec3-47df-930b-efdc554f9451 Timestamp: 2024-04-15 08:22:43Z
---> MSAL.NetCore.4.56.0.0.MsalServiceException:
ErrorCode: unauthorized_client
Microsoft.Identity.Client.MsalServiceException: AADSTS700016: Application with identifier 'SVC-XXXX-FAB-DB' was not found in the directory 'XXXX-XXXXX'. This can happen if the application has not been installed by the administrator of the tenant or consented to by any user in the tenant. You may have sent your authentication request to the wrong tenant. Trace ID: 7b444cac-e9f0-438e-bc83-35472d762300 Correlation ID: 550d3038-eec3-47df-930b-efdc554f9451 Timestamp: 2024-04-15 08:22:43Z


The user that we've created is SVC-XXXX-FAB-DB and the connection string we're using is the following.

 

string encodedPassword = Uri.EscapeDataString("xxxxxxxxxxxx");
string ConnectionString = @"Server=XXXXXXXXXXXXXXXXXXXXXXXXXXX.datawarehouse.fabric.microsoft.com; Authentication=Active Directory Managed Identity; Encrypt=True; Database=LK_XXXXXX_XXXXXXX; User Id=XX-XXXX-FAB-DB@XXXXXXXXXXX; Password={encodedPassword}";
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
//conn.Open();
String sql = "SELECT * FROM XXXXXXX";
using (SqlCommand command = new SqlCommand(sql, conn))
{
conn.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine("{0} {1}", reader.GetString(0), reader.GetString(1));
}
}
}
}


Is this the way to connect Visual Studio to this sql endpoint?? or we have to change the authentication type in order to use the created user we've comented before?

Thank you so much in advance.

Best Regards.

Eduardo.

1 ACCEPTED SOLUTION
AndyDDC
Solution Sage
Solution Sage

Hi @emartinezh is the authentication type you're using correct?  You have "Active Directory Managed Identity" but it sounds like you've created a service principal.  Perhaps change this to "Active Directory Service Principal"

 

Connect to Azure SQL with Microsoft Entra authentication and SqlClient - ADO.NET Provider for SQL Se...

View solution in original post

7 REPLIES 7
emartinezh
Helper I
Helper I

Hello all.

Yesterday i deployed the Service Principal and tried to connect to the analysis service workspace created in fabric and i can connect, now i will configure the visual studio connection.

I will answer once i have this done.

Thank you so much.

Best Regards.

Eduardo.

emartinezh
Helper I
Helper I

Hello AndyDDC and thank you for your reply.

We want to connect this throught the bestway, but as fabric still have some limitations and dont have the sql server authentication, ¿what do you think will be the best for connect this ? ¿Managed identity , Service Principal or with this new user created at Microsoft Entra ID but without typing this credential manually all the times ?

I'm developer and i dont know if i will have the necessary permissions to create the resources at Microsoft Entra, but i will ask to the arq team.

Thank you very much on advance 🙂

Best Regards.

Eduardo.

I would use a Service Principal created in Entra for the authentication.

 

Theres a guide here for creating a service principal https://youtu.be/_RXpvWjgZE8?si=F8qWMw1PuPXRWuyw

Hi @emartinezh 

 

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others.
Otherwise, will respond back with the more details and we will try to help.


Thanks.

AndyDDC
Solution Sage
Solution Sage

Hi @emartinezh is the authentication type you're using correct?  You have "Active Directory Managed Identity" but it sounds like you've created a service principal.  Perhaps change this to "Active Directory Service Principal"

 

Connect to Azure SQL with Microsoft Entra authentication and SqlClient - ADO.NET Provider for SQL Se...

Hello AndyDDC.

Finally I got the extraction connection active, i had to create the service principal with the video u post here.

I attach the SDK connection string so somebody can takes this as help.

string ConnectionString = @"Server=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX.datawarehouse.fabric.microsoft.com; 
Authentication=Active Directory Service Principal; 
Encrypt=True; 
Database=LK_XXXXXX_XXXXXXX; 
User Id=xxxxxxxx-xxxxxx-xxxx-xxxx-xxxxxx; 
Password=xxxxxxxxxx";

Where Database=The datalake to connect.

UserID= Application ID of the service principal.

Password= Secret of the service principal.

Thank you

Best Regards.

Hi @emartinezh 

 

Glad that you query got resolved and thank you for sharing the same with the community as you said it can be helpful to others. 

Please continue using Fabric Community for further queries.

 

Thanks.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Expanding the Synapse Forums

New forum boards available in Synapse

Ask questions in Data Engineering, Data Science, Data Warehouse and General Discussion.

MayFabricCarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Kudoed Authors