Protecting Patients through Background Checks

Database Installation

Background Check System

IT Deployment Guide

Version 01

2/19/2015

Table of Contents

1Overview

1.1BCS Database System Requirements

1.2BCS Source Database Files

2BCS Database Installation

2.1Using SQL Server Management Studio

2.2Restore Databases

2.3Verify Databases Restored

2.4Database Configuration Changes

2.5Database Security

3SSRS Installation

3.1Installation Pre-Requisites

3.2Installation Overview

3.3Installation of RDLs with rsScripter

3.4Manual Installation of RDLs

4SSRS Configuration

4.1Data Source

4.2Verify Configuration

4.3Configuration Parameters

4.4Authentication

4.5SQL Parameter Update Scripts

4.6Verification

5SSIS Installation

5.1Installation Pre-Requisites

5.2Installation

6BCS SMTP Service

6.1Configuration

6.2Virtual Server

6.3SQL Parameter Update Scripts

7BCS Environment Configuration

7.1Select SQL Script

7.2SQL Script for Development Environment

7.3SQL Script for Production Environment

1Overview

This guide will assist you in setting up the Background Check System (BCS) databases. There are three databases in total: (1) the SQL Server Reporting Services (SSRS), (2) the SQL Server Integration Services (SSIS), and (3) the BCS SMPT Service.

1.1BCS Database System Requirements

In order to install the BCS databases, the target machine must meet or exceed the system requirements listed in the following table.

System Component / Minimum Requirement
Database Server / Microsoft SQL Server 2008 R2, preferably with Service Pack 1
Processor / Minimum 1GHz processor or equivalent
Operating System / Windows Server 2008 R2 (Recommended); Windows Server 2008; Windows Server 2003; Windows 7; Windows 7 SP1
Memory / 2GB-4GB (Recommended)
Hard Disk / 20+ GB of available space may be required
Assumptions / Installation user must have local administrative privileges

1.2BCS Source Database Files

You will need to place the BCS source database files in the target machine's BCS database folder: “C:\BCS\Database.”The BCS source database files are as follows:

Source Database File / File Description
Bcs / Contains the core tables for the application
BcsLogging / Contains logging information regarding system events such as authentication, as well as data updates
BcsRegistry / Contains imported registry data that is used in background checking

2BCS Database Installation

2.1Using SQL Server Management Studio

Start SQL Server Management Studio (SSMS) and connect to the local database. You will need to provide the Server Name and Authenticationinformation based off of your local configuration.

2.2Restore Databases

Within SSMS, execute the three SQL statements at the end of this section in a New Querywindow. Before running the scripts, however, check the following:

  • If the databases have been copied to a location other than “C:\BCS_SourceFiles\Databases,” update the scripts to point to the new location.
  • Verify the SQL Server default path is: “N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\.” If the path for your environment is different, update the script move commands to point to the correct SQL Server directory.

After executing the scripts, verify that there are no errors and the SQL execution completes successfully.

2.2.1Script 1

RESTORE DATABASE [BCS] FROMDISK =N'C:\BCS_SourceFiles\Databases\BcsCA.bak'

WITH FILE = 1,MOVEN'BcsCa'TON'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\BCS.mdf',

MOVEN'BcsCa_log'TON'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\BCS_1.ldf',NOUNLOAD,STATS = 10

GO

2.2.2Script 2

RESTORE DATABASE [BCSLogging] FROMDISK = N'C:\BCS_SourceFiles\Databases\BcsCALogging.bak'

WITHFILE = 1,MOVEN'BcsDevLogging' TON'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\BCSLogging.mdf',

MOVEN'BcsDevLogging_log'TON'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\BCSLogging_1.ldf',NOUNLOAD,STATS = 10

GO

2.2.3Script 3

RESTORE DATABASE [BCSRegistry] FROMDISK = N'C:\BCS_SourceFiles\Databases\BcsCARegistry.bak'

WITHFILE = 1,MOVEN'Registry'TON'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\BCSRegistry.mdf',

MOVEN'Registry_log'TON'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\BCSRegistry_1.ldf',NOUNLOAD,STATS = 10

GO

2.3Verify Databases Restored

Once the three SQL scripts have executed without error, the new databases should display within your SSMS view.

Database InstallationPage 1 of 20

Version 01 | 2/19/2015 | IT Deployment Guide | Audience: State IT Staff

Protecting Patients through Background Checks


Note

Iffor any reason you change the database names, please ensure you modify the web config accordingly for the BCS application and Task Service.

Database InstallationPage 1 of 20

Version 01 | 2/19/2015 | IT Deployment Guide | Audience: State IT Staff

Protecting Patients through Background Checks

Database InstallationPage 1 of 20

Version 01 | 2/19/2015 | IT Deployment Guide | Audience: State IT Staff

Protecting Patients through Background Checks

2.4Database Configuration Changes

The following two database configuration settings need to be updatedto a state-specific setting when the database is initially setup for a state.

  • Email address that state will useto send out BCS emails
  • Location of reporting server

2.5Database Security

The BCS website connects to the BCS databases via anSQL Serverlogin. You will need to create an account (i.e. a ‘BCS’ SQL Server login) with the appropriate system level access to the database. The BCS application will be responsible for the application authorization and access.

The SQL Server login will subsequently be utilized in the web.config modifications described in the “Database and Application Deployment” IT guide. At a minimum, the SQL Server login used for the application should have:

  • db_reader
  • db_writer
  • db_executor

Db_executor may not be a standard role in your database. If it’s not, please run the following query for each database. The example uses BCS, but you will change the database name asneeded.

use BCS

CREATE ROLEdb_executor

The settings for the BCS or SQLuser should look similar to the following image.

3SSRS Installation

3.1Installation Pre-Requisites

Before utilizing SSRS, the target machine must have the following items setup:

  • SQL Server 2008 R2 Reporting Servicesinstalled in addition to a normal installation of SQL Server 2008 R2.

  • Internet Information Server 7.x.
  • A Windows account to connect the application with SSRS (i.e., DOMAIN\ssrs_user). This account will be used in the Configuration table in subsequent steps along with the account password.
  • AnSQL Server account for the application.

3.2Installation Overview

There are two methods to deploy SSRS Report Definition Language (RDL) files: (1) using a scripting program or (2) manually uploading all reports (RDL files). We prefer to use a scripting program to facilitate initial load and subsequent updates. For more information on the scripting program, please read the information provided at:

The scripting program, rsScripter, is included in the SSRSsource folder. The following section will explain how to use rsScripter. If you chose to install SSRS RDLsmanually, please skip to “Manual Installation of RDLs” (section 3.4 below). Regardless of whether you choose to use rsScripter or to install manually, you need to read all of the “SSRS Configuration” information (in sections 4, 4.1, and 4.2 below).

3.3Installation of RDLs with rsScripter

  1. Log in to your current development SSRS site for the BCS platform.
  1. Delete all folders and reports from within the BCS folder structure.
  2. Do NOT Deletethe BcsDataSource.
  3. Unzip the SSRSpackage to: “C:\SSRS.”

  1. Open the “RS Scripter Load All Items.cmd” file and locate theReportServervariable: “SET REPORTSERVER= Update the Server Name Here text to be your Server Name.
  2. Double click the “RS Scripter Load All Items.cmd” file to start the upload. Monitor the event log (i.e., the “created.txt” file) to ensure successful installation.

3.4Manual Installation of RDLs

  1. With SSRS properly deployed and initialized, browse to “ from the SSRSserver. The SSRS Home page willdisplay.

  1. Using the New Folder button, create the folder structure and hierarchy that the BCS platform is expecting. Under the root folder BCS, create the following folders:
  2. Application Forms
  3. Department Reports
  4. Provider Reports
  5. System Reports
  6. Use the Upload File buttonto upload the reports from the SSRS folder in Source Installation Package.

4SSRS Configuration

4.1Data Source

To configure the SSRS Data Source, complete the following steps:

  1. Browse to “ the SSRS server.
  1. Navigate to the root BCS folder and click the New Data Source button.
  2. Enter the designated values into the following fields:
  3. Name:BcsDataSource
  4. Data source type:Microsoft SQL Server
  5. Connection string: Data source=[SQL_SERVER_NAME]; NetworkLibrary=DBMSSOCN; Initial Catalog=[BCS_DB_NAME]
  6. Connect using:Credentials stored securely in the report server
  7. User name:[SSRS_USER](i.e., DOMAIN\ssrs_user)
  8. Password:[PASSWORD_FOR_SSRS_USER]
  9. Click Test Connectionto confirm connectivity.
  10. Click Apply.
  11. Associate this data source with EACH of the deployed reports. Complete the following procedure for each report:
  12. Hover over the report and chooseManage from the Actions menu.
  13. Click Data Sources.
  14. Select A shared data source, and click the Browse button.
  15. Navigate through the tree and select BcsDataSource.
  16. Click OK.

Database InstallationPage 1 of 20

Version 01 | 2/19/2015 | IT Deployment Guide | Audience: State IT Staff

Protecting Patients through Background Checks


Note

You must click apply on the report management screen to apply the data source association.

Database InstallationPage 1 of 20

Version 01 | 2/19/2015 | IT Deployment Guide | Audience: State IT Staff

Protecting Patients through Background Checks

4.2Verify Configuration

Once the reports are setup and configured, update the BCS platform Configurationtableto ensure the proper configuration. To do so, execute the following SQL:

select *

from Configuration c

innerjoinConfigurationTypectonct.ConfigurationTypeId = c.ConfigurationTypeId

wherect.Settinglike '%ssrs%'

4.3Configuration Parameters

Ensure that the parameters listed in the table below are updated appropriately.

SSRS Configuration Parameter / Parameter Description
SsrsReportServerUrl / path to the SSRS configuration,i.e.,
SsrsReportsPath / Root level of the location of reports within the SSRS configuration; As the imagein section 3.3, step 3 indicates, when creating the folder structure, you should have a root BCSfolder.
SsrsAuthDomain / Database server name (or ‘localhost’)
SsrsAuthUsername / SSRS user name defined for security i.e., a domain account with access to the database
SsrsAuthPassword / Password associated with the SsrsAuthUsername parameter

4.4Authentication

The SsrsAuthUsername (and password) can be established in two ways: (1) local account on the SSRS server and (2) network domain account. With either approach, setup the SSRS security accordingly. In the following example, the SSRS_User is a local account on the SSRS server and has the role “Browser” on the BCS folder. If you already use a network domain account, add that user account to the role.

4.5SQL Parameter Update Scripts

4.5.1SsrsReportServerUrl Script

update Configuration set Value = '

whereConfigurationTypeIdin (selectConfigurationTypeIdfromConfigurationTypewhere Setting = 'SsrsReportServerUrl')

4.5.2SsrsReportsPath Script

update Configuration set Value = '/BCS'

whereConfigurationTypeIdin (selectConfigurationTypeIdfromConfigurationTypewhere Setting = 'SsrsReportsPath')

4.5.3SsrsAuthDomain Script

update Configuration set Value = '<WINDOWS_DOMAIN>'

whereConfigurationTypeIdin (selectConfigurationTypeIdfromConfigurationTypewhere Setting = 'SsrsAuthDomain')

4.5.4SsrsAuthUsername

update Configuration set Value = '<SSRS_USER>'

whereConfigurationTypeIdin (selectConfigurationTypeIdfromConfigurationTypewhere Setting = 'SsrsAuthUsername')

4.5.5SsrsAuthPassword

update Configuration set Value = '<PASSWORD_FOR_SSRS_USER>'

whereConfigurationTypeIdin (selectConfigurationTypeIdfromConfigurationTypewhere Setting = 'SsrsAuthPassword')

Database InstallationPage 1 of 20

Version 01 | 2/19/2015 | IT Deployment Guide | Audience: State IT Staff

Protecting Patients through Background Checks


Note

These settings may be different depending on how you have set up your environment. Please modify accordingly for custom settings.

Database InstallationPage 1 of 20

Version 01 | 2/19/2015 | IT Deployment Guide | Audience: State IT Staff

Protecting Patients through Background Checks

4.5.6Example SQL Result

selectct.Setting, c.Value

from Configuration c

inner joinConfigurationTypectonct.ConfigurationTypeId = c.ConfigurationTypeId

wherect.Settinglike '%ssrs%'

Setting / Value
SsrsReportServerUrl /
SsrsReportsPath / /Bcs
SsrsAuthDomain / localhost
SsrsAuthUsername / SSRSUserName
SsrsAuthPassword / SSRSUserPassword

4.6Verification

Once all of the settings have been updated and the BCS application has been configured and is operating correctly, please verify that the reports section is functional and is displaying reports within the BCS application. Alternatively, you can also go directly to the SSRSserverand run the reports manually.

5SSIS Installation

5.1Installation Pre-Requisites

Before utilizing SSIS, the target machine must have the following items setup:

  • SQL Server 2008 R2
  • SQL Server 2008 R2 Reporting Services

5.2Installation

Refer to the Internet Information Services (IIS) Installation Verification IT Deployment guide for installation instructions.

6BCS SMTP Service

6.1Configuration

This section will ensure that email notifications will be sent when the BCS is in use. Run the following SQL statement to view the current configuration for the SMTP functionality:

select * from Configuration c

inner joinConfigurationTypectonct.ConfigurationTypeId = c.ConfigurationTypeId

where (ct.Setting = 'EmailFrom' orct.Settinglike '%SMTP%')

Configuration values and their meanings are listed in the following table:

Configuration Values / Description
SmtpEnableSsl / Enable SSL when sending emails
SmtpPassword / Password for the domain account being utilized for SMTP
SmtpUsername / Username for the domain account being utilized for SMTP
SmtpPort / SMTP port (default) – normally set to 25
SmtpServer / Address of the SMTP server (either IP or domain name)
EmailFrom / The display name for the actual notification,potentially the same as the SmtpUsername

6.2Virtual Server

If you are running a virtual SMTP server (via IIS manager 6.x), you need to complete a few additional items. (If you are not running a virtual SMTP server, skip this section.)

  1. Set SmtpUsername and SmtpPassword to ‘null’; the username and password would be setup in the virtual setup management.
  1. Within the virtual SMTP server setup, ensure the IP address of the application server is added for security rights.

6.3SQL Parameter Update Scripts

To update the configuration of your system, execute the UPDATE statements below to set your configuration values accordingly.

update Configuration set Value = 'YourSmtpPassword'
whereConfigurationTypeIdin (select ConfigurationTypeIdfromConfigurationTypewhere Setting = 'SmtpPassword')

update Configuration set Value = 'YourSmtpUsername'
whereConfigurationTypeId in (select ConfigurationTypeIdfromConfigurationType where Setting = 'SmtpUsername')

update Configuration set Value = 'YourSmtpServer'
whereConfigurationTypeIdin (select ConfigurationTypeIdfromConfigurationTypewhere Setting = 'SmtpServer')

update Configuration set Value = '25'
whereConfigurationTypeIdin (select ConfigurationTypeIdfromConfigurationTypewhere Setting = 'SmtpPort')

update Configuration set Value = ''
whereConfigurationTypeIdin (select ConfigurationTypeIdfromConfigurationTypewhere Setting = 'EmailFrom')

update Configuration set Value = 'False'
whereConfigurationTypeIdin (select ConfigurationTypeIdfromConfigurationTypewhere Setting = 'SmtpEnableSsl')

select * from Configuration c
inner joinConfigurationTypectonct.ConfigurationTypeId = c.ConfigurationTypeId
where (ct.Setting = 'EmailFrom' orct.Settinglike '%SMTP%')

7BCS Environment Configuration

Each installation of the BCS will exist in some environment, such as DEV, UAT, TEST, or PRODUCTION.The configuration value used for each of your installations shows which version of the application is being used and identifies the environment from which a given email notification is being sent.

The “Value” for each of these environments can be anything you would use to refer to that installation (for example, “Development” to refer to your development environment). The only caveat is when the production environment is being used; we recommend the setting of an empty string. This is because your end users probably won’t understand the meaning of “Production” and would be confused by emails or the footer in the web application referring to “Production.”

7.1Select SQL Script

select *

from Configuration c

inner joinConfigurationTypect

onct.ConfigurationTypeId = c.ConfigurationTypeId

where ct.Setting = 'Environment'

7.2SQL Script for Development Environment

update Configuration set Value = 'DEV'

whereConfigurationTypeIdin (select ConfigurationTypeIdfromConfigurationTypewhere Setting = 'Environment')

7.3SQL Script for Production Environment

update Configuration set Value = ''

whereConfigurationTypeIdin (select ConfigurationTypeIdfromConfigurationTypewhere Setting = 'Environment')

Database InstallationPage 1 of 20

Version 01 | 2/19/2015 | IT Deployment Guide | Audience: State IT Staff