Connect with SQL Server 2016 64 Bit vs SQL Server 2008 32 bit

[Invisi]

Here at my client I got a shiny SQL Server 2016 test machine all ready to go to replace the outdated SQL Server 2008 machine. When I try to connect to the new machine from Cognos (10.1), it fails, even when I just change the machine name in the existing connection string. What am I missing?

[MFGF]

Here at my client I got a shiny SQL Server 2016 test machine all ready to go to replace the outdated SQL Server 2008 machine. When I try to connect to the new machine from Cognos (10.1), it fails, even when I just change the machine name in the existing connection string. What am I missing?

How are you connecting? ODBC? Native Client? OLE-DB? JDBC?

MF.

[Invisi]

OLE-DB, sorry for omitting this info.

[MFGF]

OLE-DB, sorry for omitting this info.

In that case you need to have the 32-bit SQL Server native client libraries installed on the Cognos server. OLE-DB access is via Compatible Query Mode (32-bit) so it needs the 32-bit libraries. Also, I'm not seeing any support for SQL Server 2016 in the 10.1 Supported Environments page, so it might not work even if you install the correct native client libraries. Looks like 10.1 has support for only SQL Server 2000 / 2005 / 2008 and 2008 R2:

http://www-01.ibm.com/support/docview.wss?uid=swg27019126

Cheers!

MF.

[Invisi]

I assume you mean the 2016 client libraries, next to the already present 2008 libraries?

It better work, we are just migrating to SQL server 2016 and Windows 2012 R2 because we are so fed old with the ancient stuff... Just no time to also migrate Cognos right now (will be later).

[MFGF]

It better work, we are just migrating to SQL server 2016 and Windows 2012 R2 because we are so fed old with the ancient stuff... Just no time to also migrate Cognos right now (will be later).

That's a huge gamble you're taking! Hope for your sake you can get 10.1 to talk to SQL Server 2016, but I wouldn't bet the farm on it.

Good luck!

MF.

[Invisi]

Actually I find it hugely depressing that connecting to a database is such a hassle or 'gamble'. I already have a 10.2 64 bit Cognos Test server installed, so I am now testing this. I am also testing the JDBC driver, as it's needed for Dynamic Query Mode anyway.

Who will give me a complete connection string sample for SQL Server on JDBC? Also, I have a sqljdbc42.jar and a sqljdbc4.jar file. Should the 42 version be able to work? Or should I stick with the 4 version?

[Invisi]

Anyway, issue solved. Or you configure the Firewall of the database machine...  >:( >:(

[MFGF]

Actually I find it hugely depressing that connecting to a database is such a hassle or 'gamble'. I already have a 10.2 64 bit Cognos Test server installed, so I am now testing this. I am also testing the JDBC driver, as it's needed for Dynamic Query Mode anyway.

The issue is that you are using a version of Cognos built years before SQL Server 2016 was released. How can you expect the guys at IBM to build in connectivity for something that doesn't exist? It's a very risky strategy to update your database version without updating the other components that utilise it. Although you might "get away with it" if you're lucky, you'll still end up on an unsupported combination of products. What happens when something breaks? Where can you go for support?

Who will give me a complete connection string sample for SQL Server on JDBC? Also, I have a sqljdbc42.jar and a sqljdbc4.jar file. Should the 42 version be able to work? Or should I stick with the 4 version?

You can simply add in the database server and database name on the JDBC tab of the existing OLE-DB connection. When you test, you will see a connection string that looks like:
^User ID:^?Password:;LOCAL;JD-SS;URL=jdbc:sqlserver://<your database server>;DATABASE=<your database name>;LOGINTIMEOUT=0;DRIVER_NAME=com.microsoft.sqlserver.jdbc.SQLServerDriver

The jdbc file you need is sqljdbc4.jar. The 42 version supports only JRE 8.0 whereas the sqljdbc4.jar version supports JRE 6.0 and JRE 7.0 (and Cognos 10 uses JRE 7.0)

Cheers!

MF.

[Invisi]

You are right, it is stupid of me to expect some consistency of a Microsoft product... The fact that 10.3 has 3 native SQL Server client options in the list says enough...

So a straight JDBC connection for SQL Server is not useful? And just checking the "Build JDBC driver" checkmark brings me to DQM heaven?

[MFGF]

You are right, it is stupid of me to expect some consistency of a Microsoft product... The fact that 10.3 has 3 native SQL Server client options in the list says enough...

So a straight JDBC connection for SQL Server is not useful? And just checking the "Build JDBC driver" checkmark brings me to DQM heaven?

You stand a better chance of jdbc working correctly, I'd assume. You're limiting yourself to DQM only, though. The problem you might face is when you hit issues and try to log a support call with IBM. They are going to say "SQL Server 2016? Not supported, sorry". My guess is they will insist you replicate your issue against a supported version of the database (which will cause you delays) or get you to upgrade to a more modern build of Cognos (more delays).

Wishing you smooth sailing, though! Good luck!

MF.

[the6campbells]

Some input on comments in the thread.

CQM will require 32-bit versions of the SQL Server Native Client package which incoporates the OLE-DB and ODBC API interfaces which are both supported. As of Cognos 11.R4 you will required to use version 11 of  SQL Server Native client http://msdn.microsoft.com/en-us/library/cc280510.aspx

DQM will require the SQL Server driver (i.e sqljdbc42) and as applicable you may require the additional non-Java libs for windows authentication etc.
Microsoft JDBC, copies can always be downloaded from http://www.microsoft.com/en-ca/download/details.aspx?id=11774 along with the release notes etc.

The UI in Cognos Admin does not make you explicitly enter a full JDBC URL, instead it generates one from the input controls it presents on the form. The connection properties text control allows you to specify one or more optional name-value pairs which Microsoft documents.
http://docs.microsoft.com/en-us/sql/connect/jdbc/building-the-connection-url

[Invisi]

Thanks! I may get back here when I will experiment with DQM. So far all is running smoothly on the database with fairly out-of-the-box settings for the connection. For clarity / completeness; we are using integrated authentication.