Configure Data Gateways for SQL authentication
TThe following conditions must be met before configuring Data Gateways:
- The Blue Prism version must be version 6.8 Enterprise Edition or above, which must be configured to connect via at least one application server.
- Data Gateways requires a dedicated SQL server login with access to the Blue Prism database, created in SQL server. Whether using SQL or Windows authentication a user must exist with access to the Blue Prism database using the BPA_DataGateways role.
- The user account that runs the Blue Prism application server process must have read and write permissions to the file directory where the Logstash configuration file (bpconf.config) is stored. By default this is found in C:\Program Files\Blue Prism Limited\Blue Prism Automate.
Data Gateways requires a number of components to be installed and configured in the environment in which application servers are running the Data Gateways processes. The Data Gateways components are included in a separate installer, Blue Prism Data Gateways Components, available from the Blue Prism portal, see https://portal.blueprism.com/product/extras.
Authentication options for SQL login
You need to create a SQL login to access the Blue Prism Data Gateways database. You can then use one of the following authentication methods:
- Windows authentication (integrated security) using either existing or dedicated (recommended) credentials. If using existing Windows authentication credentials, the Data Gateways process will run in the same user context as the Blue Prism application server service.
- SQL authentication using credentials configured via Blue Prism Data Gateways configuration. The service will run under the context of the Data Gateways service.
Data Gateways requires a SQL login, and a Windows authentication or SQL user to be created against the Blue Prism database. This section describes how to create a SQL login and a Windows authentication user.
Use Microsoft SQL Server Management Studio to create a SQL login using SQL server authentication. Alternatively, run the SQL script below.
Create SQL login and user
If you plan to use SQL authentication on your database, you can run the following SQL query against the server instance on which your Blue Prism databases are located. This query creates a login and user that allows Data Gateways to access the server.
Only users with ALTER ANY LOGIN permission on the server, or membership to the securityadmin or sysadmin fixed server roles can create logins.
To create a SQL login and user, run the following command:
CREATE LOGIN [loginname] WITH PASSWORD = 'password';
CREATE USER [username] FOR LOGIN [loginname];
sp_addrolemember 'BPA_DataGatewaysEngine', ‘[username]’
Before running the query, enter the required Blue Prism database and replace password with a complex password for the Data Gateways login. For example:
CREATE LOGIN [Data Gateways SQL Login] WITH PASSWORD = '&CTh76£NkM';
Replace username with a user name of your choice and replace loginname with SQL login credentials name. For example:
CREATE USER [Data Gateways User] FOR LOGIN [Data Gateways SQL Login];
sp_addrolemember 'BPA_DataGatewaysEngine', ‘[Data Gateways SQL User]’
This section describes how to enable Data Gateways in BP Server and applies to SQL authentication and Windows authentication users.
The Data Gateways process must be enabled and the associated port defined on all application servers that are required to run Data Gateways. The Data Gateways process can only run on one instance on a single device. Where multiple application servers are configured on a single device, the Data Gateways process must be enabled on each one.
- Open BPServer.exe and open the server configuration for the relevant environment.
- Click the Data Gateways Settings tab.
- Select Enable Data Gateways Process and enter the port that the application server will listen on for commands to start or stop the Data Gateways process.
This port is also used by other Blue Prism application servers within the same environment to control the Data Gateways engine. This can be left as the default port (8101) unless there is another process on the application server that is already using that port. Ensure that security systems, such as firewalls, do not prevent other application servers from communicating with the Data Gateways engine via this port.
The application server must be stopped before changing the port number.
- If required, select Log Data Gateways Output To Console to send high-level Data Gateways messages to the console log in BPServer. This data can be used to help diagnose any potential issues with the Data Gateways integration. This option should not be left selected when not specifically required.
- Select Enable Trace Logging to enable verbose Data Gateways logging, which can be used to further diagnose any issues between Blue Prism and Data Gateways.
- In Data Gateways User Details, select one of the following options:
- Run as Current User – The Data Gateways engine will operate in the same context as the Blue Prism Server service. Select this option if the Data Gateways engine will log into the Blue Prism database using a SQL authentication account. The credentials to use will be specified separately via the Data Gateways Configuration.
- Run as Specific User – The Data Gateways engine will operate under the context of the Windows or Network account provided. Select this option if the Data Gateways engine will log into the Blue Prism database using a Windows Authentication account. It will access SQL using the specified credentials. If you select this option, additional configuration is required on the application server running the Data Gateways engine to configure a temporary folder that the Logstash components running under this context can access. See Additional configuration required to ‘Run as Specific User for details.
- It is not recommended to use Run as Current User to access the Blue Prism Database using Windows authentication. This configuration will result in the database being accessed using the account that is running the Blue Prism Server service which could result in the Data Gateways functionality having greater access to the database than it should have.
- If using integrated security, we recommend using your own certificate to encrypt the Blue Prism server configuration files to make the configuration data more secure. This can be configured via Manage Config Encryption Settings in BPServer.exe.
- Click Save to apply the settings.
- Start the Blue Prism Server Service from the Windows Services menu.
Additional configuration required to ‘Run as Specific User’
If you have configured Data Gateways to Run as a Specific User you will need to perform the following steps to configure a temporary folder that the Logstash components running under this context can access. Typically Run as a Specific User is selected when you require the Data Gateways engine to authenticate against the database using Windows authentication.
The following steps must be performed on the application server running the Data Gateways engine:
- Create a new temporary directory on the C:\ drive, for example dg-user-access.
- Assign the default Authenticated Users permissions to the directory.
- Open the C:\Logstash\logstash\config\jvm.options file in a text editor.
Add the temporary directory’s path to the -Djava.io.tmpdir= line. Uncomment the line if it is commented out (remove the # from the beginning of the line).
- Save the changes.
- Restart the Data Gateways service or the application server to apply the updates.
Data Gateways credentials are a type of credential record that can be used when configuring Data Gateways. They can be used to store the credentials needed to access databases or HTTP end points. In addition to this, a Data Gateways credential must be created to store the SQL server user account that provides the Data Gateways engine with access to the Blue Prism database, if using SQL (versus Windows) authentication.
Credentials that use the Data Gateways credential type are only accessible by the application server when generating Data Gateway configurations – they cannot be used in standard Blue Prism processes or other Blue Prism elements such as web API definitions.
Add a credential for the Blue Prism database SQL server user
Create a credential to store the SQL Server user account required for secure access to the Blue Prism database.
The credential name, username, and password must exactly match those already created for the SQL Server login.
- Select the System tab.
Select Security > Credentials.
- Click New. The Credential Details window displays.
- Configure the following credential:
- Name – Enter the SQL server login name that you have created.
- Type – Select Data Gateway Credential.
- Username – Enter the user name that you have created for the SQL server login.
- Password – The password that is specified for the SQL server user.
- Click OK to save the credential.
If you attempt to delete the Data Gateways credential that is currently in use a warning message displays and you are prompted to confirm the deletion.
Configure the Data Gateways engine by determining what data will be processed and where relevant, the frequency at which data is sent.
- Select the required options to determine where session log data will be stored - at least one option must be selected:
- Write session logs to database – Session logs will be sent to the session log table in the Blue Prism database. If not selected, the functionality to view new session logs in the Blue Prism client will be unavailable. This is the default setting and should only be changed if you no longer want new session log records to be accessible via the Blue Prism user interface.
- Send session logs to data gateways – Session logs will be sent to a temporary storage table on the Blue Prism database where they are accessed by the Data Gateways engine for use in the configured outputs. The logs will be deleted from the temporary table after they have been processed by Data Gateways.
- If the specified HTTP, Splunk, or Database Data Gateway endpoint is not reachable when the data is being processed, the data is stored temporarily until the endpoint becomes available. See Data Storage when endpoint unavailable for details.
- Select Send published dashboards to data gateways to send data from published dashboards to a database table in the Data Gateways system and set the frequency that data is sent for each dashboard. For more information about Blue Prism dashboards, see the Dashboards topic in the in-product help.
- Select Send work queue analysis snapshot data to data gateways to send work queue analysis data to the database. For more information about Blue Prism work queue analysis snapshots, see the Work queue snapshots topic in the in-product help.
- Set the Status monitoring frequency to a value between 5 and 3600 seconds. This determines how often the Data Gateways screen in Control Room screen is refreshed.
- Select the type of user that will be used by the Blue Prism database connection settings to connect to the Blue Prism database.
- Integrated Security – Windows authentication will be used to connect to the Blue Prism database. The account configured in the Data Gateways Settings tab on the Blue Prism server will be used.
- SQL Authentication User – SQL authentication will be used. Select the credential already added that contains the SQL credentials to be used. Only credentials that are configured as Data Gateways Credentials will be available for selection.
- Update the default Port used for the Blue Prism database connection settings, if required. This is the port that Data Gateways will use to attempt to connect to the Blue Prism database. The default value is 1433.
- Click Apply to save the settings.
A Data Gateways configuration is a collection of outputs that define where data from session logs, published dashboards, and/or custom objects is sent. For each output, data can be sent to a file, HTTP endpoint, Splunk instance, or a database.
A Data Gateways configuration can consist of any number of individual outputs. The data from each of the outputs is added to a single configuration file.
- Click the System tab and select Data Gateways > Configuration.
Click Add new gateway output. The Data gateway output wizard displays.
- Enter a unique name for the output.
- Select an output type, File, HTTP endpoint, Splunk, or Database and complete the fields for that output type.
- Click Next.
- Select the data to send to the configuration file for the specified output type:
- Session logs – Data Gateways will process session log data for the specified output type. If you choose this option for any output type, select which session log data will be included in the output.
- Published dashboards – Data Gateways will process data from the configured published dashboards for the specified output type.
- Work queue analysis snapshot data – Data Gateways will process the work queue analysis data for the specified output type.
- Custom object data – Data Gateways will process data from any Blue Prism action configured to use the Data Gateways internal business object.
Click Next. A preview of the output data displays.
The data in the produced output can be edited directly by clicking Edit advanced output. For more information about advanced configurations, see Advanced outputs.
Click Finish to save the output to the configuration.
- A Data gateways message displays, prompting you to restart Data Gateways to apply any changes. Click OK to close the message. For more information on restarting the data gateway process see Start and stop the Data Gateways engine.