Wiki

Options

New Case Case Status
Log In

Wiki

Options

 
Knowledge Base Articles»KB000031 - EtaPRO and Database…

KB000031 - EtaPRO and Database Security Mode Options

The steps outlined in this article assume the reader has an understanding of EtaPRO user roles and a working knowledge of SQL and SQL Server Management Studio. EtaPRO user role descriptions can be found in the EtaPRO Help Files. Many tutorials may be found online to guide the reader through use of SQL Server Management Studio.

This article replaces the previously published KB000031 and KB000037.

The EtaPRO Performance and Condition Monitoring System features two security modes: EtaPRO (occasionally referred to as "Forms") and Windows. Likewise, SQL Server features two security modes: Windows and SQL Server Authentication.

When combined, the following combinations are possible:

EtaPRO and SQL Security Authentication Choices

 

EtaPRO (Windows)

EtaPRO (EtaPRO)


  SQL (Windows)  


EtaPRO and SQL (Windows)


  EtaPRO (EtaPRO) and SQL (Windows)  


SQL (SQL)


  EtaPRO (Windows) and SQL (SQL)  


EtaPRO (EtaPRO) and SQL (SQL)

 

Only EtaPRO Security Administrators can edit the EtaPRO Security Mode using the Configuration Tools | User Management form:

EtaPRO System Administrators can edit the EtaPRO Database Security Mode using the EtaPRO Service Manager | Configuration Settings | Databases form:

We will now illustrate examples for each security mode combination.

EtaPRO (EtaPRO) and SQL (SQL Authentication)

 

The default EtaPRO Security Mode (the method used when the application is installed and configured) is EtaPRO. For this mode, individual usernames and passwords are defined for each person requiring access to EtaPRO. Instructions to add/edit/remove users can be found in the EtaPRO Help Files.

The default Database Security Mode is SQL Server.

For this mode, an explicit SQL Server Username and Password are used by EtaPRO to connect to SQL Server.

NOTE: New EtaPRO systems not using APR or EPArchive are installed with an optional instance of SQL Server Express. The default SQL Server Username for this installation is "sa" - which is also the "system administrator" account for SQL Server. EtaPRO Support recommends the creation of a new SQL account to be only used by EtaPRO - especially if the "sa" account is to be used to manage other databases on SQL. If EtaPRO uses the "sa" account to communicate with SQL, and if the password for "sa" is changed at any time, or if the account becomes locked out, EtaPRO's communication to SQL will fail.

Specific database permissions must be granted to the SQL Server account used by the EtaPRO services. These permissions can be granted using Microsoft SQL Server Management Studio.

Server Roles – the EtaPRO service account must be granted the following roles:

·       public

NOTE: If the EtaPRO service account will also be used to administer SQL server and/or to allow creation or cloning of EtaPRO databases, then the account should also be granted “sysadmin” and “dbcreator” roles.

User Mapping –The EtaPRO service account should be granted the following Database role memberships for Asset and Archive databases:

·       db ddladmin

·       db owner

·       public

The EtaPRO service account should be granted the following Database role memberships for all other EtaPRO databases (FIGURE 5):

·       db owner

·       public

If database upgrades are to be performed manually instead of automatically (such as when a remote SQL Server is used with EtaPRO), then the following reduced permission mappings may be used:

·       public

·       db_datawriter

·       db_datareader

In addition, the security settings should be updated to allow the user to execute stored procedures.

File System Permissions – In order to create new databases, clone databases, and attach/detach databases, the SQL account used by EtaPRO must have full file system permissions to the directories where database files are placed. Alternately, SQL Server service can be run on a higher privilege account with access to the file system.

When cloning, creating a new, or manually upgrading a database, a SQL login prompt will appear. An account with database creation (db_creator) permissions must be entered at the prompt.

 

EtaPRO and SQL (Windows Authentication)

 

See the procedure at the end of this document (Windows Security Mode with Local Computer or Active Directory Groups) if use of Active Directory Groups in EtaPRO is desired.

To use the "Windows" EtaPRO Security Mode option, the EtaPRO Server must be able to communicate with the domain server to verify/authenticate users. If EtaPRO cannot communicate with the domain server, or if an incorrect domain/computer name is entered, the following message will appear:

NOTE: If the EtaPRO Server resides on a different domain from that which the "business" users log into, the two domains must be "Federated" (https://docs.microsoft.com/en-us/azure/architecture/patterns/federated-identity). If the two domains cannot communicate with each other, then Windows Security Mode cannot be used for EtaPRO.
 

Individual Windows user accounts can be assigned to various EtaPRO user roles:

When the "Use Transfer Service Credentials for Database Windows Authentication" option is checked (Miscellaneous tab), individual user/group to database permission settings are not required.

If the "Use Transfer Service Credentials..." box is to remain unchecked/disabled, individual account/group Microsoft SQL Server access rights and database settings can be adjusted using Microsoft SQL Server Management Studio.

  1. In this example, we have SQL Logins for NT AUTHORITY\System, and four previously created Local Groups. NT AUTHORITY\System is the EtaPRO services “Log On As” account.


     
  2. We right-click on each group, beginning with etaproadmins to assign Login Properties. In the Server Roles category, we configure the etaproadmins account to have Server roles public and dbcreator.


     
  3. After the Server Roles are defined for each EtaPRO group, we select User Mapping and map NT AUTHORITY\System to each EtaPRO database, also assigning the user db_owner role:


     
  4. Security permissions are added for the Windows folder in which EtaPRO databases are stored:


     
  5. Finally, Database Security Mode is changed to Windows. Beginning with EtaPRO 10.1, Database Security Mode can be configured on the Site tab and the same database server and credentials can be used for all tabs:

NOTE: When installed by GP Strategies, SQL Server "Mixed Mode Authentication" (allowing both Windows and SQL Server Authentication) is enabled.

EtaPRO (EtaPRO) and SQL (Windows Authentication)

 

For this combination, follow the SQL (Windows Authentication) instructions as defined above.

EtaPRO (EtaPRO Authentication) instructions are found in the EtaPRO Help Files.

EtaPRO (Windows) and SQL (SQL Server Authentication)

 

For this combination, follow the separate EtaPRO (Windows) and SQL (SQL Server Authentication) instructions as defined above.

 

 

EtaPRO/VirtualPlant Services


Log On As may use Local System, unless a specific account is needed to connect to a Wonderware, OPC, or other historian.

SQL Database Permissions: The EtaPRO services "Log On As" account should have db_owner role membership (https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles) as defined above in EtaPRO and SQL (Windows Authentication).

 

Using Windows Security Mode with Local Computer or Active Directory Groups

 

The use of Windows groups is recommended as access can be controlled external to EtaPRO, however individual Windows account names can be used. Local computer groups and secondary domain groups can also be created, provided that the adequate access levels are set.

NOTE: If the EtaPRO Server resides on a different domain from that which the "business" users log into, the two domains must be "Federated" (https://docs.microsoft.com/en-us/azure/architecture/patterns/federated-identity). If the two domains cannot communicate with each other, then Windows Security Mode cannot be used for EtaPRO.

1. In this example, we have created four Local Groups corresponding to the top four levels of access in EtaPRO: Administrator, Analyst, User, and View.

2. Users are then assigned to each Local Group:

3. Finally, EtaPRO Security Mode is switched to Windows and access levels are assigned in EtaPRO:

 

Enabling CLR for SQL Server (EtaPRO 10.2 and earlier)

 

EtaPRO 10.3 does not use SQLCLR (SQL Common Language Runtime). Therefore it is not necessary to enable it.

For EtaPRO 10.2 and earlier, when the Asset or Archive features are used, SQLCLR must be enabled.

Execute the following script to enable CLR for all databases:

  1. Select Databases from the Object Explorer
  2. Click the New Query button
  3. Copy and paste the following code to the query tab
     

    sp_configure 'show advanced options', 1;

    GO

    RECONFIGURE;

    GO

    sp_configure 'clr enabled', 1;

    GO

    RECONFIGURE;

    GO



     
  4. Select master from the database drop down menu
  5. Click the Execute button

 

 

 

General details regarding EtaPRO security modes can be found in the EtaPRO Help Files. For additional assistance with this procedure, or to report an error, please contact EtaPRO Support at etaprosupport@gpstrategies.com or 716-799-1077.