Setting up Scripting Sandbox

Follow these instructions to set up the QPR ProcessAnalyzer Scripting Sandbox database in the SQL Server and how to configure QPR ProcessAnalyzer to use the database. The Scripting Sandbox database is a readonly database, that will not contain any data, and thus it doesn't have requirements for the disk or data file size. QPR ProcessAnalyzer uses the scripting sandbox database to run SQL commands in the ETL scripts. Temporary objects generated by the SQL queries are stored to the TempDB, and that's why the TempDB size requirements are important.

Configure Sandbox Database in SQL Server

1. Using SQL Server Management Studio, create an empty database with e.g. name QPR_PA_SANDBOX.
2. Click the QPR_PA_SANDBOX database and from the popup menu, click Properties, go to the Options tab, and change the database to use Recovery model = Simple.
3. Click Security > Server Roles > New Server Role....
4. Define name QPR_PA_SANDBOX_ROLE for the new role, and configure the Securables section as follows:
   1. For the endpoints Dedicated Admin Connection, TSQL Default VIA and TSQL Named Pipes, click Deny for the Connect permission.
   2. For the endpoints TSQL Local Machine and TSQL Default TCP, click Grant for the Connect permission.
   3. For the Servers, click Deny for the following permissions: External access assembly, Shutdown, Unsafe assembly, View any database, View any definition and View server state.
   4. For the Servers, click Grant for the permission Connect SQL.
5. Click Security > Logins > New login... (in the popup menu).
6. Define user name QPR_PA_SB_USER.
   1. Define Default database = QPR_PA_SANDBOX.
   2. In the Server Roles tab, check the QPR_PA_SANDBOX_ROLE.
   3. In the User Mapping tab, click QPR_PA_SANDBOX and click checkboxes for db_denydatareader and db_denydatawriter.
   4. In the User Mapping tab, click master and click checkboxes for db_denydatareader and db_denydatawriter.
   5. In the Securables tab, the only effective securable should be CONNECT SQL (listed in the Effective tab).
7. Click Databases > System Databases > master > Properties (in the popup menu). In the Database Properties window, click the Permissions tab, and QPR_PA_SB_USER. Check:
   1. Grant for Connect permission
   2. Deny for Delete, Insert, Select , Update and View database state permissions.
8. Click the QPR_PA_SANDBOX database and from the popup menu click Properties and go to the Options tab, and change setting Other options > State > Database Read-only to True.

Configure QPR ProcessAnalyzer to Use Sandbox

To run SQL scripts in QPR ProcessAnalyzer, the sandbox database connection string needs to be configured to the PA_Configuration table to the SandboxDatabaseConnectionString field.

In order to use a sandbox located outside the corporate domain, it is highly recommended that the communication between the QPR ProcessAnalyzer database and the sandbox database is encrypted. Therefore, an SSL certificate of the remote system must be installed as a trusted certificate into the system running the QPR ProcessAnalyzer database. The PersistSecurityInfo=False and Encrypt=True should be used whenever connecting to a database outside corporate domain. Example:

Server=SERVERNAME;User ID=QPR_PA_SB_USER;Password=<password>;Timeout=3600;Persist Security Info=False;Encrypt=True

Here is an example of a local installation where the sandbox is located on the same system as QPR ProcessAnalyzer:

Server=localhost;User ID=QPR_PA_SB_USER;Password=<password>;Persist Security Info=False