Creating ServiceCatalog Database and Login User
For a new installation, you can prepare the ServiceCatalog database and login user as described in this section before executing the Service Catalog installer, or you can let the Service Catalog installer create the database and login user for you by selecting the "Create Database" option presented by the installation wizard. The "Create Database" option of the Service Catalog installer is described in more detail in the “Installing Prime Service Catalog” section.
To create the ServiceCatalog database and login user:
Step 1 Create a database named ServiceCatalog in the SQL Server, with the following settings for the data file
- Initial size = 500 MB.
- Autogrowth = By 10 percent
Step 2 Set the collating sequence for the ServiceCatalog database to case-insensitive.
Step 3 Put the ServiceCatalog database in SINGLE-USER mode, and execute the following command:
ALTER DATABASE ServiceCatalog SET READ_COMMITTED_SNAPSHOT ON.
Step 4 Put the ServiceCatalog database back in MULTI-USER mode.
Step 5 Create a SQL Server Login named CPSCUser, with the Default Database property set to “ServiceCatalog”.
Note CPSCUser must be a SQL Server login account that authenticates to the SQL Server using SQL Server authentication method, and not Windows authentication method.
Step 6 Ensure that the “Enforce Password Policy” option is unchecked in the Security setting properties for CPSCUser. Furthermore, the password for CPSCUser must contain only alphanumeric characters. For example, enter only letters and numbers for password. Do not enter any special characters like underlines, asterisks, brackets, and so on. Some combinations of these special characters may cause the installer to fail at product installation time with a "Database Connection Test failed" error message.
Step 7 Assign this CPSCUser to be the db_owner of the “ServiceCatalog” database. Verify your setting to ensure that:
- The user name “CPSCUser” in the ServiceCatalog database is mapped to the login name “CPSCUser” in the SQL Server
- The default schema is “dbo”
- The user name “CPSCUser” has the “db_owner” database role membership.
Step 8 There are some special permissions needed for the application to monitor and automatically recover from long-running queries that may affect the performance of the product. If these additional permissions are not granted to "CPSCUser", the product will not fail; but the user may see some error messages in the application server's log file that are similar to the following:
ERROR [com.newscale.bfw.udkernel.udsql.UdSqlBean] (org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-3) SQL Exception while getting open session: java.sql.SQLException: [newscale][SQLServer JDBC Driver][SQLServer]The user does not have permission to perform this action.
Error: Error while validation policiesjava.lang.Exception: Connection SessionId could not be obtained exiting policy check for service item subscription
To grant these special permissions, log in to the SQL Server as the "sa" user, and execute the following commands:
EXEC sp_addsrvrolemember 'CPSCUser', 'sysadmin'
GRANT ALTER ANY CONNECTION TO CPSCUser