Installing Cognos 8.4 on windows 2003 with MSSQL connection

[Megsy]

Hi all,

I'm new to Cognos. I'm trying to install it to try out the IBM Maximo Asset Management System as it kept on failing when I would just try to install it alone. I would get the error of Failed on "Cognos". So after weeks of scouring I found articles that say to install Cognos 8 BI to get it up and running. Now the base install was easy enough. When I go to set up the content store I delete the db2 instance and make a new one for Microsoft SQL(as the documentation I found says to do). when I go to connect to the server I put in the IP, FQDN, and server name in an attempt to get to the sql database I get the same error :

[ ERROR ] The database connection failed.
[ ERROR ] Content Manager is unable to access the content store. Verify your database connection parameters, and then contact your database administrator for assistance.

I have tried installing SQL locally and remotely, SQL authentication and windows authentication, always the same error. In production we would be using a remote SQL server. I'm not sure what to do. it just doesn't seem to want to work. I have other connections to the same SQL database. I checked to make sure TCP connections is on. The database is in the right code format. the name is "cm". The port is 1433. I'm just at a stump. Any help would be greatly appreciated.

[ericlfg]

Hi Megsy,

I have a couple of things to start to troubleshoot this:

1. What version of SQL are you running?
2. Have you attempted to use SQL management studio (SQL 2k5+) or enterprise manager (2k) to attempt to connect to the remote DB server from the machine you have cognos installed on?
3. What kind of SQL database resource did you create in cognos config?  SQL Server, or SQL Server (Windows Authentication)?
4. Are you running the cognos service as a named account or the default local system account?

Cheers

[Megsy]

My Domain is named Tivoli.net, the SQL and Cognos servers are joined to the domain.

1. What version of SQL are you running?
2. Have you attempted to use SQL management studio (SQL 2k5+) or enterprise manager (2k) to attempt to connect to the remote DB server from the machine you have cognos installed on?
3. What kind of SQL database resource did you create in cognos config?  SQL Server, or SQL Server (Windows Authentication)?
4. Are you running the cognos service as a named account or the default local system account?

1. MS SQL 2005 Enterprise (64-Bit).
2. Yes I am able to connect with SQL Management Studio to the Remote database server using IP, FQDN and Windows server name.
3. I have tried both. Currently I have it set up with windows authentication. It's connected to our test domain and I'm logged in as a full domain administrator (Tivoli\Administrator).
4. I haven't even gotten past the configuration page to the point to where it installs the services after the initial configuration.

The SQL database is set up as SQL_Latin1_General_CP1_Cl_AS, the owner of the database is "TIVOLI\administrator", Name of the database is "cm", Status is Normal

[Megsy]

4. Are you running the cognos service as a named account or the default local system account?

So I tried to continue after with out being able to connect to the content store. It made the services but it can't start the service. I tried changing it to a domain administrator account to test and it still doesn't start.
I tried the same thinking maybe I'm having a problem with windows 2003. so I did a fresh install of windows xp and installed that way with windows XP's IIS and then installed cognos. got to the same part and got the same errors.

when I try to start the service I get "The IBM Cognos 8 service on Local Computer started and then stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts service." I got that when I tried to start it as Local System and Domain admin on both 2003 and XP.

Another thing I can think of is that maybe im installing the Cognos the wrong way or the wrong one? I installed the packages from the partner exchange which are  "CZAG1ML" and "CZAJ7EN"

[AussiePete2011]

Hi Megsy,

Could you please provide the following
The exact version of Cognos 8 you are referring to.  If you dont know, then locate the cmplst.txt file found under the ../<C8InstallPath>
E.g. ../Cognos/c84
Cognos 8.4.x and below only support 32 bit connections if you have installed a 32 bit Cognos installation.
Also you haven't mentioned the bit version of the OS.  Is this 32 or 64 bit?
This makes a difference in terms of the connectivity to the database and by association the content store.
Cognos 8.4.x by default contains the jdbc driver to connect to the SQL Server database so it's quite important what bit version of Cognos and the OS has been installed and how the database drivers are listed in the PATH.

The other point to make is how SQL Server was installed.  Is it a single or multi instance install?

Hope this helps
Cheers
Peter

[SomeClown]

SQL Server:   client protocols enabled? (SQL net config or whatever it's called)  - never sure which is needed so I enable both TCP and named pipes  -- this needs to be open on cognos server and on sql server

SQL Server: fresh install for SQL?  If so, remote connections are allowed?  (don't think they're allowed by default)

Accounts have been set up in SQL server with permissions?  ("TIVOLI\administrator  is the service account for Cognos services?  if not, did you create a sql account that has dbo or  reader/writer/ddladmin).  If doing windows auth, it tries to connect as the service account that Cognos runs under.

[Megsy]

Sorry about that. I have 50 things going through my mind I thought I had those in there already.

The exact version of Cognos 8 you are referring to.  If you dont know, then locate the cmplst.txt file found under the ../<C8InstallPath>
E.g. ../Cognos/c84

The installed version is 8.4.1

Also you haven't mentioned the bit version of the OS.  Is this 32 or 64 bit?

I've ben trying with 32 bit windows xp and server 2003.
The SQL server is Windows 2008r2 64bit and the SQL server version is 2005 64 bit enterprise.

SQL Server:   client protocols enabled? (SQL net config or whatever it's called)  - never sure which is needed so I enable both TCP and named pipes  -- this needs to be open on cognos server and on sql server

I've enabled both TCP and Named Pipes

SQL Server: fresh install for SQL?  If so, remote connections are allowed?  (don't think they're allowed by default)

It is a fresh install that I installed just for testing out Tivoli about a month ago. Only 5 other databases in it that are for Tivoli System Monitoring which connects fine through JDBC drivers that it had me download and install.

Accounts have been set up in SQL server with permissions?  ("TIVOLI\administrator  is the service account for Cognos services?  if not, did you create a sql account that has dbo or  reader/writer/ddladmin).  If doing windows auth, it tries to connect as the service account that Cognos runs under.

TIVOLI\Administrator is the service account for the Cognos services. Though it didn't ask me this when I installed it. I had to manually go into services and change IBM Cognos service to log on as that account. I created no other account in SQL for that because I was just using the domain administrator for testing these. I've tried both SQL authentication using mixed mode as thats what the server was installed as. So I put the SA in and that password and I still get the same results.

Im attaching a screen shot of the general pane for the CM database as you can see I haven't changed much and all the other panes are all default for it. The only thing that I was told to change was the Collation to Latin1_general_CP1_CL_AS which I think is the default one anyway from looking at the other databases that are created anyway. So everything seems to be defaults any way.

[SomeClown]

Does look like network/connectivity.

If sql srvr is a different machine, do you still have the tools installed on the cognos server?  You can connect that way?  if they are not there, I'd suggest installing those to verify connectivity

[Megsy]

Does look like network/connectivity.

If sql srvr is a different machine, do you still have the tools installed on the cognos server?  You can connect that way?  if they are not there, I'd suggest installing those to verify connectivity

That's the conclusion I was coming to, but I can connect fine with SQL Server Management Studio.

Also all these machines are Virtual and hosted on the same box. Usually you don't have issues talking between the other virtuals though that's not always the case. I can however ping, log into active directory, rdp to the other virtuals all fine from the cognos virtuals I made.

the only thing I can think of is that I missed something in SQL database creation for this one that's causing all the problems.

[SomeClown]

I would poke around on the sql server config items.  From memory, there's usually two places that I have to go into to make a sql server available to other servers (one is like surface area, the other net config or something).  While it's no help, I have seen this error when doing installs, I just don't remember specifically what I did to fix it.

[Megsy]

I have looked through every place I can and in the two places that lets you turn them on (SQL Surface Area Configuration and SQL Server Configuration Manager ) are on. Other applications are connecting to it fine. I'm stumped.

[AussiePete2011]

Hi there,

Just a heads up!  You need SP4 for SQL Server 2005 to be able to connect to SQL Server 2008

Cheers
Peter

[Megsy]

@AussiePete2011: Thanks I double checked and I was only on SP1, I didn't see anywhere in the documentation for the sp4 requirement for SQL Server 2005, I did see a lot of posts saying it for 2000. I'm downloading it now, Thanks for the heads up. I'll let you know how it works. I'm new to the whole SQL server deal. I wasn't aware there was more service packs. I even updated it with MS' utility and was told there were no new updates.

[Megsy]

Okay, I updated to SQL 2005 sp4, it still wouldn't let me login with windows authentication but I could log in with SQL authentication. Thanks for all the help guys, It's greatly appreciated. I'm sure this won't be the last question I post on here.

[Megsy]

I would like to say thank you again to everyone that helped me with this. I have Cognos up and with Tivoli Common reporting for Asset Management installed. It all seems to be working smoothly so far.