Connect to the database with a user other than sa
From Business Process Management, BPM and Workflow Automation Wiki | BizAgi BPMS
<keywords content="keywords">database, user, different, sa, connection, SQL Server, login, database access, database security, web.config, security, database user, db user, db login, db</keywords>
How to change the SQL Server login in Bizagi
Security policies within companies may restrict the use of the sa login for SQL Server database configuration.
Due to this reason, in this article we present how to to set or change the default login configured to access the database in Bizagi projects and their environments.
Before moving on, it is necessary to understand that there are 2 different levels of permissions involved in SQL Server security for Bizagi projects. These are:
- The SQL Server login settings for Bizagi Studio (used for the development environment).
- The SQL Server login settings for the Work portal and Scheduler (used for the environments running the workflows: Test and/or Production).
Take into account that Bizagi uses SQL Server's sa login for both settings by default but this can be changed.
If you have not created any project yet with Bizagi, then the sa login is still not used nor reference in any configuration files in Bizagi. Therefore, when creating a new project you may specify a login of your choice in the new project's information as long as it meets the login requirements as described in the sections below.
Defining the SQL Server login settings for Bizagi Studio (For Development Environment)
While using Bizagi Studio, creation of new objects at a database level takes place, mainly because Bizagi Studio works with projects in development stages.
This means that for the development environment of a project, it is strictly required to use a database login with the following characteristics:
Authentication of type SQL Server
The login must use SQL Server authentication:
Server Roles
The server roles specified for this login must include: "public" and "sysadmin".
Notice that the SQL Server login used for Bizagi Studio does not need to specify any "User mapping" for a particular database. This is so because this same login would be used for all those projects that are accessed from that given Bizagi Studio installation.
Defining the SQL Server login settings for the Work portal and Scheduler (For execution: Production and Test environment)
The SQL Server login used for a Bizagi work portal and scheduler service can be configured separately for each different project and each of its environments (development, test, production).
If you have multiple projects and/or multiple environments in the same SQL Server database instance, it is recommended to use a different login for each project and for each environment.
Summing it up, for the work portal (the workflow's execution) and scheduler (a service which performs some automatic tasks and jobs), the SQL Server login used for data access requires the following characteristics:
Authentication of type SQL Server
The login must use SQL Server authentication:
Server Roles
The server roles specified for this login must include: "public"
User mapping
The login must include the following user mapping items for that specific database (the one that corresponds to the work portal's environment): "db_datareader", "db_datawriter", "public", "rlBA_SQL_BizagiWebApp" and "rlBA_SQL_ExecuteBizagiSPs".
Editing the connection string used in the Work portal
The configuration that defines the data access connection to the database from the IIS web application is specified in the "web.config" file of the "web application" folder of the given project environment (by default at "C:\Bizagi\EDITION\Projects\PROJECT_NAME\WebApplication").
The "web.config" file is XML-formatted and can be edited as described below:
Locate the "DSNDB" element and edit its information:
<add key="DSNDB" value="Current Language=us_english;Initial Catalog=PurchaseRequest;Data Source=JUANCAMILO-PC\SQLEXPRESS;User ID=Bizagiuser;Password=testpassword;" />
In this example, user Bizagiuser and password testpassword was used for the connection.
Notice that in the password definition, you may input the encrypted string text as returned by the Password encryption feature in Bizagi's work portal.
|
Alert: This same login should be used too for the configuration of the Scheduler service for that same project's environment. |
Editing the connection string used in the Scheduler service
The configuration that defines the data access connection to the database from the scheduler service, is specified in the "Bizagi.Scheduler.Services.exe.config" file of the "scheduler" folder of the given project environment (by default at ""C:\Bizagi\EDITION\Projects\PROJECT_NAME\Scheduler").
The "Bizagi.Scheduler.Services.exe.config" file is XML-formatted and can be edited as described below:
Locate the "DSNDB" element and edit its information:
<add key="DSNDB" value="Current Language=us_english;Initial Catalog=PurchaseRequest;Data Source=JUANCAMILO-PC\SQLEXPRESS;User ID=Bizagiuser;Password=testpassword;" />
In this example, user Bizagiuser and password testpassword was used for the connection.
Notice that in the password definition, you may input the encrypted string text as returned by the Password encryption feature in Bizagi's work portal.
|
Alert: This same login corresponds to that one used for that same project environment's work portal configuration. |
Related Articles
<comments/>
