Database Authentication & Scaling

Database authentication

Using Windows Authentication instead of SQL Server Authentication

You can set up your SQL Server connection to use Windows Authentication (SSPI) instead of SQL Server Authentication for the WorkflowGen back-end database and other external databases. This SQL authentication mode provides additional security, since no credentials are stored in the web.config file.

To configure Windows Authentication, use one of the following connection strings in the WorkflowGen web.config file, located in the DRIVE:\Inetpub\wwwroot\wfgen folder:

Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI; 

OR

Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;

You'll also need to modify the WorkflowGen application pool identity for a service account. To do this:

1. In IIS Manager, right-click on the WorkflowGen application pool, then select Advanced Settings.

2. In the Process Model section, select Identity, then click the ... button.

3. Select Custom Account, then click Set...

4. Enter your credentials, then click OK.

5. Make sure the application pools for the other WorkflowGen web applications (auth, graphql, hooks, scim,ws, and webforms) match the wfgen application pool.

6. Make sure the custom account has read/write and modify permissions for the WorkflowGen SQL database and the WorkflowGen files and folders.

7. Use the same account as the application identity to run the WorkflowGen engine and directory synchronization services.

For more information on SQL Server connection strings, see https://www.connectionstrings.com/sql-server/.

Database scaling

Overview

The database scaling feature allows for the addition of database servers in order to dramatically improve server performance and response times. The additional read-only replica database servers will be used as dedicated read-only servers (SELECT SQL queries). The read-only database servers are replicated from the existing primary database using the SQL Server Replication service.

Requirements

Overview

• SQL Server 2014 or later for all database servers

• SQL Server Replication feature must be installed on all SQL Server instances

• SQL Server Agent must be installed and configured on all SQL Server instances

Setup and configuration

Overview

This section provides a general procedure for configuring the SQL Server Replication service on all the SQL Server instances and enabling the WorkflowGen web server in database scaling mode. This procedure is an example based on one WorkflowGen instance and two database server instances architecture.

If your current architecture differs from this example, you can adapt the procedure and some specific configuration options according to your needs.

Your current WorkflowGen environment must meet the following prerequisites:

• The initial WorkflowGen database is already installed on the primary database instance.

• The read-only database instance does not contain an existing WorkflowGen database.

• The WorkflowGen web server is fully configured and allows access to the Administration Module with a WorkflowGen administrator account.

SQL Server configuration options

It is mandatory to update the SQL Server's max text repl size configuration option with a higher value in order for the database replication to function properly. We recommend setting this to the maximum value of 2147483647 instead of the default value of 65536.

Run the following SQL statements in the source (primary) and destination (read-only) SQL Server database instances (this example uses SQL Server 2008):

EXEC sp_configure "max text repl size", 2147483647;
GO
RECONFIGURE;
GO

For more information, see http://msdn.microsoft.com/en-us/library/ms186225.aspx and select your version of SQL Server from the drop-down menu.

SQL Server replication services

To set up a publication on the primary database:

1. Open SQL Server Management Studio.

2. Connect to the primary database instance.

3. Open the Replication node.

4. Right-click on Local Publications and choose New Publication. The New Publication Wizard will open. Click Next.

5. Select WFGEN in the Publication Database list. Click Next.

6. Select Transactional publication in the Publication type list. Click Next.

7. Check all Tables and Views (optional if the WorkflowGen Reporting Module is installed in the current database instance) in the Articles list. Click Next.

8. The Article Issues dialog box will open if you selected Views in the previous step. Click Next.

9. The Filter Table Rows dialog box will open. There is no need to filter the data in the publication. Click Next.

10. The Snapshot Agent dialog box will open. Check Create a snapshot immediately and keep the snapshot available to initialize subscriptions. Click Next.

11. In Snapshot Agent Security, provide valid service or administrator credentials for the Windows account. Click Next.

12. Check Create the publication. Click Next.

13. Enter the Publication name WFGEN_PUB.

14. Click Finish. If all is successful, close the New Publication Wizard.

To set up a subscription on the read-only database:

1. Open SQL Server Management Studio.

2. Connect to the read-only database instance.

3. Open the Replication node.

4. Right-click on Local Subscriptions and choose New Subscription. The New Subscription Wizard will open. Click Next.

5. Select <Find SQL Server Publisher...>.

6. Connect to the primary database instance.

7. Select WFGEN_PUB in the Databases and publications list. Click Next.

8. Select Run each agent at its Subscriber (pull subscriptions). Click Next.

9. In the Subscription Database column, select <New database...>.

10. Enter the WFGEN as the Database name and click OK. Click Next.

11. In Distribution Agent Security, provide valid service or administrator credentials for the Windows account. Click Next.

12. In Synchronization Schedule, select Run continuously. Click Next.

13. In Initialize Subscriptions, select Immediately. Click Next.

14. Select Create the subscription(s). Click Next.

15. Click Finish. If all is successful, close the New Subscription Wizard.

16. Create and add the SQL user WFGEN_USER to the WFGEN read-only database security with db_datareader and db_datawriter permissions.

The WFGEN_USER account will be used in the connection string for the WFGEN read-only database in WorkflowGen.

Database scaling in WorkflowGen

Enable the multi-database mode in WorkflowGen:

1. In the WorkflowGen Administration Module, open the Configuration Panel and select the General tab.

2. In the Read-only database connection string field, enter the WorkflowGen connection string of the read-only database instance.

3. Check Enable on Multi-database to turn on the database scaling feature.

4. Check each of the specific Portal pages and Modules:

   • If unchecked, it will use the primary database.

   • If checked, it will use the read-only replica database.

5. Save and close.

References

• For more information on SQL Server replication, see http://msdn.microsoft.com/en-us/library/ms151198.aspx and select your version of SQL Server from the drop-down menu.

• For more information on the SQL Server max text repl size option, see http://msdn.microsoft.com/en-us/library/ms186225.aspx and select your version of SQL Server from the drop-down menu.