protecting vulnerable populations through … · web viewusing sql server management studio start...

Database InstallationBackground Check System

IT Deployment GuideVersion 01

4/21/2015

Protecting Patients through Background Checks

Table of Contents

1 Overview.............................................................................................................................................. 4

1.1 BCS Database System Requirements.........................................................................................4

1.2 BCS Source Database Files.........................................................................................................4

2 BCS Database Installation.................................................................................................................. 5

2.1 Using SQL Server Management Studio.......................................................................................5

2.2 Restore Databases...................................................................................................................... 6

2.3 Verify Databases Restored..........................................................................................................7

2.4 Database Configuration Changes................................................................................................8

2.5 Database Security........................................................................................................................ 8

3 SSRS Installation............................................................................................................................... 10

3.1 Installation Pre-Requisites..........................................................................................................10

3.2 Installation Overview..................................................................................................................11

3.3 Installation of RDLs with rsScripter.............................................................................................11

3.4 Manual Installation of RDLs.......................................................................................................12

4 SSRS Configuration..........................................................................................................................13

4.1 Data Source............................................................................................................................... 13

4.2 Verify Configuration.................................................................................................................... 14

4.3 Configuration Parameters..........................................................................................................14

4.4 Authentication............................................................................................................................ 15

4.5 SQL Parameter Update Scripts..................................................................................................15

4.6 Verification................................................................................................................................. 17

5 SSIS Installation................................................................................................................................ 17

5.1 Installation Pre-Requisites..........................................................................................................17

5.2 Installation.................................................................................................................................. 17

6 BCS SMTP Service............................................................................................................................ 18

6.1 Configuration.............................................................................................................................. 18

Database Installation of 20Version 01 | 4/21/2015 | IT Deployment Guide | Audience: State IT Staff


6.2 Virtual Server............................................................................................................................. 18

6.3 SQL Parameter Update Scripts..................................................................................................19

7 BCS Environment Configuration.....................................................................................................20

7.1 Select SQL Script....................................................................................................................... 20

7.2 SQL Script for Development Environment..................................................................................20

7.3 SQL Script for Production Environment......................................................................................20



1 OverviewThis 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.1 BCS Database System RequirementsIn 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.2 BCS Source Database FilesYou 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



2 BCS Database Installation

2.1 Using SQL Server Management StudioStart SQL Server Management Studio (SSMS) and connect to the local database. You will need to provide the Server Name and Authentication information based off of your local configuration.



2.2 Restore DatabasesWithin SSMS, execute the three SQL statements at the end of this section in a New Query window. 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.1 Script 1

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

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

MOVE N'BcsCa_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\BCS_1.ldf',NOUNLOAD,STATS = 10GO

2.2.2 Script 2

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

WITH FILE = 1, MOVE N'BcsDevLogging' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\BCSLogging.mdf',

MOVE N'BcsDevLogging_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\BCSLogging_1.ldf',NOUNLOAD, STATS = 10GO



2.2.3 Script 3

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

WITH FILE = 1, MOVE N'Registry' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\BCSRegistry.mdf',

MOVE N'Registry_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\BCSRegistry_1.ldf',NOUNLOAD, STATS = 10GO

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

Note

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



2.4 Database Configuration ChangesThe following two database configuration settings need to be updated to a state-specific setting when the database is initially setup for a state.

Email address that state will use to send out BCS emails

Location of reporting server

2.5 Database SecurityThe BCS website connects to the BCS databases via an SQL Server login. 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 as needed.

USE BCSCREATE ROLE db_executor



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



3 SSRS Installation

3.1 Installation Pre-RequisitesBefore utilizing SSRS, the target machine must have the following items setup:

SQL Server 2008 R2 Reporting Services installed 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.

An SQL Server account for the application.



3.2 Installation OverviewThere 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: http://jpedroalmeida.wordpress.com/2013/02/06/rsscripter-nice-tool-to-help-you-migrate-reporting-services/.

The scripting program, rsScripter, is included in the SSRS source folder. The following section will explain how to use rsScripter. If you chose to install SSRS RDLs manually, 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.3 Installation of RDLs with rsScripter

1. Log in to your current development SSRS site for the BCS platform.

2. Delete all folders and reports from within the BCS folder structure.

o Do NOT Delete the BcsDataSource.

3. Unzip the SSRS package to: “C:\SSRS.”

4. Open the “RS Scripter Load All Items.cmd” file and locate the ReportServer variable: “SET REPORTSERVER=http://SSRSServerNameHere/ReportServer.” Update the Server Name Here text to be your Server Name.

5. 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.


http://jpedroalmeida.wordpress.com/2013/02/06/rsscripter-nice-tool-to-help-you-migrate-reporting-services/

http://jpedroalmeida.wordpress.com/2013/02/06/rsscripter-nice-tool-to-help-you-migrate-reporting-services/


3.4 Manual Installation of RDLs

1. With SSRS properly deployed and initialized, browse to “http://localhost/reports” from the SSRS server. The SSRS Home page will display.

2. 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:

o Application Forms

o Department Reports

o Provider Reports

o System Reports

3. Use the Upload File button to upload the reports from the SSRS folder in Source Installation Package.


http://localhost/reports


4 SSRS Configuration

4.1 Data SourceTo configure the SSRS Data Source, complete the following steps:

1. Browse to “http://localhost/reports” from the SSRS server.

2. Navigate to the root BCS folder and click the New Data Source button.

3. Enter the designated values into the following fields:

o Name: BcsDataSource

o Data source type: Microsoft SQL Server

o Connection string: Data source=[SQL_SERVER_NAME]; Network Library=DBMSSOCN; Initial Catalog=[BCS_DB_NAME]

o Connect using: Credentials stored securely in the report server

o User name: [SSRS_USER] (i.e., DOMAIN\ssrs_user)

o Password: [PASSWORD_FOR_SSRS_USER]

4. Click Test Connection to confirm connectivity.

5. Click Apply.

6. Associate this data source with EACH of the deployed reports. Complete the following procedure for each report:

o Hover over the report and choose Manage from the Actions menu.

o Click Data Sources.

o Select A shared data source, and click the Browse button.

o Navigate through the tree and select BcsDataSource.

o Click OK.

Note

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



4.2 Verify ConfigurationOnce the reports are setup and configured, update the BCS platform Configuration table to ensure the proper configuration. To do so, execute the following SQL:

SELECT *FROM Configuration cINNER JOIN ConfigurationType ct ON ct.ConfigurationTypeId = c.ConfigurationTypeIdWHERE ct.Setting LIKE '%ssrs%'

4.3 Configuration ParametersEnsure that the parameters listed in the table below are updated appropriately.

SSRS Configuration Parameter Parameter Description

SsrsReportServerUrl http:// path to the SSRS configuration, i.e., http://localhost/Reports

SsrsReportsPath Root level of the location of reports within the SSRS configuration; As the image in section 3.3, step 3 indicates, when creating the folder structure, you should have a root BCS folder.

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.4 AuthenticationThe 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.5 SQL Parameter Update Scripts

4.5.1 SsrsReportServerUrl ScriptUPDATE Configuration SET Value = 'http://<SSRS_SERVER>/Reportserver'WHERE ConfigurationTypeId IN (SELECT ConfigurationTypeId FROM ConfigurationType WHERE Setting = 'SsrsReportServerUrl')

4.5.2 SsrsReportsPath ScriptUPDATE Configuration SET Value = '/BCS'WHERE ConfigurationTypeId IN (SELECT ConfigurationTypeId FROM ConfigurationType WHERE Setting = 'SsrsReportsPath')

4.5.3 SsrsAuthDomain ScriptUPDATE Configuration SET Value = '<WINDOWS_DOMAIN>'WHERE ConfigurationTypeId IN (SELECT ConfigurationTypeId FROM ConfigurationType WHERE Setting = 'SsrsAuthDomain')



4.5.4 SsrsAuthUsernameUPDATE Configuration SET Value = '<SSRS_USER>'WHERE ConfigurationTypeId IN (SELECT ConfigurationTypeId FROM ConfigurationType WHERE Setting = 'SsrsAuthUsername')

4.5.5 SsrsAuthPasswordUPDATE Configuration SET Value = '<PASSWORD_FOR_SSRS_USER>'WHERE ConfigurationTypeId IN (SELECT ConfigurationTypeId FROM ConfigurationType WHERE Setting = 'SsrsAuthPassword')

Note

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

4.5.6 Example SQL ResultSELECT ct.Setting, c.Value FROM CONFIGURATION CINNER JOIN ConfigurationType ct ON ct.ConfigurationTypeId = c.ConfigurationTypeIdWHERE ct.Setting LIKE '%ssrs%'

Setting Value

SsrsReportServerUrl http://localhost/ReportServer

SsrsReportsPath /Bcs

SsrsAuthDomain localhost

SsrsAuthUsername SSRSUserName

SsrsAuthPassword SSRSUserPassword

4.6 VerificationOnce 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 SSRS server and run the reports manually.



5 SSIS Installation

5.1 Installation Pre-RequisitesBefore utilizing SSIS, the target machine must have the following items setup:

SQL Server 2008 R2

SQL Server 2008 R2 Reporting Services

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



6 BCS SMTP Service

6.1 ConfigurationThis 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 cINNER JOIN ConfigurationType ct ON ct.ConfigurationTypeId = c.ConfigurationTypeIdWHERE (ct.Setting = 'EmailFrom' OR ct.Setting LIKE '%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.2 Virtual ServerIf 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.

2. Within the virtual SMTP server setup, ensure the IP address of the application server is added for security rights.



6.3 SQL 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'WHERE ConfigurationTypeId IN (select ConfigurationTypeId FROM ConfigurationType WHERE Setting = 'SmtpPassword')

UPDATE Configuration SET Value = 'YourSmtpUsername'WHERE ConfigurationTypeId in (select ConfigurationTypeId FROM ConfigurationType where Setting = 'SmtpUsername')

UPDATE Configuration SET Value = 'YourSmtpServer'WHERE ConfigurationTypeId IN (select ConfigurationTypeId FROM ConfigurationType WHERE Setting = 'SmtpServer')

UPDATE Configuration SET Value = '25'WHERE ConfigurationTypeId IN (select ConfigurationTypeId FROM ConfigurationType WHERE Setting = 'SmtpPort')

UPDATE Configuration SET Value = '[email protected]'WHERE ConfigurationTypeId IN (select ConfigurationTypeId FROM ConfigurationType WHERE Setting = 'EmailFrom')

UPDATE Configuration SET Value = 'False'WHERE ConfigurationTypeId IN (select ConfigurationTypeId FROM ConfigurationType WHERE Setting = 'SmtpEnableSsl')

SELECT * FROM Configuration cINNER JOIN ConfigurationType ct ON ct.ConfigurationTypeId = c.ConfigurationTypeIdWHERE (ct.Setting = 'EmailFrom' OR ct.Setting LIKE '%SMTP%')



7 BCS Environment ConfigurationEach 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.1 Select SQL ScriptSELECT * FROM Configuration cINNER JOIN ConfigurationType ct ON ct.ConfigurationTypeId = c.ConfigurationTypeIdWHERE ct.Setting = 'Environment'

7.2 SQL Script for Development EnvironmentUPDATE Configuration SET Value = 'DEV'WHERE ConfigurationTypeId IN (select ConfigurationTypeId FROM ConfigurationType WHERE Setting = 'Environment')

7.3 SQL Script for Production EnvironmentUPDATE Configuration SET Value = ''WHERE ConfigurationTypeId IN (select ConfigurationTypeId FROM ConfigurationType WHERE Setting = 'Environment')


protecting vulnerable populations through … · web viewusing sql server management studio start...

Documents