Reporting Services – Attaching to Vendor Supplied Backup file

Background

We are provisioning a new Application written by a 3rd Party vendor.

Availing the OLTP and OLAP databases from backup files has been a straight forward restore from disk operation.

 

Avail Report Server Database

The next step is to avail the Reporting Services Database.

 

Provision New ReportServer & ReportServerTempdb Database

 

Outline

  1. Restore Vendor supplied ReportServer database
  2. If Vendor did not supply ReportServerTempDB database, take backup from in-house matching version
  3. Restore Vendor supplied ReportServerTempDB or one taking from in-house
  4. Using SQL Server Reporting Services Configuration Manager, set-up RS to use restored ReportServer DB
  5. Use SQL Server Reporting Services Configuration Manager and apply encrypted key supplied by Vendor, or delete RS encryption keys
  6. Launch Internet Explorer ( IE ) in Administrator Mode
  7. Find and Review all Data Sources

Restore Database

I really did not want to tinker with MSFT’s Database.  And, so chose to restore the backup file that the Vendor sent under a new name.

 
exec master.dbo.xp_create_subdir 'E:\Microsoft\SQLServer\Datafiles\ReportServer'
go
	
exec master.dbo.xp_create_subdir 'E:\Microsoft\SQLServer\logfiles\ReportServer\'
go

RESTORE DATABASE [ReportServer.Vendor] 
FROM  DISK = N'E:\Temp\Vendor\Database\Backup\ReportServer.bak' 
WITH  FILE = 1
	, MOVE N'ReportServer' TO N'E:\Microsoft\SQLServer\Datafiles\ReportServer\ReportServer_Data_Vendor.mdf'
	, MOVE N'ReportServer_log' TO N'E:\Microsoft\SQLServer\logfiles\ReportServer\ReportServer_Log_Vendor.ldf'
	, NOUNLOAD
	, REPLACE
	, STATS = 1
 

 

Backup ReportServerTempDB

 


exec master.dbo.xp_create_subdir 'E:\temp'
go

 
backup database [ReportServerTempdb]
to  disk = 'e:\temp\ReportServerTempdb.bak'
with init, format, stats=1 
 

 

Restore MSFT ReportServerTempDB as ReportServerVendorTempDB

 
USE [master]
go

exec master.dbo.xp_create_subdir 'E:\Microsoft\SQLServer\Datafiles\ReportServerVendor\'
go

exec master.dbo.xp_create_subdir 'E:\Microsoft\SQLServer\Logfiles\ReportServerVendor\'
go

RESTORE DATABASE [ReportServer.VendorTempDB] 
FROM  DISK = N'D:\Temp\ReportServerTempDB_FULL_20161107_183213_v2.bak' 
WITH  FILE = 1
,  MOVE N'ReportServerTempDB' TO N'E:\Microsoft\SQLServer\Datafiles\ReportServerVendor\ReportServer.VendorTempDB_Data.mdf'
,  MOVE N'ReportServerTempDB_log' TO N'E:\Microsoft\SQLServer\Logfiles\ReportServerVendorReportServer.VendorTempDB_Log.ldf'
,  NOUNLOAD,  STATS = 5

GO
 

 

 

Configure Reporting Services to use availed Databases

Launch “Reporting Services Configuration Manager” and point it to the restored Reporting Services Database.

  1. Launch Reporting Services Configuration Manager
  2. From the left panel, select Database
  3. On the right panel, the “Report Server Database ” panel appears
  4. In the “Current Report Server Database” section, click the “Change Database” button

Report Server Database

reportserverdatabase-20161123-1230pm-brushed-up

 

Change Database – Choose Whether to Create or Configure a Report Server database

Here is what the Choose whether to create or configure a report server database” screen looks like:

changedatabase-choosewhethertocreateorchange-20161122-0551pm

 

Choices

  1. Select a task from the following list
    • The choices we have are:
      • Create a new report server database
      • Choose an existing report server database
    • Please choose “Choose an existing report report server database

 

Change Database – Connect to the Database Server

Here is what the Connect to the Database Server” screen looks like:

Screen – Connect to the Database Server

connecttothedatabaseserver-20161123-1256pm-brushed-up

Screen – Test Connection

testconnectionsuceeded

 

Choices

  1. Server Name
    • Please choose the Server Name
  2. Authentication Type
    • The Authentication Types available are
      • Current User – Integrated Security
      • SQL Server Account
    • In our case, we chose “Current User – Integrated Security”
  3. Please click “Test Connection” button to validate the connection

 

Change Database – Select a Report Server Database

Here is what the Select a Report Server database” screen looks like:

Screen – Report Server Database

selectareportserverdatabase-20161123-0105pm-brushed-up

 

 

Change Database – Credentials

Here is what the Credentials” screen looks like:

Screen – Credentials

credentials-20161123-0109pm

 

Change Database – Summary

Here is what the Summary” screen looks like:

Screen – Summary

summary-20161123-0115pm-brushed-up

 

Change Database – Progress and Finish

Here is what the Progress and Finish” screen looks like:

Screen – Progress and Finish

progressandfinish-20161123-0143pm

 

Encryption Keys

Error – Report Server Installation is not initialized

If you attempt to access Reporting Services web site without ensuring that Encryption Keys are right aligned, you will get the error message listed below:

Textual

The report server installation is not initialized. (rsReportServerNotActivated)  

Image

reportserverisnotinitialized

 

This offers us the impetus to go address our Encryption keys blindside.

Here is what the Encryption Keys” screen looks like:

Screen – Encryption Keys

Returning to “Reporting Services Configuration Manager

encryptionkeys-20161123-0208pm

 

Choices

  1. If you have been given a backup of the Encryption Keys and the password that was used during the backup
    • You can choose to restore it
  2. If you do not have the backup and can not have someone take that backup
    • You will likely have to choose the “Delete Encrypted Content” choice

 

Delete Encrypted Content

Image

deleteallencryptedcontent

 

Deleted All Encrypted Content

Image

deletingallencryptedcontent-20161123-031pm

 

Launch IE in Administrator Mode

To start IE in Administrator Mode, choose one of the options listed below

  • Launch from Windows Explorer
    • Launch Windows Explorer
    • Navigate to “C:\Program Files\Internet Explorer
    • Select iexplorer.exe and right click on your selection
    • From the dropdown menu, choose “Run as Administrator
  • Using Windows Start icon
    • At the bottom of the desktop, you will see the Windows Icon
    • Using the left mouse button click on it
    • In the “Search Programs and Files” textbox, enter cmd.exe
    • In the results panel, select and right-click on cmd.exe, and choose to “Run as Administrator”

 

Find and Review all Data Sources

  1. Once IE is launched, please enter the RS Report Manager URL
  2. URLs

 

Data Sources – Sample

Folder Listing

datasources-assistdw1-folderlist-20161123-0221pm

 

Choose Data Source to Manage

Here are the list of Objects

datasources-assistdw1-manage-20161123-0223pm

 

Steps
  1. Choose the Data Source
  2. Right click the Data Source and from the drop-down menu, choose the Manage option

 

Review Data Source

Sample Data Source ….

datasources-assistdw1-properties-20161123-0242pm

Explanation

The most important options are:

  1. Data Source Type
    • In our case “Microsoft SQL Server”
  2. Connection String
    • Enter the Connection String
    • In our case, data source=”(local)”;initial catalog=assistDW
  3. Connect Using
    • Options include
      • Credentials stored securely in the Report Server
        • Use as Windows credential when connecting to the data source
        • Impersonate the authenticated user after a connection has been made to the data source
      • Windows Integrated Security
    • The choice is basically
      • If you will like the same account to be used by all users, please
        • Choose “Credentials stored securely in the Report Server
        • Specify a surrogate account
        • If it is an OS Account and not SQL Account, please choose “Use as Windows credentials when connecting to the data source
      • If you will like for the login user to continue using its login credentials when connecting to the database, please
        • Choose “Window Integrated security

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s