Category: Data Design

All about Azure Databases, Azure single database, Azure managed instance, Azure Cosmos database, Azure VM based databases

How to embed a Power BI Report Server report into an ASP.Net web application

Every once in a while, teams from different functional areas of the business (i.e. business intelligence, software development, web development etc.) would join forces to form a cross-functional development team with a common goal of integrating a business intelligence artefact such as a SQL Server Reporting Services (SSRS) report into a front-end web application. There are many reasons for forming such a partnership including a lack of report-development skill by web developers, BI team owns a better reporting tool for data visualization, or maybe to prevent the software team from “reinventing the wheel” by developing a report that has already been produced elsewhere.

Read more here

References

https://community.fabric.microsoft.com/t5/Report-Server/Hide-filter-pane-on-Embedded-report/m-p/310162#M2485

https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-report-filter?tabs=powerbi-desktop

Troubleshooting SSL Certificate Binding on SSRS/PBIRS

Common Errors Covered

  • An HTTPS binding already exists for the specified IP address and port combination!
  • We were unable to create the certificate binding. An unknown error has occurred in the WMI Provider.
  • Unable to add a certificate for multiple domains

First Thing’s First

I highly suggest you review your certificate to make sure it is valid. You can do so following these steps:

  1. Right click the start button > Select “Run” > Type in “mmc.exe” and hit enter
  2. Click File > Select Add or Remove Snap-ins
  3. Select the Certificates snap-in and click add
  4. Choose Computer account > Local computer
  5. Select OK
  6. Browse to the Console Root > Certificates > Personal folder.

This brings you to the local system’s personal store of certificates. Your certificate should be imported here. We want to make sure your certificate is valid. First, it should have a key icon. Notice the following screenshot shows a certificate icon but no key icon. This means the certificate is missing a private key. It can be used to encrypt data but not to decrypt it. That makes communication with clients impossible. Clients will use the public certificate to encrypt the data they send to us. We need a corresponding private key to decrypt that data.

Subject (Common Name) and SAN (DNS Name)

The most important properties in a certificate will be the subject (specifically the common name) and the Subject Alternative Name (DNS Name) entries.

If you ever receive the following error stating “Your Connection is not Private” and ERR_CERT_COMMON_NAME_INVALID then it most likely means you failed to include the hostname you’re attempting to use in the DNS Name value.

Note the address bar in the screenshot below shows the hostname “biwin-y”:

If we click the “Certificate (Invalid)” button highlighted above, we will be able to look at all the properties for the certificate. As we discussed earlier, the most important fields are the subject (common name) and the subject alternative name (DNS name) entries. This screenshot shows what they look like for this certificate:

Subject

CN = biwin-y

Subject Alternative Name

DNS = biwin-y.ad.businesswintelligence.com

Understand This Above All Else

The Subject Alternative Name is the most important field because it determines what the certificate will be valid for. If you visit a website on an address (hostname) that is not listed as a DNS value, it will likely be reported as invalid on modern browsers!

For SSRS and PBIRS, the Subject/CN plays an important role as well (but not nearly as important): it determines what address SSRS/PBIRS will automatically create a URL reservation for. The URL reservation will say “I’m going to listen for incoming requests that bear this hostname.” If the URL reservation does not exist, you may get a failure to connect or even a 404 “not found” response status code.

The reason why it’s not as important is because you can always create a URL reservation manually. However, you can’t do anything to remedy a binding used on a hostname that is missing from the DNS value other than requesting a new certificate.

Using Multiple Hostnames with a Single Certificate

If you’ve tried adding creating a certificate with multiple DNS entries expecting to use the same certificate for multiple web addresses (hostnames), you may have noticed that SSRS/PBIRS only create a single URL reservation — and the hostname it uses for that is the single value you provided as the Subject CN. As such, while the certificate would work with all of the hostnames you specified in the SAN DNS field, SSRS/PBIRS won’t be listening for incoming requests on that additional hostname because a URL reservation does not exist for it. There are two different ways to get around this.

The easiest method is to create a certificate that has the Subject CN defined as a wildcard (“*”). This will cause SSRS/PBIRS to automatically create a URL reservation under the wildcard value. Consequently, it will service all incoming requests no matter what hostname they come in with. However, because you will specify only specific hostnames for the SAN DNS field, the certificate will only show as valid when visiting SSRS/PBIRS via one of those hostnames.

The more difficult approach involves manually creating the reservations for any additional hostnames. Define the Subject CN as one of the value you intend to use (one of the SAN DNS values). RS will automatically handle the creation of this hostname. For the others, you’ll need to perform the following steps.

Start by executing the following command in command prompt after you’ve created the certificate binding in the Report Server Configuration Tool.

This will list out all URL reservations on your system. There can be quite a few and you’ll need to copy and paste a lot of the values, so we’re sending the results to a text file (change the path to your own destination.

You are looking for the reservations that correspond with the Subject CN that did get registered automatically. You will be copying those fields, editing them to include your additional domains, and then adding them. What you’re looking for looks something like this:

The entries that end in “wopi” and “PowerBI” only apply to Power BI Report Server, not SQL Server Reporting Services. You now edit the following command:

For each entry, copy the user, the SDDL, and the URL from the entries we obtained via the netsh http show urlacl command — but change the URL’s hostname to match the second URL you’d like to add. You’re adding four entries for PBIRS and two for SSRS.

Once that’s done, you’ll need to manually add the URL reservations to one more place. Open your rsreportserver.config file. Find the following section.

There will be one entry for your /Reports and another for your /ReportServer virtual directories. You will need to duplicate both and edit the hostname of the duplicates to use your second URL. You will want to duplicate this entire section:

Be very careful when executing these steps. One wrong move and you’ll be seeing a 503 error code. But pay attention to detail and you’ll be fine!

An HTTPS binding already exists for the specified IP address and port combination!

Microsoft.ReportingServices.WmiProvider.WMIProviderException: An HTTPS binding already exists for the specified IP address and port combination. The existing binding uses a different certificate from the current request. Only one certificate can be used for each IP address and port combination. To correct the problem, either use the same certificate as the existing binding, or remove the existing SSL binding and create a new binding using the certificate of the current request.

Usually, this happens when you delete a certificate before unbinding it (in my anecdotal experience, anyway). The solution is very simple. You manually delete the binding.

Open up a command prompt and type the following command:

This command lists all active bindings. If the list is too long, you can pipe everything into a text file by doing something like this:

You’ll have to change the path to match a real path. Once you get the output, you’re looking for any entries under the port you’re attempting to bind on. For most people, this means port 443 — the default port for HTTPS. In all likelihood, you will find one or two matching entries. They will look like this:

They will probably match exactly what I’ve listed above. If so, you can simply run the following commands to delete the bindings:

That’s it. You’re done. You should be able to recreate the binding using the Report Server Configuration Manager now without getting a “binding already exists” error.

We were unable to create the certificate binding. An unknown error has occurred in the WMI Provider.

You attempt to bind a certificate but it immediately fails with an error similar to this one:

In this case, you are most likely trying to bind to a public certificate. That’s not going to work. A web server requires both a private and public key to be able to both encrypt and decrypt information. If you only have a public key then you can only encrypt data — you won’t be able to decrypt it. This is obviously a problem. An easy way to tell if this is the problem you’re facing is to examine the certificates panel in MMC. Please refer to the introductory section “First Thing’s First” for more information. To fix this problem, you will need a certificate with both a private and public key. You’re not going to get this working with just a public key.

Alternatively, if you ARE using a certificate with a private key, try marking the private key as exportable on the certificate.

Cleaning the RSReportServer.config File

The most common advice I give when dealing with binding issues (if we suspect any sort of ‘corruption’) is to remove them all and add them again, especially when dealing with certificate/HTTPS bindings. To do this, I often recommend unbinding any HTTPS addresses in the web portal first, followed by unbinding any HTTPS addresses in the web service. If you do this and you still see HTTPS addresses listed in the Report Server Configuration Tool, then you need to manually clean up the RSReportServer.config file as well. These lingering entries, especially when one exists in the “portal” configuration that does not exist in the “service” configuration, can cause your server to malfunction or even become inaccessible. To clean it up, simply open your RSReportServer.config file and clear the HTTPS entries (and potentially the HTTP entries too, if you suspect them of being a problem).

In the above example, you would be removing the following section.

It may come back once you rebind the URLs, but that’s fine. You just want a clean slate for when you create your binding.

Renewing Certificates

Keep these simple steps in mind when renewing a certificate.

  1. Do not delete the old certificate until AFTER the old certificate has been unbound.
  2. Delete the bindings from the Report Manager Configuration Manager for the “Web Portal URL” first. Delete the bindings from “Web Service URL” second.
  3. Add the new bindings to “Web Service URL” third. Add the new bindings to “Web Portal URL” fourth.

I Screwed Something Up

If you’ve gotten yourself to a place where things don’t work, I recommend a reset of the bindings (start from a blank slate).

  1. Using the Report Server Configuration Manager, remove all URL bindings (or just HTTPS if you suspect only HTTPS is the problem) from the “Web Portal” tab using the “Advanced” editor.
  2. Using the Report Server Configuration Manager, remove all URL bindings (or just HTTPS if you suspect only HTTPS is the problem) from the “Web Service” tab using the “Advanced” editor.
  3. If you removed URLs from the “Advanced” editor but they are still listed in the Report Server Configuration manager, that is because they are still present in the RSReportServer.config file. Follow the instructions from the “Cleaning the RSReportServer.config File” section of this article to remove them from there.
  4. After cleaning the configuration file, the Report Server Configuration Manager should no longer display the URLs. Re-open the configuration tool if it needs to be refreshed.
  5. Using the Report Server Configuration Manager, attempt to recreate the bindings under the “Web Service” tab first.
  6. Using the Report Server Configuration Manager, attempt to recreate the bindings under the “Web Portal“.
  7. If while rebinding you get the “We were unable to create the certificate binding. An unknown error has occurred in the WMI Provider” error message, see the relevant section above on how to resolve the issue.

This step saved me:
netsh http delete sslcert ipport=0.0.0.0:443
netsh http delete sslcert ipport=[::]:443

Thanks! Those two “netsh http delete sslcert” commands solved my issue replacing the certificate with SQL Server Reporting Services 2019

Reference

https://learn.microsoft.com/en-us/sql/reporting-services/report-server-sharepoint/configure-reporting-services-to-use-a-subject-alternative-name?view=sql-server-ver16

https://businesswintelligence.com/content/60/troubleshooting-ssl-binding-ssrs

How to configure SSL on Power BI

Using TransactionScope multiple times

Ignore anything about object structure or responsibilities for persistence. This is an example to help me understand how I should be doing things. Partly because it seems not to work when I try and replace oracle with SqlLite as the db provider factory, and I’m wondering where I should spend time investigating.

Let’s begin with an example;

public class ThingPart
{
    private DbProviderFactory connectionFactory;

    public void SavePart()
    {
        using (TransactionScope ts = new TransactionScope()
        {
            ///save the bits I want to be done in a single transaction
            SavePartA();
            SavePartB();
            ts.Complete(); 
        }
    }

    private void SavePartA()
    {
        using (Connection con = connectionFactory.CreateConnection()
        {
            con.Open();
            Command command = con.CreateCommand();
            ...
            command.ExecuteNonQuery();             
        }
    }

    private void SavePartB()
    {
        using (Connection con = connectionFactory.CreateConnection()
        {
            con.Open();
            Command command = con.CreateCommand();
            ...
            command.ExecuteNonQuery();             
        }
    }
}

And something which represents the Thing:

public class Thing
{
    private DbProviderFactory connectionFactory;

    public void SaveThing()
    {
        using (TransactionScope ts = new TransactionScope()
        {
            ///save the bits I want to be done in a single transaction
            SaveHeader();
            foreach (ThingPart part in parts)
            {
                part.SavePart();
            }  
            ts.Complete();    
        }
    }

    private void SaveHeader()
    {
        using (Connection con = connectionFactory.CreateConnection()
        {
            con.Open();
            Command command = con.CreateCommand();
            ...
            command.ExecuteNonQuery();             
        }
    }
}

I also have something which manages many things.

public class ThingManager
{    
    public void SaveThings
    {        
        using (TransactionScope ts = new TransactionScope)
        {            
            foreach (Thing thing in things)
            {
                thing.SaveThing();
            }            
        }        
    }    
}

Its my understanding that:

The connections will not be new and will be reused from the pool each time (assuming DbProvider supports connection pooling and it is enabled)

This depends – e.g. the SAME connection will be reused for successive steps in your aggregate transaction if all connections are to the same DB, with the same credentials, and if SQL is able to use the Lightweight transaction manager (SQL 2005 and later). (but SQL Connection pooling still works if that was what you were asking?)


The transactions will be such that if I just called ThingPart.SavePart (from outside the context of any other class) then part A and B would either both be saved or neither would be.

Atomic SavePart – yes, this will work ACID as expected.


If I call Thing.Save (from outside the context of any other class) then the Header and all the parts will be all saved or non will be, ie everything will happen in the same transaction

Yes nesting TransactionScopes with the same scope will also be atomic. Transaction will only commit when the outermost TS is Completed.


If I call ThingManager.SaveThings then all my things will be saved or none will be, ie everything will happen in the same transaction.

Yes , also atomic, but note that you will be escalating SQL locks. If it makes sense to commit each Thing (and its ThingParts) individually, this would be preferable from a SQL concurrency point of view.


If I change the DbProviderFactory implementation that is used, it shouldn’t make a difference.

The Provider will need to be compatable as a TransactionScope resource manager (and probably DTC Compliant as well). e.g. don’t move your database to Rocket U2 and expect TransactionScopes to work.

Just one gotcha – new TransactionScope() defaults to isolation level READ_SERIALIZABLE – this is often over pessimistic for most scenarios – READ COMMITTED is usually more applicable.

Reference

https://stackoverflow.com/questions/7553971/using-transactionscope-multiple-times