Tag: HTTP

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!

Viewing Supplemental Logging status in your Oracle database

Viewing the status of supplemental logging on all of our Oracle tables is done via the all_log_groups table, but it’s not in an easy-to-use format and needs to be pivoted (and it only shows you which tables have logging of some kind, which makes it difficult to tell which tables don’t have anything). I’ve done that below to provide an easier view of the status of supplemental logging on all tables, regardless of their current status:

  select t.owner, t.table_name,
         max(case when lg.log_group_type = 'USER LOG GROUP'      then lg.always end ) as "User Log Group",
         max(case when lg.log_group_type = 'ALL COLUMN LOGGING'  then lg.always end ) as "All Column",
         max(case when lg.log_group_type = 'PRIMARY KEY LOGGING' then lg.always end ) as "Primary Key",
         max(case when lg.log_group_type = 'UNIQUE KEY LOGGING'  then lg.always end ) as "Unique Key",
         max(case when lg.log_group_type = 'FOREIGN KEY LOGGING' then lg.always end ) as "Foreign Key"
    from dba_tables t
    left
    join all_log_groups lg
      on t.owner = lg.owner
     and t.table_name = lg.table_name
   where t.owner = 'Table Owner'
   group by t.owner, t.table_name
   order by t.table_name

In the recent spat of Goldengate troubleshooting, I’ve had to check these values a number of times and I hope this query is useful to others (since I couldn’t find a good example online that made this information easy to view).

Please let me know below if you have any issues!

Unexpected results in an SSRS report when using Oracle OLEDB driver and OPENQUERY

Here’s a pretty specific situation we ran across recently – a comment in our OPENQUERY text was causing the results of our query to be completely different than expected.

The T-SQL query we’d written in SSMS that fetched data from an Oracle linked server was running in about 30 seconds, but when we put the query directly into an SSRS report, it would never finish. We were stumped, since we were executing the exact same query in both SSMS and SSRS, but SSRS appeared to hang when executing it. We trimmed down our original query (a few CTEs containing OPENQUERY, joined to a handful of local tables) until we found the single CTE that was doing it. Executing that query in SSMS (or executing the OPENQUERY portion directly in PL/SQL Developer) returned results in about two seconds – however, putting that same portion on a report (even using the report wizard and with no special formatting) resulted in a report that never finished running, whether in the report designer or deployed to our SSRS site.

As it turns out, the SSRS engine (it occurred in both the BIDS/SSDT and once the report was deployed) was consolidating the OPENQUERY text to a single line when executing it, rather than respecting the line breaks in our query text. As a result, the “–” that preceded a comment mid-way through the query was actually commenting out the entire rest of the query, a la SQL injection style. This resulted in a completely ignored WHERE clause on the query, and different results – effectively, SSRS was taking this query:

SELECT *
  FROM OPENQUERY(OracleServer,
                 'SELECT *
                    FROM SomeSchema.SomeTable -- Note the comment here
                   WHERE ID = 50')

and executing it like this:

SELECT *
  FROM OPENQUERY(OracleServer,
                 'SELECT * FROM SomeSchema.SomeTable -- Note the comment here WHERE ID = 50')

Since our query wasn’t returning results, but just hanging for 30 minutes before we gave up, we asked our Oracle admin to watch the execution plan after seeing this Stackoverflow question (and the comments on the accepted answer) that suggested that they were seeing SSRS generate a complete different execution plan for the query (by comparing the plan generated when it was executed from both SSMS and SSRS, I’d hoped the difference would be obvious – it was). This didn’t make any sense at all, since the same query should be hitting SQL Server, and so exactly the same OPENQUERY pass-through query hitting Oracle, but the Oracle DBA confirmed that half of our Oracle query was commented out when it came from SSRS. This meant that our 1000 row expected resultset had become a 1.5 billion row dump of the entire table, which would explain why we were seeing the delay on our report!

This didn’t happen in SSMS, and it didn’t happen when previewing the dataset itself in SSRS, but only occurred when previewing the actual report (this is the only case where SSRS submits a modified version of the query to the SQL engine). Removing the comment from the query resolved the issue, as did switching the comment syntax to /* */ from double-dash.

This occurs both in SQL Server Data Tools (based on VS2010) and BIDS 2008 R2, which is what we had handy to test. Also, it appears to only apply to the Oracle OLEDB driver – creating an Oracle linked server via either ODBC, as well a SQL OLEDB linked server, didn’t exhibit the same issue, and the final report results were filtered as expected.

Steps to reproduce the issue:

1. Create a linked server using the Oracle OLEDB driver

2. In SSMS, create a query that accesses the linked server using OPENQUERY with a simple WHERE clause inside

For example, we had something like the following:

SELECT *
  FROM OPENQUERY(OracleServer,
                 'SELECT *
                    FROM SomeSchema.SomeTable
                   WHERE ID = 50')

3. Execute your query and make note of the number of rows it returns

4. Modify your query by adding a short comment at the end of your FROM line – this should not impact the query results at all

SELECT *
  FROM OPENQUERY(OracleServer,
                 'SELECT *
                    FROM SomeSchema.SomeTable -- Note the comment here
                   WHERE ID = 50')

5. Execute your query again – the row count should be unchanged from step 3

6. In BIDS or SSDT, create a new report, either manually or using the wizard, providing your modified query from step 4 as the dataset

7. Once your report is created, view the dataset properties and preview the query results – note that they match the expected row count

To do this, right-click on your dataset in the “Report Data” panel, select “Dataset properties”. When it opens to the Properties window, select “Query designer…” near the bottom, and then click the exclamation point in the toolbar to execute your query and preview the results. You’ll see your filtered result set, as expected. When you’re done, close this window and go back to your report.

8. Preview your report and notice that the WHERE clause is ignored – all rows from your table are displayed

Summary, and take-away

I’m only using the placement of the comment and ignoring the WHERE clause as an example – the comment could be anywhere in the query, and could even result in an query with invalid syntax that refuses to execute at all.

I hope this explanation helps you avoid the two days of troubleshooting we did to get to this point and find the cause! I’m unclear on why the driver behaves this way, or if it’s an SSRS issue specifically (I suspect it is, since it doesn’t occur in SSMS with the exact same query). If anybody can point me to an open Connect item, I’d be happy to vote for it, but until then, I’m making the effort to migrate to using /* */ comment syntax everywhere – not only is it more clear to readers and flexible for in-line comments, it doesn’t break OPENQUERY functionality (and that’s reason enough for me).