0

The company requires we use SQL Server 2016 Reporting Services to generate reports. I attempted to restore a database from a backup, but have encountered permission errors when I go to the web portal URL.

I found some advice online about making sure I had the right permissions enabled, however despite making the http web portal url trusted, a domain account with sysadmin permissions, and running the browser as administrator I still encounter the same error - see images for what I'm dealing with.

I'm simply flummoxed as to what my next step should be.

web service

web portal

I've tried adding the permissions within SSMS and adding my domain account to the sysadmin level, but it hasn't changed anything. I think I must be missing some key setting or misunderstanding where I need to enable a permission.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
CrescentDream
  • 1
  • Since the DB was restored, have you tried removing the DB login and then re-adding it. I've had issues in the past where the restored login was in essence "orphaned" (the DB login didn't match the SQLServer login)... the login looked valid and identical but I ran into all kinds of security issues. Removing the login and readding fixed those issues in my instance. – Tim Jarosz Nov 30 '22 at 21:33
  • Those are SSRS's internal application permissions as opposed to SQL login/user permissions. Did you restore the database to a server that was participating in the same (Azure/Active Directory/Windows) domain? The ReportServer..Users table stores binary SIDs for all of the logins, so if the database has moved between domains (or between two servers that were using local Windows authentication) then the actual account SIDs will be different to those stored in the table. – AlwaysLearning Nov 30 '22 at 23:36
  • I don't believe I did - the db was a .bak on an FTP that I downloaded then restored through the restore database task - I think this is likely closer to the root of the issue since it would be two servers using local windows authentication. Would I want to drop the SID from the db? – CrescentDream Dec 01 '22 at 00:06
  • Sometimes this will be due to the VPN if your company is using one. I have encountered this one previously and the reason why it happened was because I'm not connected to our VPN – Cedrick Clemente Dec 01 '22 at 00:11
  • If they were local Windows accounts for two different SQL Servers with their own SSRS that could explain it. One of our customers has a similar issue when restoring PRD databases back in their DEV/UAT environments. I believe they use Powershell/WMI to lookup the actual SIDs of the account names in the new environment and update the SIDs in ReportServer..Users to map them correctly. – AlwaysLearning Dec 01 '22 at 01:30
  • Solution - it turned out to be something wrong with the initial registry install. We did a full reinstall after clearing out the registry and everything worked as expected. – CrescentDream Jan 12 '23 at 02:04

1 Answers1

0

It turned out to be some error in the registry, we did a full clean and reinstall and it worked.

CrescentDream
  • 1