If you want just to transfer database schema, you can use Extract Data-Tier Application for creating file and Deploy Data-Tier Application for deploying created database schema.
I’ve tried this process on different versions of SQL Server from SQL 2022 to SQL 2017, SQL 2014 to SQL 2012 and from SQL 2014 to SQL 2008R2 and worked well.
SELECT
CONNECTIONPROPERTY('net_transport') AS net_transport,
CONNECTIONPROPERTY('protocol_type') AS protocol_type,
CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
CONNECTIONPROPERTY('local_net_address') AS local_net_address,
CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
CONNECTIONPROPERTY('client_net_address') AS client_net_address
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.
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:
Right click the start button > Select “Run” > Type in “mmc.exe” and hit enter
Click File > Select Add or Remove Snap-ins
Select the Certificates snap-in and click add
Choose Computer account > Local computer
Select OK
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:
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.
Do not delete the old certificate until AFTER the old certificate has been unbound.
Delete the bindings from the Report Manager Configuration Manager for the “Web Portal URL” first. Delete the bindings from “Web Service URL” second.
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).
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.
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.
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.
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.
Using the Report Server Configuration Manager, attempt to recreate the bindings under the “Web Service” tab first.
Using the Report Server Configuration Manager, attempt to recreate the bindings under the “Web Portal“.
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.
