Using SQL Server Express as the Cognos 8 Content Store
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.
Caveat emptor: Keep in mind that SQL Server Express is not supported by IBM Cognos for use as the content store database.
Thanks to the SQL Server Express Weblog for the pointers that helped me get this working.
7 Steps for Troubleshooting BI Software
Earlier today I published the final version of this white paper: 7 Steps for Troubleshooting BI Software. Have a look, and if you find it helpful, please say thanks by sending me a note or by leaving a comment.
Cognos Business Intelligence Technical Articles
I have started posting some technical articles on my company website. My focus is on Cognos 8 Business Intelligence, as I am currently interested in that type of work. Any feedback would be appreciated, either through the contact page or by leaving a comment. If you work with Cognos Business Intelligence, let me know if there is anything that you would like to see as I am open to suggestions for future topics.