by ·Comments Off on You can not use a PFX file to Cognos

Product:
Cognos Analytics 11.1.7
Cognos Controller 10.4.2
Microsoft Windows 2016 server

Issue:
How setup SSL(TLS1.2) with Cognos Analytics, when i only got a PFX file from my company?

Solution:

This is not the recommended way – you should use the IBM guides instead.

https://www.ibm.com/docs/en/cognos-analytics/11.1.0?topic=components-configuring-ssl-cognos-analytics

https://www.ibm.com/support/pages/how-configure-force-controller-use-tls-12

(This instructions will fail as the next time you start Cognos Configuration and click save, the CAMkeystore file is overwritten)

 

 

First, ensure Cognos Analytics is working with out issues. Check this log files and solve all errors before you start.
D:\Program Files\ibm\cognos\analytics\logs\p2pd_messages.log
D:\Program Files\ibm\cognos\analytics\logs\cognosserver.log
D:\Program Files\ibm\cognos\analytics\wlp\usr\servers\cognosserver\logs\messages.log

Second, always backup this folders – by copy them to a different folder like d:\temp
D:\Program Files\ibm\cognos\analytics\bin64\ssl
D:\Program Files\ibm\cognos\analytics\configuration\certs
D:\Program Files\ibm\cognos\analytics\ibm-jre\jre\lib\security

and for cognos controller backup this folders
D:\Program Files\ibm\IBM Cognos Controller Local Client\Integration\jre\lib\security
D:\Program Files\ibm\cognos\ccr_64\bin64\jre\8.0\lib\security

You should export a text file of the configuration in Cognos Configuration and create screen dumps of your setup in Cognos Controller Configuration, so you can apply them back in case something goes wrong. Use Select File – Export as.

Download OPENSSL to your windows laptop, with this program you can convert and check certificates. Download GIT for Windows to easy also get the OPENSSL tool. Download from https://git-scm.com/download/win
Run Git-2.37.3-64-bit.exe (with all defaults) to install GIT on you laptop.  Then you find openssl in folder: C:\Program Files\Git\mingw64\bin

 

IMPORTANT: Add a rule in the local windows defender firewall to allow inbound traffic on both port 80 and 443 on all cognos servers.

How install PFX in Windows (IIS)

Copy your PFX file to your server, place it in a folder like c:\cert
Right click on PFX file and select Install PFX

Select Local Machine and click Next

Click next on file to import.

Enter the password you got with the PFX file and click Next.

Click next on Certificate store, and let it be automatically selected.

Click Finish.

Start IIS (Internet Information Services Manager)

Click on the servername (home) and click on Server Certificates, to view that you have valid certificates. Check the Expiration Date to know witch one you can use.

In IIS manager, expand Sites, and mark Default Web Site. Click on bindings link on the right side.

Click ADD button.

Select HTTPS as type.

Select your certificate from the drop-down list SSL certificate, and press OK.

Later, when it is working, remove the support for port 80 in the bindings dialog.

Go to default web site\ibmcognos\bi folder and click on URL Rewrite icon

Double click on Reverse Proxy, to change it values.

Scroll down and edit the ReWrite URL to: https://ca_servers/bi/{R:0}

Click Apply on the right side.

Restart IIS.

 

If you have had the Cognos BI site running HTTP before, you must change in IIS webfarm to use the new HTTPS, and also update the port in use. Otherwise you will get 404 or 502 errors in Web browser.

Clear the IIS setup.  https://www.ibm.com/support/pages/node/301009

Update the CA_IIS_Config.bat file with HTTPS and correct port.

Run CA_IIS_Config.bat as a administrator in Command prompt.

 

How export the root and intermediate cer files from IIS

Go to server certificates, double click on your certificate.

Select Certificate Path tab, and select the middle certificate in the list, and click View Certificate button.

A new windows will open, in there go to the Details tab.

Select Subject row, and copy the CN line to the clipboard.

Click on the Copy to File button.

Click Next in the Certificate Export Wizard.

Mark Base-64 encoded X.509 and click next.

Enter a name and path, suggest use the CN name as name of the cer file. Click Next.

Click Finish.

Now, you need to repate this for the root certificate.

Click on Certificate path tab.

Click on top root cert and click on View Certificate button.

Go to Details tab and select Subject line.

Copy CN line to clipboard.

Click Copy to File button.

Click next in the Certification Export Wizard.

Mark Base-64 encoded X.509 and click Next.

Enter a file name like c:\cert\root.cer and click Next.

Click Finish to create the file.

Now you should have two cer files in your c:\cert folder.

You can test the certificate, by browse to your server at https://servername to see if you get errors in Internet Explorer.

Under Internet Options – Advance – you can uncheck “warn about certificate address mismatch” to suppress error messages.

Update the CAMstore

Before you change the certificate store in Cognos, stop all IBM Cognos services and java process.

Go to folder D:\Program Files\ibm\cognos\analytics\ibm-jre\jre\bin

Right click on IKEYMAN.EXE and select “run as administator”.

Click on the open icon.

Browse to D:\Program Files\ibm\cognos\analytics\configuration\certs folder and select CAMkeystore file.

Set Key database type to PKC$12 and press OK.

Enter the password: NoPassWordSet

Mark encryption, and click rename, to change it to encryptionold.

Click on Export/import button.

Browse to your PFX file.

Select type to be PKCS12 and press OK.

Enter the password you got with the PFX file.

Click OK on the question to change labels.

Mark the new line, and click rename button. Change the name to encryption.

Press OK.

Double click on encryption to check that the certificate is valid. Check the date and the DNS name is the same as your servername.

Select Signer Certificates in the drop down list.

Check that the root and issuing certificate for your company is in the list, if not then click on ADD button, and select the two cer files we created before and import them.

Exit IKEYMAN.  All changes in IKEYMAN are saved directly to your key store file. That is why you need to backup the key store file before you open IKEYMAN program.

Add root cert to java key store

Start IKEYMAN program with Run as Administrator

Click on Open icon.

Browse to D:\Program Files\ibm\cognos\analytics\ibm-jre\jre\lib\security and open cacerts as Type JKS.

Click OK and enter the password: changeit

Click on ADD button.

Find your issue cer file and import it.

Enter a name that describe the certificate and press OK.

Click on ADD button again, and import the root cer file.

Select the cer file and press OK.

Enter it a name and press OK.

Close IKEYMAN program.

Change CA11 to use custom certificate

Start Cognos Configuration program as Administrator.

At Local configuration, click on Advance properties.

Add the value StandaloneCerificateAuthority = True

Click OK

Go to Environment and change all HTTP to HTTPS.

Update the port number 80 to 443.

Keep the port-number 9300 for the other rows. We will use SSL over the port 9300.

Gateway should be: https://servername.domain.com:443/ibmcognos/bi/v1/disp

Controller URI for gateway should be: https://servername.domain.com:443/ibmcognos/controllerServer

Content Manager URI should be: https://servername.domain.com:9300/p2pd/servlet

Go to Cryptography – Cognos

Change Use Third Party CA? to True.

Save the configuration (this will update some key store files for WebSphere Liberty Profile).

Start the IBM Cognos service from inside Cognos Configuration.  This take around 10 minutes.

There should be no errors, when starting Cognos Analytics.

To test the certificate inside Cognos Analytics, start a web browser and go to https://servername.domain.com:9300/p2pd/servlet

There should be no errors, and the padlock should be secure in the web browser.

 

 

 

 

Add the trusted ca root cert to java store

On the cognos controller client computer, start IKEYMAN from D:\Program Files\ibm\IBM Cognos Controller Local Client\Integration\jre\bin  folder.

Click on OPEN icon and go to D:\Program Files\ibm\IBM Cognos Controller Local Client\Integration\jre\lib\security folder

Select file cacerts mad key database type JKS. Click on OK.

Enter the password: changeit

Select Signer Certificates from the drop down list.

Click on ADD button, to add the root.cer and issuing.cer files you have created before from IIS.

After you have added both certs, exit IKEYMAN program.

Copy now the cacerts file to all cognos controller installations, and place the file in folder D:\Program Files\ibm\IBM Cognos Controller Local Client\Integration\jre\lib\security.

If you are using ccrRemoteServer in file D:\Program Files\ibm\cognos\ccr_64\ControllerProxyServer\web.config

<add key=”ccrRemoteServer” value=”https://servername.domain.com/ibmcognos/controllerserver” />

That need to be updated with https, and the server need to be restarted.

 

Update Cognos Controller to support TLS 1.2

Go to the folder: d:\Program Files\ibm\cognos\ccr_64\server\
Open CCRProxy.options in NOTEPAD++
Add the following lines (at the end):
-Dcom.ibm.jsse2.overrideDefaultTLS=true
-Dcom.ibm.jsse2.overrideDefaultProtocol=TLSv12

Save the file.

On the Cognos Controller server and Cognos Controller clients do this:
1. Open the registry editor, by clicking on ‘Start’ menu and typing:    REGEDIT
2. Navigate to the following path: [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v4.0.30319]
3. Right-click on v4.0.30319 and select New –> DWORD (32-bit)
  • Set the name to: SchUseStrongCrypto
  • Set the value to 00000001

 

Go into Cognos Controller Configuration and update the Report Server dialog to use HTTPS instead of HTTP.

Report server should be: https://servername.domain.com/ibmcognos/bi/v1/disp

Dispatcher URI should be: https://servername.domain..com:9300/p2pd/servlet/dispatch

Important to also update the Cognos Controller Client Distribution Server Configuration. All should use HTTPS.

When you change cognos controller configuration to HTTPS, you must also stop and start the Batch service from inside controller configuration, to update all files correct.

WSSUrl should be: https://servername.domain.com/ibmcognos/controllerserver

Save the changes in Controller Configuration.

To test that the SSL is working on the server, from the cognos controller client start a web browser and browse to

https://myservername.mycompany.com/ibmcognos/controllerserver/ccrws.asmx

There should be no errors.

 

 

(Please do not use this instructions – as they will not work – when you change in Cognos Configuration, the certificate store is replaced with the cognos default certificate – and the system will break)

Use this instructions instead:

https://www.ibm.com/support/pages/how-add-3rd-party-ca-allow-ssl-between-components-ibm-cognos-analytics-11

 

More Information:

https://www.ibm.com/support/pages/how-configure-main-controller-client-ccrexe-use-https-ssl

https://www.ibm.com/support/pages/how-configure-force-controller-use-tls-12

https://www.sslshopper.com/ssl-converter.html

by ·Comments Off on The TM1Web service parameter was not specified or is not one of the configured locations

Product:

Planning Analytics 2.0.9

Microsoft Windows 2019 server

Issue:

After adding support for HTTPS(SSL) to TM1WEB, the url does not work. You get loged in to CA11, but then you get a error like this:

The TM1Web service parameter was not specified or is not one of the configured locations

If you check the URL (copy it to notepad) you see that the TM1WEB server still are using the old port you had before HTTPS was implemented.

The TM1WEB.HTML file contain the correct value, with the new port for SSL communication, on the Cognos Analytics server.

Check folder c:\Program Files\ibm\cognos\analytics\webcontent\bi\tm1\web.

c:\Program Files\ibm\cognos\tm1web\wlp\usr\servers\tm1web\SERVER.XML have also a correct row for the new port;

<httpEndpoint id="defaultHttpEndpoint" httpPort="-1" httpsPort="9510" host="*" removeServerHeader="true" >

Solution:

tm1web_config.xml in folder c:\Program Files\ibm\cognos\tm1web\webapps\tm1web\WEB-INF\configuration, has the old server value hard-coded to this line ExternalUrl, it should be empty “”.

 <add key="X-Frame-Options" value="2" />
<!-- When performing CAM authentication, optional redirection url override example http://127.0.0.1:9510/tm1web -->
<add key="ExternalUrl" value="" /> 
<!-- LogoutUrl to be executed after logout normally completes-->
<add key="CustomCAMLogoutUrl" value="" />

Update the file and save it.

 

More information:

TM1WEB picks up your URL, and sends it to CA11 for login, then CA11 will check if the URL you come from matches the URL listed in the file TM1WEB.HTML, if it is not listed, then you get this error.

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=tls-use-key-management-configure-custom-certificates

https://www.ibm.com/docs/en/SSYKE2_7.0.0/com.ibm.java.security.component.70.doc/security-component/iKeyman.8.User.Guide.pdf

by ·Comments Off on NET ERR_CERT_COMMON_NAME_INVALID

Product:

Planning Analytics

Microsoft Windows 2019

Issue:

When user browse to tm1web site, they get a warning in web browser that “your connection is not private”.

If you check the certificate – by click on the padlock in the browser – windows says the certificate is OK.

Subject Alternative Name Missing The certificate for this site does not contain a Subject Alternative Name extension containing a domain name or IP address.

Certificate Error There are issues with the site’s certificate chain (net::ERR_CERT_COMMON_NAME_INVALID).

Solution:

Order a new certificate for your TM1WEB server that contain a Subject Alternative Name, that is the server-name or DNS alias.

The certificate you have today, if you open it in MMC, under Details tab, there is no line for Subject Alternative Name, only a line for Subject.

 

More Information:

https://www.digicert.com/faq/subject-alternative-name.htm

Security certificate does not specify subject alternative names

by ·Comments Off on Only add rows for a date to table

Product:

Microsoft SQL server 2016

Microsoft Windows 2016 server

Problem:

How add rows for a date to a table?

Solution:

For this example we use this demo database:

https://www.brentozar.com/archive/2021/03/download-the-current-stack-overflow-database-for-free-2021-02/

Create a table with;

CREATE TABLE [dbo].[Storage](
[Id] [int] IDENTITY(1,1) NOT NULL,
[CreationDate] [datetime] NOT NULL,
[DisplayName] [nvarchar](40) NOT NULL,
[DownVotes] [int] NOT NULL,
[LastAccessDate] [datetime] NOT NULL,
[Location] [nvarchar](100) NULL,
[Reputation] [int] NOT NULL,
[UpVotes] [int] NOT NULL,
[Views] [int] NOT NULL,
[AccountId] [int] NULL,
[UpdateDate] [datetime] NOT NULL
CONSTRAINT [PK_Storage_Id] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

To insert one row based on date and time;

INSERT INTO [dbo].[Storage]
(
[CreationDate],
[DisplayName],
[DownVotes],
[LastAccessDate],
[Location],
[Reputation],
[UpVotes],
[Views],
[AccountId],
[UpdateDate] 
)
SELECT
[CreationDate],
[DisplayName],
[DownVotes],
[LastAccessDate],
[Location],
[Reputation],
[UpVotes],
[Views],
[AccountId],
(GETDATE()) AS UpdateDate
FROM
[dbo].[Users]
WHERE
LastAccessDate = '2018-06-18 11:47:32.200'

To set a variable for a date;

DECLARE @@TargetDate DATETIME = '2018-06-18'
SELECT @@TargetDate

To insert all rows for a specific LastAccessDate;

INSERT INTO [dbo].[Storage]
(
[CreationDate],
[DisplayName],
[DownVotes],
[LastAccessDate],
[Location],
[Reputation],
[UpVotes],
[Views],
[AccountId],
[UpdateDate] 
)
SELECT
[CreationDate],
[DisplayName],
[DownVotes],
[LastAccessDate],
[Location],
[Reputation],
[UpVotes],
[Views],
[AccountId],
(GETDATE()) AS UpdateDate
FROM
[dbo].[Users]
WHERE
CONVERT (date,(LastAccessDate)) = @@TargetDate

 

More information

https://www.sqlshack.com/how-to-update-from-a-select-statement-in-sql-server/

https://popsql.com/learn-sql/sql-server/how-to-query-date-and-time-in-sql-server

SQL Server GETDATE () function and its use cases

by ·Comments Off on How add data from other table

Product:

Microsoft SQL server 2016

Microsoft Windows 2016

Problem:

How add data from one table to other table?

Suggested solution:

For the example we have downloaded the StackOverflow2013 database from here

https://www.brentozar.com/archive/2021/03/download-the-current-stack-overflow-database-for-free-2021-02/

Create a new table;

CREATE TABLE [dbo].[Staff](
[Id] [int] NOT NULL,
[CreationDate] [datetime] NOT NULL,
[DisplayName] [nvarchar](40) NOT NULL,
[DownVotes] [int] NOT NULL,
[LastAccessDate] [datetime] NOT NULL,
[Location] [nvarchar](100) NULL,
[Reputation] [int] NOT NULL,
[UpVotes] [int] NOT NULL,
[Views] [int] NOT NULL,
[AccountId] [int] NULL,
CONSTRAINT [PK_Staff_Id] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

To be able to copy IDENTITY values between tables you need to use IDENTITY_INSERT and specify the columns in the INSERT INTO statement (not used in our example) .

SET IDENTITY_INSERT dbo.Employee ON

To copy all rows for users from Denmark to the new table;

INSERT INTO dbo.Staff
SELECT
    [Id],
    [CreationDate] ,
    [DisplayName] ,
    [DownVotes] ,
    [LastAccessDate],
    [Location] ,
    [Reputation] ,
    [UpVotes] ,
    [Views] ,
    [AccountId]
FROM windows2016.stackoverflow2013.dbo.users
WHERE location = 'Denmark'

To set the value to zero for column DownVotes when View is less than 100;

UPDATE [StackOverflow2013].[dbo].[Staff]
  SET DownVotes = 0
  WHERE views < 100

Select only one row by use of ID column;

SELECT *
FROM StackOverflow2013.dbo.staff
WHERE id = 19 AND location = 'denmark'
Update one column value for one row;
UPDATE StackOverflow2013.dbo.staff
SET DownVotes = 1
WHERE id = 19 AND location = 'denmark'

To list all users sorted by views column;

SELECT 
       [Id]
      ,[CreationDate]
      ,[DisplayName]
      ,[DownVotes]
      ,[LastAccessDate]
      ,[Location]
      ,[Reputation]
      ,[UpVotes]
      ,[Views]
      ,[AccountId]
  FROM [StackOverflow2013].[dbo].[Staff]
  ORDER BY views DESC

 

 

More information:

http://www.sql-server-helper.com/error-messages/msg-8101.aspx

https://www.sqlshack.com/how-to-copy-tables-from-one-database-to-another-in-sql-server/

https://www.sqlshack.com/overview-of-the-sql-insert-statement/

https://www.tutorialspoint.com/sql/sql-order-by.htm