Power BI Server – Resolving “Web Portal URLs and Web Service URLs don’t match”

After replacing an expired SSL certificate on Power BI Server, we were no longer able to browse the site – we received an error that “The report server isn’t configured properly… The report server Web Portal URLs and Web Service URLs don’t match”:

We hadn’t changed the Power BI Server settings aside from updating the certificate, but now couldn’t load the site without getting this error message.

We were able to resolve it by manually updating both URLs (Web Service and Web Portal) in Report Server Configuration Manager to the Fully Qualified Domain Name (FQDN) – normally, the HTTP version of the URL is set to “All Assigned” (and shows the server name) and the HTTPS version shows the name on the certificate (in our case, both the old and new certificate had the FQDN). This is how it looked on the other servers that worked:

No obvious errors, but manually changing the HTTP URL for both Service and Portal to FQDN fixed the Power BI Server site – things started to work again instantly. This bothered me, though – none of the other servers needed this setting changed, and they were all perfectly fine with the server short name in the HTTP URL.

Cause/Solution:

The issue ended up being Internet Information Services installed on the same server – even though the primary site was disabled, it was bound to port 80 on all IPs without a host name, so it was claiming the “HTTP://+:80” that Power BI Server was also trying to bind. Because this bind was failing, Power BI couldn’t register matching URLs and gave us the error.

To resolve it, we added a phony hostname to the Default Web Site in IIS (even though it was stopped and shouldn’t have caused an issue) so that that it didn’t claim everything at Port 80:

Once this was done, we set the Power BI configuration back to “All Assigned” and it re-registered its own URLs, and then things started working perfectly again. I’m not sure if IIS was installed by our server admins during our certificate update, or if some other configuration change caused IIS to change how it registered the URL, but this resolved this issue.

Hopefully it helps somebody else!

Clean up vendor names and other data with unwanted numbers/characters

In creating an accounting report, the vendor names we get back from our credit card processor needed some major clean-up: “52334SOUTHWESTAIR1234”, “ABD2343-BLUE DINER 843”, and so on. I initially found a great function for this from Pinal Dave:

http://blog.sqlauthority.com/2007/05/13/sql-server-udf-function-to-parse-alphanumeric-characters-from-string/

But I wanted to make a few enhancements to it:

  1. He leaves numbers in the string and I’d like to remove them
  2. I’d like to combine multiple spaces in a row into a single space, but leave spaces intact

The first is pretty easy to do – just remove the [0-9] and add a space to the PATINDEX. The second one uses a trick from another post I did a few years ago.

Here’s the modified version:

CREATE FUNCTION dbo.UDF_ParseAlphaChars2
(
   @string VARCHAR(8000)
)
RETURNS VARCHAR(8000) WITH SCHEMABINDING
AS
BEGIN
   DECLARE @IncorrectCharLoc SMALLINT
   SET @IncorrectCharLoc = PATINDEX('%[^ A-Za-z]%', @string)

   WHILE @IncorrectCharLoc > 0
   BEGIN
      SET @string = STUFF(@string, @IncorrectCharLoc, 1, '')
      SET @IncorrectCharLoc = PATINDEX('%[^ A-Za-z]%', @string)
   END

   -- Trim groups of spaces into single space
   SET @string = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(@string,' ','<>'),'><',''),'<>',' ')))

   RETURN @string
END
GO

--Test
SELECT dbo.UDF_ParseAlphaChars2('ABC”_I+{D[]}4|:e;””5,<.F>/?6')
SELECT dbo.UDF_ParseAlphaChars2('52334SOUTHWESTAIR1234')
SELECT dbo.UDF_ParseAlphaChars2('ABD2343-BLUE DINER 843')
GO