Connecting a Report Server from Management Studio

To connect to a Report Server from Management Studio, there are a few things to consider.

First, I can not connect to an RS 2000 instance. I'll keep trying. Second, make sure the machine is functional. That really helps too. :-( IT can be a real time killer LOL Last, I have read many a sites/posts all of which say use the syntax for a servername as:

http:///reportserver

What I found that truly works is http://reports_web_site/reportserver

* reports web site example --> rptsvrname.yoursite.com i.e. the url you give your report server in IIS.

* reportserver --> the actual text "reportserver "

rsConfig utility for resetting the rsReportServer.config encrypted settings

Here's a good error that will require use of rsconfig:

The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. (rsReportServerDatabaseUnavailable).

This error occurs when the report server cannot connect to the SQL Server relational database that provides internal storage to the server. The connection to the report server database is managed through the Reporting Services Configuration tool. You can run the tool, go to the Database Setup page, and correct the connection information. Using the tool to update connection information is a best practice; the tool ensures that dependent settings are updated and that services are restarted. For more information, see Configuring a Report Server Database Connection and Configuring Service Accounts and Passwords in Reporting Services http://msdn.microsoft.com/en-us/library/ms160340.aspx.

This error can also occur if the Database Engine instance that hosts the report server database is not configured for remote connections. Remote connection is enabled by default in some releases of SQL Server 2005. To verify whether it is enabled on the SQL Server Database Engine instance you are using, run the SQL Server Surface Area Configuration tool. You must enable remote connections for both TCP/IP and named pipes. A report server uses both protocols. For instructions on how to enable remote connections, see the section "How to Configure Remote Connections to the Report Server Database" in Configuring a Report Server for Remote Administration http://msdn.microsoft.com/en-us/library/ms365170.aspx.

If the error includes the following additional text, the password expired on the account used to run the Database Engine instance: "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not permit remote connections. (provider: SQL Server Network Interfaces, error: 26 - Error Locating Server/Instance Specified)." To resolve this error, reset the password. For more information, see Changing Passwords and User Accounts .


Product: SQL Server Reporting Services
ID: rsReportServerDatabaseUnavailable
Source: Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings.resources.Strings
Version: 8.00
Message: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing.
Explanation
This error occurs when the report server is unable to connect to the report server database. The rsreportserver.config file stores encrypted values for the database name and credentials that it uses to connect to the report server database. If the database name or credentials have changed, the report server can no longer connect to the database.

User Action
You can use the rsconfig utility to update or repair the configuration settings that the report server uses to connect to the report server database. You can use the -d option of the utility to set the name of the report server database and you can use the -u and -p options to set the username and password. For information about how to use the rsconfig utility, see Books Online.
--------------------------------------------------------------------------------
Currently there are no Microsoft Knowledge Base articles available for this specific error or event message. For information about other support options you can use to find answers online, see http://support.microsoft.com/default.aspx.


Here's how to use rsconfig and where to find it:

\Program Files\Microsoft SQL Server\90\Tools\Binn\rsconfig.exe

http://msdn.microsoft.com/en-us/library/ms162837.aspx

rsconfig –c for the encrypted connection settings –e for the encrypted unattended account settings -m computername that RS resides on –i RS instance name –s server name that the database resides on –d RS database name –a authentication method which is either "windows" or "sql" -u username -p password (-t trace)

rsconfig -? will show you the syntax

Report Server errors

Reports Server errors

things to remember:

RS service account --> DOMAIN ACCOUNT!!!
Login account encrypted in the rsReportServer.config --> also a domain account (go ahead and use the same one!) use the rsConfig.exe utility. see the next post

The problem is the accounts. notice the login failure. Login failed for user 'ReportServer'

w3wp!library!ac4!07/23/2008-10:15:10:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing., ; Info: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. ---> System.Data.SqlClient.SqlException: Login failed for user 'ReportServer'. at System.Data.SqlClient.ConnectionPool.CreateConnection() at System.Data.SqlClient.ConnectionPool.UserCreateRequest() at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.ReportingServices.Library.ConnectionManager.OpenConnection() --- End of inner exception stack trace ---w3wp!library!428!07/23/2008-10:15:15:: i INFO: Call to GetSystemPermissionsw3wp!library!428!07/23/2008-10:15:15:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing., ; Info: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. ---> System.Data.SqlClient.SqlException: Login failed for user 'ReportServer'. at System.Data.SqlClient.ConnectionPool.CreateConnection() at System.Data.SqlClient.ConnectionPool.UserCreateRequest() at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) at System.Data.SqlClient.SqlConnection.Open() at Microsoft.ReportingServices.Library.ConnectionManager.OpenConnection() --- End of inner exception stack trace ---


Cool error below - notice .dbo.ExecutionCache. Check to see if the RSTempDB is missing. Most likely it had be removed during an install of an RS server. You'll need to re-attach the mdf/ldf files of the DB that got dropped. Be sure you have admin access to run the attach no matter which method you use.

w3wp!library!e1c!07/23/2008-13:47:28:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details., ; Info: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. ---> System.Data.SqlClient.SqlException: Invalid object name 'ReportServerTempDB.dbo.ExecutionCache'. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteReader() at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteReader() at Microsoft.ReportingServices.Library.DBInterface.GetReportForExecution(String objectName, String effectiveParams, Boolean& foundInCache, ItemType& type, ReportSnapshot& intermediateSnapshot, ReportSnapshot& snapshotData, Guid& link, String& linkPath, String& properties, String& description, Byte[]& secDesc, Guid& reportID, Int32& execOptions, DateTime& executionDateTime, Boolean& hasData, Boolean& cachingRequested, DateTime& expirationDateTime) at Microsoft.ReportingServices.Library.RSService.GetReportAndDataSourcesForExecution(CatalogItemContext itemContext, String effectiveParametersXml, Boolean& hasData, DataSourceInfoCollection& dataSources, String& reportProperties, ReportSnapshot& intermediateSnapshot, Guid& reportId, ReportSnapshot& snapshotData, String& description, Boolean& cachingRequested, DateTime& executionDateTime, DateTime& expirationDateTime, Int32& execOptions, Boolean& foundInCache) at Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(CatalogItemContext reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection& effectiveParameters) at Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection& effectiveParameters, String[]& secondaryStreamNames) --- End of inner exception stack trace ---w3wp!library!e1c!07/23/2008-13:47:28:: i INFO: Initializing EnableExecutionLogging to 'True' as specified in Server system properties.w3wp!webserver!e1c!07/23/2008-13:47:31:: e ERROR: Reporting Services error Microsoft.ReportingServices.Diagnostics.Utilities.RSException: An internal error occurred on the report server. See the error log for more details. ---> Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException: An internal error occurred on the report server. See the error log for more details. ---> System.Data.SqlClient.SqlException: Invalid object name 'ReportServerTempDB.dbo.ExecutionCache'.w3wp!dbcleanup!858!7/23/2008-13:57:06:: e ERROR: Sql Error in CleanExpiredSessions: System.Data.SqlClient.SqlException: Invalid object name 'ReportServerTempDB.dbo.SessionData'. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.DatabaseSessionStorage.CleanExpiredSessions()w3wp!dbcleanup!858!7/23/2008-13:57:06:: e ERROR: Sql Error in CleanExpiredCache: System.Data.SqlClient.SqlException: Invalid object name 'ReportServerTempDB.dbo.SnapshotData'. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.DatabaseSessionStorage.CleanExpiredCache()w3wp!dbcleanup!858!7/23/2008-13:57:06:: e ERROR: Sql Error in CleanOrphanedSnapshots: System.Data.SqlClient.SqlException: Invalid object name 'ReportServerTempDB.dbo.SnapshotData'. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteReader() at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteReader() at Microsoft.ReportingServices.Library.DatabaseSessionStorage.CleanOrphanedSnapshots(Int32& chunksCleaned)w3wp!library!858!7/23/2008-13:57:06:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 0 running jobsw3wp!dbcleanup!858!7/23/2008-14:07:06:: e ERROR: Sql Error in CleanExpiredSessions: System.Data.SqlClient.SqlException: Invalid object name 'ReportServerTempDB.dbo.SessionData'. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.DatabaseSessionStorage.CleanExpiredSessions()w3wp!dbcleanup!858!7/23/2008-14:07:06:: e ERROR: Sql Error in CleanExpiredCache: System.Data.SqlClient.SqlException: Invalid object name 'ReportServerTempDB.dbo.SnapshotData'. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.DatabaseSessionStorage.CleanExpiredCache()w3wp!dbcleanup!858!7/23/2008-14:07:06:: e ERROR: Sql Error in CleanOrphanedSnapshots: System.Data.SqlClient.SqlException: Invalid object name 'ReportServerTempDB.dbo.SnapshotData'. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteReader() at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteReader() at Microsoft.ReportingServices.Library.DatabaseSessionStorage.CleanOrphanedSnapshots(Int32& chunksCleaned)w3wp!library!858!7/23/2008-14:07:06:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 0 running jobsw3wp!webserver!b70!7/23/2008-14:08:23:: i INFO: Reporting Web Server stopped

cant use USE

this should work - am trying it now. it doesn't fail.

--**************************** --REPLACES USE @NAME;--****************************DECLARE @cmd nvarchar(50)SELECT @cmd = 'USE '+ @NAME+';'EXEC sp_executesql @cmd;--GO;--****************************


more coming!