Setting up Scripting Sandbox - Revision history

Ollvihe: /* Configure QPR ProcessAnalyzer to Use Sandbox */

2022-10-14T12:27:22Z

Configure QPR ProcessAnalyzer to Use Sandbox

← Older revision		Revision as of 12:27, 14 October 2022
Line 25:		Line 25:
	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.		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 '''PersistSecurityInfo=~~false~~''' and '''Encrypt=~~true~~''' 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;Encrypt=True		Server=SERVERNAME;User ID=QPR_PA_SB_USER;Password=<password>;Timeout=3600;Persist Security Info=False;Encrypt=True

Ollvihe: /* Configure QPR ProcessAnalyzer to Use Sandbox */

2022-10-14T12:25:52Z

Configure QPR ProcessAnalyzer to Use Sandbox

← Older revision		Revision as of 12:25, 14 October 2022
Line 27:		Line 27:
	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:		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;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 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

Ollvihe: /* Configure QPR ProcessAnalyzer to Use Sandbox */

2022-10-14T12:24:41Z

Configure QPR ProcessAnalyzer to Use Sandbox

← Older revision		Revision as of 12:24, 14 October 2022
Line 25:		Line 25:
	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.		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:		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

Ollvihe at 19:35, 11 December 2021

2021-12-11T19:35:43Z

← Older revision		Revision as of 19:35, 11 December 2021
Line 23:		Line 23:

	== 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 '''Encrypt=true''' and '''PersistSecurityInfo=false''' should be used whenever connecting to a database outside corporate domain. Example:

Ollvihe: Ollvihe moved page Setting up QPR ProcessAnalyzer Scripting Sandbox to Setting up Scripting Sandbox without leaving a redirect

2021-12-11T00:49:23Z

Ollvihe moved page Setting up QPR ProcessAnalyzer Scripting Sandbox to Setting up Scripting Sandbox without leaving a redirect

← Older revision	Revision as of 00:49, 11 December 2021
(No difference)

Ollvihe: /* Configure Sandbox Database in SQL Server */

2020-08-20T09:15:31Z

Configure Sandbox Database in SQL Server

← Older revision		Revision as of 09:15, 20 August 2020
Line 7:		Line 7:
	# Define name '''QPR_PA_SANDBOX_ROLE''' for the new role, and configure the '''Securables''' section as follows:		# Define name '''QPR_PA_SANDBOX_ROLE''' for the new role, and configure the '''Securables''' section as follows:
	## For the endpoints '''Dedicated Admin Connection''', '''TSQL Default VIA''' and '''TSQL Named Pipes''', click '''Deny''' for the '''Connect''' permission.		## For the endpoints '''Dedicated Admin Connection''', '''TSQL Default VIA''' and '''TSQL Named Pipes''', click '''Deny''' for the '''Connect''' permission.
	## For the ~~endpoint~~ '''TSQL Local Machine''' and '''TSQL Default TCP''', click '''Grant''' for the '''Connect''' permission.		## 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 '''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'''.		## For the '''Servers''', click '''Grant''' for the permission '''Connect SQL'''.

Ollvihe: /* Configure Sandbox Database in SQL Server */

2020-08-20T09:15:23Z

Configure Sandbox Database in SQL Server

← Older revision		Revision as of 09:15, 20 August 2020
Line 6:		Line 6:
	# Click '''Security''' > '''Server Roles''' > '''New Server Role...'''.		# Click '''Security''' > '''Server Roles''' > '''New Server Role...'''.
	# Define name '''QPR_PA_SANDBOX_ROLE''' for the new role, and configure the '''Securables''' section as follows:		# Define name '''QPR_PA_SANDBOX_ROLE''' for the new role, and configure the '''Securables''' section as follows:
	## For the endpoints '''Dedicated Admin Connection~~''', '''TSQL Default TCP~~''', '''TSQL Default VIA''' and '''TSQL Named Pipes''', click '''Deny''' for the '''Connect''' permission.		## For the endpoints '''Dedicated Admin Connection''', '''TSQL Default VIA''' and '''TSQL Named Pipes''', click '''Deny''' for the '''Connect''' permission.
	## For the endpoint '''TSQL Local Machine''', click '''Grant''' for the '''Connect''' permission.		## For the endpoint '''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 '''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'''.		## For the '''Servers''', click '''Grant''' for the permission '''Connect SQL'''.

Ollvihe at 10:23, 17 March 2020

2020-03-17T10:23:18Z

Ollvihe at 10:16, 17 March 2020

2020-03-17T10:16:36Z

← Older revision		Revision as of 10:16, 17 March 2020
Line 1:		Line 1:
	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 ~~any~~ requirements on the disk or data file size. QPR ProcessAnalyzer uses the ~~Scripting Sandbox~~ database to run SQL commands ~~that are part of~~ the ETL scripts. Temporary objects in 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.		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.

	== Configure Sandbox Database in SQL Server ==		== Configure Sandbox Database in SQL Server ==

Ollvihe: /* Create Sandbox Database */

2020-03-17T09:57:58Z

Create Sandbox Database

← Older revision		Revision as of 09:57, 17 March 2020
Line 1:		Line 1:
	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 any requirements on the disk or data file size. QPR ProcessAnalyzer uses the Scripting Sandbox database to run SQL commands that are part of the ETL scripts. Temporary objects in 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.		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 any requirements on the disk or data file size. QPR ProcessAnalyzer uses the Scripting Sandbox database to run SQL commands that are part of the ETL scripts. Temporary objects in 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.

	== ~~Create~~ Sandbox Database ==		== Configure Sandbox Database in SQL Server ==
	# Using '''SQL Server Management Studio''', create an empty database with e.g. name '''QPR_PA_SANDBOX'''.		# Using '''SQL Server Management Studio''', create an empty database with e.g. name '''QPR_PA_SANDBOX'''.
	# 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'''.		# 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'''.

← Older revision		Revision as of 10:23, 17 March 2020
Line 6:		Line 6:
	# Click '''Security''' > '''Server Roles''' > '''New Server Role...'''.		# Click '''Security''' > '''Server Roles''' > '''New Server Role...'''.
	# Define name '''QPR_PA_SANDBOX_ROLE''' for the new role, and configure the '''Securables''' section as follows:		# Define name '''QPR_PA_SANDBOX_ROLE''' for the new role, and configure the '''Securables''' section as follows:
	## For the endpoints '''Dedicated Admin Connection''', '''TSQL Default TCP''' ~~(*)~~, '''TSQL Default VIA''' and '''TSQL Named Pipes''', click '''Deny''' for the '''Connect''' permission.		## For the endpoints '''Dedicated Admin Connection''', '''TSQL Default TCP''', '''TSQL Default VIA''' and '''TSQL Named Pipes''', click '''Deny''' for the '''Connect''' permission.
	## For the endpoint '''TSQL Local Machine''', click '''Grant''' for the '''Connect''' permission.		## For the endpoint '''TSQL Local Machine''', 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 '''Deny''' for the following permissions: '''External access assembly''', '''Shutdown''', '''Unsafe assembly''', '''View any database''', '''View any definition''' and '''View server state'''.
Line 14:		Line 14:
	## 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:		# 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		## '''Grant''' for '''Connect''' permission
	## '''Deny''' for '''Delete''', '''Insert''', '''Select''' ~~(*)~~, '''Update''' and '''View database state''' permissions.		## '''Deny''' for '''Delete''', '''Insert''', '''Select''' , '''Update''' and '''View database state''' permissions.
	# 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'''.		# 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'''.

	~~==== 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 ==		== Configure QPR ProcessAnalyzer to Use Sandbox ==