Unable to connect to Content Store in Config - 11.1.3/SQL Server 2016

[tschuman13]

Happy New Year!

I am an experienced installer for Cognos and have been doing it for years for multiple clients.  I'm getting stuck on setting up a basic single-server config during the content store connection.  I am hoping someone might have run into something similar and can think of something I might have missed.

The details
New Windows Server 2016, 2CPU, 16GB RAM
Cognos 11.1.3
SQL Server 2016

Note: Cognos and SQL are running on the same box per my request they be separated.  I have seen this done in the past even though it's a bad choice.

No matter what I do, I get a generic connection error (not the login or driver, or other helpful messages that would point me in the right direction.

[Content Manager database connection]
[ ERROR ] The database connection failed.
[ ERROR ] Content Manager is unable to connect to the content store. Verify that the database connection properties in the configuration tool are correct and that when you test the connection, the test is successful.

Things I have tried.
- Create a brand new DB with nothing in it - same error
- Cognos can connect to the old content store DB on an older version of SQL Server but not the new. 
- I have tried the IP, computer name, and FQDN. 
- I have tested and verified the standard 1433 port is open and available
- I tried to change the JRE and regen keys
- Reinstalled the Cognos software

Ideas:
Is there a setting in SQL Server that needs to be turned on/off? 
The hardware specs are low but I don't think that is the culprit.

Any thoughts or ideas would be greatly appreciated.

Thanks!
Todd

[bus_pass_man]

Open the cognosserver.log file and see if any relevant messages appear. 

The message says to verify the data base connection properties and that when you test the connection, the test is successful. 

Here are some actions involved in that.


1. Verify that you have chosen the msssql server option for the content store.  There's two for mssql IIRC.  One is the windows authentication and the other isn't.  The latter needs a user ID which has been defined in the sql server logins.

The default is DB2.  You will need to delete the default content store entry in the configuration tool and create a new one.

2.  Verify that the data base server name is correct and there's no trailing spaces or typos.   You might need to qualify the name with the domain.  For example, {VM name}.{some thing}.{com usually}
3.  Verify that the port is correct.
4.  Verify that your user ID and password are correct and there's no trailing spaces or typos.
While you're at it, verify that the user has the correct rights to write to the data base.
5.  Verify that the content store data base name is correct and there's no trailing spaces or typos.
6.  Select the content store ( or it's parent) and choose test from the context menu.   It should display a message about generating keys.

If these steps are successful then press the start up button. 

While you're starting the cognos server try to open the details tab when it becomes enabled.   That and the cognosserver.log file should be places where errors will appear.

If you run into problems with generating keys you might need to shut down the configuration tool, delete the cogstartup.xml file, and restart the configuration tool.  It will generate a new, default one from the cogstartup.xsd.   


Hopefully this will do the trick or at least narrow down the problem space.

[tschuman13]

Thanks for the response.

There is no cognosserver.log as the service has never been started on this machine.  I have some other files but nothing contains any useful info.

The test fails with the generic message

[Content Manager database connection]
[ ERROR ] The database connection failed.
[ ERROR ] Content Manager is unable to connect to the content store. Verify that the database connection properties in the configuration tool are correct and that when you test the connection, the test is successful.

1 - I deleted the DB2 and replaces with SQL Server.  I am using SQL auth (i have SQL set up for dual auth Windows/SQL).  I tested the login to SSMS with this login and it works.
2,3,4,5 - The database, port, userid, and server name are correct, no spaces or typos
6 - The test fails with the message above.  Nothing about keys and I have also tried deleting and regen'ing keys

Any other ideas?  I'm messing around with TLS settings but havent gotten that working either.

[oscarca]

Check to see if you have the sqljdbc42.jar file in the Cognos\Analytics\Driver folder

Best regards,
Oscar

[MFGF]

Hi,

Just a quick sanity check. You're not using the Express version of SQL server by any chance, are you?

MF.

[tschuman13]

Hi oscarca - I  do have the SQLjdbc42.jar in the driver folder.

MFGF - I don't believe so.  It says SQL Server 2017 (I had it incorrectly listed as 2016 in my prior post). 

Microsoft SQL Server 2017 (RTM-GDR) (KB4505224) - 14.0.2027.2 (X64)   Jun 15 2019 00:26:19   Copyright (C) 2017 Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)

[MFGF]

Hi oscarca - I  do have the SQLjdbc42.jar in the driver folder.

MFGF - I don't believe so.  It says SQL Server 2017 (I had it incorrectly listed as 2016 in my prior post). 

Microsoft SQL Server 2017 (RTM-GDR) (KB4505224) - 14.0.2027.2 (X64)   Jun 15 2019 00:26:19   Copyright (C) 2017 Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)

Hi,

Ok - you're using SQL Server 2017 Standard Edition, so not the Express edition.

The notes for using this as a content store (from the Supported Environments page) say this:

Available as a Content Stores via JDBC.

The database must be configured to use a Unicode character set.

Express edition is not supported.

Must be run in SQL Server 2012 compatibility mode.

So there are two more things I'd check - the Unicode setting, and that you are using SQL Server 2012 compatibility mode.

Cheers!

MF.

[tschuman13]

Thanks, MFGF.

I just checked and I see:

Collation: SQL_Latin1_General_CP1_CI_AS
Compatibility Level: SQL Server 2012 (110)

I don't see anything for char set = Unicode.  Do you know where that setting is?

[Andrei I]

Things I have tried.
- Create a brand new DB with nothing in it - same error
- Cognos can connect to the old content store DB on an older version of SQL Server but not the new.

Which version of MSSQL can your Cognos Configuration connect?
Is it on the same box as MSSQL 2017?

[tschuman13]

I believe it's SQL 2008 which was used for Cognos 10.1. It's on a separate server.

[Andrei I]

Is it possible that Cognos CM Db is on the SQL Server Named instance? And some ports (other than default 1433) are not open on that box?

[tschuman13]

How would I check that?  I did try changing the SQL Server port to 2020 to see if it was port-related but it didn't change anything.  IBM has not been able to figure this out either.  I wonder if there is something about 11.1.3 and SQL Server 2017?

[Andrei I]

I had an issue long time ago configuring C10  with SQL Server Named Instance

Named instance is specified like this:  <server name>\<instance name>  or <host:port>
It is using a different port than Default 1433. And I am assuming it might cause issues in secured environments where all ports are disabled except for required ones.

Check if this helps

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/logging-in-to-sql-server?view=sql-server-ver15

[Andrei I]

I was installing Cognos 11 in Google Cloud (Linux with MS SQL 2017 backend ) and ran into the same problems.

Why the issue is happening:
https://techcommunity.microsoft.com/t5/sql-server-support/jdbc-connections-fails-with-quot-the-driver-could-not-establish/ba-p/414157

Clues how to fix your Cognos environment (actual details may vary depending on your setup):
https://www.ibm.com/support/pages/connection-sql-server-fails-when-server-configured-use-tls-12-or-connecting-sql-server-2016

Essentially  for all of Cognos Java invocations we should add an option:   -Dcom.ibm.jsse2.overrideDefaultTLS=true
IBM could have done it easily by default  but I guess they want MS SQL users to suffer  8)

Please let me know if it helps.

[tschuman13]

Hi Andrei,

It was the TLS/SQL Server issue in the second link.  IBM sent me the same article.  I thought this was resolved in 11.13+ but apparently not.  Once I made the changes there I was able to connect.

Thanks to everyone for the help and suggestions!