Content Manager is unable to access the content store. Verify your database conn

[haripali]

Ennironment:
SQL Server 2000, IIS, Win XP SP2, cognos 8.3

i installed &configured C8.3 & everthing was working well for some days
now i am not able to access cognos web page ,
error message is
" The Cognos gateway is unable to connect to the Cognos BI server. The server may be unavailable or the gateway may not be correctly configured. 
Try again or contact your administrator. "

when i check cognos configuration & right click content store & select test it is giving this error:

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

earlier this test was succesful & all entries at content store is same as was earlier.

please guide me
haripali@gmail.com

[dlafrance]

maybe your login expired or the password changed ?

[haripali]

password is same as was set at SQL Server 2000 installation time i.e. sa/sa there is no other password & even later password has not been changed

checking with installation & adm. guide ...suggested to delete content store at cognos configuration level & recreate it again...
done that but still same problem is coming

haripali@gmail.com

[haripali]

checking with installation & adm. guide ...suggested to delete content store at cognos configuration level & recreate it again...
done that but still same problem is coming

Please please somebody guide me how to resolve this issue
expert help me to resolve

haripali@gmail.com

[smiley]

sql server 2000 needs to be patched to SP3 minimum for cognos.

[haripali]

hi

FYI I got Sql Server 2000 developer edition

i went to MS site i saw even higher patch SP4 available for this SQL.

Should i appply this higher patch SP4 ?
will this resolve this

reply asap
haripali@gmail.com

[smiley]

That is what the minimum stands for.  ;)

[haripali]

it took some time but I have downloaded & applied SQL Server2000 SP4 also.

Still problem is same

experts guide me

[smiley]

On the cognos server, go to administrative tools, and then ODBC.
Create a system DSN to you content store DB, using your sql userid.
Can you test that connection succesfully?

[haripali]

smiley i appreciate your effort to write back

since i am not able to open/access cognos connection web page (as i already mentioned in my first post) , I m not able to access Administration tool (which i normally acces through cognos connection web page)

FYI:
1) this is a single PC system
2) I uninstalled & reinstalled COGNOS again but problem is still same
3) SQL Server 2000 was suucessfully updated to SP4 as i check the version number in Query Analyzer

smiley is there any other way

[Ganesh Kedari]

One Quick check .. Is your SQL Browser Service running ? If not enable it for TCP/IP

[smiley]

Administrative tools is part of your OS, not Cognos.

[haripali]

simley since i was so deep in cognos ...if somebody says "administation" for me its cognos

administration. I forgot i also use OS  administation for other purposes like to work in IIS. forgive for my honest mistake.

Ya i did odbc test ...steps are below

from my OS i.e. WIN XP SP2

START\SETTING\CONTOL PANEL\ADMINISTRATION\DATASOURCE(ODBC)\SYSTEM DSN TAB

Click ADD button , from list select SQL SERVER
Name:cog83 (name i anm giving for my odbc connection)
description:connect to cognos83 i.e. content store for cognos8.3
Server:local (SINCE SINGLE PC SYSTEM)
Click NEXT

"How should Sql Server verify the authenticity of the login ID?" (choose 1 out of following 2 radio button)
1. Wiht Window Nt authentication using network login ID
2. With Sql Server authentication using a login ID & password entered by the user

I click the second button & following checkbox was enabled
"Connect to Sql Serve to obtain default settings for the additional configuration options: "

then i entered
login id : sa
password :sa

Click NEXT

In next screen I enable check box for "change the default database to: "
& from list i selected "cognos83" (this is my content store created in Sql Server)

Click NEXT & then  ClIck FINISH & then click TEST DATA SOURCE

test was successful with following message


"Microsoft SQL Server ODBC Driver Version 03.85.1117

Running connectivity tests...

Attempting connection
Connection established
Verifying option settings
Disconnecting from server

TESTS COMPLETED SUCCESSFULLY! "

Click ok three times.

I hope this was what you asked me to do ?

If yes does this proves that content store cognos83 at sqlserver is up & running fine & at sql server end  things are well?

Now where I should look ...

guide me so that I can resolve this issue ...& make cognos up & running

haripali@gmail.com

[haripali]

Hi smacorp

One Quick check .. Is your SQL Browser Service running ? If not enable it for TCP/IP

wher to find this & do this enabling part...

In my Sql Server 2000...ther are

Server  network utility &
Client network utility

but SQL Browser Service was not ther in either of them ...
where it is located...

guide me

[haripali]

Hi smiley ...please see my last mail regarding ODBC that was in response to your mail.

I am waiting your comment on this ...ihave listed the steps ...i hope this is what u were asking me to do.

wating for ur reply.

experts any other advice..its seems as if I m only one eho has facedthis issue...guys need ur help on this

[smiley]

Did you install your sql server instance in the default one, running on 1433?

[haripali]

yes simley i installed SQl Server 2000 with default instance thats for sure.
& since think went well, I assume  port number would be 1433.

Is there way to check whether port number 1433 is being used by SQl Server 2000?

guide me

[SomeClown]

netstat   command might do it

[dlafrance]

is the user "sa" the default schema of the database ? if it's dbo, try to change it to sa.

[haripali]

"sa" is the default being used by sqlserver...its has admin. right also.


i tried netstat it gives these columns

C:\>netstat

Active Connections

  Proto  Local Address          Foreign Address        State
  TCP    office-815bdb32:1077   172.31.6.4:8443        CLOSE_WAIT
  TCP    office-815bdb32:1079   172.31.6.4:8443        CLOSE_WAIT
  TCP    office-815bdb32:1354   cds183.sjc9.msecn.net:http  ESTABLISHED
  TCP    office-815bdb32:1413   cds183.sjc9.msecn.net:http  CLOSE_WAIT
  TCP    office-815bdb32:1488   im-in-f85.google.com:https  TIME_WAIT
  TCP    office-815bdb32:1528   72.14.213.85:http      ESTABLISHED
  TCP    office-815bdb32:1535   74.125.127.85:http     CLOSE_WAIT
  TCP    office-815bdb32:1552   74.125.127.86:http     ESTABLISHED
  TCP    office-815bdb32:1555   im-in-f100.google.com:http  TIME_WAIT
  TCP    office-815bdb32:1558   im-in-f100.google.com:http  ESTABLISHED
  TCP    office-815bdb32:1562   im-in-f100.google.com:https  TIME_WAIT
  TCP    office-815bdb32:1581   74.125.127.85:http     CLOSE_WAIT
  TCP    office-815bdb32:1583   im-in-f154.google.com:http  ESTABLISHED
  TCP    office-815bdb32:1586   74.125.127.85:http     ESTABLISHED
  TCP    office-815bdb32:1587   ip-208-109-232-196.ip.secureserver.net:http  TIM
E_WAIT
  TCP    office-815bdb32:1589   img150.imageshack.us:http  TIME_WAIT
  TCP    office-815bdb32:1592   im-in-f118.google.com:http  ESTABLISHED
  TCP    office-815bdb32:1613   209.85.153.86:http     ESTABLISHED
  TCP    office-815bdb32:1615   im-in-f85.google.com:http  ESTABLISHED
  TCP    office-815bdb32:1616   209.85.153.86:http     ESTABLISHED
  TCP    office-815bdb32:1617   im-in-f85.google.com:http  ESTABLISHED
  TCP    office-815bdb32:1618   72.14.203.138:http     ESTABLISHED
  TCP    office-815bdb32:1619   im-in-f100.google.com:http  ESTABLISHED
  TCP    office-815bdb32:1620   72.14.203.138:http     ESTABLISHED

this is my system name "office-815bdb32" & if "1077" & rest like this are port number then nowhere is port1433 or SQL Server or something that
could tell sql server is using this port1433.

can we modify netstar command with some option that can tell which port is being used by which service ...

or anyother command that can tell which port is being used by which service

because in that case we can figure  out which port is being used by Sql Server...it might lead to somewhere...i hope.

please guide me :(

[dlafrance]

"sa" is the default being used by sqlserver...its has admin. right also.

I don't think you understanded me. I am not talking about the user SA, but the default schema of the database itself.

If you look also in SQL Server Configuration Manager or SQL Server Area Configuration, you should have an option to allow tcp connexion, as proposed by someone else

[haripali]

ya sql server schema is default one

[haripali]

Hi all ....I HAVE RESOLVE THIS ISSUE & SYSTEM IS RUNNING FINE.

IF IN FUTURE SOMEBODY FACE THIS PROBLEM PLEASE READ THIS HOW I HAVE DONE THIS & SAVE YOUR TIME & EFFORT.

Here it goes

FYI : SQL SERVER 2000 USES 1433 PORT BY DEFAULT & SAME IS GIVEN IN CONTENT STORE ENTRIES IN COGNOS CONFIGURATION

If thing  went wrong then First thing to check is the cognos configuration entries.If something went wrong like content store is not able to connect to sql server 2000 DB then check the entries here first.

Second check at sql server & content store are up & running & u are able to access it at DB level. like

i did it by using Query Analyzer (comes with  Sql Server2000). FOr Oracle u can use Toad.
To Further chech the same thing u can even try to connect by creating ODBC/DSN connection pointing to content store & test it.
In my case all these test were successful.

so it appears to me that at sql server2000 end things are up & running. Also at cognos end entries are checked & correct so somewhere in middle cognos is not able to connect at sql server.

Now this connection part we define in content store entries only...so i have to check following part
type : Microsoft Sql Server Database
Database server with portnumber & instance name:localhost:1433
user id & password : sa
database name : cognos83 

Since again & again testing giving the same problem so i zero down to port number.

Here i have to check wheather port number 1433 is being usedby sqlserver or not.

How to do this every OS have some way to do this either throught some command or through third party tools.
In my case OS WinXpSP2 has command prompt,i used netstat -ao it gives following column

Proto    Local Address          Foreign Address        State                PID
TCP    office-815bdb32:smtp   office-815bdb32:0      LISTENING     668
  TCP    office-815bdb32:http   office-815bdb32:0      LISTENING   1668
  TCP    office-815bdb32:epmap  office-815bdb32:0      LISTENING  1048
  TCP    office-815bdb32:https  office-815bdb32:0      LISTENING   1668
  TCP    office-815bdb32:microsoft-ds  office-815bdb32:0 LISTENING          4
  TCP    office-815bdb32:1035   office-815bdb32:0      LISTENING  1668
  TCP    office-815bdb32:1052   office-815bdb32:0      LISTENING   2104
  TCP    office-815bdb32:6646   office-815bdb32:0      LISTENING   1972
  TCP    office-815bdb32:netbios-ssn  office-815bdb32:0      LISTENING       4
  TCP    office-815bdb32:1354   cds183.sjc9.msecn.net:http  ESTABLISHED     1380

herE port number are in second column. Now each service or programme running on ur computer is given a PID & same is shown above in last column. But name of  service or programme is not there.

now my job was to find sqlservice PID & find its port number using above table.

For this in my OS WinXP SP2, i used "window task manager " by clicking contrl+alt+del. Click process tab,click "view" menu button,click "select column" , click check box for PID, click ok .
now see under in "image name " look for "sqlservr.exe" entry, corresponding PID is 2104.

Now 2104 in above table correspond to 1052 port number. Now this shows that sql server(PID2104) is using port number 1052 & not 1433 as again its default 1433.

So in  Cognos Configuration\data access\content manager\content store entries i changed the port
type : Microsoft Sql Server Database
Database server with portnumber & instance name:localhost:1052
user id & password : sa
database name : cognos83   

now right click content store\test ,it passed the test i.e. cognos is able to connect to content store created at sql server2000.

Now when i open cognos web page it is working properly.

So the problem was due to port conflict. I redirected cognos to port 1052 being used by sql server & it resolve the issue.


But...ya...something more ....when i try to create datasource its test fails & message comes

QE-DEF-0285 The logon failed.
RQP-DEF-0068
UDA-SQL-0031 Unable to access the "testDataSourceConnection" database

Accordign to documents "The solution is to ensure that MDAC version 2.71 or higher is installed. The registry of the local system should hold the MDAC version information. "
this was checked & version was 2.8 so something else is needed.


To check Sql Server is using TCP/IP, i did using ODBC connection & find that Network library was configured to TCP/IP. so now what to check... again port number was getting my attention.


in SQL Server, in both client Network Utility & Server Network Utility, in TCP/IP, click property button, type in default port : 1433 click ok & restarted the system.

Now change port number to 1433 in cognos configuration\content store, right click,test successfull.
click green button & click save .

Now creating datasource is successfull.

now whole system is up.


It took me lot of effort,time & positive will to resolve this.
I want to thanks all guys who have replied, i really appreciate your time & effort towards this.

now i can breath & can go to sleep.
Bingo...hurrray....:) :) :)

Note:I will monitor the system for couple of days to make sure everything is right, if something comes up i will post here , otherwise consider it good

[dlafrance]

I am very glad you found the solution.

Thank you for your detailed solution, it will certainly help someone else ;)

[prath]

thank you haripali, your explanation helped me today, only difference is, i am on windows 7 and installing sql server 2005. Thanks for your patience in writing ur resolution!!