Expecting a non-empty string for 'providerInvariantName' parameter

[Ron]

Good morning.

I wanted to address an issue we're running into while trying to post a .Net Core / Angular app with a folder of standalone reports. For some unknown reason these will work fine on my machine, I have a couple dozen built from the Telerik Reporting standalone designer, but it won't work on an Azure VM.

The need to set up an Azure VM occurred to me when I realized that I couldn't update a SQL connection for a report unless the connection returns as valid, which in this case it's a private SQL resource so they set us up with a VM to momentarily upload the reports and edit them from the same internal domain as the SQL server. The problem I'm running into now is that we get the following error, seemingly unconditoinally, from the Azure VM environment:

Connection unsuccessful. Check the connection string and try again. Details: Expecting a non-empty string for 'providerInvariantName' parameter.

That error comes back even if we add providerInvariantName to the string. Something else is clearly going on, I don't exactly know what but it seems like it's an environment issue with the Azure VM if no changes to the sql string either cause a different error or succeed.

What I'd like to ask for is more insight into what kinds of things can cause this error so we understand the problem space a bit better. Please let us know your thoughts on what we need to look at in order to troubleshoot this.

[Ron]

We actually have a customer deployment waiting on this so it would be great if someone can answer.

[Dimitar]

Hello Ron,

Thank you for the provided information about your scenario!

The error suggests that the data provider for the connection string used by the SqlDataSource(s) in the report is not defined. The connection string and the data provider must be defined in the configuration file of the server application, for ASP.NET Core applications that usually is the appsettings.json file:

  "ConnectionStrings": {
    "Telerik.Reporting.Examples.CSharp.Properties.Settings.TelerikConnectionString": {
      "connectionString": "Data Source=(local)\\SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated Security=SSPI",
      "providerName": "System.Data.SqlClient"
    }
  },

Additionally, depending on what database is used and which data provider it requires, the data provider may need to be registered within our engine. You may visit the How to register a new DbProviderFactory instance in a .NET Core project - Telerik Reporting KB article for the full list of data providers that are registered out of the box as well as how to register new ones.

If you are attempting to use a data provider that is not among the registered ones, it will not be recognized and the engine use the default one for the system. However, since the error specifies that the providerInvariantName is empty, it is possible that there are no data providers installed on the Azure VM and thus there is no data provider for the engine to use.

Depending on your Azure setup, it is also possible that the connection string settings defined in the configuration file are overwritten by the Azure environment. You may visit the On Azure SqlDataSource with named connectionstring fails to connect - Telerik Reporting KB article for more details on those scenarios.

If the above information is not enough to resolve the problem, I would need more details about the case:

• What kind of database is used and which data provider?
• Have the necessary packages for the provider been installed on the service project? For example, to use the Npgsql data provider, unless it is registered on the system, you would need to install the NuGet Gallery | Npgsql 8.0.2 package
• Which version of Telerik Reporting do you use?
• Can you show us the configuration file of the project where the connections are defined?

Besides answering the above questions, could you attach a copy of one of the reports as well as a trace log - How to troubleshoot errors in ASP.NET Core projects - Telerik Reporting, generated on the Azure VM?

I hope that the provided information will help with figuring out the root of the issue.

Regards,
Dimitar
Progress Telerik

[Ron]

Dimitar,

Thank you for the prompt response.

There's something I feel like I need to clear up because I'm sensing there might have been at least some confusion.

This step - what I'm trying to do right now - is not yet attempting to get the reports running within .Net Core / C#, rather I have the standalone Telerik Report Designer (2021 R2) installed on the Azure VM and I'm trying to set up the connection string in there. That is where I'm getting my 'providerInvariantName' error.

If the standalone Telerik Report Designer needs to see the app or it's appsettings.json somehow even while running without it, I've never run into that issue before but I can consider it, the app is being deployed from git so it would not be found in the Azure VM.

Let me know how much of the advice above still applies to this scenario with the additional clarifications I just gave. While I'm sure the information above will be helpful when we're ready to try running these reports again within the application we're not there yet.

[Dimitar]

Hello Ron,

Thank you for the clarification!

I had indeed misunderstood that the issue appears within the .NET Core application instead of in the Standalone Report Designer. In this case, most of the suggestions given in my reply will most likely not be relevant. However, that is not the case for all of them.

I think my assumption that there are no data providers installed on the VM might turn out to be true. From the newly provided information, I assume that the error appears when you attempt to create a new connection with the SqlDataSource Wizard, is that correct?

If that is indeed so and there are no data providers installed on the VM, then the dropdown with the available providers would be empty and this error might be thrown. I recommend installing a data provider that can be used with your database on the Azure VM. For example, if you have an MSSQL database, you may use the Microsoft.Data.SqlClient data provider which unfortunately seems to not have an installer so you would instead need to install it manually from its NuGet package - NuGet Gallery | Microsoft.Data.SqlClient 5.1.5.

The recommended approach for those situations is to use the .NET Standalone Report Designer since it will automatically load the provider after putting the data provider's assemblies in the folder with the executable - Starting the Standalone Report Designer for .NET.

You may have a look at the System.Data.SqlClient Conversion overflows errors within reports in Reporting | Telerik Forums forum post for details on how to enable the Microsoft.Data.SqlClient data provider.

If you have PostgreSQL database you would instead need to use the Npgsql data provider but the process is the same for all of them so even if you do not need the Microsoft.Data.SqlCleint data provider, this post should help with understanding the process of registering a new data provider in the Standalone Report Designer application.

I hope this will help. In case the issue persists even when the required data provider is registered, please follow the Standalone Report Designer Problems Troubleshooting - Telerik Reporting article to generate a trace log from the Standalone application with more information about the error.

 

 

[Ron]

Dimitar,

Thank you for your feedback. I forwarded it to the team whose handling the VM. Will reach back out if there's any additional challenges related to this issue.

[Ron]

A quick update on this - we have the reports working in the VM's, turned Entra off to use SQL creds.

What I'm noticing now is that the reports will work on the VM, they'll work on my machine, but when I publish them to Azure all of them fail with the same error:

An error has occurred while processing Table 'table1':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.------------- InnerException -------------Format of the initialization string does not conform to specification starting at index 0.

I can confirm that neither the connection strings nor queries were changed and there is no 'table1' in my db that I'd call out to. Let me know if there's anything you can think of that we can look at either in the .Net Core project or Azure that might help find the cause.

[Dimitar]

Thanks for the update, I am glad to hear that the reports now work on the VMs!

Let me start by mentioning that the table 'table1' in the error message refers to the report table in the report definition which uses the SqlDataSource component. 

The important part in this case is the inner exception - "Format of the initialization string does not conform to specification starting at index 0". Usually, this error occurs when the format of the connection string is incorrect or when the connection string cannot be found altogether.

Considering that you haven't made changes to the working connection string, I assume that it is missing from the .NET Core project. The connection string used by the report, if they are shared connections, must be defined in the configuration file of the running application. In the case of .NET Core projects, that is usually the appsettings.json file.

If the connection is missing there, please add it alongside the data provider that the connection uses, for example:

"ConnectionStrings": {
  //This connection string will use System.Data.SqlClient as data provider invariant name.
  //"Telerik.Reporting.Examples.CSharp.Properties.Settings.TelerikConnectionString": "Data Source=(local)\\SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated Security=SSPI"

  //This connection string explicitly states the data provider invariant name - mandatory for databases other than MSSQL Server.
  "Telerik.Reporting.Examples.CSharp.Properties.Settings.TelerikConnectionString": {
    "connectionString": "Data Source=(local)\\SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated Security=SSPI",
    "providerName": "System.Data.SqlClient"
  }
},

If the connections are present in the .NET Core app but the error still appears, I suspect that Azure might be unknowingly making changes. We had a similar case in the past, please visit the On Azure SqlDataSource with named connectionstring fails to connect - Telerik Reporting KB article for details on how to resolve it.

 

[Ron]

Dimitar,

Thank you for the above - I did get the connection strings working but a few things I wanted to note:

1) The connection name needed the publication profile name that I established in the standalone reports (adding this in case the examples are ambiguous for anyone else).

2) I tried using the secrets.json to stash the string because it has username and password credentials but this didn't work, it only seems to work out if I do the exact format of "ProfileName": "Sql_String".

Let me know if there's anything special that needs to be done with attempts to access secrets.json, otherwise if it's not available it might be a capability worth adding for security purposes.

[Dimitar]

Hello Ron,

I am glad to hear that you got the connections working!

The connection name should indeed be the one defined in the report designer as that is how it is saved in the report template. When the reporting engine gets to the step where it needs to make the connection to the database to retrieve the data, it takes the connection name as it is saved in the report definition's XML and searches it in the loaded IConfiguration instance.

This is also likely the reason why it does not currently work with the secrets.json format since seting the secrets seems to flatten the data with changes in the property key in the JSON and now the connection cannot be found.

With that being said, it is possible to load the connection strings more safely. The ReportServiceConfiguration class has a property named ReportingEngineConfiguration which accepts an IConfiguration instance. However, you won't find the property in the API ref on the reporting docs site because it is available only in projects that use the .NET Standand 2.0 Telerik.Reporting.dll and the docs are built with the .NET Framework variant.

The property accepts an IConfiguration instance which can be created dynamically by pulling values from the database, environment variable, secrets.json, etc. which means that you can store the connection in a secure way of choice as long as you can create an IConfiguration from them.

For example, in your scenario where you wish to store the password and user as secrets, you may combine the code from the String replacement with secrets section of the official MS Docs for making a connection string from said secrets with a custom method that builds an IConfigurarion from them:

static IConfiguration ResolveSpecificReportingConfiguration(IConfiguration configuration)
{
    var connectionKey = "ConnectionStrings:Movies:connectionString";
    var conStrBuilder = new SqlConnectionStringBuilder(configuration.GetConnectionString("Movies"));
    conStrBuilder.Password = configuration["DbPassword"];
    var connection = conStrBuilder.ConnectionString;

    configuration[connectionKey] = connection;

    return configuration;
}

and then call the method where the ReportServiceConfiguration is initialized, for example:

// Configure dependencies for ReportsController.
builder.Services.TryAddSingleton<IReportServiceConfiguration>(sp =>
    new ReportServiceConfiguration
    {
        ReportingEngineConfiguration = ResolveSpecificReportingConfiguration(sp.GetService<IConfiguration>()),
        HostAppId = "Net7Demo",
        Storage = new FileStorage(),
        ReportSourceResolver = new TypeReportSourceResolver()
            .AddFallbackResolver(
                new UriReportSourceResolver(reportsPath))
    });