SSRS reports -- "The subscription encrypted content was removed."
We have a large migration from SSRS v2005 to v2012. We are not just doing a backup/restore of ReportServer. We are handling it at the level of the data. Meaning, we script all report definition from source and recreate it at the target. I've created 2 procedures to handle this. They do exactly as intended, and seem to work well. Pretty simple --
delete proc - delete all ssrs subscription jobs and schedules from target server
recreate proc - collect all ssrs subscription job definition and schedules from source server, recreate on target server
One piece of the recreate proc changes the report output path from old server to new. The folder hierarchy on the server is the same, but the actual servername is different. I use the mass UPDATE below to perform this change.
-- update report path, changing 'serverX' to 'serverY' in Description and ExtensionSettings
UPDATE ReportServer.dbo.Subscript
SET [Description] = REPLACE([Description],'ser
ExtensionSettings = REPLACE(CAST(ExtensionSett
WHERE [Description] LIKE '%serverX%'
AND ExtensionSettings LIKE '%serverY%'
That updates ExtensionSettings in ReportServer..Subscription
<ParameterValue><Name>PATH
To this:
<ParameterValue><Name>PATH
So that's about it. Everything at the end of the process looks good. The agent jobs are recreated. They are all scheduled properly, and the output path for all reports is correct. All of the data is correct. But -- when I run the job, the report is not generated, nor output to the location on the new server.
I put a log on the Agent job, but unfortunately, it's nothing more than 'the job XXXXXXX has begun...'. I have checked the reports themselves, within ReportServer..Subscription
'The subscription encrypted content was removed.'
Somewhere in the process I lost the encrypted content, and I need some help with this correction. There are more than 2000 reports. Best case, this is handled in mass. Hopefully this was an avoidable error in my process, because this is not a one-time thing. The customer will perform this process multiple times while testing this cut-over. The process being as I said before -- delete all subscription jobs/schedules on target server, collect definition and recreate all subscription jobs/schedules
Are there any Experts able to assist?
delete proc - delete all ssrs subscription jobs and schedules from target server
recreate proc - collect all ssrs subscription job definition and schedules from source server, recreate on target server
One piece of the recreate proc changes the report output path from old server to new. The folder hierarchy on the server is the same, but the actual servername is different. I use the mass UPDATE below to perform this change.
-- update report path, changing 'serverX' to 'serverY' in Description and ExtensionSettings
UPDATE ReportServer.dbo.Subscript
SET [Description] = REPLACE([Description],'ser
ExtensionSettings = REPLACE(CAST(ExtensionSett
WHERE [Description] LIKE '%serverX%'
AND ExtensionSettings LIKE '%serverY%'
That updates ExtensionSettings in ReportServer..Subscription
<ParameterValue><Name>PATH
To this:
<ParameterValue><Name>PATH
So that's about it. Everything at the end of the process looks good. The agent jobs are recreated. They are all scheduled properly, and the output path for all reports is correct. All of the data is correct. But -- when I run the job, the report is not generated, nor output to the location on the new server.
I put a log on the Agent job, but unfortunately, it's nothing more than 'the job XXXXXXX has begun...'. I have checked the reports themselves, within ReportServer..Subscription
'The subscription encrypted content was removed.'
Somewhere in the process I lost the encrypted content, and I need some help with this correction. There are more than 2000 reports. Best case, this is handled in mass. Hopefully this was an avoidable error in my process, because this is not a one-time thing. The customer will perform this process multiple times while testing this cut-over. The process being as I said before -- delete all subscription jobs/schedules on target server, collect definition and recreate all subscription jobs/schedules
Are there any Experts able to assist?
ASKER
As I said, I need some help with this correction. It's less about what I may have missed than it is the fact that the encrypted content was removed. I was hoping for Expert assistance with the encrypted content. Hopefully in mass, as I said, given that I am dealing with more than 2000 reports.
Here is Microsoft's migration procedure: Migrate a Reporting Services Installation (Native Mode). It does not cover EOL versions such as SQL Server 2005, but it includes many details that would apply. For the case where a new target database must be created, it calls for using the web service to copy the report data, and offers sample code for creating the RS.exe script. Changing paths in an RS script is a text search-and-replace operation, as opposed to directly modifying the report server DB.
I have use RSScripter in the past to create migration scripts. Passwords must be inserted manually, as is the case with the sample script generator mentioned above. The tool is not actively supported, but can be found with a bit of Googling. It is an old tool which should work with RS 2005, and I have used it with RS 2012.
I have use RSScripter in the past to create migration scripts. Passwords must be inserted manually, as is the case with the sample script generator mentioned above. The tool is not actively supported, but can be found with a bit of Googling. It is an old tool which should work with RS 2005, and I have used it with RS 2012.
ASKER
Yes, I've seen that reference. Regardless, the method I've used is the route the customer wishes to take, and I need assistance correcting the problem at hand.
'The subscription encrypted content was removed.'
'The subscription encrypted content was removed.'
ASKER
Are there any SSRS experts that can advise? What is the correction to 'The subscription encrypted content was removed'?
The problem is the links between the report, the data-sources and the shared data-sets. These links are stored in the ReportServer database. Also, the reports are divided into Chunks & Segments. The storage is encrypted using the master key (which you can generate/backup using the Reporting Services Configuration Manager). The source will have a different key, whereas the target machine will have a different key - which is why SSRS is unable to preserve your encrypted content.
Instead of doing mass updates, here's what I would suggest - if you have the report definitions, data sources and data sets available, can you evaluate using the Report Server Command Line utilities (https://msdn.microsoft.com/en-us/library/ms157252(v=sql.105).aspx)? The utility would ensure to use the key from the target machine, while you will have full scripting control.
Instead of doing mass updates, here's what I would suggest - if you have the report definitions, data sources and data sets available, can you evaluate using the Report Server Command Line utilities (https://msdn.microsoft.com/en-us/library/ms157252(v=sql.105).aspx)? The utility would ensure to use the key from the target machine, while you will have full scripting control.
ASKER
I understand the suggestion, but the customer wants to handle it this way. As I said in the initial post, it is a migration from v2005 to v2012, and there will be several attempts to cut over and test, before the final migration. Running a procedural driven cutover will be much smaller/easier than the ReportServer db restore and the associated actions. Of course, I will consider another method, if necessary, but I don't believe that is the case yet.
Can you tell me, what is the corrective action for this problem --
'The subscription encrypted content was removed.'
Can you tell me, what is the corrective action for this problem --
'The subscription encrypted content was removed.'
That is exactly what I am trying to tell. The reason you are facing the problem is that a non-standard, non-supported method is being used and the step for migrating and associating data-sources and data-sets is not handled as required by SSRS. (Not following the recommended way means that Microsoft support will not be available in case something goes wrong with the SQL Server instance).
Here is what Microsoft officially supports: https://msdn.microsoft.com/en-us/library/ms143724.aspx
You can even use the Reporting Services Migration Tool: https://www.microsoft.com/en-us/download/details.aspx?id=29560
In each of these steps and in the tool, the prime importance is given to the encryption key.
In your case, you will need to do a 2 step process:
- Upgrade from SQL 2005 to SQL 2008 using the ReportSync (freeware) tool (https://github.com/dapaxx/reportsync)
- Using the Microsoft tools to migrate from 2008 to 2012.
Here is what Microsoft officially supports: https://msdn.microsoft.com/en-us/library/ms143724.aspx
You can even use the Reporting Services Migration Tool: https://www.microsoft.com/en-us/download/details.aspx?id=29560
In each of these steps and in the tool, the prime importance is given to the encryption key.
In your case, you will need to do a 2 step process:
- Upgrade from SQL 2005 to SQL 2008 using the ReportSync (freeware) tool (https://github.com/dapaxx/reportsync)
- Using the Microsoft tools to migrate from 2008 to 2012.
ASKER
I am not worried about Microsoft right now, Nakul. Again, are you aware of the corrective action necessary to address this problem?
'The subscription encrypted content was removed.'
'The subscription encrypted content was removed.'
As I have been saying, if you migrate the Reports, Data Sources, Data Sets and the Links between them, your problem should theoretically be resolved.
You can script out the required information using https://gallery.technet.microsoft.com/scriptcenter/42440a6b-c5b1-4acc-9632-d608d1c40a5c.
I haven't tried restoring the information to another ReportServer DB, but the mappings generated by the script appear to be correct.
I believe that you will need to populate the mappings in the [dbo].[DataSource], [dbo].[DataSet] tables after fetching the respective Report, DataSource and DataSet Ids from the [dbo].[Catalog] table. That should do the trick.
Please note that other problems may surface because this something that is not recommended and never tried before. I am sorry, but I need to call this out because this process is trying to reverse engineer the SSRS design.
You can script out the required information using https://gallery.technet.microsoft.com/scriptcenter/42440a6b-c5b1-4acc-9632-d608d1c40a5c.
I haven't tried restoring the information to another ReportServer DB, but the mappings generated by the script appear to be correct.
I believe that you will need to populate the mappings in the [dbo].[DataSource], [dbo].[DataSet] tables after fetching the respective Report, DataSource and DataSet Ids from the [dbo].[Catalog] table. That should do the trick.
Please note that other problems may surface because this something that is not recommended and never tried before. I am sorry, but I need to call this out because this process is trying to reverse engineer the SSRS design.
ASKER
I cannot migrate the reports, datasets, datasources, etc., because the current system is production, and it must continue to function as the customer is testing the cutover. It's not just a sql cutover. There are several other components that are migrating as well, and the customer will be testing this cutover numerous times. That is why we've tried to handle the SQL portion of the migration as simply as possible. I will review the link you've provided, but I still don't have the answer to my question --- the LastStatus that I have copied again below -- do you have any idea how to address this particular problem, specifically? To re-add the encrypted content, or repair/recreate the encrypted keys? What is the specific answer for this problem? Do you know?
'The subscription encrypted content was removed.'
'The subscription encrypted content was removed.'
When migrating the reports, datasets & datasources you do not actually need to remove them from the source system. You can download the RDL files, the DataSources and DataSets and then upload to the target system.
From what I understand, you have the subscriptions and jobs migrated, but the underlying objects are not there (the report that the subscription executes, etc).
As I have said in my previous responses, the error can only be truly resolved when all the bits and pieces are migrated over to the target system. I'm sorry, but that's the SSRS architecture.
From what I understand, you have the subscriptions and jobs migrated, but the underlying objects are not there (the report that the subscription executes, etc).
As I have said in my previous responses, the error can only be truly resolved when all the bits and pieces are migrated over to the target system. I'm sorry, but that's the SSRS architecture.
ASKER
>>Here is what Microsoft officially supports: https://msdn.microsoft.com/en-us/library/ms143724.aspx
I'm sure you realize, many times these types of manipulations must be done in unsupported architecture. My SSRS source server is v2005 -- which fell off the supported list in April of this year.
>>You can even use the Reporting Services Migration Tool: https://www.microsoft.com/en-us/download/details.aspx?id=29560
Incorrect. This is one of the details of the migration tool:
Source and target server must be SQL Server Reporting Services 2008 R2 or later.
Again, my source server is v2005.
>>From what I understand, you have the subscriptions and jobs migrated, but the underlying objects are not there (the report that the subscription executes, etc).
Is the rdl not stored in the Catalog table? The ReportServer database was restored to the target server at the beginning of this task, and multiple times throughout this endeavor. When I said 'We are not just doing a backup/restore of ReportServer.', I should have been more clear. The process did begin with a backup/restore of ReportServer from source to target server, and exactly the data that resides in one Catalog table resides in the other.
I really need a solution to this problem. This has become terribly critical.
The reportserver database was backed up and restored to the the target server. I can get into ReportManager on the new server, and run all the reports through the web interface. All SSRS Subscription Agent jobs and schedules were migrated from source to target server. The report output path was also updated from target to source server.
At the surface, all the data looks good, yet the reports are not processed when the subscription agent jobs are run. There is no error anywhere. The only status that I have suggesting anything amiss is the LastStatus column in the ReportServer..Subscription
In the source server, all the reports have something like this, varied only by report name and output path:
The file "report name" has been saved to the "\\serverX\foldername\cust
In the target server, all reports have this, verbatim:
The subscription encrypted content was removed.
Is there not a response to this LastStatus in normal circumstances? There must be something that I did to render the status to this value. No?
I'm sure you realize, many times these types of manipulations must be done in unsupported architecture. My SSRS source server is v2005 -- which fell off the supported list in April of this year.
>>You can even use the Reporting Services Migration Tool: https://www.microsoft.com/en-us/download/details.aspx?id=29560
Incorrect. This is one of the details of the migration tool:
Source and target server must be SQL Server Reporting Services 2008 R2 or later.
Again, my source server is v2005.
>>From what I understand, you have the subscriptions and jobs migrated, but the underlying objects are not there (the report that the subscription executes, etc).
Is the rdl not stored in the Catalog table? The ReportServer database was restored to the target server at the beginning of this task, and multiple times throughout this endeavor. When I said 'We are not just doing a backup/restore of ReportServer.', I should have been more clear. The process did begin with a backup/restore of ReportServer from source to target server, and exactly the data that resides in one Catalog table resides in the other.
I really need a solution to this problem. This has become terribly critical.
The reportserver database was backed up and restored to the the target server. I can get into ReportManager on the new server, and run all the reports through the web interface. All SSRS Subscription Agent jobs and schedules were migrated from source to target server. The report output path was also updated from target to source server.
At the surface, all the data looks good, yet the reports are not processed when the subscription agent jobs are run. There is no error anywhere. The only status that I have suggesting anything amiss is the LastStatus column in the ReportServer..Subscription
In the source server, all the reports have something like this, varied only by report name and output path:
The file "report name" has been saved to the "\\serverX\foldername\cust
In the target server, all reports have this, verbatim:
The subscription encrypted content was removed.
Is there not a response to this LastStatus in normal circumstances? There must be something that I did to render the status to this value. No?
Thank-you for walking through the process that you have already done. Now, it makes things much more clear.
Given that the reports work from the new server's web interface, the part about linking the report to datasets and datasources is all set. We no longer need to worry about that.
Just to confirm, did you update/replace the encryption key in this process by going to: Reporting Services Configuration Manager -> Encryption Keys -> Change?
I am trying to replay the steps you did and will revert back in a couple of hours.
Given that the reports work from the new server's web interface, the part about linking the report to datasets and datasources is all set. We no longer need to worry about that.
Just to confirm, did you update/replace the encryption key in this process by going to: Reporting Services Configuration Manager -> Encryption Keys -> Change?
I am trying to replay the steps you did and will revert back in a couple of hours.
Ok, so I had to change a couple of things on the subscription. Just wanted to confirm that have done the same in your case as well:
1. Verify the Report_OID references the appropriate CatalogId (in your case it should because you restored the DB as-is)
2. Update the USERNAME and PASSWORD values in the ExtensionSettings (you have already updated the PATH value here).
Now because the USERNAME and PASSWORD are encrypted, this one may be causing the problem. Because every SQL Server instance has a different key, your new/target server will be unable to decrypt this information. To ensure that this works, try doing the following:
1. Backup the encryption key from the source server using Reporting Services Configuration Manager -> Encryption Keys -> Backup
2. Restore the encryption key on the target server using Reporting Services Configuration Manager -> Encryption Keys -> Restore
After this step, you may want to reset the encryption key by using Reporting Services Configuration Manager -> Encryption Keys -> Change
In short: Keep your steps as-is. Only ensure that you transfer/copy over the encryption key from one server to another as well so that the decryption works as expected.
1. Verify the Report_OID references the appropriate CatalogId (in your case it should because you restored the DB as-is)
2. Update the USERNAME and PASSWORD values in the ExtensionSettings (you have already updated the PATH value here).
Now because the USERNAME and PASSWORD are encrypted, this one may be causing the problem. Because every SQL Server instance has a different key, your new/target server will be unable to decrypt this information. To ensure that this works, try doing the following:
1. Backup the encryption key from the source server using Reporting Services Configuration Manager -> Encryption Keys -> Backup
2. Restore the encryption key on the target server using Reporting Services Configuration Manager -> Encryption Keys -> Restore
After this step, you may want to reset the encryption key by using Reporting Services Configuration Manager -> Encryption Keys -> Change
In short: Keep your steps as-is. Only ensure that you transfer/copy over the encryption key from one server to another as well so that the decryption works as expected.
ASKER
My apologies for the delay. This issue has definitely become quite urgent. I am very grateful for your input.
One question -- if I backup/restore the encryption key from source to target, what does that do to the current state of the reports? Granted, I realize they are not running through th subscription jobs, but they are available at the web layer. Where will the backup/restore of the ekey leave me?
One question -- if I backup/restore the encryption key from source to target, what does that do to the current state of the reports? Granted, I realize they are not running through th subscription jobs, but they are available at the web layer. Where will the backup/restore of the ekey leave me?
The reports should continue to work as-is.
ASKER
When I restore the key to the new server, will this not overwrite any report credentials? Changing them from new server to old server?
No - the encryption key is used to encrypt the credentials. It effectively "sits above" the data - changing the key will not change the data itself.
ASKER
Alright then. Back soon w/status.
ASKER
>>The reports should continue to work as-is.
They don't. This is now worse than it was, and twice as critical. I have attempted to open several reports via ReportManager, but none of them will open successfully. My actions were exaclty as you suggested -- backup, restore -- and now they don't open. My error message is below.
An error has occurred during report processing. (rsProcessingAborted)
The report server is unable to access encrypted data. Apply a back-up key or delete all encrypted content. (rsEncryptedDataUnavailabl
They don't. This is now worse than it was, and twice as critical. I have attempted to open several reports via ReportManager, but none of them will open successfully. My actions were exaclty as you suggested -- backup, restore -- and now they don't open. My error message is below.
An error has occurred during report processing. (rsProcessingAborted)
The report server is unable to access encrypted data. Apply a back-up key or delete all encrypted content. (rsEncryptedDataUnavailabl
ASKER
Attempting to access the datasources at the Report Mgr level, returns this;
The report server is unable to access encrypted data. Apply a back-up key or delete all encrypted content. (rsEncryptedDataUnavailabl
This is why I asked where the restore of the encrypted key would leave me in the new server.
The report server is unable to access encrypted data. Apply a back-up key or delete all encrypted content. (rsEncryptedDataUnavailabl
This is why I asked where the restore of the encrypted key would leave me in the new server.
If the ReportServer database was restored as-is from the old server, this should work.
What changed after the DB was restored?
At this point, one of the things that can be done is to restore the key back (I trust you also backed up the key from the target instance). Once restored, try changing the encryption key.
Again, I repeat - the process being followed is purely undocumented and unsupported. We are purely experimenting here and experiments can have unexpected results. When you say it's critical, I hope you are not doing this directly on a production instance.
Troubleshooting an experimental process without looking at the environment is a bit difficult, especially when my trial went fine when I did it yesterday. I will give the sequence another shot later tonight and revert back.
What changed after the DB was restored?
At this point, one of the things that can be done is to restore the key back (I trust you also backed up the key from the target instance). Once restored, try changing the encryption key.
Again, I repeat - the process being followed is purely undocumented and unsupported. We are purely experimenting here and experiments can have unexpected results. When you say it's critical, I hope you are not doing this directly on a production instance.
Troubleshooting an experimental process without looking at the environment is a bit difficult, especially when my trial went fine when I did it yesterday. I will give the sequence another shot later tonight and revert back.
ASKER
Yes, the report server database was restored as-is. I can even do it again if you feel that will help remove any question regarding the database. The problem is terribly, terribly urgent and I must determine how best to move forward, and resolve.
To get us back to where we were, please restore it again. That way at least the reports will start working again.
I am setting up a SQL 2005 instance once again to walk through the scenario again.
I am setting up a SQL 2005 instance once again to walk through the scenario again.
ASKER
>>I am setting up a SQL 2005 instance once again to walk through the scenario again.
That is wonderful! Thank you very much for continuing to help me. I have to leave for now, but I will be back early evening.
That is wonderful! Thank you very much for continuing to help me. I have to leave for now, but I will be back early evening.
Ok, after 3 tries, finally I was able to figure out the steps for a fix. Here's what I did. Let me know how it goes for you.
Assume that my source SQL instance is named as "MSSQL2005", while the target is "MSSQL2014" (I have 2014, but it should not make a difference) .
On the source instance:
1. Backup "ReportServer" and "ReportServerTempDB" databases from source server (i.e., "MSSQL2005")
On the target instance:
1. Login to the target environment, launch the SSRS Reporting Services Configuration Manager and shutdown the Reporting service
2. In the SSRS Reporting Services Configuration Manager, go to "Encryption Keys" page and Backup the existing key
3. Restore both the ReportServer & ReportServerTempDB databases on a SQL 2014 instance (i.e., "MSSQL2014")
4. Run the following query on the restored database:
7. In the SSRS Reporting Services Configuration Manager, go to "Encryption Keys" page
8. Click on "Change"
9. Test your environment
10. You may now go ahead and change your report distribution UNC path
It should work - the report and the subscription both worked for me.
Assume that my source SQL instance is named as "MSSQL2005", while the target is "MSSQL2014" (I have 2014, but it should not make a difference) .
On the source instance:
1. Backup "ReportServer" and "ReportServerTempDB" databases from source server (i.e., "MSSQL2005")
On the target instance:
1. Login to the target environment, launch the SSRS Reporting Services Configuration Manager and shutdown the Reporting service
2. In the SSRS Reporting Services Configuration Manager, go to "Encryption Keys" page and Backup the existing key
3. Restore both the ReportServer & ReportServerTempDB databases on a SQL 2014 instance (i.e., "MSSQL2014")
4. Run the following query on the restored database:
SELECT * FROM dbo.Keys;DELETE FROM dbo.Keys WHERE MachineName = 'TargetMachine' AND InstanceName = 'MSSQL2014'7. In the SSRS Reporting Services Configuration Manager, go to "Encryption Keys" page
8. Click on "Change"
9. Test your environment
10. You may now go ahead and change your report distribution UNC path
It should work - the report and the subscription both worked for me.
ASKER
What is the expected result to the reports at the web layer, or within Report Manager?
ASKER
fyi, the first two steps at the target instances must be reversed. I cannot log into SSRS Config Mgr to backup the ekey, if the SSRS service is not running.
On the target instance:
1. Login to the target environment, launch the SSRS Reporting Services Configuration Manager and shutdown the Reporting service
2. In the SSRS Reporting Services Configuration Manager, go to "Encryption Keys" page and Backup the existing key
On the target instance:
1. Login to the target environment, launch the SSRS Reporting Services Configuration Manager and shutdown the Reporting service
2. In the SSRS Reporting Services Configuration Manager, go to "Encryption Keys" page and Backup the existing key
ASKER
For step 5, you say the delete is of the local server. Meaning, you are connected to the target server, and you you are deleting from the Keys table the values for the target server --
DELETE FROM dbo.Keys WHERE MachineName = 'TargetMachine' AND InstanceName = 'MSSQL2014'
As you can see from the attached, I don't have a value actually representing the target server. The machinename and instancename for the first record are both NULL, and the second record references the server/instance name from the source server, or the v2005 instance.
Did you mean that I should delete that record -- the one that reflects the source machine?
keys_table.png
DELETE FROM dbo.Keys WHERE MachineName = 'TargetMachine' AND InstanceName = 'MSSQL2014'
As you can see from the attached, I don't have a value actually representing the target server. The machinename and instancename for the first record are both NULL, and the second record references the server/instance name from the source server, or the v2005 instance.
Did you mean that I should delete that record -- the one that reflects the source machine?
keys_table.png
Yes, please. Sorry about that. Please delete the entry with the machine name.
DELETE FROM dbo.Keys WHERE MachineName = 'SourceMachine' AND InstanceName = 'MSSQL2005'ASKER
>>Did you mean that I should delete that record -- the one that reflects the source machine?
Yes. I have removed the Keys record for the source server.
Yes. I have removed the Keys record for the source server.
ASKER
I performed each of your proposed steps (with the modifications already noted), but the report still is not being output. This is the LastStatus for the subscription I am testing:
The subscription contains parameter values that are not valid.
The subscription contains parameter values that are not valid.
ASKER
Having reviewed the ssrs logs, this is the source of that error:
library!WindowsService_2!2
notification!WindowsServic
library!WindowsService_2!2
library!WindowsService_2!2
library!WindowsService_2!2
library!WindowsService_2!2
library!WindowsService_2!2
library!WindowsService_2!2
I have not dealt with this error in SSRS before, but I believe it must be a change in the underlying engine properties, as this report works just fine in the v2005 instance.
This is more information regarding the error:
https://blogs.msdn.microsoft.com/selvar/2014/03/03/bursting-the-mysteries-of-invalidreportparameterexception-in-reporting-service/
library!WindowsService_2!2
notification!WindowsServic
library!WindowsService_2!2
library!WindowsService_2!2
library!WindowsService_2!2
library!WindowsService_2!2
library!WindowsService_2!2
library!WindowsService_2!2
I have not dealt with this error in SSRS before, but I believe it must be a change in the underlying engine properties, as this report works just fine in the v2005 instance.
This is more information regarding the error:
https://blogs.msdn.microsoft.com/selvar/2014/03/03/bursting-the-mysteries-of-invalidreportparameterexception-in-reporting-service/
Ok, the core problem is now resolved. SSRS would not have been able to validate the parameters if it was still having problems reading the encrypted content.
You will need to validate the content of the Parameters XMl in the dbo.Subscriptions table in the ReportServer. There may be a datatype or a value mismatch.
In any case, the fact that SSRS went ahead to validate the parameters means that we moved one step ahead - we are now able to get SSRS to read encrypted content.
You will need to validate the content of the Parameters XMl in the dbo.Subscriptions table in the ReportServer. There may be a datatype or a value mismatch.
In any case, the fact that SSRS went ahead to validate the parameters means that we moved one step ahead - we are now able to get SSRS to read encrypted content.
Oh, your report has a parameter "ParishName" which is mandatory for the report, but has not been defined on the subscription. Review the parameters being passed to the report when defining the subscription. You need to pass some value for this parameter for the report to run.
This is not related to the original problem that you were facing.
This is not related to the original problem that you were facing.
ASKER
>>You will need to validate the content of the Parameters XMl in the dbo.Subscriptions table in the ReportServer. There may be a datatype or a value mismatch.
Very odd, given that the ReportServer database was just restored from the source server, where the report is running just fine every day.
Or, it is a difference between the two engines -- v2005 and v2012.
Very odd, given that the ReportServer database was just restored from the source server, where the report is running just fine every day.
Or, it is a difference between the two engines -- v2005 and v2012.
There may be a difference in the report structure where unless explicitly defined as optional, all parameters are mandatory. That is a separate issue which needs a study of the documentation.
ASKER
>>Oh, your report has a parameter "ParishName" which is mandatory for the report, but has not been defined on the subscription. Review the parameters being passed to the report when defining the subscription. You need to pass some value for this parameter for the report to run.
Again, how is this possible, given the very same report runs in the source server every day? We just restored the SSRS database, and the Subscriptions table. Completely unchanged except for my update the the path. Possibly that is the source of this error. This is the logic I am using to update the report output path - changing 'serverX' to 'serverY' --
UPDATE ReportServer.dbo.Subscript
SET [Description] = REPLACE([Description],'ser
ExtensionSettings = REPLACE(CAST(ExtensionSett
WHERE [Description] LIKE '%serverX%'
AND ExtensionSettings LIKE '%serverY%'
Again, how is this possible, given the very same report runs in the source server every day? We just restored the SSRS database, and the Subscriptions table. Completely unchanged except for my update the the path. Possibly that is the source of this error. This is the logic I am using to update the report output path - changing 'serverX' to 'serverY' --
UPDATE ReportServer.dbo.Subscript
SET [Description] = REPLACE([Description],'ser
ExtensionSettings = REPLACE(CAST(ExtensionSett
WHERE [Description] LIKE '%serverX%'
AND ExtensionSettings LIKE '%serverY%'
No. ExtensionSettings and Description are unrelated to the Parameters.
I am a little surprised by the number of assumptions that this customer has taken - not studying the differences between various versions and yet forcing you not to follow the process recommended by the people who designed the software.
In any case, the original issue is now resolved.
What we are seeing now is a different issue. I will try to do the study that should have gone at the start of the project and revert back.
I am a little surprised by the number of assumptions that this customer has taken - not studying the differences between various versions and yet forcing you not to follow the process recommended by the people who designed the software.
In any case, the original issue is now resolved.
What we are seeing now is a different issue. I will try to do the study that should have gone at the start of the project and revert back.
ASKER
>>No. ExtensionSettings and Description are unrelated to the Parameters.
I have checked Catalog.parameters for the report I am testing -- they are identical. Which of course, is what I'd expect given the restore that was just performed. Again, the only change I made after the restore was the report output path, with the update I've already posted.
I have checked Catalog.parameters for the report I am testing -- they are identical. Which of course, is what I'd expect given the restore that was just performed. Again, the only change I made after the restore was the report output path, with the update I've already posted.
ASKER
I just tested another one, and the results are the same -- no error, no output, the LastStatus is the same as before --
The subscription contains parameter values that are not valid.
I need to do some research to see what variance may exist between the two engines that could cause this. If you find anything, please let me know.
The subscription contains parameter values that are not valid.
I need to do some research to see what variance may exist between the two engines that could cause this. If you find anything, please let me know.
ASKER
It is not a problem specific to the different engine versions. The majority of the reports have parameters passed in by a procedure which exists in one of the databases targeted by the report datasources. I have just checked the data source for the two reports that have ended with the same LastStatus, and it does not have any credentials defined. I'd expect this if we deleted the encryption key, rather than changing it. Regardless, there are no credentials defined for that data source. For this reason, I believe it is unable to connect to the database and run the procedure which passes parms into the reports.
Missing credentials on a data source would cause connectivity/login issues, not parameter validation issues.
Also, deleting encrypted content would also cause the link between a report, datasource and dataset to break - which has not happened (I believe you are manually able to run the report from the Report Manager).
I believe things are on the right track - the original issue is resolved and we are now looking at a different issue (which is expected due to the unconventional and experimental nature of this migration).
Also, deleting encrypted content would also cause the link between a report, datasource and dataset to break - which has not happened (I believe you are manually able to run the report from the Report Manager).
I believe things are on the right track - the original issue is resolved and we are now looking at a different issue (which is expected due to the unconventional and experimental nature of this migration).
ASKER
>>I believe you are manually able to run the report from the Report Manager
No, Nakul. I am not. We need to backup here -- yes, I could run the report from Report Manager before, when I was receiving the 'encrypted content was removed' as LastStatus for the report in the Subscriptions table.
Then I lost that ability after backing up the source key and restoring it to the target server, as per your suggestion in this comment:
(# a41875342)
At this point, I believe we can disregard anything that occurred before the most recent backup/restore of the ReportServer database, that I performed per your suggestion in this comment: (# a41878217)
>>Missing credentials on a data source would cause connectivity/login issues, not parameter validation issues.
Seemingly yes, but consider the fact that the parameters are supplied by a procedure which exists in the database that is defined in the datasource without credentials. Yes -- I would expect an error potentially saying 'cannot connect..' or something of that nature, but again, the report parameters are supplied by a procedure which is in that database that cannot be reached. Add to that the number of nonsensical error messages and outputs that we know SQL Server is prone to, and I am inclined to think this is the reason those parameters are not being passed in.
I've requested a domain account creation that I will use as the unattended execution account. Hopefully I will have that tomorrow, and I will use it with the datasource credentials. I will let you know as soon as I have done this.
No, Nakul. I am not. We need to backup here -- yes, I could run the report from Report Manager before, when I was receiving the 'encrypted content was removed' as LastStatus for the report in the Subscriptions table.
Then I lost that ability after backing up the source key and restoring it to the target server, as per your suggestion in this comment:
(# a41875342)
At this point, I believe we can disregard anything that occurred before the most recent backup/restore of the ReportServer database, that I performed per your suggestion in this comment: (# a41878217)
>>Missing credentials on a data source would cause connectivity/login issues, not parameter validation issues.
Seemingly yes, but consider the fact that the parameters are supplied by a procedure which exists in the database that is defined in the datasource without credentials. Yes -- I would expect an error potentially saying 'cannot connect..' or something of that nature, but again, the report parameters are supplied by a procedure which is in that database that cannot be reached. Add to that the number of nonsensical error messages and outputs that we know SQL Server is prone to, and I am inclined to think this is the reason those parameters are not being passed in.
I've requested a domain account creation that I will use as the unattended execution account. Hopefully I will have that tomorrow, and I will use it with the datasource credentials. I will let you know as soon as I have done this.
>>I believe you are manually able to run the report from the Report Manager
I meant after the detailed steps I sent out (# a41878217) - please do not mix this with solutions suggested earlier - troubleshooting will become extremely difficult in this case.
I meant after the detailed steps I sent out (# a41878217) - please do not mix this with solutions suggested earlier - troubleshooting will become extremely difficult in this case.
ASKER
I am not confused, Nakul. After I performed the steps you suggested in that comment, I have not yet been able to run the reports via Report Manager. I was told that I will have the new domain account in the morning, I will provide an update as soon as I have enabled the data source credentials.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hello!
Just wanted to check-in on the output with the updated steps I provided earlier in the day.
Just wanted to check-in on the output with the updated steps I provided earlier in the day.
ASKER
Hello Nakul. I did get the unattended execution account into place, but it was not the success that I had hoped for. I do, however, have more information now. I test ran several subscription jobs after setting up the account. They all perfomed as before -- completed successfully, yet output no reports.
This is the Subscription.LastStatus: The setting USERNAME is not allowed to have null value.
And this is the relevant portion of the SSRS log:
notification!WindowsServic
notification!WindowsServic
I mentioned to you yesterday that there is a procedure which is used to pass the parameters into the reports. In this particular case, the username would have been passed by the procedure. So this suggests that proc is not being fired for some reason. I need to get into the
report definition to identify why.
I got into the Report Manager just now to begin exactly that. Comparing both instances -- old version on left, new version on right. I got all the way down to the subscriptions for this particular report, and everything is setup identically between both servers -- including the parameters. The subscriptions, however, I am unable to access on the new instance. This is the message I receive simply when selecting 'Subscriptions' in the Report Mgr gui, to go in and see them:
The report server was unable to validate the integrity of encrypted data in the database. (rsCannotValidateEncrypted
I'm not sure how the encrypted data could still be a factor, given the restore and encrypted key change that I performed yesterday, per your suggestion.
Your thoughts?
This is the Subscription.LastStatus: The setting USERNAME is not allowed to have null value.
And this is the relevant portion of the SSRS log:
notification!WindowsServic
notification!WindowsServic
I mentioned to you yesterday that there is a procedure which is used to pass the parameters into the reports. In this particular case, the username would have been passed by the procedure. So this suggests that proc is not being fired for some reason. I need to get into the
report definition to identify why.
I got into the Report Manager just now to begin exactly that. Comparing both instances -- old version on left, new version on right. I got all the way down to the subscriptions for this particular report, and everything is setup identically between both servers -- including the parameters. The subscriptions, however, I am unable to access on the new instance. This is the message I receive simply when selecting 'Subscriptions' in the Report Mgr gui, to go in and see them:
The report server was unable to validate the integrity of encrypted data in the database. (rsCannotValidateEncrypted
I'm not sure how the encrypted data could still be a factor, given the restore and encrypted key change that I performed yesterday, per your suggestion.
Your thoughts?
ASKER
I should add, I do intend to perform your suggested routine still. I just wanted to see the impact of the execution account before moving forward w/another restore.
Yes, my findings were on similar lines too. I was able to confirm that that something strange happens to the encrypted data in the dbo.DataSource table. However, which step/process does it, I am not very sure.
Do let me know the outcome of the steps I proposed today - I was able to confirm that using today's steps, the data in dbo.DataSet, dbo.DataSource, dbo.Catalog and dbo.Subscription tables remains the same between the source & target servers.
Do let me know the outcome of the steps I proposed today - I was able to confirm that using today's steps, the data in dbo.DataSet, dbo.DataSource, dbo.Catalog and dbo.Subscription tables remains the same between the source & target servers.
ASKER
>>Do let me know the outcome of the steps I proposed today
Yes. I am doing it now, will let you know.
Yes. I am doing it now, will let you know.
Hello!
Just wanted to check in on the progress of the report server migration. Hope it's going well.
Just wanted to check in on the progress of the report server migration. Hope it's going well.
ASKER
Still on it, Nakul. Please forgive the delay. Will provide status as soon as I am able.
No worries. Just an FYI - I am a bit preoccupied tomorrow. I will review your feedback as soon as I get a chance.
Hello!
Just wanted to check-in on the results of the suggested steps in resolving your report deployment issue. Can I assume they worked since I have not heard anything back?
Just wanted to check-in on the results of the suggested steps in resolving your report deployment issue. Can I assume they worked since I have not heard anything back?
ASKER
I just went through it, Nakul. And I have tested three subscription jobs successfully. I feel avery good about this, but please give me a chance to do a more thorough assessment, and I will send you status tomorrow.
Sure. Happy to note that things are looking positive as of now.
ASKER
Excellent assist, Nakul. Thank you for your patience, and for a very thorough solution.
Thank-you for the update, points and happy to note that I could be of help! Have a wonderful day!
ASKER
Hello Nakul. May I ask another question? Please let me know if you would like me to open another ticket.
I generally do everything through the web service unless there is no method available to do it (I've encountered that just once, and I believe it was something to do with subscriptions).
You can trace what the Report Manager UI (or a web service call) does to the database when creating subscriptions, using Extended Events or Profiler, or you can do before/after comparisons of the database data to see if there is something you missed.