by ·Comments Off on How add user who run SP to a table

Product:

Microsoft SQL server 2019

Issue:

How create a store procedure, than when run will add username and date to a row?

Suggested solution:

Paste below code in SQL management studio and edit it to your needs:

CREATE PROCEDURE [dbo].[A_AddDataToTable] 
AS
BEGIN
---- create the variables
DECLARE @TableName as nvarchar(50)
DECLARE @SqlCode as nvarchar(200)
DECLARE @TimeValue as nvarchar(50)
DECLARE @Userman as nvarchar(50)

---- enter the name of your table below instead of Donald
SET @TableName = 'Table_'+'Donald'

BEGIN
---- check if the table exist, then not create it
IF NOT EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME = @TableName) 

---- fill variable with the SQL code to create the table
set @SqlCode=''
set @SqlCode= @SqlCode + 'CREATE TABLE ' + @TableName + ' '
set @SqlCode= @SqlCode + ' (Rowid int IDENTITY(1000, 1) PRIMARY KEY, '
set @SqlCode= @SqlCode + ' ProUser nvarchar(50) , '
set @SqlCode= @SqlCode + ' ProDate nvarchar(50) ) ;'


---- execute the code in the string
EXEC sp_executesql @SqlCode

END
---- every time the processes is run, do below
---- add a row with date and username

---- get the values for date
SELECT @TimeValue = convert(varchar, getdate(), 0)
---- get the name of the user who run the process
SELECT @Userman = CURRENT_USER

---- build the string of values
set @SqlCode=''
set @SqlCode= @SqlCode + 'INSERT INTO '+ QUOTENAME(@TableName) +' (ProUser,ProDate) ' 
set @SqlCode= @SqlCode + 'VALUES ( ''' + (@UserMan) + ''' , ''' + (@TimeValue) + ''' ) '

-- execute the code in the string
EXEC sp_executesql @SqlCode

END


The result in the table will be like this, after a few run of the SP.

 

More Information:

https://www.sqlshack.com/introduction-to-sp_executesql-stored-procedure-with-examples/

https://codingsight.com/10-sp_executesql-gotchas-to-avoid-for-better-dynamic-sql/

by ·Comments Off on Can not find /content/package[@name=’Frango on Collinsd’]/model

Product:
Cognos Controller 10.4.2
Microsoft Windows 2012 Server

Issue:

When run a standard report, the BI session start, but you get a error that you do not have rights to the package.

QE-DEF-0157

/messageString></message></messages> http://www.ibm.com/xmlns/prod/cognos/reportService/202004/.sessionRSV-SRV-0042 Spåra bakåt:RSReportService.cpp(734): QFException: CCL_CAUGHT: RSReportService::processImpl()RSReportServiceMethod.cpp(254): QFException: CCL_RETHROW: RSReportServiceMethod::process(): asynchRunSpecification_RequestRSASyncExecutionThread.cpp(887): QFException: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(331): QFException: CCL_CAUGHT: RSASyncExecutionThread::runImpl(): asynchRunSpecification_RequestRSASyncExecutionThread.cpp(932): QFException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): asynchRunSpecification_RequestRSRequest.cpp(1656): QFException: CCL_THROW: RSRequest::executeInteractivePrompting()RSQueryMgr.cpp(840): QSModelNotFound: CCL_RETHROW: RSQueryMgr::getListIteratorRSQueryMgr.cpp(912): QSModelNotFound: CCL_RETHROW: RSQueryMgr::getResultSetIteratorRSQueryMgr.cpp(1020): QSModelNotFound: CCL_RETHROW: RSQueryMgr::createIteratorRSQueryMgrBasic.cpp(296): QSModelNotFound: CCL_RETHROW: RSQueryMgrBasic::executeRsapiCommandRSQueryMgrBasic.cpp(669): QSModelNotFound: CCL_THROW: RSQueryMgrBasic::processExceptionRSQueryMgrExecutionHandlerImpl.cpp(167): QSModelNotFound: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute()RSFaultHandler.cpp(193): QSModelNotFound: RSFaultHandler::handleNonCriticalExceptionQFSSession.cpp(1190): QSModelNotFound: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSSession.cpp(1188): QSModelNotFound: CCL_CAUGHT: QFSSession::ProcessDoRequest()QFSSession.cpp(723): QSModelNotFound: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSXQEModelChecker.cpp(90): QSModelNotFound: CCL_RETHROW: QFSXQEModelChecker::IsModelPointingToTheNewStack()Source/QECMAuthentication_4.cpp(6149): QSModelNotFound: CCL_THROW: QE

Solution:

The Cognos Controller server have lost the knowledge of that the package name is “Controller”, go into the Cognos Controller configuration.

Go to report server tab.

Ensure the package name is Controller.

And press SAVE anyway to ensure the settings are saved.

Exit Cognos Controller Configuration.

Start Cognos Controller Client and test the standard report again.

More Information:

https://www.ibm.com/support/pages/qe-def-0157-model-abcdefgh-does-not-exist-when-trying-run-standard-report-caused-renaming-package-controller-configuration

 

by ·Comments Off on Adobe open and tell the pdf is not a readable file

Product:
Cognos Controller 10.4.2 fix pack 2
CONTRL_UPDATE_version=CCR-AW64-ML-RTM-10.4.2200.70-0
CONTRL_UPDATE_name=IBM Cognos Controller Update
Microsoft Windows 2019 server

Issue:

When run a standard report in Cognos Controller program, the adobe program starts up and give an error message.

or error like this when open the pdf report. That is if the report is shown both in cognos controller and in a separate adobe reader.

 

Solution:

Change the Cognos Controller server to use the internal pdf viewer.

Steps:
1. Obtain a short amount of downtime (nobody logged onto Controller)
2. Logon to the Controller application server
3. Browse to the folder C:\Program Files\ibm\cognos\ccr_64\ControllerProxyServer
4. As a precaution, create a backup copy of the file:  web.config
5. Use Notepad to edit the file   web.config
6. Locate the section   <appSettings>
7. Add the following line into that section:
    <add key="useInternalPdfViewer" value="true" />
Save the file, and restart the server to test again.
More Information:

by ·Comments Off on How show standard report in HTML?

Product:
Cognos Controller 10.4.2 fix pack 2
CONTRL_UPDATE_version=CCR-AW64-ML-RTM-10.4.2200.70-0
CONTRL_UPDATE_name=IBM Cognos Controller Update
Microsoft Windows 2019 server

Issue:

After the upgrade to the controller fix pack, how do i change to get standard reports to show in HTML as they did before?

Before it looked like this:

after upgrade it look like this:

Solution:

You don’t. As the use of WebView2 from Cognos Controller 10.4.2 fp1 there is only possible to see reports in PDF or XLS.

 

Other information:

Change to use Cognos Controller own pdf viewer:

Steps:
1. Obtain a short amount of downtime (nobody logged onto Controller)
2. Logon to the Controller application server
3. Browse to the folder ‘ControllerProxyServer’
  • TIP: By default this is located here: C:\Program Files\ibm\cognos\ccr_64\ControllerProxyServer
4. As a precaution, create a backup copy of the file:  web.config
5. Use Notepad to edit the file   web.config
6. Locate the section   <appSettings>
7. Add the following line into that section:
    <add key=”useInternalPdfViewer” value=”true” />
To change the order of report generation in Cognos Controller client:
The following must be done on a per-user basis:

  1. Launch Controller
  2. Click ‘Maintain – Installation – Local Preferences
  3. Add a new key called ‘StandardReportPreviewFormat‘, with a value of either ‘html‘ or ‘pdf‘ or ‘xls‘ or ‘xml‘
  4. Click ‘save’

NOTE: If you change the setting so that the default report is ‘pdf’, then be aware that the report will take longer to view (longer to initially display). This is because there is more workload that the Controller application/report server must perform, in order to generate a ‘pdf’ report (compared with the default ‘html’ format). In other words, the end user(s) may perceive this as a performance loss.

Use the undocumented local preference ‘StandardReportBackProc‘ which will stop the PDF rendition in Cognos Controller client.

Steps:
Each individual end user must perform the following themselves:

1. Launch Controller and logon as normal
2. Click ‘Maintain – Installation – Local Preferences‘
3. Inside the ‘key‘ column, create a new entry called: StandardReportBackProc
4. Click ‘Save’
5. Exit Controller
6. Re-launch Controller and test
  • Inside the corresponding ‘value‘ column, create a new entry: false

https://www.ibm.com/support/pages/how-disable-pdf-generation-standard-reports

https://www.ibm.com/support/pages/standard-report-pdf-output-very-small-full-report-shown-small-portion-top-left-corner-mainly-white-display

Adobe Reader X . to make the pdf show up inside the cognos controller report window:

1. Launch Adobe Reader
2. Click “Edit – Preferences”
3. Click on “Internet” section
4. TICK “Display PDF in browser“

https://www.ibm.com/support/pages/how-change-adobe-reader-client-display-pdf-files-insideoutside-web-browser-use-controller-standard-reports

The Adobe Reader client installs both the ‘main’ PDF reader tool (the full client application) and also a smaller ‘web client’.

  • This Adobe Reader web client is installed as a plugin in Microsoft Internet Explorer
  • By default, this ‘web client’ is what gets activated inside the Cognos report display box.

Unfortunately, in some environments this ‘web client’ suffers from an inability to save more than one PDF file.

https://www.ibm.com/support/pages/cannot-save-more-one-pdf-file-standard-reports-system-reports

https://www.ibm.com/support/pages/ibm-cognos-controller-builds-ccr-name-and-database-version

by ·Comments Off on The server selected protocol version TLS10 is not accepted by client preferences [TLS12]

Product:

Cognos Controller 10.4.2 fix pack 2
CONTRL_UPDATE_version=CCR-AW64-ML-RTM-10.4.2200.70-0
CONTRL_UPDATE_name=IBM Cognos Controller Update
Microsoft Windows 2019 server

Issue:

Inside Cognos Controller Client, when using java menu like jobs – define or command center, you get a error.

Caused by: com.ibm.cognos.ccr.common.FatalException: Failed to launch!

When try to connect to database in DBconv program, you get a error.

org.eclipse.persistence.exceptions.DatabaseException

Internal Exception: java.sql.SQLException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: “The server selected protocol version TLS10 is not accepted by client preferences [TLS12]”. ClientConnectionId:ab713261-e19e-4c2d-b02b-8e7bce540fae DSRA0010E: SQL State = 08S01, Error Code = 0

Error Code: 0

You can check controller web error in files in folder C:\Program Files\ibm\cognos\ccr_64\fcmweb\wlp\usr\servers\fcm.web\logs

Solution:

The java version that is shipped with Cognos Controller 10.4.2 fp2, is set default to only allow TLS 1.2 communication.

Upgrade SQL server to support TLS 1.2 or as a workaround change the java 8 system to support TLS 1.0 and TLS 1.1

Go into the folder C:\Program Files\ibm\cognos\ccr_64\bin64\jre\8.0\lib\security open the file java.security in notepad++

Change this line:

jdk.tls.disabledAlgorithms=SSLv3, TLSv1, TLSv1.1, RC4, DES, MD5withRSA, DH keySize < 1024, DESede, \
EC keySize < 224, 3DES_EDE_CBC, anon, NULL, DES_CBC

To this line:

jdk.tls.disabledAlgorithms=SSLv3, RC4, DES, MD5withRSA, DH keySize < 1024, DESede, \
EC keySize < 224, 3DES_EDE_CBC, anon, NULL, DES_CBC

by remove the TLSv1, TLSv1.1 value, the system will support TLS 1, TLS 1.1.

Also go into the Controller Web folder, and do the same change.

Open C:\Program Files\ibm\cognos\ccr_64\fcmweb\jre\lib\security\java.security file in notepad++

# In some environments, certain algorithms or key lengths may be undesirable
# when using SSL/TLS. This section describes the mechanism for disabling
# algorithms during SSL/TLS security parameters negotiation, including
# protocol version negotiation, cipher suites selection, signature schemes
# selection, peer authentication and key exchange mechanisms.
#
# Disabled algorithms will not be negotiated for SSL/TLS connections, even
# if they are enabled explicitly in an application.
#
# For PKI-based peer authentication and key exchange mechanisms, this list
# of disabled algorithms will also be checked during certification path
# building and validation, including algorithms used in certificates, as
# well as revocation information such as CRLs and signed OCSP Responses.
# This is in addition to the jdk.certpath.disabledAlgorithms property above.
#
# See the specification of “jdk.certpath.disabledAlgorithms” for the
# syntax of the disabled algorithm string.
#
# Note: The algorithm restrictions do not apply to trust anchors or
# self-signed certificates.
#
# Note: This property is currently used by the JDK Reference implementation.
# It is not guaranteed to be examined and used by other implementations.
#
# Example:
# jdk.tls.disabledAlgorithms=MD5, SSLv3, DSA, RSA keySize < 2048, \
# rsa_pkcs1_sha1
jdk.tls.disabledAlgorithms=SSLv3, TLSv1, TLSv1.1, RC4, DES, MD5withRSA, DH keySize < 1024, DESede, \
EC keySize < 224, 3DES_EDE_CBC, anon, NULL, DES_CBC

# Legacy algorithms for Secure Socket Layer/Transport Layer Security (SSL/TLS)
# processing in JSSE implementation.

Save the file.

Reboot the windows controller server to make the change take effect.

More information:

https://help.mulesoft.com/s/article/Error-The-Server-Selected-Protocol-Version-TLS10-Is-Not-Accepted-by-Client-Preferences-TLS12-After-Upgrading-To-OpenJDK8u292

https://support.microsoft.com/en-us/topic/kb3135244-tls-1-2-support-for-microsoft-sql-server-e4472ef8-90a9-13c1-e4d8-44aad198cdbe

https://www.ibm.com/support/pages/connection-sql-server-fails-when-server-configured-use-tls-12-or-connecting-sql-server-2016

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

by ·Comments Off on could not set system in single user mode

Product:
Cognos Controller 10.4.2
Microsoft Windows 2019 server

Issue:

Scheduled optimization of the cognos controller database does not work from inside the cognos controller program. If you run it manually direct, then it works.

Error message: Session Manager login failed – unable to connect to the remote server

Solution:

When change Cognos Controller to use HTTPS, you must update all files to have it. The Controller Batch Service look into the file CCRBatch.exe.config at D:\Program Files\ibm\cognos\ccr_64\server folder.

Open file in notepad and edit the line WSSurl;

<appSettings>
<add key="numberOfThreads" value="1" />
<add key="dbPollingFrequency" value="20" />
<add key="WSSUrl" value="https://servername.domain.com/ibmcognos/controllerserver" />
</appSettings>

Save the file, reboot the windows server for the change to take affect.

You may need to update this files when you change to HTTPS;

D:\Program Files\ibm\cognos\ccr_64\server\CCRProxy.options

D:\Program Files\ibm\cognos\ccr_64\ControllerProxyServer\web.config

C:\Program Files\ibm\IBM Cognos Controller Local Client\ccr.exe.config

D:\Program Files\ibm\cognos\analytics\templates\ps\portal\variables_CCRWeb.xml

 

 

More Information:

https://www.ibm.com/support/pages/scheduled-database-optimization-fails-message-database-optimization-completed-error-remote-server-url-not-defined

https://www.ibm.com/support/pages/requirement-fill-details-inside-client-distribution-server-and-start-batch-service-controller-configuration-controller-1040-onwards

https://www.ibm.com/support/pages/error-contacting-user-manager-systemexception-security-error-1-when-launching-controller-caused-firewall-between-primarysecondary-servers

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

https://www.ibm.com/support/pages/controller-client-disappears-after-choosing-database-caused-restrict-access-members-built-namespace-set-false-when-security-set-cam-authentication-not-native

by ·Comments Off on controller excel add in is disabled at start of excel

Product:

Cognos Controller 10.4.2 version
Microsoft Windows 2016 server

Issue:

Every time you start data entry, you get a message that excel need to restart or that cognos controller add in has stop working and should be disabled.

Press NO on questions to disable the excel plug in.

Solution:

Upgrade to later version of Cognos Controller or disable the azure information protection plug in.

Open excel.

Go to file – options.

Select Add-ins.

Select com objects and press go.

Uncheck Microsoft Azure Information protection and click OK.

Close Excel.

 

More information:

https://www.ibm.com/support/pages/node/6838477

This issue (APAR PH38143) has been addressed in version 10.4.2.1 IF1.
It is recommended to update to that version of IBM Cognos Controller, or higher.

Please contact IBM Technical Support to request access to this specific interim fix (10.4.2.1 IF1) as it is not generally available.

https://www.ibm.com/support/pages/ibm-cognos-controller-builds-ccr-name-and-database-version

by ·Comments Off on how see events for cognos controller

Product:
Cognos Controller 10.4.2
Microsoft Windows 2019 server

Issue:

How find events for cognos controller in windows event log?

Suggested solution:

Start windows event viewer on the controller server.

Create a custom view of the application log.

 

Enter the event to zero and press OK

Enter the name to: events 0 and press OK

Then you have a custom list of event to look at.

 

More information:

How to Create Custom Views in Windows Server 2012 R2 Event Viewer

by ·Comments Off on The underlying connection was closed

Product:
Cognos Controller 10.4.2
Microsoft Windows 2019 server

Issue:
After apply HTTPS and TLS 1.2 users can not login to Cognos Controller Client.

They get a error like:

an error occurred while trying to access the server.

Solution:

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

4. Navigate to the following path: [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\v4.0.30319]

5. Right-click on v4.0.30319 and select New –> DWORD (32-bit)
  • Set the name to: SchUseStrongCrypto
  • Set the value to 00000001

More information:

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

by ·Comments Off on How export keyfile from java store

Product:
Cognos Controller 10.4.2
Microsoft Windows 2019 server

Issue:

When setup HTTPS for Cognos Controller web, need to convert the jks file to keyfile.key and cert.cer

https://www.ibm.com/support/pages/how-install-controller-web

https://www.ibm.com/support/pages/how-configure-controller-web-use-ssl-https

Solution:

If you setup HTTPS/TLS/SSL for Cognos Analytics on the same server as you have Cognos Controller installed, then you may want to use that certificate for the Controller Web installation.

(This is a summary of some of the steps – you may need to do additional steps to get HTTPS to work for you).

CA11 is setup with commands like this:

https://www.ibm.com/docs/en/cognos-analytics/11.1.0?topic=cmica-configuring-cognos-analytics-components-use-another-certificate-authority

ThirdPartyCertificateTool.bat -c -e -p NoPassWordSet -a RSA -r "request.csr" -d "CN=servername.domain.com,OU=department,O=company,L=place,C=SE" -H "servername.domain.com"

Above to create a certificate request, that you can send to your company CA. This will update the CAMKeystore file in the install_location\configuration\certs directory.

The pem file you get from the company CA, needs to be separated up into 4 files with a certificate in each. You can use Notepad++ for this.

Then import the pem files with below command.

ThirdPartyCertificateTool.bat -i -T -r root.pem -p NoPassWordSet

ThirdPartyCertificateTool.bat -i -T -r issuing.pem -p NoPassWordSet

ThirdPartyCertificateTool.bat -i -e -r server.pem -t chain.pem -p NoPassWordSet

Update Cognos Configuration to HTTPS/443 and under Certificate Authority settings, click the Use third party CA property, and set its Value to True.

Save and restart CA11 service.

Import the p7b cert into IIS, and configure its bindings to use HTTPS.

Update the IIS settings with the file CA_IIS_CONFIG.BAT.

How to properly clear Microsoft’s Internet Information Service (IIS) configurations to setup new configuration of IIS to support IBM Cognos

Change Cognos Controller Configuration to support HTTPS.

https://www.ibm.com/support/pages/how-enable-ssl-https-cognos-controller

To get a key.jks file from the CAMkeystore, start IKEYMAN.EXE as administrator.

Open the D:\Program Files\ibm\cognos\analytics\configuration\certs\CAMkeystore file.

Select personal certificate: encryption and click on export button.

Select Export Key.

Select the folder where you want to store the key.jks file.

Press OK and set a new password: changeit

Then you have a key.jks file with the content of the CAMkeystore.

Put the *.pem files you created before in c:\cert\ folder and use below commands to import them in a key.jks file for controller web.

Start a DOS prompt and change to controller web folder:

cd d:\Program Files\ibm\cognos\ccr_64\fcmweb\jre\bin

keytool -keystore key.jks -importcert -file C:\Cert\issuing.pem -alias issuing1

keytool -import -trustcacerts -alias root1 -file C:\Cert\root.pem -keystore key.jks

keytool -importkeystore -destkeystore key.jks -srckeystore C:\Cert\key.jks -srcstorepass changeit

Restart the Controller Web service. Copy key.jks file in folder D:\Program Files\ibm\cognos\ccr_64\fcmweb\jre\bin to folder ..\lib\security.

 

Open d:\Program Files\ibm\cognos\ccr_64\fcmweb\wlp\usr\servers\fcm.web\server.xml in Notepad++
Remove brackets around  “<feature>ssl-1.0</feature>”
Add below line last

</webApplication>
<keyStore id="defaultKeyStore" password="changeit" sslProtocol="SSL_TLS" />
</server>

Save the file and restart the Controller Web services.

Copy the file …jre\bin\key.jks to D:\Program Files\ibm\cognos\ccr_64\fcmweb\wlp\usr\servers\fcm.web\resources\security folder.

Edit the file d:\Program Files\IBM\cognos\ccr_64\analytics\templates\ps\portal\variables_CCRWeb.xml to use correct port number 9081.

Edit the file com.ibm.cognos.fcm.web.properties in Notepad++ to have https instead of http.

Reboot the Cognos Controller Web server.

For the Cognos Controller Web frontend you need to extract the key and certificate from your jks file.

To extract the certificate for the server use this command:

keytool -export -alias encryption -file C:\Cert\cert.crt -keystore C:\Cert\key.jks

(The alias was encryption in the CAMkeystore file, therefor is it the same in the key.jks file)

To find if a certificate for a server exist in a jks file use this command:

keytool -list -v -srckeystore C:\Cert\key.jks | findstr "server001"

 

To extract the key from a jks file, you must first convert it to a pck12 file with this command:

keytool -importkeystore -srckeystore C:\Cert\key.jks -srcstorepass changeit -srckeypass changeit -srcalias encryption -destalias encryption -destkeystore C:\Cert\identity.p12 -deststoretype PKCS12 -deststorepass changeit -destkeypass changeit

Now you have the file identity.p12 that you can copy to a server where you have openssl installed, so you can export the key with command:

"D:\Program Files\Git\mingw64\bin\openssl.exe" pkcs12 -in D:\install\cert\prod\identity.p12 -nocerts -out D:\install\cert\prod\keyfile2.key

Copy both of the above resulting files (cert.crt , keyfile2.key) to the following folder D:\Program Files\ibm\cognos\ccr_64\frontend.

Open config.js from folder D:\Program Files\ibm\cognos\ccr_64\frontend

Edit to

  • Proxies – options – target – port : 3443
  • Proxies – options – target – protocol : “https:”
  • Proxies – options – secure : false
  • expressJs – port : “9081”
  • Enable the ssl section, by uncommenting out the relevant lines

Save the file and and restart the Controller web service.

Check for errors in folder D:\Program Files\ibm\cognos\ccr_64\frontend\logs and D:\Program Files\ibm\cognos\ccr_64\fcmweb\wlp\usr\servers\fcm.web\logs

Error like: Error: error:0906D06C:PEM routines:PEM_read_bio:no start line

Then you have to do the steps again – as one of the files are wrong.

 

In case the above jks does not work – do it again but with a pfx-file.

You can export a p12 file from IKEYMAN, then only rename the p12 file to pfx. Then you have a pfx file.

cd d:\Program Files\ibm\cognos\ccr_64\fcmweb\jre\bin

keytool -keystore key.jks -importcert -file C:\Cert\fromcamkey\issuecert.arm -alias issuing2

keytool -import -trustcacerts -alias root2 -file C:\Cert\fromcamkey\rootcert.arm -keystore key.jks

keytool -importkeystore -destkeystore key.jks -srckeystore C:\Cert\fromcamkey\camkey.pfx -srcstoretype PKCS12 -srcstorepass changeit

Then copy the pfx to your computer where OPENSSL is installed, and do this commands to get out the files to frontend folder.

"D:\Program Files\Git\mingw64\bin\openssl.exe" pkcs12 -in D:\install\cert\prod\camkey.pfx -clcerts -nokeys -out D:\install\cert\prod\cert3.crt

"D:\Program Files\Git\mingw64\bin\openssl.exe" pkcs12 -in D:\install\cert\prod\camkey.pfx -nocerts -out D:\install\cert\prod\keyfile3.key

Copy the cert3.crt and keyfile3.key to the folder D:\Program Files\ibm\cognos\ccr_64\frontend on controller web server.

Update the config.js file for above new file names.

Reboot the cognos controller web server.

 

 

More information:

https://security.stackexchange.com/questions/98282/difference-between-openssl-and-keytool

Keytool is a tool that comes with Java that works with KeyStores – it can create KeyStores and manipulate keys and certificates inside them. It can also create keys and sign certificates. So it is both a key generation and a KeyStore-file-administration tool.

OpenSSL works with standard formats (PEM/CER/CRT/PKCS/etc) but does not manipulate KeyStore files. It is possible to generate a key and/or certificate with OpenSSL, and then import that key/cert into a KeyStore using keytool, but you can’t put the key/cert into the KeyStore directly using OpenSSL.

Both OpenSSL and keytool have the same purpose: generating/storing keys and certificate(s) (chaines). The thing is that Java can only work with certificates/keys contained within its keystore (JKS). Those certificates and keys are generated using the keytool library, not by using openssl.

Java keytool will always need a keystore in order to store the certificates and keys it has generated, where this is not the case for openssl. Do note that OpenSSL can also be used to create a similar container, namely PKCS12 (.p12). This is a password protected container containing keys and certificates (just like Java’s keystore). However, it’s not compatible with Java… You’d need to convert the .p12 container to .jks before your Java application will be able to work on the certificates.

https://www.ibm.com/support/pages/controller-web-windows-services-keep-shutting-down-when-using-windows-server-2019-unsupported-controller-1031-or-1040

https://www.ibm.com/support/pages/how-export-private-key-and-public-key-keystore

https://dzone.com/articles/extracting-a-private-key-from-java-keystore-jks

https://www.sslshopper.com/article-most-common-java-keytool-keystore-commands.html

https://docs.oracle.com/javase/7/docs/technotes/tools/solaris/keytool.html