Using SQL Server Express as the Cognos 8 Content Store

June 19, 2008 · Filed Under BI, Cognos, Databases, Microsoft, SQL Server

Microsoft SQL Server is fully supported by Cognos 8 Business Intelligence Server, but the Express Edition is not officially supported for use as the Cognos 8 content store, as per the support website. If you try anyway with a default install of SQL Server Express, you will see an error like the following in the cogserver.log file when the Cognos service is started:

192.168.0.103:9300 5496 2008-06-19 15:16:24.390 -5 Initialization_SESS Initialization_REQ Thread-16 CM 888 1 Audit.cms.CM StartService Warning CM-CFG-5063 A Content Manager configuration error was detected while connecting to the content store. CM-SYS-5003 Content Manager is unable to access the content store. Verify your database connection parameters, and then contact your database administrator for assistance. Failed Logon:com.jnetdirect.jsql.JSQLException: TCP/IP connection failed to host:localhost java.net.Con nectException: Connection refused: connect url:jdbc:JSQLConnect://localhost:1433/cognos83

This is a generic error that states that the Cognos 8 application cannot connect to the content store database. However, with a few configuration tweaks, this can be resolved. By default, the SQL Server Express networking protocols are disabled, which means that it will not accept TCP/IP connections. This is why Cognos cannot make the connection and throws the error. To resolve the problem, we need to do two things with SQL Server Express: first, enable TCP/IP, and then second, specify a port number to listen on. Here are the steps:

Launch the SQL Server Configuration Manager.
In the left hand window tree view, expand SQL Server 2005 Network Configuration.
Select “Protocols for SQLEXPRESS”.
In the right hand window, right click on “TCP/IP” in the list of protocols and choose “Enable”.
Right click on TCP/IP again and select properties.
Click on the tab labelled “IP Addresses”
Scroll down to the bottom to view the section titled “IPALL”
Clear the value for “TCP Dynamic Ports” so that it is blank.
Enter TcpPort number to use when making remote connections, for example, “1433″.

Restart the SQL Server service. Now, when you start Cognos 8, the connection will be made and the content store tables will be created in the database. You can verify this by watching for xml files being created in the logs directory (with names like 001.CMCreateWSRPObjects_results.xml), or by refreshing the tables folder in the SQL Server. I saw 116 tables created, but I know that can vary, according to the version of Cognos 8.