Setting up Scripting Sandbox

Create Sandbox Database

1. Using SQL Server Management Studio, create an empty database with e.g. name QPR_PA_SANDBOX. Change the database to use Recovery model = Simple (in the Options tab).
2. Click Security > Server Roles > New Server Role... (in the popup menu).
   1. Define name QPR_PA_SANDBOX_ROLE for the new role.
   2. In the Securables section:
      1. For the endpoints Dedicated Admin Connection, TSQL Default TCP (*), TSQL Default VIA and TSQL Named Pipes, click Deny for the Connect permission.
      2. For the endpoint TSQL Local Machine, 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.
3. Click Security > Logins > New login... (in the popup menu).
4. Define name QPR_PA_SB_USER and a password (store the password into a safe place).
   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).
5. 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 Grant for Connect permission, and Deny for Delete, Insert, Select (*), Update and View database state permissions.
6. Make the QPR_PA_SANDBOX database read-only as follows: Click the QPR_PA_SANDBOX database and from the popup menu click Properties. In the Options tab, change setting Other options > State > Database Read-only to True.
7. Ensure that both the SQL Server and Windows Authentication modes are supported in the Server Properties > Security > Server Authentication.

Testing connection

For temporary connection tests with, e.g., SQL Server Management Studio, the following changes should be done:

• (*) Instead of denying the permission, grant it for: TSQL Default TCP and Select
• (**) Do not select db_denydatareader in: QPR_PA_SANDBOX and master

After the tests, it is highly recommended to restore the original recommended settings.

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 Encrypt=true and PersistSecurityInfo=false 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;Asynchronous Processing=True;Encrypt=true

Local installation where the sandbox is located on the same system as the QPR ProcessAnalyzer:

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