Macroscope ProcessUnifier  >  Knowledge Base
 Printable Version 
kb8022101 - Converting CHAR to DATETIME type error when importing
kb8052301 - Using SQL Server Authentication mode with Microsoft SQL Server 2005
   
Support
  
kb8052301 - Using SQL Server Authentication mode with Microsoft SQL Server 2005
  Description  |  Automation scripts

Summary

Some enterprise requires their users to communicate using the SQL Server Authentication mode. The Macroscope ProcessUnifier allows this type of connection but requires some additional configuration to be applied to the SQL Server and its database.

Application

First of all, users will need to install the latest ProcessUnifier Quickpatch, which resolves some situations that can be encountered otherwise.
Administrators will next have to manually apply the security settings to the server to complete the configuration sequence.
Note1: To apply these settings, you must be a sysadmin of the SQL Server.  If you are unsure or your familiarity level is not adequate, please contact your administrator since modifying the SQL security settings could cause access difficulty when done improperly.
Note2: All of the following operation were done using the "Microsoft SQL Server Management Studio Express". The tool can be downloaded from the Microsoft web site following this link http://www.microsoft.com/downloads/details.aspx?FamilyID=c243a5ae-4bd1-4e3d-94b8-5a0f62bf7796.

The following steps will all be performed using the Microsoft SQL Server Management Studio Express tool. Connect to your SQL Server instance to continue.

1. Configuring SQL Server to allow SQL Server Authentication

In the tree view Object Explorer, right-click on you server name and select Properties.
In the "Select a page" section, make sure "Security" is selected.
Click on "SQL Server and Windows Authentication mode".
Click OK.

2. Creation of the MPU_Admins role

In the tree view Object Explorer, locate the folder [servername]\Databases\[ProcessUnifier database]\Security\Roles\Database Roles.
Right-click on "Database Roles" and select "New Database Role".
Make sure the "General" page is selected.
As a database Role name, specify "mpu_admins".
In the "Owned Schemas" list box, select the following two items:
  • db_accessadmin
  • db_owner

3. Creation of the MPU_Users role

In the tree view Object Explorer, locate the folder [servername]\Databases\[ProcessUnifier database]\Security\Roles\Database Roles.
Right-click on "Database Roles" and select "New Database Role".
Make sure the "General" page is selected.
As a database Role name, specify "mpu_users".
In the "Owned Schemas" list box, select the following two items:
  • db_datareader
  • db_datawriter
  • public

4. Configuration the database access of the two roles

In the tree view Object Explorer, locate the folder [servername]\Databases\[ProcessUnifier database].
Right click on the database name and select "Properties".
Make sure the "Permissions" page is selected.
Click the "Add" button.
In the text box, type "mpu_admins; mpu_users" and click the "Check Names" button.
Click the "OK" button.

MPU_Admins

In the "Users or roles" list box, select "mpu_admins".
In the "Explicit permissions for mpu_admins" list box, make sure the following rows a check in the grant column.  There should not be any other boxes checked.
  • Alter
  • Control
  • Create function
  • Create table
  • Create view
  • Delete
  • Execute
  • Insert
  • Select
  • Update
  • Take ownership
  • View database state
  • View definition

MPU_Users

In the "Users or roles" list box, select "mpu_users".
In the "Explicit permissions for mpu_users" list box, make sure the following rows a check in the grant column.  There should not be any other checked boxes.
  • Create table
  • Delete
  • Execute
  • Insert
  • Select
  • Update
  • View database state
  • View definition

Click "OK" when your are finish selecting permission.

5. Configuring the database schema

In the tree view Object Explorer, locate the folder [servername]\Databases\[ProcessUnifier database]\Security\Schemas\dbo.
Right click on the "dbo" schema and select "Properties".
Make sure the "Permissions" page is selected.
Click the "Add" button.
In the text box, type "mpu_admins; mpu_users" and click the "Check Names" button.
Click the "OK" button.

MPU_Admins

In the "Users or roles" list box, select "mpu_admins".
In the "Explicit permissions for mpu_admins" list box, make sure the following rows a check in the grant column.  There should not be any other checked boxes.
  • Alter
  • Control
  • Delete
  • Execute
  • Insert
  • Select
  • Update

MPU_Users

In the "Users or roles" list box, select "mpu_users".
In the "Explicit permissions for mpu_users" list box, make sure the following rows a check in the grant column.  There should not be any other checked boxes.
  • Alter
  • Delete
  • Execute
  • Insert
  • Select
  • Update

Click "OK" when your are finish selecting permission.

6. Creating users profiles

For the actual ProcessUnifier release, each username that is created in SQL Server also needs to be created in the ProcessUnifier database using the application.  Also the usernames needs to be identical so they are properly identified by the application.
The ProcessUnifier recognizes three types of users. The following list specifies which role each user type needs to work properly:
ProcessUnifier user type
Sql role
- Administrator
mpu_admin
- Domain Expert
mpu_user
- Content Editor
mpu_user

In the tree view Object Explorer, locate the folder [servername]\Security\Logins.
Right-click on "Logins" and select "New Login"

Administrators

Select the "General" page.
Type in your user's name in the "Login name" text box.
Select the "SQL Server authentication".
Type in your user's password in the "Password" and "Confirm password" boxes.
Select the "Server Roles" page.
Check the "dbcreator" box.
Select the "User Mapping" page.
In the "Users mapped to this login" list box, select the "master" database.
Check the "map" box.
In the "Database role membership for: master" list box, make sure only the following is checked:
  • public
In the "Users mapped to this login" list box, select the "[ProcessUnfier database name]" database.
Check the "map" box.
In the "Database role membership for: master" list box, make sure only the following is checked:
  • MPU_Admins
  • public
Select the "Status" page.
Make sure "Permission to connect to database engine" specifies "Grant".
Make sure "Login" specifies "Enabled"
Click "OK".

Domain Experts or Content Editors

Select the "General" page.
Type in your user's name in the "Login name" text box.
Select the "SQL Server authentication".
Type in your user's password in the "Password" and "Confirm password" boxes.
Select the "Server Roles" page.
Check the "dbcreator" box.
Select the "User Mapping" page.
In the "Users mapped to this login" list box, select the "master" database.
Check the "map" box.
In the "Database role membership for: master" list box, make sure only the following is checked:
  • public
In the "Users mapped to this login" list box, select the "[ProcessUnfier database name]" database.
Check the "map" box.
In the "Database role membership for: master" list box, make sure only the following is checked:
  • MPU_Users
  • public
Select the "Status" page.
Make sure "Permission to connect to database engine" specifies "Grant".
Make sure "Login" specifies "Enabled"
Click "OK".

Applies To

Versions prior to Macroscope ProcessUnifier 20.10.47.9303.

Contact Us

Should you need more information about this Knowledge Base document, do not hesitate to contact us.