Trying to login to a SQL Analytics endpoint of Microsoft Fabric

M4Al commented 1 month ago

What happened?

I tried several options to get a connection stirng that would connect me to a Warehouse SQL Fabric endpoint from a container running in a Container Application Environment

I tried using a service principal: "Server=<<ID>>.fabric.microsoft.com;Initial Catalog=DWH;Authentication=Active Directory Service Principal;Persist Security Info=False;User ID=<<client>>@<<tenant>>;Password=<<secret>>", (with or without the @ tenantId section

I tried a user assigned managed identity: Server=<<ID>>.datawarehouse.fabric.microsoft.com;Initial Catalog=DWH;Authentication=ActiveDirectoryIntegrated;Trusted_Connection=NO The container id evidently running with this identiry, and the identiry has access to the SQL Endpoint and the Workspace in Fabric

The interesting part is that it works just fine from my local PC if I clone the repo, and run it from Visual Studio, using the Service Principal approach.

I'm fresh out of ideas here ... so any suggestions? Greets Kobe

Version

1.2.9-rc

What database are you using?

Azure SQL

What hosting model are you using?

Container Apps

Which API approach are you accessing DAB through?

REST

Relevant log output

Azure.DataApiBuilder.Service.Startup[0]
      Unable to complete runtime initialization. Refer to exception for error details.
      Azure.DataApiBuilder.Service.Exceptions.DataApiBuilderException: Cannot obtain Schema for entity ports with underlying database object source: gold_ops.ops_dim_port due to: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 26 - Error Locating Server/Instance Specified)
         at Azure.DataApiBuilder.Core.Services.SqlMetadataProvider`3.HandleOrRecordException(Exception e) in /_/src/Core/Services/MetadataProviders/SqlMetadataProvider.cs:line 100
         at Azure.DataApiBuilder.Core.Services.SqlMetadataProvider`3.PopulateObjectDefinitionForEntity(String entityName, Entity entity) in /_/src/Core/Services/MetadataProviders/SqlMetadataProvider.cs:line 1116
         at Azure.DataApiBuilder.Core.Services.SqlMetadataProvider`3.PopulateObjectDefinitionForEntities() in /_/src/Core/Services/MetadataProviders/SqlMetadataProvider.cs:line 1054
         at Azure.DataApiBuilder.Core.Services.SqlMetadataProvider`3.InitializeAsync() in /_/src/Core/Services/MetadataProviders/SqlMetadataProvider.cs:line 289
         at Azure.DataApiBuilder.Core.Services.MetadataProviders.MetadataProviderFactory.InitializeAsync() in /_/src/Core/Services/MetadataProviders/MetadataProviderFactory.cs:line 65
         at Azure.DataApiBuilder.Service.Startup.PerformOnConfigChangeAsync(IApplicationBuilder app) in /_/src/Service/Startup.cs:line 625

Code of Conduct

[X] I agree to follow this project's Code of Conduct

M4Al commented 1 month ago

I have been further investigating this behaviour. It seems to be related to the length of the hostname.

First I verified that I could connect to the SQL Analytics instance with my SSMS to be sure all the data is correct.

When using the IP address, or any of the CNAME's in between the nqf4kgvoqm4ufazdzriupb2pay-sjlwegpsq2sullr32zh7vrazpe.datawarehouse.fabric.microsoft.com and the actual address I get the error "A server name like 20.50.0.77 is not allowed (Framework Microsoft SqlClient Data Provider)"

I'm not sure what checks are happening where exactly but that's what it is.

So if I try to cheat a bit and use a-a.datawarehouse.fabric.microsoft.com to circumvent the checks mentioned before, at that point I get the error: Failed to get datamart connection information.. This validates my assumption that my container can connect to the Fabric SQL Endpoint.

This can be double checked by using the same faulty data in SSMS.

If i put the correct hostname in the connection string, it fails again with the original error from my previous post.

Important details:

The original connection error (The server was not found or was not accessible.) happens after 30 seconds (the connection timeout). This would suggest that the client cannot make a connection to the server
The second error (Failed to get datamart connection information.) with an adjusted hostname happens instantly, suggesting there is a connection to the SQL Endpoint and a functional error was received

I have not further tried to reproduce this issue is a more limited way.

M4Al commented 1 month ago

Upon further invtigation with a nice collegue we managed to find the root cause. If we downgrade the Microsoft.Data.SqlClient from the 5.2.x in the current version to 5.1.4 it all works perfectly.

So it appears something is broken in that library.

(btw, we retested of course on the 1.2.10 release, and build our own container from that version)

Azure / data-api-builder