Subscribe
Accepted Solution

DWH how to use own SSL certificates

Hello, I tried to find any manual, how to use CA signed SSL certificates in the DWH. Is there any documentation available? Thank you Regs Matthias

Re: DWH how to use own SSL certificates

[ Edited ]

Hey Matthias,

 

The procedure for 7.2 is different.

 

It is best to break it down into phases.

 

Phase 1 - We need to generate a certificate signing request, and submit it to our internal Certificate Authority

 

RDP to the DWH

 

Make a backup of ..\SANscreen\cognos\c10_64\configuration\cogstartup.xml

 

Open a cmd prompt, running with administrative permissions.

 

We may have difficulty due to Java path statements- so, in this command prompt we are going to wipe out the PATH variable

 

SET PATH=.

 

In the cmd prompt, navigate to ..\SANscreen\cognos\c10_64\bin

 

ThirdPartyCertificateTool.bat -java:local -c -e -d "CN=EncryptCert,O=NetApp,C=CA" -r c:\encryptRequest.csr

 

Set the CN to the FQDN of the OCI DWH you expect your end users to use to access the DWH. OCI doesn't care about the O or C values

 

 

Use c:\encryptRequest.csr to obtain a signed certificate from your CA.

 

Before we move to Phase 2, you need to have your signed certificate, and all the certificates in your CA chain of trust. Typically, your organization will have a root CA certificate, and one or more intermediate signing certificate authorities - we need ALL the public certs from any CA in the chain between the signed OCI DWH cert, and your organizations root CA cert

 

 

Phase 2 - Making Cognos happy

 

We have a handful of certificates.

 

To proceed, we want:

 

The OCI DWH certificate in its own file

The Root CA certificate in its own file

All the CA certificates, including the root CA, in one .p7b file

 

Microsoft's native certificate functionality is pretty strong - you probably can import all these certificates into a Microsoft keystore. Within Microsoft certificate tools, you can shift+select multiple certificates, and export them together into a .p7b. It may be possible to also arrange these files with OpenSSL or another certificate tool.

 

When we have the files ready, RDP to the OCI DWH server, and copy the files

 

Open a cmd prompt, running with administrative permissions.

 

We may have difficulty due to Java path statements- so, in this command prompt we are going to wipe out the PATH variable

 

SET PATH=.

 

In the cmd prompt, navigate to ..\SANscreen\cognos\c10_64\bin

 

ThirdPartyCertificateTool.bat -java:local -i -e -r OCIDWH.cer -t chain.p7b

 

Where OCIDWH.cer is the OCI DWH certifiacte, and the chain .p7b is the chain of certificates including the root

 

ThirdPartyCertificateTool.bat -java:local -i -T -r ca.cer

 

Where ca.cer is your organization's root certificate - this step is required to tell Cognos to trust this root certificate. It is weird, but trust us that this is required, even though the previous certificate importation steps seems to log messages about trusting certificate authorities.

 

Now, open IBM Cognos Configuration

 

Cognos 3rd party.jpg

 

 

We need to change Cognos to use a 3rd party CA

 

Save the Configuration by clicking the floppy disk icon (if you are under 35 years old, ask someone older what a floppy disk is)

 

Save Cognos.jpg

 

 

Restart the Cognos Services by clicking this icon:

 

Cognos Restart.jpg

 

A Window will popup, logging all the events as Cognos services recycle - you may see an error if you have NOT configured SMTP as it will attempt to test it. When things are back up, log into the OCI DWH reporting portal on tcp 9300 - you should no longer see warnings about certificates

 

FYI - IE and Chrome "outsource" certificate management to Windows' native certificate stores, whereas Firefox uses its own certificate store. If you are using Firefox, and you have NOT installed your organization's CA certs into its certificate store, you will likely still see warnings about untrusted certificates... because you didn't configure Firefox to trust your CA chain.

 

Phase 3 to come

Re: DWH how to use own SSL certificates

Hello Matt, Thank you for your fast response. Could we also use openssl to create the request? For our internal CA, we have to set different values in the certificate. Regs Matthias

Re: DWH how to use own SSL certificates

Ugh, this is a bit tricky.

 

If you could PM with what attributes are required, I can have engineering open a case with IBM to determine if this is possible

 

Matt

Re: DWH how to use own SSL certificates

Hello Matt, I have now the signed certificate and the certificate chain. Regs Matthias

Re: DWH how to use own SSL certificates

Hello Matt,

 

Today I had the time to finish the certificate stuff.

Thank you for your manual, it worked without any problems.

 

Regs

Matthias

Re: DWH how to use own SSL certificates

Hello Matt,

 

I have now the problem, that the backup of the DWH does not work any longer? From teh log file it seems to have a problem with the certificate:

 

2016-10-16 17:02:00,492 ERROR [Thread-85939 (group:HornetQ-client-global-threads-306425475)] BackupScheduleBean (BackupScheduleBean.java:58) - Failed to generate and send dwh and cognos reports.: com.netapp.sanscreen.dwh.upgrade.UpgradeException: Failed to connect to reporting server, server might be down. Confirm reporting server availability and try again.

 

Caused by: com.netapp.sanscreen.dwh.cognos.CognosClientException: Failed to connect to reporting server, server might be down. Confirm reporting server availability and try again.
 at com.netapp.sanscreen.dwh.cognos.CognosClient.open(CognosClient.java:112)
 at com.netapp.sanscreen.dwh.cognos.CognosClient.<init>(CognosClient.java:92)
 at com.netapp.sanscreen.dwh.cognos.CognosClient.<init>(CognosClient.java:69)
 at com.netapp.sanscreen.dwh.upgrade.Backup.backup(Backup.java:73)
 ... 332 more
Caused by: javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
 at org.apache.axis.AxisFault.makeFault(AxisFault.java:101)

Re: DWH how to use own SSL certificates

That was supposed to be step 3

 

We still have Jboss in the mix with OCI 7.2.x - it provides the DWH admin portal, and runs the ETL.

 

We need to get its trusted certificate store to trust some more certs

 

Backup ..\SANscreen\jboss\server\onaro\cert\server.trustore

 

C:\Program Files\SANscreen\java64\bin>keytool -importcert -file C:\ocidwhcert.cer  -keystore "C:\Program Files\SANscreen\jboss\server\onaro\cert\server.trustore"

 

 

Where c:\ocidwhcert.cer is the public certificate of your OCI DWH server

 

Once this is done, perform a DWH backup - you should find it is now successful

Re: DWH how to use own SSL certificates

Hello Matthias

 

We use also own Certificates with different values, I try to import the cert files as Matt describes it.

When we start

ThirdPartyCertificateTool.bat -java:local -i -e -r C:\Daten\cognos_cert\sanrep101.cer -t C:\Daten\cognos_cert\chain.p7b

 

the tool returns

CAM-CRP-1238 Unable to store the third party encryption certificate. Unable to store the third party certificate in the keystore because a certificate already exists for that keypair.

 

regards

Michael

Re: DWH how to use own SSL certificates

Sorry for the comically late reply.  I'm mostly posting so I see this next time I run into the same problem. 

I recently encountered this problem in a customer environment and discovered that it was due to a misspelled hostname in the CN portion of the certificate request.  Once we corrected that, we were able to move on to other errors.