Suggested settings for creating the content store in Microsoft SQL Server

The database you create for the content store must contain the specified configuration settings.

To ensure a successful installation, use the following guidelines when creating the content store. Use the same guidelines to create a database for log messages.

Use the following checklist to help you set up the content store on Microsoft SQL Server.

  • Ensure that the collation sequence is case-insensitive.

    In a Custom installation, you choose a collation, which includes character sets and sort order, during the Microsoft SQL Server setup. In a Typical installation, the installation uses the locale identified by the installation program for the collation. This setting cannot be changed later.

  • When connecting to Microsoft SQL Server Management Studio to create the database, use Microsoft SQL Server authentication.

    If you connect using Microsoft Windows operating system authentication, the database that you create will also use Windows authentication. In this situation, you must configure the database connection using a database type of SQL Server database (Windows Authentication) in IBM® Cognos® Configuration.

  • For the user account that will be used to access the database, create a new login under Security and use the following settings:
    • Select SQL Server authentication.
    • Clear the Enforce password policy check box.
    Tip: If you want to host more than one content store on your Microsoft SQL Server instance and you will use both at the same time, use a different user account for each content store to ensure that each IBM Cognos Analytics instance is fully isolated from the others.
  • For Microsoft SQL Server, grant EXECUTE permission to the user account that accesses the database.
  • For the content store database, create a new database under Databases.
  • Under Security for the new database, create a new schema and assign a name to it.
  • Under Security for the new database, create a new user with the following settings:
    • For Login name, specify the new login that you created for the user account.
    • For Default schema, specify the new schema.
    • For Owned Schemas, select the new schema.
    • For Role Members, select db_datareader, db_datawriter, and db_ddladmin.