Configure SQL Server Connection in Cognos 10.2

You have tried Oracle, MySQL so far in Cognos 10.2. Let’s configure another popular data source for Cognos.

When your environment uses the default instance name MSSQLSERVER

  • Copy the appropriate Oracle jdbc driver to the Cognos directories outlined in this post IBM Cognos 10.2 evaluation
  • Launch your Cognos Administration via this URL http://localhost:19300/p2pd/servlet/dispatch (replace localhost with the name of your machine as appropriate)
  • Highlight the ‘Configuration‘ tab, and select the ‘New Data Source‘ icon as shown in the following screenshot

oracon01

  • Enter the name of the connection, e.g. rdwSQLServer, and proceed to define the type of driver as shown below

ms01

Notes that I am using SQL 2012 but you can use other versions as you wish.

  • Enter the connection information with the correct host name, port number, and the database specific to your test environment.

ms03

  • Click ‘Test the connection…‘ to validate your configuration. You should get a confirmation back from Cognos such as shown below.

ms04

When your environment uses a named instance

This scenario will be a bit more involved because you will need to verify that the named SQL Server instance of your interest is actually listening on the port you have designated for it (including the default 1433). Here is how you go about wrestling with your named SQL Server connection…

  • Ensure your named instance is accepting TCP/IP calls. You can do this by running the SQL Server Configuration Manager SQLServerManager11.msc (this utility is SQL Server 2012 so yours might be different – you can look for it in Windows\SysWOW64 directory).
  • When the utility is shown, highlight your named instance (e.g. DEVSQLSERVER) and ensure the TCP/IP shown on the right panel is set to ‘Enabled’. Right click on it to enable if necessary.

ns01

  • Run c:\windows\system32\cliconfg.exe to verify that port 1433 is being listened by SQL Server (or any port number you have designated).
    • In the following screenshot, you might need to enable TCP/IP first. Just select it on the left panel and click on ‘Enable‘ to get it over to the right panel.

ns02

  • Highlight TCP/IP and click on ‘Properties‘ to review the IP settings shown below.

ns03

  • Look for IPAll, and edit TCP Port with your designated port number (or use the default 1433). Then click ‘Apply‘ when done.
  • Restart the Windows services for your named instance, and the SQL Server Browser.

 

When the services restarted ok, do the following additional checks to ensure your named instance is now accessible by TCP/IP with the port number you have configured

  • Open cmd.exe session
  • Initiate two sqlcmd sessions as shown below (one with Trusted Connection, and the other using SQL Logon – but both would use the connection format with a named instance and a specific port number). You can go here https://msdn.microsoft.com/en-us/library/ms162773.aspx for a quick refresh of sqlcmd.

ns04

  • If you see outputs similar to the above then you are good so far!
  • The following final tests are just to confirm that SSMS still works, and your designated port number will show up by running the SQL snippets seen in the screenshots shown below.

ns05

ns06

You are now ready to configure your named instance SQL Server in Cognos!

  • Open the usual Cognos Administration, go to the Configuration tab to set up a new data source.

nsc01

  • Select Microsoft SQL Server (SQL 2012 Native Client) as the Type of your connection (you could select other SQL Server version if you wish).

nsc02

  • In the next screen, just enter the Signon information and ignore everything above it. Do not click ‘Test the connection…’ here yet.

nsc03

  • Enter the correct port number, and your named instance in the screen shown below.

nsc04

  • Then click ‘Test the connection…

nsc05

  • Click on ‘Test‘ to get a confirmation as shown below

nsc06

You have just successfully created a SQL Server connection in Cognos to connect to your named SQL Server instance on a specific port number. Have fun exploring IBM Cognos 10.2!

 

 

TaiPh Author

Professional Business Intelligence Architect

Comments

    Sandra

    (May 29, 2017 - 1:39 pm)

    Hi, thanks for above guidelines but we have a problem when we leave the ‘sql 2012 native client’ part open as you do above. We only configure the jdbc because we have a named instance. But when you test the datasource, it tries to test all 2 connection strings. And the empty one does not pass, of course. Is there a way to say you only want jdbc?

Leave a Reply

Your email address will not be published. Required fields are marked *