Problem connecting sql analytics endpoint to visual studio

[emartinezh]

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.

[emartinezh]

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]

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.

[AndyDDC]

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

[v-cboorla-msft]

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 · Accepted solution]

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...

[emartinezh]

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.

[v-cboorla-msft]

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.