Page Comparison

Table of Contents

...

Table of Contents

System Profile

System Full Name	Canadian Port State Control System
Product Owner(s)	Bill Henderson
Business Owner	Naim Nazha

...

CPSCS-INNAV web service: https://wwwapps.tc.gc.ca/Saf-Sec-Sur/4/tc-cpscs-ws/service.asmx (used for internal testing only - does not communicate with INNAV)
CPSCS-THETIS web service: https://wwwapps.tc.gc.ca/saf-sec-sur/4/cpscs-ws/service.asmx (which communicates with THETIS production site - https://portal.emsa.europa.eu/)
CPSCS-APCIS web service: https://wwwapps.tc.gc.ca/saf-sec-sur/4/cpscs-apcis-ws/apcisproxyws.asmx (which communicates with APCIS production site - https://apcis.tmou.org)

Source Code:
Dev: https://transport-canada@dev.azure.com/transport-canada/DSD-MARINE%20Support/_git/CPSCS

System

Overview

<…either point to some document or put some info / diagram here>

...

(Inspection:InspectionTombstone Method)

strSQL = 'Inspection:InspectionTombstone Method (1): VID=759742, CurrVID = 759741, SaveID = 759741'

Invalid procedure call or argument

...

Use elevated powershell to find the Guid key for this certificate in
C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\
- $Cert = Get-Item Cert:\LocalMachine\My\<Thumbprint of Certificate>
- $Cert.PrivateKey.CspKeyContainerInfo.UniqueKeyContainerName
  - <Thumbprint of Certificate> can be found from MMC: Certificates(Local Computer)->Personal->Certificates->cpscs-ws.tc.gc.ca
  - In Certificate property window, it’s at the bottom of the Details tab
  - If a key is not returned, the certificate may not have been properly installed and need to be reinstalled.
- Full path to key is C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\<key returned>
- To to grant access, run following with key returned:
  ICACLS <Full path to key> "IIS AppPool\CPSCS-Thetis:R"

Info from DOS:

Asuquo Eniang

commented Nov 26, 2019

This confirms that IIS AppPool\CPSCS-Thetis account access to the certificate key file is the issue; Here's a before and after screen shots of permissions on this folder, on dev server:

...

Asuquo Eniang

commented Nov 23, 2019

(edited)

Researching the error "The request was aborted: Could not create SSL/TLS secure channel." further, this Stack Overflow page suggests to use ICAL to register certificate with ASP.

Install Certificate in Dev LocalMachine

Instructions located here:
\\tc4s0b\groups\AFCCB\MS_APPS\CPSCS\THETIS\Certificates\March 2018
If this certificate is not installed, you'll get: Unexpected error has occured: '(91): Object reference not set to an instance of an object. when service is called.
It is possible for a certificate to exist in MMC console but missing a file entry in C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys

Find certificate path using tool FindPrivateKey.exe

Copy the tool FindPrivateKey.exe from
\\tc4s0b\groups\AFCCB\MS_APPS\CPSCS\THETIS\Certificates\March 2018 to a location on Dev server
open command prompt as administrator, navigate to location of tool.
Run command: FindPrivateKey.exe My LocalMachine -n "MarApps Production Intermediate - SHA256" -a
Copy and save the result: C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\ff85bd97ae6a76c3dcbc4925e3eb6ca3_518d76a8-4ded-49de-b4a9-d981cc355562

Use ICALS to grant IIS AppPool access to the .cer file, with path:

ICACLS C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\ff85bd97ae6a76c3dcbc4925e3eb6ca3_518d76a8-4ded-49de-b4a9-d981cc355562 /grant "IIS AppPool\CPSCS-Thetis:R"
Confirm result: Successfully processed 1 files; Failed processing 0 files

...

Asuquo Eniang

commented Nov 25, 2019

(edited)

After granting access following steps below, service was tested and passed on three servers 336, 337 and 338:

Log can be found here: \\ncrws536\wwwfiles\cpscs-ws\logs

Asuquo Eniang

commented Nov 25, 2019

The solution below failed on first try on Prod server.

FindPrivateKey.exe My LocalMachine -n "MarApps Production Intermediate - SHA256" -a

FindPrivateKey failed for the following reason:

No certificates with key 'MarApps Production Intermediate - SHA256' found in the store.

We need to verify that the certificate is properly installed.

Look under Certificates(Local Computer)->Personal->Certificates

Next we use elevated powershell to find the Guid key for this certificate in

C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\

Run:

$Cert = Get-Item Cert:\LocalMachine\My\<Thumbprint of Certificate>

$Cert.PrivateKey.CspKeyContainerInfo.UniqueKeyContainerName

Where <Thumbprint of Certificate> can be found from MMC: Certificates(Local Computer)->Personal->Certificates->cpscs-ws.tc.gc.ca

In Certificate property window, it’s at the bottom of the Details tab. See attached image.

If a key is not returned, the certificate may not have been properly installed.

Right click and delete it from the console. Install the certificate again. Instructions can be found here:

\\tc4s0b\groups\AFCCB\MS_APPS\CPSCS\THETIS\Certificates\March 2018

Once the certificate is installed, this command should be able to return a full path to the key:

FindPrivateKey.exe My LocalMachine -n "MarApps Production Intermediate - SHA256" -a

To to grant access, run following with key returned:

ICACLS <Full path to key> "IIS AppPool\CPSCS-Thetis:R"

Asuquo Eniang

commented Nov 23, 2019

@Justin Green I found a way to grant IIS AppPool access to the thetis certificate (MarApps Production...)

I tested on dev and it worked. You can find more details in the child tasks of bug 4195.

Web team would have to follow these steps to correct the issue. Step 1 can be skipped if certificate is already installed.

Install Certificate in Prod LocalMachine

Instructions located here:
\\tc4s0b\groups\AFCCB\MS_APPS\CPSCS\THETIS\Certificates\March 2018
If this certificate is not installed, you'll get: Unexpected error has occured: '(91): Object reference not set to an instance of an object. when service is called.

Find certificate path using tool FindPrivateKey.exe

Copy the tool FindPrivateKey.exe from
\\tc4s0b\groups\AFCCB\MS_APPS\CPSCS\THETIS\Certificates\March 2018 to a location on server
open command prompt as administrator, navigate to location of tool.
Run command: FindPrivateKey.exe My LocalMachine -n "MarApps Production Intermediate - SHA256" -a
Copy and save the result: C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\ff85bd97ae6a76c3dcbc4925e3eb6ca3_518d76a8-4ded-49de-b4a9-d981cc355562

Use ICALS to grant IIS AppPool access to the .cer file, with path:

ICACLS C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys\ff85bd97ae6a76c3dcbc4925e3eb6ca3_518d76a8-4ded-49de-b4a9-d981cc355562 /grant "IIS AppPool\CPSCS-Thetis":R
Confirm result: Successfully processed 1 files; Failed processing 0 files

Error: Call Sign must confirm to Basic and ITU data validation standards

...

See the attached email from APCIS support. They have mentioned :

According to the DVR for Ship Callsigns, callsigns shall be formatted according to ITU Radio Regulations Section III-Formation of call signs, in one of the following 11 formats:

Image Modified

where a=any alpha character A-Z incl., n=any digit 0-9 incl., N=only digits 2-9

Here the callsign J8QY1 is formed according to the 5-character format “aNaaN” but according to the rules the last digit should be from 2 to 9 (other than the digits 0 or 1). The last digit of the callsign J8QY1 is 1, so this callsign does not comply to DVR. This is why this callsign is not accepted by the APCIS system.

If ship call sign is unavailable or it does not comply the DVR, it is recommended to enter “n/a” into the field “Callsign” of the inspection report.

...

Asuquo Eniang

commented Sep 17, 2019

(edited)

The error is:

0 of 1 inspections successfully sent to THETIS
Inspection ID 731326: ERROR: THETIS Export Error T.IE.5: [PostInspectionResultsJson Error] Deficiencies: ISM deficiency 15150 - ISM but no deficiency marked as ISM Related (IMO: 9792022)

According to Simon:

9792022 ORIENT SKY: This error is the reason the report was submitted. It’s claiming that none of the deficiencies were marked as ISM related, which is a prerequisite to having an ISM deficiency. Deficiency 14615 has in fact been marked as ISM related.

To find this deficiency, we run the following query:

select d.DEFICIENCY_ID,

d.DEF_CODE_ID,

d.INSPECTION_ID,

d.ISM_RELATED_IND,

dc.CODE,

dc.THETIS_CD,

dc.APCIS_CD,

d.NOTES

From Deficiency d Left Join S_DEFICIENCY_CODE dc on d.def_code_id = dc.def_code_id

Where d.INSPECTION_ID = 731326 and dc.code = 14615

It returns:

We can see that the THETIS_CD column is null. To check with Bill on what the correct code should be.

...

Delete the inspections listed (in CPSCSP.WORLD database):

KOTA EKSPRES IMO 9644990

To find the proper inspection ID:

...

Inspection ID associated with St. John's is IID = 725886. Delete this one.

Global Echo IMO 9633288

To find the proper inspection ID:

SELECT

i.inspection_id,

so.english_value

FROM

inspection i,

s_office so,

tm008_port tm008

WHERE

...

We need to specify is_vetted = 0 because client wants the inspection showing in the vetting list to be deleted. An inspection only appears in the Vetting list if it has NOT been vetted (is_vetted = 0);

IID = 725968

Chiara D’Amato, IMO 9423267

SELECT

i.inspection_id,

so.english_value

FROM

inspection i,

s_office so,

tm008_port tm008

WHERE

imo_no = 9423267

AND TO_CHAR(date_of_inspection,'DD/MM/YYYY') = '24/04/2019'

AND i.inspection_port_cd = tm008.port_cd

AND tm008.office_id = so.office_id;

...

The delete script can be found at: \\tc4s0b\groups\AFCCB\MS_APPS\CPSCS\scripts; file name is CPSCS_delete_inspection.sql. Replace the "&&inspection_id" value with the appropriate values identified above. If the query works, comment out "rollback;" and and uncomment "commit;" before running a second time.

KOTA EKSPRES IMO 9644990

delete from inspection where inspection_id = 725886;

delete from deficiency_action_code where deficiency_id in (select deficiency_id from deficiency where inspection_id = 725886);

delete from deficiency_description where deficiency_id in (select deficiency_id from deficiency where inspection_id = 725886);

delete from certificates where inspection_id = 725886;

delete from deficiency where inspection_id = 725886;

delete from detention where inspection_id = 725886;

delete from inspection_area where inspection_id = 725886;

delete from operational_control where inspection_id = 725886;

delete from vessel_action_code where inspection_id = 725886;

Delete From Tm028_Xref_Inspection_Up Where Inspection_Id = 725886;

update vessel set last_inspection_id = null where last_inspection_id = 725886;

delete from inspection where inspection_id = 725886;

rollback;

-- commit;

Global Echo IMO 9633288

delete from inspection where inspection_id = 725968;

delete from deficiency_action_code where deficiency_id in (select deficiency_id from deficiency where inspection_id = 725968);

delete from deficiency_description where deficiency_id in (select deficiency_id from deficiency where inspection_id = 725968);

delete from certificates where inspection_id = 725968;

delete from deficiency where inspection_id = 725968;

delete from detention where inspection_id = 725968;

delete from inspection_area where inspection_id = 725968;

delete from operational_control where inspection_id = 725968;

delete from vessel_action_code where inspection_id = 725968;

Delete From Tm028_Xref_Inspection_Up Where Inspection_Id = 725968;

update vessel set last_inspection_id = null where last_inspection_id = 725968;

delete from inspection where inspection_id = 725968;

rollback;

-- commit;

Chiara D’Amato, IMO 9423267

delete from inspection where inspection_id = 726228;

delete from deficiency_action_code where deficiency_id in (select deficiency_id from deficiency where inspection_id = 726228);

delete from deficiency_description where deficiency_id in (select deficiency_id from deficiency where inspection_id = 726228);

delete from certificates where inspection_id = 726228;

delete from deficiency where inspection_id = 726228;

delete from detention where inspection_id = 726228;

delete from inspection_area where inspection_id = 726228;

delete from operational_control where inspection_id = 726228;

delete from vessel_action_code where inspection_id = 726228;

Delete From Tm028_Xref_Inspection_Up Where Inspection_Id = 726228;

update vessel set last_inspection_id = null where last_inspection_id = 726228;

delete from inspection where inspection_id = 726228;

rollback;

-- commit;

Inspections deleted. Contacting client (see attachments).

...

CPSCS - IM310089 - CPSCS: Server Certificate Expiring

Problem:

The SSL certificate for apcis.tmou.org which is installed on our web servers – for the CPSCS web application - will expire on June 11, 2020.

Solution:

Provide a replacement certificate before it expires:

In Dev

Create a new folder under \\tc4s0b\groups\AFCCB\MS_APPS\CPSCS\CERTIFICATES\ and store the new APCIS certificate (apcis.tmou.org.cer) in that folder, ex. July 2021.
Ensure that you can access the file when you Remote into NCRWS535
Remote into NCRWS535.
From NCRWS535, open the Certificate Manager, under Personal/Certificates, find the existing apcis.tmou.org

...

CPSCS - IM312504 - A list of the inspectors in Marine Safety (by specific role, or for all).

Problem:

Is there a way of getting a list (Excel or CV) all of the inspectors in Marine Safety with a designation of “Port State Control Officer”?

Solution:

This solution could apply to any specific type of inspector role, or for all.

The list of possible inspector roles can be found in the S_USER_ROLE table in the CPSCSP database. In the case of this ticket we were looking for the "PSCO" (Port State Control Officer) role.

Code Block

language	sql
title	Get List of Inspector Roles
collapse	true

Select * from S_USER_ROLE;

...

CPSCS - IM312526 - Remove (or modify) a deficiency. Find a deficiency in the DB from the website or client given deficiency #

Problem:

The client will give you a deficiency # that is not the same as what you need to drill for in the database to get to the same record.

" I am attempting to modify two deficiencies in the 2020-07-02 inspection report for the GOLDEN ARCUS (IMO 9743162). I am trying to delete deficiency 15150, and I am trying to remove the “ISM-related” mark from deficiency 10138. "

Solution:

Log into CPSCSWEB : https://wwwapps2.tc.gc.ca/saf-sec-sur/4/cpscs-scepc/ using the login/pw from the password file.

Click on "Vessel Search" and search the IMO# given in the ticket.

Click on the Details button:

Image Modified

Click on the Inspections button:

Image Modified

Click on the Details button:

Image Modified

Click on the Deficiencies tab:

Image Modified

Look for the deficiencies # that client stipulated and press the details button:

Image Modified

The pop-up will have the actual DB value ("DID") to query against:

Image Modified

Logged into CPSCSP.world:

Code Block

language	sql
title	Get the Deficiency
collapse	true

select * from cpscsw.deficiency t where t.Deficiency_ID = 719372;

(as per the example above)

From this point the deficiency record can be modified or deleted.

...

CPSCS - IM321375 - APCIS Export Failure: "Empty deficiency action list() for deficiency #xxxxx (IMO XXXXXXX)"

Problem:

Export failure for an inspection with deficiencies.

Image Modified

"Inspection report for the CALLISTO (IMO 9040077) failed to export to APCIS."

Solution:

The solution is based on a previous one described above.

APCIS does not allow Code 30 (“Grounds for detention") and Code 17 (“Rectify deficiency before departure”) on the same deficiency.

...

CPSCS - Server object error 'ASP 0177 : 800401f3' Server.CreateObject Failed

Problem:

When running CPSCS locally in Visual Studio you get the following error: Server object error 'ASP 0177 : 800401f3' Server.CreateObject Failed

Solution:

The solution is to open a command prompt 'run as administrator' and unregister and re-register the DLL using the following commands:

Code Block
regsvr32 -u C:\Users\graingm\source\repos\CPSCS\src\CPSCS_ROOT\CPSCS\CPSCS_Objects\cpscs6010.dll regsvr32 C:\Users\graingm\source\repos\CPSCS\src\CPSCS_ROOT\CPSCS\CPSCS_Objects\cpscs6010.dll

Then rerun the application.

CPSCS - Application is down

Problem:

CPSCS is down

Solution:

Contact the web services team. They will do the following to resolve the issue:

...

CPSCS - Application is down - EXPLAINED

Problem:

CPSCS is down

Solution:

CPSCS might have become inaccessible because the printing function will sometimes freeze up and stall the entire website. In this case, we follow the steps provided in solution, above.

...

CPSCS - Vetting Vessel Needs to be put back in the Export Queue

Problem:

ON occasion, we may need to put a record back in the APCIS Export Queue, to do so Bill H will provide the IMO NO and the Inspection ID. Make sure they are both in the select otherwise you will get several records that shouldn't be processed

...

CPSCS - DLL register Failed

Problem:

When running regsvr32 cpscs6010.dll returns error:

Error "The module "cpscs6010.dll" was loaded but the call to DllRegisterServer failed with error code 0x80004005"

...

Code Block
regsvr32 C:\Users\xxx\source\repos\CPSCS\src\CPSCS_ROOT\CPSCS\CPSCS_Objects\cpscs6010.dll

CPSCS - DLL access denied

Problem:

after registering cpscs6010.dll on the Dev server tcapps2mssdev, the CPSCS app doesn't run. It returns error: ---------------------------- Server object error 'ASP 0178 : 80070005' Server.CreateObject Access Error /LM/W3SVC/3/ROOT/SAF-SEC-SUR/4/CPSCS-SCEPC/global.asa, line 163 The call to Server.CreateObject failed while checking permissions. Access is denied to this object.

Solution:

Login to the server console on NCRWS497

Open IIS Manager

Stop the CPSCS application pool

Launch command prompt as an administrator

Run this command regsvr32 E:\wwwappsroot\saf-sec-sur\4\cpscs-scepc\CPSCS_Objects\cpscs6010.dll

Start the application pool

Test the application http://wwwapps2mssdev/saf-sec-sur/4/cpscs-scepc/sso.asp?L=E

Versions Compared

Old Version 94

New Version Current

Key

System Profile

Source Code:
Dev: https://transport-canada@dev.azure.com/transport-canada/DSD-MARINE%20Support/_git/CPSCS

System

Overview

Error: Call Sign must confirm to Basic and ITU data validation standards

Image Modified

CPSCS - IM310089 - CPSCS: Server Certificate Expiring

CPSCS - IM312504 - A list of the inspectors in Marine Safety (by specific role, or for all).

CPSCS - IM312526 - Remove (or modify) a deficiency. Find a deficiency in the DB from the website or client given deficiency #

CPSCS - IM321375 - APCIS Export Failure: "Empty deficiency action list() for deficiency #xxxxx (IMO XXXXXXX)"

CPSCS - Server object error 'ASP 0177 : 800401f3' Server.CreateObject Failed

CPSCS - Application is down

CPSCS - Application is down - EXPLAINED

CPSCS - Vetting Vessel Needs to be put back in the Export Queue

CPSCS - DLL register Failed

CPSCS - DLL access denied

Page Comparison

Versions Compared

Old Version 94

New Version Current

Key

System Profile

Source Code: Dev: https://transport-canada@dev.azure.com/transport-canada/DSD-MARINE%20Support/_git/CPSCS

System

Overview

Error: Call Sign must confirm to Basic and ITU data validation standards

Image Modified

CPSCS - IM310089 - CPSCS: Server Certificate Expiring

CPSCS - IM312504 - A list of the inspectors in Marine Safety (by specific role, or for all).

CPSCS - IM312526 - Remove (or modify) a deficiency. Find a deficiency in the DB from the website or client given deficiency #

CPSCS - IM321375 - APCIS Export Failure: "Empty deficiency action list() for deficiency #xxxxx (IMO XXXXXXX)"

CPSCS - Server object error 'ASP 0177 : 800401f3' Server.CreateObject Failed

CPSCS - Application is down

CPSCS - Application is down - EXPLAINED

CPSCS - Vetting Vessel Needs to be put back in the Export Queue

CPSCS - DLL register Failed

CPSCS - DLL access denied

Source Code:
Dev: https://transport-canada@dev.azure.com/transport-canada/DSD-MARINE%20Support/_git/CPSCS