Can't connect to private Azure SQL server

[Ron]

We have a project (.Net Core / Angular) that we've been working on with a client for the past year. It's an application with Telerik reports to which we both built a development / sandbox for user training as well as vetting changes and we also built a production app for final use. The development app database has open access to VPN and I've had no trouble connecting the reports to the SQL server. The second database, for prod, will not work over VPN and must be used on-site.

What we've been able to vet for the prod Azure SQL server:

1) Both the ActiveDirectoryManagedIdentity connection and username / password connections have been vetted as working.
2) Running the project from a VM where the prod server is local the reports work fine.
3) Deploying the app to Azure causes the Telerik reports to fail with the following message:

"Unable to get report parameters. An error has occurred. Unable to establish a connection to the database. Please verify that your connection string is valid. In case you use a named connection string from the application configuration file, make sure the name is correct and the connection string settings are present in the configuration file of your application"

I was able to confirm that if I make this same connection (username and password rather than Managed Identity) my DefaultConnection as well for other data in the application everything works and the data still pulls, it only seems to fail in the Telerik reports.

I've attached an image demonstrating that the named connections match both in the Telerik reports and in my appsettings.Production.json. What I'd like to know is what next steps you would recommend for resolving this. 

[Todor]

Hi Ron,

Thank you for the detailed description of the issue and the image.

Based on the available information, I suspect your Reporting SqlDataSources may be using the default data provider, which is System.Data.SqlClient that doesn't support the Managed Identity. You may attach a Trace Listener to your Reporting project to check for the server-side error.

I suggest trying with Microsoft.Data.SqlClient Data Provider instead.

If the above is not relevant or doesn't help, please consider sending the Stack Trace of the error.

Regards,
Todor
Progress Telerik

[Ron]

Todor,

Thank you. I did some research and installed Microsoft.Data.SqlClient, I'm using Telerik Reporting R2 2021 so I put it in ProviderName and per the definition.xml that took:

<DataSources>
    <SqlDataSource QueryDesignerState="null" ParameterValues="null" ConnectionString="SparePartPrd" ProviderName="Microsoft.Data.SqlClient" SelectCommand="{redacted}" Name="sqlDataSource1" />
  </DataSources>

I used the trace to see what was happening and here's the synopsis:

Relevant appsettings.json files:

appsettings.Development.json:

  "ConnectionStrings": {
    "DefaultConnection": "{redacted}",
    "SparePartPrePrd": "{redacted}"
  },


appsettings.Production.json:

  "ConnectionStrings": {
    "DefaultConnection": "{redacted}",
    "SparePartPrd": "{redacted}"
  },


<DataSources>
    <SqlDataSource QueryDesignerState="null" ParameterValues="null" ConnectionString="SparePartPrd" ProviderName="Microsoft.Data.SqlClient" SelectCommand="{redacted}" Name="sqlDataSource1" />
  </DataSources>

In my production app environment variables: ASPNETCORE_ENVIRONMENT=Production


The new error:

MyProj Error: 0 : DataSource connection string: SparePartPrePrd; Resolved connection string: SparePartPrePrd; Exception: System.ArgumentException: Format of the initialization string does not conform to specification starting at index 0.
   at System.Data.Common.DbConnectionOptions.GetKeyValuePair(String connectionString, Int32 currentPosition, StringBuilder buffer, Boolean useOdbcRules, String& keyname, String& keyvalue)
   at System.Data.Common.DbConnectionOptions.ParseInternal(Dictionary`2 parsetable, String connectionString, Boolean buildChain, Dictionary`2 synonyms, Boolean firstKey)
   at System.Data.Common.DbConnectionOptions..ctor(String connectionString, Dictionary`2 synonyms)
   at System.Data.SqlClient.SqlConnectionString..ctor(String connectionString)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(DbConnectionPoolKey key, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions)
   at System.Data.SqlClient.SqlConnection.ConnectionString_Set(DbConnectionPoolKey key)
   at System.Data.SqlClient.SqlConnection.set_ConnectionString(String value)
   at Telerik.Reporting.Processing.Data.SqlCommandProvider.CreateConnection(String connectionString)
   at Telerik.Reporting.Processing.Data.SqlQueryProvider.CreateConnection()
MyProj Error: 0 : An exception has occurred while processing 'table1' item:
Telerik.Reporting.Processing.Data.SqlDataSourceException: Unable to establish a connection to the database. Please verify that your connection string is valid. In case you use a named connection string from the application configuration file, make sure the name is correct and the connection string settings are present in the configuration file of your application.
 ---> System.ArgumentException: Format of the initialization string does not conform to specification starting at index 0.
   at System.Data.Common.DbConnectionOptions.GetKeyValuePair(String connectionString, Int32 currentPosition, StringBuilder buffer, Boolean useOdbcRules, String& keyname, String& keyvalue)
   at System.Data.Common.DbConnectionOptions.ParseInternal(Dictionary`2 parsetable, String connectionString, Boolean buildChain, Dictionary`2 synonyms, Boolean firstKey)
   at System.Data.Common.DbConnectionOptions..ctor(String connectionString, Dictionary`2 synonyms)
   at System.Data.SqlClient.SqlConnectionString..ctor(String connectionString)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(DbConnectionPoolKey key, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions)
   at System.Data.SqlClient.SqlConnection.ConnectionString_Set(DbConnectionPoolKey key)
   at System.Data.SqlClient.SqlConnection.set_ConnectionString(String value)
   at Telerik.Reporting.Processing.Data.SqlCommandProvider.CreateConnection(String connectionString)
   at Telerik.Reporting.Processing.Data.SqlQueryProvider.CreateConnection()
   --- End of inner exception stack trace ---
   at Telerik.Reporting.Processing.Data.SqlQueryProvider.CreateConnection()
   at Telerik.Reporting.Processing.Data.SqlDataEnumerable.GetEnumerator()+MoveNext()
   at Telerik.Reporting.Processing.Data.LazyList`1.LazyListEnumerator.MoveNext()
   at System.Collections.Generic.LargeArrayBuilder`1.AddRange(IEnumerable`1 items)
   at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   at Telerik.Reporting.Processing.Data.SeedDataAdapter.Execute(IEnumerable`1 data)
   at Telerik.Reporting.Processing.Data.ResultSetAdapter.Execute(IEnumerable`1 data)
   at Telerik.Reporting.Processing.Data.MultidimentionalDataProvider.Execute(MultidimensionalQuery query)
   at Telerik.Reporting.Processing.DataItemResolveDataAlgorithm.GetDataCore(IDataSource dataSource, MultidimensionalQuery query, IServiceProvider serviceProvider, EvalObject expressionContext, IProcessingContext processingContext)
   at Telerik.Reporting.Processing.DataItem.GetDataCore(IDataSource dataSource, MultidimensionalQuery query)
   at Telerik.Reporting.Processing.DataItem.<>c__DisplayClass42_0.<ResolveData>b__0()
   at Telerik.Reporting.Processing.DataItemResolveDataAlgorithm.ResolveData(String processingId, InMemoryState inMemoryState, MultidimensionalQuery query, Func`1 getDataCore, EvalObject expressionContext)
   at Telerik.Reporting.Processing.DataItem.ResolveData()
   at Telerik.Reporting.Processing.DataItem.ProcessItem()
   at Telerik.Reporting.Processing.Table.ProcessItem()
   at Telerik.Reporting.Processing.ReportItemBase.ProcessElement()
   at Telerik.Reporting.Processing.DataItem.ProcessElement()
   at Telerik.Reporting.Processing.ProcessingElement.Process(IDataMember dataContext)



The standalone report seems to be ignoring my settings and I'm wondering if my setup in Program.cs might not be sufficient:

builder.Services.TryAddSingleton<IReportServiceConfiguration>(sp =>
          new ReportServiceConfiguration
          {
              ReportingEngineConfiguration = ConfigurationHelper.ResolveConfiguration(sp.GetService<IWebHostEnvironment>()),
              HostAppId = TManager.GetSQLDB(),
              Storage = new Telerik.Reporting.Cache.File.FileStorage(),
              ClientSessionTimeout = 720,
              ReportSourceResolver = new UriReportSourceResolver(
              Path.Combine(sp.GetService<IWebHostEnvironment>().ContentRootPath, _config["Root:Report_Base"]))
          });


Let me know if this information yields any additional insights into what might be happening.

[Ron]

One thing I just realized that I didn't label as clearly as I could have:

definition.xml:

<DataSources>
    <SqlDataSource QueryDesignerState="null" ParameterValues="null" ConnectionString="SparePartPrd" ProviderName="Microsoft.Data.SqlClient" SelectCommand="{redacted}" Name="sqlDataSource1" />
  </DataSources>

That's the evidence that the file is indeed set up for both Microsoft.Data.SqlClient and connecting to SparePartPrd despite the error showing that the app is trying to use SparePartPrePrd with System.Data.SqlClient despite that information.

[Todor]

Hi Ron,

Indeed, based on the stack trace, it looks like the data provider used at runtime is System.Data.SqlClient.

Did you try setting the data provider in the configuration for the connection string - see Adding Connection Strings to the Configuration?

The ReportingEngineConfiguration is set up with the following code:

ReportingEngineConfiguration = ConfigurationHelper.ResolveConfiguration(sp.GetService<IWebHostEnvironment>())

What IConfiguration does the method ConfigurationHelper.ResolveConfiguration return? This is essential as it defines what configuration, hence what connection string will be used.

Can you send a sample application demonstrating the problem for investigation?

[Ron]

Todor,

To follow up I did get it working today. I figured out that there was a configuration issue in my appsettings.Production.json that the errors weren't hinting at until I saw the 'Error: 0 : DataSource connection string: SparePartPrePrd'.

The gist of the issue - the reporting location in production was still pointing at the development reports folder and I didn't see any feedback in the errors up until the last few days to get enough certainty that if the problem wasn't in Program.cs or ConfigurationHelper.cs or the reports that it had to either be in something coming back from the Angular report viewer or ultimately an appsettings.Production.json issue. The complaints were initially only about connection string issues which obscured the cause.

One other mention more for in case it's helpful to know going forward - I'm using the Telerik.Reporting NuGet series 18.1.24.514 with 2021 R2 standalone reports. It looks like the SQL credential string is supported and will work however the managed identity version of the string won't and I'm guessing this is because 18.1.24.514 or the 2021 R2 are prior to certain changes enabling that capacity.

Thank you for the additional advice - our first time through pushing reports to a private production server artifact so I appreciate your patience.

[Todor]

Hello Ron,

Thank you for sharing your findings. It is good to know you have overcome the problems.

Regarding the connection string, our code relies entirely on the data provider for resolving it and connecting to the database. If the specified data provider supports the keywords in the connection string, the connection should be successful.

That said, the Reporting functionality itself is not involved with the connection strings. It utilizes the referenced data provider for all data connections. This lets us support all databases that have ADO.NET data providers. This is valid for all Reporting versions, hasn't changed recently and there are no plans for changing it.