Setting up Scripting Sandbox: Difference between revisions

From QPR ProcessAnalyzer Wiki
Jump to navigation Jump to search
 
(20 intermediate revisions by the same user not shown)
Line 1: Line 1:
== Create Sandbox Database ==
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 [[QPR_ProcessAnalyzer_System_Requirements#Recommended_Hardware_for_QPR_ProcessAnalyzer_Database_Server|TempDB size requirements]] are important.
# 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).
 
# Click '''Security''' > '''Server Roles''' > '''New Server Role...''' (in the popup menu).
== Configure Sandbox Database in SQL Server ==
## Define name '''QPR_PA_SANDBOX_ROLE''' for the new role.
# Using '''SQL Server Management Studio''', create an empty database with e.g. name '''QPR_PA_SANDBOX'''.
## In the '''Securables''' section:
# 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'''.
### For the endpoints '''Dedicated Admin Connection''', '''TSQL Default TCP''' (*), '''TSQL Default VIA''' and '''TSQL Named Pipes''', click '''Deny''' for  the '''Connect''' permission.
# Click '''Security''' > '''Server Roles''' > '''New Server Role...'''.
### For the endpoint '''TSQL Local Machine''', click '''Grant for the '''Connect''' permission.
# Define name '''QPR_PA_SANDBOX_ROLE''' for the new role, and configure the '''Securables''' section as follows:
### 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'''.
## For the endpoints '''Dedicated Admin Connection''', '''TSQL Default VIA''' and '''TSQL Named Pipes''', click '''Deny''' for  the '''Connect''' permission.
### For the '''Servers''' click '''Grant''' for the permission '''Connect SQL'''.
## For the endpoints '''TSQL Local Machine''' and '''TSQL Default TCP''', click '''Grant''' for the '''Connect''' permission.
## 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'''.
## For the '''Servers''', click '''Grant''' for the permission '''Connect SQL'''.
# Click '''Security''' > '''Logins''' > '''New login...''' (in the popup menu).
# Click '''Security''' > '''Logins''' > '''New login...''' (in the popup menu).
# Define name '''QPR_PA_SB_USER''' and a password (store the password into a safe place).
# Define user name '''QPR_PA_SB_USER'''.
## Define '''Default database''' = '''QPR_PA_SANDBOX'''.
## Define '''Default database''' = '''QPR_PA_SANDBOX'''.
## In the '''Server Roles''' tab, check the '''QPR_PA_SANDBOX_ROLE'''.
## In the '''Server Roles''' tab, check the '''QPR_PA_SANDBOX_ROLE'''.
## In the '''User Mapping''' tab, click '''QPR_PA_SANDBOX''' and click checkboxes for '''db_denydatareader''' (**?) and '''db_denydatawriter'''.
## In the '''User Mapping''' tab, click '''QPR_PA_SANDBOX''' and click checkboxes for '''db_denydatareader''' and '''db_denydatawriter'''.
## In the '''User Mapping''' tab, click '''master''' and click checkboxes for '''db_denydatareader''' (**) and '''db_denydatawriter'''.
## In the '''User Mapping''' tab, click '''master''' and click checkboxes for '''db_denydatareader''' and '''db_denydatawriter'''.
## In the '''Securables''' tab, the only effective securable should be '''CONNECT SQL''' (listed in the '''Effective''' tab).
## In the '''Securables''' tab, the only effective securable should be '''CONNECT SQL''' (listed in the '''Effective''' tab).
# 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.
# 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:
# 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'''.
## '''Grant''' for '''Connect''' permission
# Ensure that both the SQL Server and Windows Authentication modes are supported in the '''Server Properties''' > '''Security''' > '''Server Authentication'''.
## '''Deny''' for '''Delete''', '''Insert''', '''Select''' , '''Update''' and '''View database state''' permissions.
==== Testing connection ====
# 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'''.
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 ==
== 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 database table in QPR ProcessAnalyzer|PA_Configuration]] table to the  '''SandboxDatabaseConnectionString''' field.
To run SQL scripts in QPR ProcessAnalyzer, the sandbox database connection string needs to be configured to the [[PA Configuration database table|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:
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:
<pre>
<pre>
Server=SERVERNAME;User ID=QPR_PA_SB_USER;Password=<password>;Timeout=3600;Persist Security Info=False;Asynchronous Processing=True;Encrypt=true
Server=SERVERNAME;User ID=QPR_PA_SB_USER;Password=<password>;Timeout=3600;Persist Security Info=False;Encrypt=True
</pre>
</pre>


Local installation where the sandbox is located on the same system as the QPR ProcessAnalyzer:
Here is an example of a local installation where the sandbox is located on the same system as QPR ProcessAnalyzer:
<pre>
<pre>
Server=localhost;User ID=QPR_PA_SB_USER;Password=<password>;Persist Security Info=False
Server=localhost;User ID=QPR_PA_SB_USER;Password=<password>;Persist Security Info=False
</pre>
</pre>
[[Category: QPR ProcessAnalyzer]]

Latest revision as of 12:27, 14 October 2022

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
Retrieved from "https://wiki.onqpr.com/pa/index.php?title=Setting_up_Scripting_Sandbox&oldid=21857"