Category: Solutions

We recently updated our Power BI Report Server with a May 2024 update. Found that we were unable to open any reports using direct query. Paginated reports were all working fine. This also doesn’t apply to any reports using the import mode. We were not having this issue when using the Power BI desktop but only on the web page.

Below is the error message shown when opening the reports using direct query.

I had to check with my Community friends first as I was searching online if any others had the same issue. James Reeves, one of my community friends pointed me to resolution steps by sharing this medium article. Thank you to my friend.

As per my company standard procedures to contact Microsoft support for any issues, I have open a ticket with them to get action steps for resolution. They helped me resolve the issue. I promised them that I will be writing a blog post about how we resolved this issue so anyone having the same issue will have an idea of the resolution steps to this error.

When you check the Change log for Power BI Report Server, Version: 1.20.8910.25479 (build 15.0.1115.165), Released: May 28, 2024 release notes mentioned about the security change made during this update to add the environment variable and system variable on the Power BI Report Server machine.

Steps

Connect to your Power BI Report Server. In the search bar at the bottom > search settings > system > about > Advance system settings > Environmental variables

Click on the New under the Environment Variables > create new variable with name PBI_SQL_TRUSTED_SERVERS. In the variable value (usually, the value shown in your datasource of the direct query report)- give the FQDN (example – mysvr.microsoft.com) or Servernames seperated by commas (example – contososql, contososql2) or Machinename with the * at the end if you want to include all the SQL Server instances on the machine (example – contososql* which includes contososqlinstance1, contososqlinstance2 and so on). Click OK.

Repeat the same by creating the same variable with value in the System Variables too. Click OK.

Restart the Power BI Report Server and now try to connect to the report and you should be able to open it.

Also, do not forget to read the limitations to Connect to SQL Server database from Power Query Online here.

Thank you for reading!

This error is caused Intermittently by the Log reader agent in the transnational replication. When you observe the error, the database principal ‘dbo’ associated to the login mapped to the ‘dbo’ user cannot be impersonated. The database owner or login associated to ‘dbo’ user are not ‘sa’ but other windows authentication/SQL authentication login.

Solution:

1. Stop the Log reader agent
2. Change the ownership of the database using the below query:

ALTER AUTHORIZATION ON DATABASE::Databasename TO sa;
GO

3. Restart the Log Reader Agent.

Conclusion:

It is always advisable to map the ‘dbo’ user of the login to ‘sa’ to avoid the intermittent issues with Log reader agent in Transnational Replication.

Everything seems to be normal until someone yells at a DBA asking why they couldn’t give database role permissions to a login due to this error.

This error occurs when particular login in question is associated to ‘dbo’ user in the database. In other words, this particular login is the ‘db_owner’ of the database which is causing the issue.

This error occurs while we try to give any database role permissions to the login associated with ‘dbo’ user

I recreated the error by creating a new database name ‘Test’, create a new login with name ‘TestUser’ and giving the authorization to the newly created ‘testuser’.

CREATE DATABASE TEST
GO
USE [master]
GO
CREATE LOGIN [TestUser] WITH PASSWORD=N'@eE$9@', DEFAULT_DATABASE=[master]                
GO
ALTER AUTHORIZATION ON DATABASE::[Test] TO [TestUser]
GO

In the Login ‘TestUser’ properties, database mapping tab, you should see the login associated to user ‘dbo’

I then tried to add a database role ‘db_datareader’ to the database Test which throws an error

Solution:

Change the database owner to ‘sa’, then create a user associated to the login in the Database and then add the login to database roles with the new created user.

USE [Test]
GO
-- Change the database owner to 'sa' account
ALTER AUTHORIZATION ON DATABASE::[Test] TO [sa]
GO
USE [Test]
GO
CREATE USER [TestUser] FOR LOGIN [TestUser]
GO
USE [Test]
GO
ALTER ROLE [db_datareader] ADD MEMBER [TestUser]
GO

Conclusion:

These kind of errors seems to be tricky in the beginning, but once we understand the reason behind it, its pretty easy to solve. These kind of errors are caused by couple of reasons including database migration from lower environments of sql server. When the database is migrated to latest versions, the user ‘dbo’ user was not associated with ‘sa’ account. The other reason is when the authorization of the database is changed to specific login, ‘dbo’ user is associated to the login. It is always important to check which login is associated with the ‘dbo’ user in the database. You can check that using the below query:

--check which login is associated with 'dbo' user
select ab.name as DatabaseUser, cd.name as SrvrLogin, ab.type_desc, ab.owning_principal_id, ab.authentication_type_desc
	From sys.database_principals ab
		inner join sys.server_principals cd
			on ab.sid = cd.sid
where ab.name = 'dbo';

The result of the query looks like this:

Hope this helps!

While we upgrade the sql server, we do upgrade the DTS/SSIS packages to the latest versions compatible with the upgraded sql server version. SSIS packages for sql server 2008/2008 R2 are build by Business Intelligence Development Studio (BIDS) and sql server 2012 or higher are build by using SQL server Data tools.

In some situations, Developers build SSIS packages using latest version of Data tools thinking it can be compatible with lower versions of sql server. I recently faced this situation where a developer build a package in 2012 version of data tools and tried to run the package in sql 2008 through sql agent job.

Changing the package version seems to be an easy fix, but usually its not!

Here’s why?

Once you change the package version by right clicking the dtsx package>open in notepad and change the version from 6 (sql 2012 version number) to 3 (sql 2008 version number), the package will be able to run on sql 2008 via sql agent job. But what about the execution results? Are they executing properly? Not always.

To test this scenario, I created a SSIS package using sql 2008 BIDS with name package.dtsx. This package has a execute sql task which executes a simple error raising script to see how these versions handle errors.

Below is the piece of code I use in the execute sql task for both versions of packages

Raiserror(‘Something went wrong’,16,1)

Note: For both the SSIS packages created, in the Execute SQL task properties> set both FailPackageOnFailure and FailParentOnFailure to ‘True’. This will make sure the package fails if the Execute SQL task fails.

Once I created the package, I tried to execute this ‘package.dtsx’ ( created by using sql 2008 BIDS) on sql 2008/2008 R2 using sql agent job. Ideally, it should throw an error.

Executed the job and as expected, job failed. This is an expected behavior.

I Created the same package in Data tools 2012 with name ‘package_2012.dtsx’. Open the package in notepad and Change the package version 6 to version 3 and save the file.

Now run this ‘package_2012.dtsx’ using sql server agent job

Execute the job and check the results, it get executed successfully. In this scenario. this is a wrong behavior. As Execute sql task has raiseerror code in it, package should throw an error, but it didn’t.

Conclusion: By just changing the Package version from higher to lower version numbers may allow lower versions of sql server execute the package through sql agent job but It doesn’t mean that the package is executed efficiently. In this scenario, package should fail by throwing an error but it doesn’t fail and executed as success. It is always better to create the SSIS packages using BIDS or Data tools based on the sql server version on which the package should run.