SQL queries does not work after upgrading to 2023.1

We have some reports and widgets that queries data from other databases than the default orion databases (however they resides on the same SQL server and the orion user account have access rights).

Before upgrading to 2023.1 we could access other databases with a simple USE statement. Now this gives us a security error;

Error executing SQL: The server principal "S-1-9-3-672660477-1187579460-1546597023-3964073033" is not able to access the database "<snip>" under the current security context.

Does anyone else have this problem, or any clue why we get this error ?

Find more posts tagged with

Accepted answers

All comments

dsimpkins

i had something similar but not the same, Found there was a user account inside the database called "Reporting" that was missing select permissions. Granted this and my issues cleared up.

monitoringlife

Also having the same issue.

monitoringlife

What exactly did you have to do to get this working? I see a 'reportingclient' user but its on the DB level and cant access other DBs on the instance.

lll8lll

Thx to dsimpkins pointing me in the right direction (and absolutely no thanks at all to SW support), I figured it out;

The reports are now executed by a new database user that SW has created ; reportingclient.

You can verify this by creating a SQL report with; SELECT SUSER_NAME(), USER_NAME(); and preview the result.

We solved it by giving this user the access rights it needed on the other databases.

lll8lll

I am sorry, but I was a bit ahead of myself with the solution. I thought it was as simple as that, but since this user is a database level user without a password, it was not possible to grant it access to other databases.

So we found the problem, but have no solution yet.

monitoringlife

You make any progress the report issue?

juniorbink

GRANT SELECT ON [SolarWindsOrion].[dbo].[SEUM_RecordingSteps] TO [Reporting];

dgsmith80

I'm also now encountering this issue. The fix mentioned by @dsimpkins is the most logical, but my question to SolarWinds is still why this account was introduced and if it can be changed somewhere within the SolarWinds configuration. I have one client who do not allow local SQL accounts and we have an AD Service Account which was used by the ConfWiz up to this point for all SW <> DB transactions. Now the upgrade has introduced this new Role & Account with no notification or notice in the release notes, and I don't see anywhere to manually define something different.

monitoringlife

Create a user (on the DB you need to query) with same SID as the local user from the Orion DB.

LDave

Hi did you manage to solve this?
I have the same issue and I think that the only solution would be to somehow have the reporting user defined at the server level, instead of at db level as it is now, so that I can then grant permission on other dbs / linked servers.. but how can we do that? We don't even have the password with which the user has been defined by Solarwinds itself.

juniorbink

GRANT SELECT ON [SolarWindsOrion].[dbo].[SEUM_RecordingSteps] TO [Reporting];

LDave

Hi, thanks, but this will grant select rights on that particular table, still inside your orion db, I have to grant a db which is a different one (not the orion one), and that I cannot do it since the user is defined at the database level and not at server level... which is the same issue the OP is encountering, he cannot query other databases on the same sql instance (or external ones via linked server) anymore

LDave

I tried it and it doesn't work either.
The solarwinds' reportinguser is a SQL user without login, I've created exactly the same, with the same SID and the same name on my support db and then I've given it permissions, but it does not work.. still the same error as OP (just a different sid of course).

dbruynb

Yes, same issue, have this document, but we are stuck at step 3 because of the database running in AWS on RDS.

The command

ALTER DATABASE SolarWindsOrion SET TRUSTWORTHY ON;

is not supported on AWS RDS.

https://support.solarwinds.com/SuccessCenter/s/article/Custom-SQL-reports-fail-after-the-upgrade-to-2023-2-Error-executing-SQL-not-able-to-access-the-database-SolarWindsOrionLog-FlowStorage-or-a-custom-database?language=en_US

Any other possible solutions to this issue?

dbruynb

was it required to issue this command also?

ALTER DATABASE SolarWindsOrion SET TRUSTWORTHY ON;

LDave

Yep for me the setting the db to trustworthy was absolutely needed, for both databases and linked servers (together with "cloning" the user)