PKI & Web Services

1. PKI & Web Services SPS Spotlight Series January 2015

2. Introduction and Purpose • Purpose of this webinar is to educate and keep the SPS User Community abreast of the functionality and ongoing changes to the core capabilities of the SPS suite of software. • We developed these learning modules that are geared toward product functionality, configuration, and maintenance for SPS users to sharpen their system administration skills and increase their overall product and system knowledge. • Follow-Up Surveys were sent to all attendees requesting suggestions for improving future webinars as well as their feedback on the value of the material presented, the instructor, and overall satisfaction of the session.

3. Overview • Enhancement Overview • Definitions • Logging in with PKI • Sybase/PD² Passwords • Updates to sql.ini files • PD² Client Information • Sybase Web Services Information • Java Keystore & Certificates • HTTP, HTTPS and Tectia • HTTPS Configuration – Server side & Client side

4. Enhancement Overview • Beginning with the release of PD² v4.2 Incr 2 SR15, the PD² 4.2 client software is now Public Key-enabled (PKE) for authentication of users to the underlying Sybase Adaptive Server Enterprise (ASE) database server. • For sites deploying SR15, individual use of a Common Access Card (CAC) to uniquely identify an SPS user should be enforced through local policy and standard operating procedures. • Non-CAC users continue to use the traditional user name/password login functionality.

5. Definitions • Sybase Web Services (aka ws) • New web services service name (ex: SPS_HD_ASE15_WS) • PD² Sybase Adaptive Service • PD² Sybase service name (ex: SPS_HD_ASE15) • Producer Port • Port used to communicate between a client (e.g., PD² application) and Web Services. • Consumer Port • Port used to communicate between the Web Services and the Sybase ASE server

6. Definitions continued • wsOwner • Sybase login name • Used in the pkidod.pbd file to communicate between client and Web Services • New Script-Aid script updates this user’s Password Expiration interval • PasswordGenerator.exe is used to update and encrypt this password in the pkidod.pbd file • saws • Sybase login name • Used to start/stop Web Services • Encrypted password is stored in SPS_<DODAAC>_WS_setenv.bat file • First change password in ASE using sp_password • Then use sps_update_pwd.bat to encrypt password

7. CAC Login Functional Steps • Insert CAC into machine. • Launch PD² application. • Select Login with CAC button. • System detects certificates associated with CAC. • CAC certificate is checked for validity to ensure that it is an X.509 certificate, is readable, and is active. • If this is the user’s first CAC login, user is prompted to enter PD² User ID/password. • System will perform the CAC self registration process. • Subsequent logins by the user will use CAC credentials and will not prompt for PD² User ID/password.

8. CAC Login Functional Steps (continued) • Once registered with CAC, PD² users will be no longer be able to use traditional PD² User ID/password. • If a user receives a new CAC, the PD² sysadmin will deregister the user and reset the user’s password so the user may re-register with the new CAC. • The new password will only be used once, to re-register the new CAC. • Users who will not use CAC based authentication for accessing PD² will continue to use their traditional User ID/password to log in to the PD² client application.

9. Traditional Login vs CAC Login Method Username password PD² Client Sybase Server Username password CAC Credentials Web Services Server PD² Client Sybase Server

10. PD² Logon Window The logon window has changed in SR15. There are now 2 ways to login to PD. The traditional User ID/password option. Logging in with CAC

11. PKI Login – CAC Registration – Database Logon Window • For users who decided to use the CAC option, the first time the Logon with CAC button is clicked, the user will be prompted to register their CAC information. • The following window will appear, where the user will enter their PD² login credentials to register as a CAC user. • Again, this is only required for first CAC login or re-registering a CAC login.

12. PKI Login – Select a Certificate Window • The Select a Certificate window will appear each time the user logs into PD² using the CAC option. • These certificates are setup by the System Administrator.

13. System Administration User Task for non-CAC user

14. System Administration User Task for CAC user • Deregister User button replaces Change Password fields for CAC users.

15. Deregistration of a user • Once the Deregister User button is clicked the following window opens. • The SA will assign the user a temporary password, which they will use when they re-register their new CAC. • Deregistered users will not be able to log in using the traditional method and must re-register as a CAC user. • MOTTO: Once you go CAC you can’t go back!

16. Deregistration of a user continued Once a user gets the new temporary password, they still cannot login to PD² using the traditional method. Any attempt to use the standard User ID/Password process with the temporary password will result in the following error message:

17. Sybase/PD² Passwords • When a PD² user registers a CAC, the PD² user automatically gets a new random password and the Sybase login’s password is set to never expire.  The PD² application will change the password periodically based on the current expiration policy divided by 2.  • Example: the password expiration policy is 60 days, then the PD² app will change the password to another random password every 30 days. • The CAC user won’t be able to change his/her own password because no one knows what the random password is, and the PD² application does not provide the change password function for CAC users. • A Sybase login with sso_role can change the CAC user’s password using sp_password function.  That will be OK as the new password is encrypted using the user’s public key and PKI would be able to pick that up. • The user will then be able to use that password for other utilities, until the PD² app changes the password again.  CAC logins are still subject to 3 failed login attempts before locked.

18. Sybase/PD² Passwords continued • Ideally, the PD² user should be registered with CAC if: • The PD² user is not shared (e.g., sysadmin). • The PD² user is only used within PD².  The PD² user should not be used for other non-PD² applications, such as external reporting tools, Integrity tools, or Script-Aid.   • Script-Aid only requires a Sybase login, not PD² login. • PKI does not affect non-PD² users, such as Doc Transfer, Archiving, Adapter, sa, MWS, etc. • For other Sybase logins that are not used for PKI (CAC associated PD² users), it’s business as usual. General note: All PD² users are Sybase users but not all Sybase users are PD² users . PKI only affects PD² users.

19. Web Services Communication Information

20. Sybase Web Services Information • When the Web Services gets installed a new folder under the \Sybase15 directory gets created. This folder name will be WS-15_0. • Subfolders include: • Bin • SPS_<DODAAC>_ASE15_WS_setenv.bat file • sps_update_pwd.bat • Encrypts the Web Services password. • Logs • commons-daemon.<datetimestamp>.log • [2014-10-14 09:25:48] [info] [ 1516] Service started in 1328 ms. • SPS_<DODAAC>_WS_https.log • SPS_<DODAAC>_WS_webservices.log • Props • SPS_<DODAAC>_ASE15_WS.properties This message usually indicates that the Web Services has started successfully.

21. Sybase Web Services Information continued • A test can be performed to verify if the Web Services is fully started. By opening a web browser and entering in the following information. • http://<DBHOST>:<PORT>/ webpage • (ex: http://12.34.567.89:8171/) • If the page is able to be displayed, web services is fully started. • Notice that we are using the Producer Port.

22. Updates to sql.ini files • On the Sybase Server, in the \Sybase15\ini directory, the sql.ini file needs to be updated to include the new Web Services information. [SPS_HD_ASE15_WS] master=NLWNSCK,12.34.567.89,8173 query=NLWNSCK, 12.34.567.89,8173 • On the PD² Client side, in the \Program Files (x86)\Sybase\ini directory, the sql.ini file needs to be updated to include the new Web Services information. [SYB_SERVER] query=TCP, 12.34.567.89,5970 master=TCP, 12.34.567.89,5970 webservice=http,12.34.567.89,8171 • Note the port values are different: Consumer on Sybase server; Producer on Client.

23. PD² Client Information • pkidod.pbd file • \Program Files (x86)\PD²\BIN • Must exist on all client machines • Must be up-to-date with the correct web services password for the wsOwnerlogin. • See KB ID: 15211 for error messages if not up-to-date • One-to-one connection • One pkidod.pbd file to one Sybase server. If users are logging into multiple servers, the file must be switched accordingly

24. PD² Client Information continued • Web Services Connection User Password Change Utility • If the wsOwner’s password ever has to be updated in Sybase Central, the PasswordGenerator.exe will need to be run. • Updates & encrypts the wsOwner’s password value in the pkidod.pbd file • Creates a back-up copy of the pkidod.pbd file each time it is run • New pkidod.pbd file must then be transferred to every client machine, failure to do so will cause issues when trying to login with CAC.

25. Java Keystore & Certificates • The Sybase Web Services uses the Java Key Store (JKS) technology to access the server certificate for HTTPS. The server certificate is an X.509 certificate and has public and private key pair stored in the JKS-based keystore. The server certificate and the JKS-based keystore generation processes are dependent on the certificate tools employed by the site. • The keystore must meet the following criteria: • Compatible with Java 7 • The keystorehas a keystore password (storepass). This password is required when configuring the Sybase Web Services to access the keystore. • The keystorehas a password for the server key (keypass). This password is required when configuring the Sybase Web Services to access the private key of the server certificate in the keystore. • The server key entry is stored in the keystore under the “ws” alias (without quotes; both letters are in lower-case). • The CA certificates (root and intermediate) in the certificate chain of the server certificate are stored as trusted certificates in the keystore.

26. Java Keystore Knowledge Base articles • KB ID: 15187 JKS-based Keystore for Sybase Web Services FAQs • KB ID: 15221 - What is a keystore for Sybase Web Services? • KB ID: 15222 - What is the process of generating the JKS-based keystore? • KB ID: 15223 - Is there an example for generating the JKS-based keystore on a Windows-based system? • KB ID: 15224 - Is there an example for generating the JKS-based keystore on a UNIX system? • KB ID: 15225 - Generating the JKS-based keystore on a Windows-based system. • KB ID: 15226 - Generating the JKS-based keystore on a UNIX system • KB ID: 15211 • Error #1: Error 7: Unresolvable external u_dod_pki_vars when linking reference at line 21 in function uf_ztrieve_pwd of object u_dsk_pki_authentication. • Error #2: Error 2: Unable to establish communications with web services error message.

27. HTTP, HTTPS and Tectia • HTTP is simpler to configure, but it does not encrypt communication between the Sybase Web Services and PD² client. Encryption may be done by other tools, such as SSH Tectia. • HTTPS is more secure, encrypting communication between the Sybase Web Services and PD² client. However, additional preparations are required on both database server and PD² client machines.

28. Traditional Login vs CAC Login Method with HTTPS Username password PD² Client Sybase Server HTTPS Username password Web Services Server CAC Credentials PD² Client Sybase Server

29. HTTPS Configuration – Server side • Details are in Section 6 of the Sybase ASE 15.7 SP102 Web Services Configuration Guide. • The keystore file is named <WSNAME>.keystore, where <WSNAME> is the Web Services name. Ex: SPS_DODAAC_ASE15_WS.keystore. • The keystore file is located \Sybase15\WS-15_0\props. • The Sybase Web Services is configured to use a server certificate that is properly stored on the Sybase server. • \Sybase15\Shared\JavaKeyStore • Ex: 77832260_wi2hddb01x64.caci.com • The Sybase Web Services HTTPS Configuration Utility is needed to configure the Sybase Web Services for HTTPS support. This utility updates the Web Services properties file with HTTPS information. • SPS_<DODAAC>_ASE15_WS.properties file gets updated with keystore information at the bottom of the file.

30. HTTPS Configuration – Client side • Section 6.3 Configure Client Machine Certificate Store in the guide. • The client machine where the PD² Client is installed has the proper Certificate Authority (CA) certificate imported in order to validate the server certificate. • When the PD² Client initiates the communication with the Sybase Web Services using the HTTPS protocol, the server certificate must be validated. If the server certificate cannot be validated, the “Certificate Error” error occurs when using the PKI functionality in PD². To ensure the success of the validation process, the client machine must import the root CA certificate that is included in the certificate chain of the server certificate. • In the sql.ini file, the protocol needs to be changed from http to https and the port number must be updated to the HTTPS port if it is different from the original HTTP port. • From: webservice=http,12.34.567.89,8171 • To: webservice=https,12.34.567.89,443

31. Questions?

32. Q&A • Where are the Web services located and if they are on a different server, what happens if it goes down? • The Web Services are setup on the Sybase ASE server and run as a separate service on that server. • Will a user need to perform the registration on each client they use? • No, it is not necessary for a user to register more than once. The registration occurs on the server and so long as the user connects to the same server they can use CAC on other local client machines. • Is the process the same for users accessing a DB via Citrix? • Accessing through Citrix is a different process layer, essentially connecting into PD² only differs. The configuration or interaction with Citrix is separate. • Will there be any additional work needed for dual persona users? • The configuration of SPS and Sybase Web Services was not established for dual persona. These were not tested during development. The system will identify any certificates on the CAC. It may be necessary to coordinate with your internal IT staff. • Where can I find the 'Knowledge Base'? • This site is the SPS Knowledge Base and is the same site which you accessed to register for this Spotlight Series Webinar. You can access at: https://spssite.caci.com

33. Q&A • Will you be able to select a different database from the login screen? • No, there is not a choice or display to choose the database for the user. They must still alter the pddod.ini file to point to the appropriate database prior to launching PD². • What if the system administrator has access to other databases? Would the traditional sysadmin login continue to work? • Yes, the access via a user account is maintained, provided it remains standard. Meaning, it is recommended to refrain from CAC enabling Administrator accounts. • Have there been situations where the web services service goes down or has issues authenticating? If this happens will user/password accounts work? • If the Sybase Web Services service goes down, then CAC users will be unable to login to PD². • For Solaris servers, are the changes the obvious ones, or are there more substantial changes? • In general the updates are similar to other platforms, generally speaking updates to the "interfaces" files. • Is there any reason to actually change the wsOwner password? • If the site’s policy dictates that Sybase server user passwords must be updated at a given interval then the wsOwner’s password would have to be updated accordingly.

34. Q&A • Can we be provided a script that would update the wsOwner’s password automatically? • The PasswordGenerator.exe file will update the pkidod.pbd file, which exists on all SR15 client machines, with the new wsOwner’spassword. • So, in the future, users do not have to change passwords every 60 days, but we will have to upload a new wsOwner password file to each station if our policy is to change the wsOwner password every 60 days? • That is correct. User passwords will never need to be updated, but the wsOwner’s password could potentially need to be changed every 60 days (depending on site’s policy). The wsOwner’s password will have to be update in the pkidod.pbd file and that file will have to be distributed to each client machine. • If the database server's IP address and/or server name changes, does this require a new certificate to be generated for the server? • Certificates are tied to Server information, so if the IP address and/or server name changes, then a new certificate will have to be generated. • Does this mean that all PKI users will be unable to access PD² until the new certificate is created by DISA, and then installed on the server? • Yes. Until the new certificates are created, CAC users will be unable to access PD². • Is there an estimated schedule of deployment yet for SR15? • This would be determined by the service or command. CACI does not have this information or control the deployment.

35. Q&A • How are name changes handled w/CAC registration? • CAC registration is tied to the PD² Login user id. If there is a name change, the deregistration and reregistration process might need to be implemented if the user is issued a new CAC card associated with the name change. •  We have 2 databases on the same Sybase Server. Is it possible to log into one database with CAC and other with traditional user id/password? • Yes, it is possible to login into one database using CAC and login into the other using the traditional method. Updating the pddod.ini file is the only change that is needed. • Will CAC logon work on a terminal server? • This functionality is not validated with terminal server environments until SR16. While it could potentially work with SR15, sites should proceed with caution. • The date on the CAC user screen is greyed out, does it pre-populate based on the user's certificates? • Yes, all the information on that screen is pulled from the certificates identified on the user's CAC, dates and other coded information. It is not editable.

36. Q&A • Can you use the same CAC to log into multiple PD² databases? • Yes, you can use 1 CAC to log into multiple PD² databases. • What happens to the PKI certificate if a user receives a new CAC card (lost or renewal)? • The certificate will remain on the user’s machine and is unaffected by the CAC change; however, the System Administrator would need to edit the PD² user account, to deregister and reregister the user’s CAC. • How does the user determine which certificate to choose when logging in? • Typically there would only be 1 certificate on a user’s machine; however, if there are multiple certificates, it would be up to the site to inform users which Certificate to choose. • Will the user's PKI certificate still be valid with the new ID card? • Yes, the certificates are not tied directly to the CAC. The registration process ties the pduser to the certificate. • Whatis the projected upgrade timeline for sites that have been consolidated? • This is not something that CACI currently handles.

37. Q&A • Could you use the same user for multiple databases?  Understanding that if the user id is CAC enabled on one database, it would be on all databases. • A unique PD user id must exist for each database on a single Sybase ASE server, but an individual could access different PD² databases using the same CAC card, as long as each user id is registered with the CAC. • Will we have to update the pddod.ini manually on each client, or is this done by the updater? • The pddod.ini file does not have to be updated, but the sql.ini file needs to be updated on each client. This update is done by the Client Upgrade Installer.  The admin should verify that both INI files are indeed up-to-date and accurate. • After a user goes CAC can their name be changed? i.e. if a user gets married and her last name gets changed. • Yes. The name change would be done in the PD² sysadmin--> User task. The PD² user id would remain the same. The name change will have no effect on PKI or the CAC login. If the name change includes the user being issued a new CAC, the deregistration and reregistration process will need to be implemented. • Are there any additional instructions or guidance for Citrix or Terminal Service environments? • This functionality is not validated with terminal server environments until SR16. While it could potentially work with SR15, sites should proceed with caution.

38. Q&A • Once you set up as a CAC user in PD² will you automatically be a CAC user in your unit’s PD² test database? • The CAC setup occurs per Sybase ASE instance/web service. Therefore if the test database resides on the same server, it is possible to setup the user to gain access with their CAC. Though they would need unique PD² user accounts. • Will the new login screen be seen by all users even if the site is not set up for CAC login? If so, what happens if the user tries to login with the CAC ? • Yes, the new login screen will be seen by all users, whether or not they are CAC enabled. If a non CAC user clicks the Login with CAC button, an error message is generated.