This chapter illustrates by example how you use Microsoft SQL Server Management Studio to set up an SQL server database for a PPM demo system and create a database user for a PPM client server.
If you want to set up an SQL server database for a productive system, please contact your SQL server system administrator. Detailed information on Microsoft SQL Server Management Studio is available in the relevant product documentation.
The example refers to SQL server version 2012 and Microsoft SQL Server Management Studio 11.0.
Create a database
Start Microsoft SQL Server Management Studio and log in with an administrative user, for example by using a Windows system administrator access.
In the pop-up menu of the node Databases in the tree displayed in Object Explorer, select New Database.
General page
Specify a name (e.g., PPM) for the new database.
Specify the settings for the database files. To operate the PPM demo databases, a 1 GB container is sufficient.
Options page
Check the Collation setting. We recommend that you use the value Latin1_General_BIN. UTF-8 character sets are not supported for MS SQL Server databases.
Check the Recovery Model setting. For a demo database, the simple option is sufficient.
Click OK to exit the window and create a new database.
Create a login
After having created the database, you need to create a login for it. To do so, select the entry New Login in the pop-up menu of the Security/Logins node in Object Explorer.
In the subsequent window, select General under Select a page, then enable the SQL Server authentication option, and enter the name of the database user, e.g., PPMDEMO (in uppercase letters) as well as the password. Assign the user the relevant default database, e.g., the database you just created, PPM.
You will need the assigned password later when creating a PPM client. Disable the Enforce password policy option if you want to specify any password.
Now, select the User Mapping page in order to generate the default schema automatically upon the first login of the newly created user. In the Map column, select the database you want to map a schema to for this login name. The User column then shows the login name you just created and the field in the Default schema column becomes editable. Specify the required name in this column. We recommend that you use the login name as the default schema name, e.g., PPMDEMO.
The default schema name must be in upper-case letters, e.g., PPMDEMO.
Entering the name of the default schema and clicking OK creates this schema.
Create DB user
Open the required database,e.g., the database PPM you just created, and select the entry New User under the node Security/Login node in the Object Explorer configuration tree.
On the General page, enter the DB user name and the login name you just created. The DB user name is recommended to be the same as the login name. In the Default schema field, enter the name of the schema that you had assigned to the user when creating the login name.
Adjust DB properties
To be able to use the database schema with the user for PPM, you need to specify certain permissions for the new user and schema. In the Microsoft SQL Server Management Studio Object Explorer, select the schema node of the created database, e.g., the node /Databases/PPM/Security/Schemas/PPMDEMO of the previously created PPM database. Select Properties from the pop-up menu.
In the Properties window, select the Permissions node and click View Database Permissions to open the window Database Properties. The Permissions node is already selected there. Select the newly created user, e.g., PPMDEMO and assign it the permissions Create Schema and Create table by enabling the check boxes in the Assign.
Check database connection
Start PPM Customizing Toolkit and create a new client. In the Database settings dialog, enter the data for the new SQL server database and click Test database connection.
The test result is displayed in a separate window.
If the connection fails, you can click the Info button to display detailed information.
Before starting PPM Customizing Toolkit, make sure that the current driver for the SQL server database is located in the correct directory <installation directory>\ppmmashzone\server\bin\work\data_ppm\drivers.