Problems moving Report Server catalog
-
Books On Line makes breaking out the report server databases on to a separate machine from the report server itself sound very easy. I have tried it and it is but I can't get the report server to connect to the databases once they are on the other machine.
Everything was done by the book(s). We use SSRS only for intranet use so all inside firewall. The SQL Server 2005 services on both machines (including the RS Windows service) all run as the same domain administrator account, this account was used to log in to RS Config Tool and it was told to use this account under Windows Authentication to connect to the database server. The two machines are in the same domain, even on the same switch. The account has sysadmin rights on both SQL Servers, as well as having all the RSExecRole rights as specified in BOL. And it has rights to logon as a service in group policy on both machines. Its password hasn't been changed.
The RS Config Tool completed fine and reported no errors.
But when you try to connect to the report server you get the error:
"The report server cannot open a connection to the report server database. The logon failed. (rsReportServerDatabaseLogonFailed)" The log shows that a privilege required by the client is missing but doesn't specify what! I thought this was all taken care of when the RS Config Tool told it which account to connect with.
This is driving me daft. It should be so easy!! Does anyone have any idea what might be wrong? Nothing says I need to do anything with the encryption key when I am keeping the report server itself exactly as it was and just moving the catalog - or have I missed something here?
Grateful for any ideas.
-
I am not quite sure and cannot test it right now. Bute maybe it goes this direction:
The encryption key inside that Catalog database is secured by the Service master key of the FORMER server. Maybe ypu should re-secure it with the service master key of the new server.
Hope I didn't miss the point. Please post the result.
AW
Andreas
---------------------------------------------------
MVP SQL Server
Microsoft Certified Master SQL Server 2008
Microsoft Certified Solutions Master Data Platform, SQL Server 2012
www.insidesql.org/blogs/andreaswolter
www.andreas-wolter.com -
Thanks for the suggestion Andreas.
Since at present there is nothing else on the new server I completely regenerated the service master key which should I think have performed what you suggested.
However it has made no difference, still getting same error!
-
and Re-encrypted the DATABASE-Master Key with the new Service Master Key?
ALTER KEY -- DB MAster Key
ADD Encryption -- service master key or your password for testing
Andreas
---------------------------------------------------
MVP SQL Server
Microsoft Certified Master SQL Server 2008
Microsoft Certified Solutions Master Data Platform, SQL Server 2012
www.insidesql.org/blogs/andreaswolter
www.andreas-wolter.com -
Andreas thanks for sticking with me but there is no database master key. I haven't created one and apparently the Reporting Services installation doesn't create one. I did try anyway to run the code you suggest and just got the error message that it couldn't find any such key.
I had in any event used the RS Config Tool to recreate the RS encryption key which is a different thing.
The error message I am getting is that "A required privilege is not held by the client". I honestly don't think it is an encryption problem. I have given the RS windows service account, which is a domain administrator, virtually every privilege in the book on the new server hosting the (moved) report server databases and I am still getting the error!
-
Hi, I'm sorry
was just a good guessing, since I have no time to test it presently.
Next week I'll have a Class in Reporting-Services.. I will definetely find spare time to test it then. If you didn't find out till then.
Andreas
---------------------------------------------------
MVP SQL Server
Microsoft Certified Master SQL Server 2008
Microsoft Certified Solutions Master Data Platform, SQL Server 2012
www.insidesql.org/blogs/andreaswolter
www.andreas-wolter.com -
I have finally fixed this problem.
The "privilege required by the client" turned out to be "Act as part of operating system" for the ASPNET user (under which the RS web service runs) on its own native machine which is Windows 2000 Server.
Since it did not require this privilege when the report server catalog was also on this machine then I can only assume it became necessary either in principle when that catalog was moved, in which case you would think Books On Line would tell you, or because they had been moved to a Windows 2003 server machine and there then appeared some obscure (to me anyway!) conflict.
I don't suppose this would be much of a fix if either of the servers involved faced the internet but they are entirely internal so I am going with it.
Thanks for trying anyway Andreas it did spur me to some overdue research into the new encryption functions in SQL Server 2005!
-
very good.. I will try that myself anyway, it seemy like its worth an extra practice.
Sorry for hinting you in the wrong direction, but I think the encryption-topic is definetely worth some time and would hit you some time anyway, because SQL Server is using it a lot already under the covers
Andreas
---------------------------------------------------
MVP SQL Server
Microsoft Certified Master SQL Server 2008
Microsoft Certified Solutions Master Data Platform, SQL Server 2012
www.insidesql.org/blogs/andreaswolter
www.andreas-wolter.com
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply