Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Question
Tuesday, January 15, 2013 12:03 AM | 1 vote
I am running Reporting Services (2008 R2 SP2 CU1) on a Windows 2008 R2 SP1 server (serverA). I am trying to configure it to use a database on another server running SQL Server 2008 R2 SP2 CU1 running on Windows 2008 R2 SP1 (ServerB).
The account that I am using when opening up the Reporting Services Configuration Manager has the following permissions on the remote database server (ServerB): DBCreator Server Role, RSExecRole database role on both the master and msdb databases.
I open Reporting Services and enter the Remote Server (ServerB) for the server name and click on connect. I then Click on Database and choose the "Change Database" button. I choose "Create New Report Server" database and click next. I enter ServerB for the server name and leave it on "Current User - Intetgrated Security" for the authentication type. Test Connection works so l click on next. I enter the ReportServer database name and choose "native mode" for the Report Server Mode. I then click next. For Credentials I choose Windows Authentication and then enter the login name and password for the account I am running under. I click next twice and the configuration process starts. The databases get created but an error is generated running the database script:
System.Data.SqlClient.SqlException: Cannot find the object 'xp_sqlagent_notify', because it does not exist or you do not have permission.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.ReportingServices.Common.DBUtils.ApplyScript(SqlConnection conn, String script, ICommandWrapperFactory commandWrapper)
at Microsoft.ReportingServices.Common.DBUtils.ApplyScript(String connectionString, String script)
at ReportServicesConfigUI.SqlClientTools.SqlTools.ApplyScript(String connectionString, String script)
Any advice and help on helping me get Reporting Services to use a database on another server would be appreciated.
All replies (6)
Thursday, January 24, 2013 6:23 PM ✅Answered | 1 vote
We opened a case with Microsoft and they gave us the solution below. I should note that step 7 makes all the previous steps redundant. I will also note that we are puzzled why the grant option is necessary for all the permissions to be given.
RESOLUTION
============
If one wants to use an account which is not a sysadmin to be used for usage of Report Server Database. These are the minimum permissions required
1. Create Database Permission and Security Admin permissions
Since we would be creating a database on SQL Server, the account using which you are performing the configuration needs to have Create Database Permissions.
To grant permissions to the user to create the database, you can Go to management studio and connect to SQL Server. Got to security, Logins and then go to the property of the account. In the property windows, Go to server roles and check the following:
- Dbcreator
- SecurityAdmin
2. The user should be granted permissions to create a role. So you can execute the following command to grant the account permissions to create a role:
grant Create Role to <UserName>
3. The user should be granted permissions to create a schema. So you can execute the following command to grant the account permissions to create a schema:
grant create schema to <UserName>
4. Select the master database and grant execute permissions to the users on the stored procedures using the following commands:
GRANT EXECUTE ON xp_sqlagent_notify TO <UserName> with Grant Option
GRANT EXECUTE ON xp_sqlagent_enum_jobs TO <UserName> with Grant Option
GRANT EXECUTE ON xp_sqlagent_is_starting TO <UserName> with Grant Option
GRANT Execute On sp_grantlogin TO <UserName> with Grant Option
5. Select the MSDB database and grant execute permissions to the user on the stored procedures using the following commands:
GRANT EXECUTE ON sp_help_category TO <UserName> with Grant Option
GRANT EXECUTE ON sp_add_category TO <UserName> with Grant Option
GRANT EXECUTE ON sp_add_job TO <UserName> with Grant Option
GRANT EXECUTE ON sp_add_jobserver TO <UserName> with Grant Option
GRANT EXECUTE ON sp_add_jobstep TO <UserName> with Grant Option
GRANT EXECUTE ON sp_add_jobschedule TO <UserName> with Grant Option
GRANT EXECUTE ON sp_help_job TO <UserName> with Grant Option
GRANT EXECUTE ON sp_delete_job TO <UserName> with Grant Option
GRANT EXECUTE ON sp_help_jobschedule TO <UserName> with Grant Option
GRANT EXECUTE ON sp_verify_job_identifiers TO <UserName> with Grant Option
6. Select the MSDB database and grant select permissions to the user on the stored procedures using the following commands:
GRANT Select On Sysjobs TO <UserName> with Grant Option
GRANT Select On syscategories TO <UserName> with Grant Option
Please note that in the above GRANT statements, I have used the following clause “with Grant Option”. This give the user permissions not only to execute the statement, but it can also grant permissions to other accounts. This is important as this account needs to grant permissions to the account which would connect to the report server database.
7. On the master and MSDB, add the user to the following roles.
- db_accessadmin
- db_owner
You can do this by going to the database, Security, Users and then the property of the user. On the property window, Under the “Database role membership” check the above 2 roles for the user.
Tuesday, January 15, 2013 6:24 AM
Hi,
try to add rights for sql agent too, Becase Reporting services sometimes have to create jobs.
maybe this helps
Database Permissions
Accounts used to connect to the report server database are granted the following roles:
public and RSExecRole roles for the ReportServer database.
RSExecRole role for the master, msdb, and ReportServerTempDB databases.
When you use the Reporting Services Configuration tool to create or modify the connection, these permissions are granted automatically. If you use the rsconfig utility, and you are specifying a different account for the connection, you must update the SQL Server login for that new account. You can create script files in the Reporting Services Configuration tool that will update the SQL Server login for the report server.
from http://technet.microsoft.com/en-us/library/ms159133.aspx
Zdenek
Please mark as helpful and propose as answer if you find this as correct. nosekz.eu
Tuesday, January 15, 2013 8:40 PM
Hi Zdenek,
Thanks for getting back to me. I read the article you referenced and have followed it closely in creating/configuring the ReportServer database. The database does get created on the remote server (ServerB) and the account I am running Reporting Services Configuration Manager on ServerA is the dbo of the ReportServer and ReportServerTemp databases on ServerB so it would have RSExecRole permissions. I have even tried creating the databases on another server with Reporting Services and the same error comes up.
The article says that Reporting Services uses System.Data.SqlClient to connect to the Database Engine that hosts the report server database. The error I am getting is on System.Data.SqlClient. Could I be missing permissions somewhere? Is there anywhere I can check the connection configuration?
Thanks,
Loren
Tuesday, January 15, 2013 10:23 PM
Also, how do you create script files using the Reporting Services Configuration tool?
On the remote server, looking at SQL Server Configuration Manager, Named Pipes and TCP/IP are enabled in that order.
Doing a search, I see this person had the same problem:
He got the database configured by making the domain account a local administrator on both the local Reporting Services server and the remote database server. I do not see anywhere in MS documentation that this is necessary but are there any other permissions that are necessary for this?
Thursday, January 17, 2013 3:55 PM
Further info. Both the Report Server and the database server are running .NET Framework 4.
I looked at the Report Server error log (C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\LogFiles) and here is the info:
rshost!rshost!1078!01/16/2013-15:57:05:: i INFO: CLR runtime is initialized.
rshost!rshost!1078!01/16/2013-15:57:05:: i INFO: Derived memory configuration based on physical memory as 8388152 KB
appdomainmanager!DefaultDomain!a7c!01/16/2013-15:57:05:: i INFO: Entered managed ServiceMain in DefaultDomain.
library!DefaultDomain!1a58!01/16/2013-15:57:05:: i INFO: Initializing ConnectionType to '0' as specified in Configuration file.
library!DefaultDomain!1a58!01/16/2013-15:57:05:: i INFO: Initializing SecureConnectionLevel to '0' as specified in Configuration file.
library!DefaultDomain!1a58!01/16/2013-15:57:05:: i INFO: Initializing CleanupCycleMinutes to '10' minute(s) as specified in Configuration file.
library!DefaultDomain!1a58!01/16/2013-15:57:05:: i INFO: Initializing MaxActiveReqForOneUser to '20' requests(s) as specified in Configuration file.
library!DefaultDomain!1a58!01/16/2013-15:57:05:: i INFO: Initializing DatabaseQueryTimeout to '120' second(s) as specified in Configuration file.
library!DefaultDomain!1a58!01/16/2013-15:57:05:: i INFO: Initializing RunningRequestsScavengerCycle to '60' second(s) as specified in Configuration file.
library!DefaultDomain!1a58!01/16/2013-15:57:05:: i INFO: Initializing RunningRequestsDbCycle to '60' second(s) as specified in Configuration file.
library!DefaultDomain!1a58!01/16/2013-15:57:05:: i INFO: Initializing RunningRequestsAge to '30' second(s) as specified in Configuration file.
library!DefaultDomain!1a58!01/16/2013-15:57:05:: i INFO: Initializing MaxScheduleWait to '5' second(s) as specified in Configuration file.
library!DefaultDomain!1a58!01/16/2013-15:57:05:: i INFO: Initializing DisplayErrorLink to 'True' as specified in Configuration file.
library!DefaultDomain!1a58!01/16/2013-15:57:05:: i INFO: Initializing WebServiceUseFileShareStorage to 'False' as specified in Configuration file.
library!DefaultDomain!1a58!01/16/2013-15:57:05:: i INFO: Initializing WatsonFlags to '1064' as specified in Configuration file.
library!DefaultDomain!1a58!01/16/2013-15:57:05:: i INFO: Initializing WatsonDumpOnExceptions to 'Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException,Microsoft.ReportingServices.Modeling.InternalModelingException,Microsoft.ReportingServices.ReportProcessing.UnhandledReportRenderingException' as specified in Configuration file.
library!DefaultDomain!1a58!01/16/2013-15:57:05:: i INFO: Initializing WatsonDumpExcludeIfContainsExceptions to 'System.Threading.ThreadAbortException,System.Web.UI.ViewStateException,System.OutOfMemoryException,System.Web.HttpException,System.IO.IOException,System.IO.FileLoadException,Microsoft.SharePoint.SPException' as specified in Configuration file.
library!DefaultDomain!1a58!01/16/2013-15:57:05:: i INFO: Initializing AuthenticationTypes to '4' as specified in Configuration file.
library!DefaultDomain!1a58!01/16/2013-15:57:05:: i INFO: Initializing RSWindowsExtendedProtectionLevel to 'Off' as specified in Configuration file.
library!DefaultDomain!1a58!01/16/2013-15:57:05:: i INFO: Initializing RSWindowsExtendedProtectionScenario to 'Proxy' as specified in Configuration file.
library!DefaultDomain!1a58!01/16/2013-15:57:05:: i INFO: Initializing EnableAuthPersistence to 'True' as specified in Configuration file.
library!DefaultDomain!1a58!01/16/2013-15:57:05:: i INFO: Initializing IsSchedulingService to 'True' as specified in Configuration file.
library!DefaultDomain!1a58!01/16/2013-15:57:05:: i INFO: Initializing IsNotificationService to 'True' as specified in Configuration file.
library!DefaultDomain!1a58!01/16/2013-15:57:05:: i INFO: Initializing IsEventService to 'True' as specified in Configuration file.
library!DefaultDomain!1a58!01/16/2013-15:57:05:: i INFO: Initializing PollingInterval to '10' second(s) as specified in Configuration file.
library!DefaultDomain!1a58!01/16/2013-15:57:05:: i INFO: Initializing WindowsServiceUseFileShareStorage to 'False' as specified in Configuration file.
library!DefaultDomain!1a58!01/16/2013-15:57:05:: i INFO: Initializing MemorySafetyMargin to '80' percent as specified in Configuration file.
library!DefaultDomain!1a58!01/16/2013-15:57:05:: i INFO: Initializing MemoryThreshold to '90' percent as specified in Configuration file.
library!DefaultDomain!1a58!01/16/2013-15:57:05:: i INFO: Initializing RecycleTime to '720' minute(s) as specified in Configuration file.
library!DefaultDomain!1a58!01/16/2013-15:57:05:: i INFO: Initializing MaxAppDomainUnloadTime to '30' minute(s) as specified in Configuration file.
library!DefaultDomain!1a58!01/16/2013-15:57:05:: i INFO: Initializing MaxQueueThreads to '0' thread(s) as specified in Configuration file.
library!DefaultDomain!1a58!01/16/2013-15:57:05:: w WARN: Initializing UrlRoot to default value of '' because it was incorrectly specified in Configuration file as ''.
library!DefaultDomain!1a58!01/16/2013-15:57:05:: i INFO: Initializing IsWebServiceEnabled to 'True' as specified in Configuration file.
library!DefaultDomain!1a58!01/16/2013-15:57:05:: i INFO: Initializing IsReportManagerEnabled to 'True' as specified in Configuration file.
library!DefaultDomain!1a58!01/16/2013-15:57:05:: i INFO: Initializing MaxConnections to '2' as specified in Configuration file.
library!DefaultDomain!1a58!01/16/2013-15:57:05:: i INFO: Initializing Timeout to '10' second(s) as specified in Configuration file.
library!DefaultDomain!1a58!01/16/2013-15:57:05:: i INFO: Initializing AppID to '(Default)' as specified in Configuration file.
library!DefaultDomain!1a58!01/16/2013-15:57:05:: i INFO: Initializing CacheLevel to 'Default' as specified in Configuration file.
resourceutilities!DefaultDomain!1a58!01/16/2013-15:57:05:: i INFO: Reporting Services starting SKU: Standard
configmanager!DefaultDomain!1a58!01/16/2013-15:57:05:: i INFO: Using report server internal url http://localhost:80/ReportServer.
configmanager!DefaultDomain!1a58!01/16/2013-15:57:05:: i INFO: Using report server external url http://EIPSM42V:80/ReportServer.
configmanager!DefaultDomain!1a58!01/16/2013-15:57:05:: i INFO: Using url root http://EIPSM42V:80/ReportServer.
rshost!rshost!1a58!01/16/2013-15:57:05:: i INFO: Configuring maxPhysicalCpu=4, minLogicalCpu=0
rshost!rshost!1a58!01/16/2013-15:57:05:: i INFO: Logical processor per physical processor 2
rshost!rshost!1a58!01/16/2013-15:57:05:: i INFO: Maximum logical CPU is 2
library!DefaultDomain!1a58!01/16/2013-15:57:05:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: No DSN present in configuration file, Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. ;
servicecontroller!DefaultDomain!1a58!01/16/2013-15:57:05:: e ERROR: Error initializing configuration from the database: Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error.
appdomainmanager!DefaultDomain!1a58!01/16/2013-15:57:05:: i INFO: Appdomain:2 WindowsService_0 started.
library!WindowsService_0!1a58!01/16/2013-15:57:05:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: No DSN present in configuration file, Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. ;
servicecontroller!WindowsService_0!1a58!01/16/2013-15:57:05:: e ERROR: Error initializing configuration from the database: Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error.
resourceutilities!WindowsService_0!1a58!01/16/2013-15:57:06:: i INFO: Reporting Services starting SKU: Standard
library!WindowsService_0!13b4!01/16/2013-15:57:06:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: No DSN present in configuration file, Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. ;
library!WindowsService_0!13b4!01/16/2013-15:57:06:: e ERROR: ServiceStartThread: Exception caught while starting service. Error: Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error.
library!WindowsService_0!13b4!01/16/2013-15:57:06:: e ERROR: ServiceStartThread: Attempting to start service again...
rpcserver!DefaultDomain!13b4!01/16/2013-15:57:06:: i INFO: RPC Server started.
servicecontroller!DefaultDomain!13b4!01/16/2013-15:57:06:: i INFO: RPC Server started. Endpoint name ='ReportingServices$MSRS10_50.MSSQLSERVER'
appdomainmanager!DefaultDomain!1a58!01/16/2013-15:57:06:: i INFO: The UserAccountControl value for the service account is 66048
rshost!rshost!1b3c!01/16/2013-15:57:06:: i INFO: Registered url=http://+:80/ReportServer/, vdir=/ReportServer, pdir=C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer.
rshost!rshost!1b3c!01/16/2013-15:57:06:: i INFO: Currently registered url http://+:80/ReportServer/ on endpoint 2
appdomainmanager!DefaultDomain!1a58!01/16/2013-15:57:06:: i INFO: The UserAccountControl value for the service account is 66048
rshost!rshost!1b3c!01/16/2013-15:57:06:: i INFO: Registered url=http://+:80/Reports/, vdir=/Reports, pdir=C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportManager.
rshost!rshost!1b3c!01/16/2013-15:57:06:: i INFO: Registered url=https://WMSvc-WIN-RIOKC8ASFSH:443/Reports/, vdir=/Reports, pdir=C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportManager.
rshost!rshost!1b3c!01/16/2013-15:57:06:: i INFO: Currently registered url https://WMSvc-WIN-RIOKC8ASFSH:443/Reports/ on endpoint 3
rshost!rshost!1b3c!01/16/2013-15:57:06:: i INFO: Currently registered url http://+:80/Reports/ on endpoint 3
rshost!rshost!1b3c!01/16/2013-15:57:06:: i INFO: Endpoint 4 is disabled and no url is registered vdir=/ReportServer/ReportBuilder, pdir=C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\ReportBuilder.
resourceutilities!DefaultDomain!1a58!01/16/2013-15:57:06:: i INFO: Maximum memory limit is 65536Mb
rshost!rshost!1a58!01/16/2013-15:57:06:: i INFO: Derived memory configuration based on physical memory as 8388152 KB
rpcserver!DefaultDomain!1624!01/16/2013-15:57:06:: i INFO: Process monitoring started.
servicecontroller!DefaultDomain!1b3c!01/16/2013-15:57:06:: i INFO: Total Physical memory: 8589467648
library!DefaultDomain!a7c!01/16/2013-15:57:06:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: No DSN present in configuration file, Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. ;
library!DefaultDomain!a7c!01/16/2013-15:57:31:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: No DSN present in configuration file, Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. ;
library!DefaultDomain!a7c!01/16/2013-15:57:32:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: No DSN present in configuration file, Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. ;
What stands out is “No DSN present in configuration file” so I created a System DSN and got the same results. I then went to the rsreportserver.config file and manually entered the DSN. This showed up in the log:
"FailedToDecryptConfigInformationException: The encrypted value for the " Dsn" configuration setting cannot be decrypted"
So it appears that Reporting Services has to encrypt the DSN and save it in the file.
I searched other sites and found people who had the “No DSN present in configuration file” message because they did not create create the database using the Report Server Configuration Manager. Creating the database should input the DSN info in the rsreportserver.config file. However, it is not doing so in this case.
To recap: Going through the database creation/configuration process "Verifying database sku" is success, "Generating database script" is success, but "Running database script" results in error. When I look at the remote database server, the databases are created with tables and stored procedures.
It seems that when it comes to the "Running database script" step, it looks at the rsreportserver.config file for DNS and URLRoot info which does not exist but should have been populated in the previous two steps. This results in failure.
Searching other cases always ends up with no solution. Has anybody had any luck connecting Reporting Services to a remote database using Reporting Services (2008 R2 SP2 CU1)?
Wednesday, October 18, 2017 2:43 PM
I also got the DSN issue, but the issue was caused by renaming the machine from DEVSQL1-NEW to DEVSQL1. The DSNs were encrypted, so to fix the issue, I went into Configuration Manager, clicked Database tab, Change Database, and fixed the problem there.
edit: I am running SSRS 2016.