StarQuest: keeping data in motion

StarQuest Technical Documents

SSL/TLS Hints for StarSQL and StarPipes on Windows


Last Update: 19 May 2022
Product: StarSQL, StarPipes
Version: 5.x, 6.x
Article ID: SQV00SQ064

Abstract

This technical document provides details and hints regarding the SSL (Secure Sockets Layer) implementation used by StarSQL for Windows and StarPipes for Windows. Both products use Microsoft Secure Channel (Schannel), a standard part of the Windows operating system that facilitates the use of Secure Sockets Layer (SSL) and/or Transport Layer Security (TLS) encryption.

Much of the discussion below references StarSQL, but it is also applicable to StarPipes. StarSQL functions as an SSL client, talking to an SSL server implementation running on the DB2 host computer (or to a StarPipes server); StarPipes can act as either a client to the DB2 host or as a server for StarSQL or other DRDA clients (Application Requester) such as StarSQL for Java, the IBM DB2 ODBC driver, or the IBM JCC JDBC driver. Two copies of StarPipes can also be used (where both act as both client and server) to provide SSL communication without changes to existing clients and hosts.

Information on configuring SSL on the host computer can be found in the Host Preparation chapter of the StarSQL for Windows User Guide (PDF); refer to the subsections Configuring SSL for DB2 for z/OS, Configuring SSL for DB2 for i, and Configuring SSL for DB2 for LUW.

The StarPipes Help (CHM) contains similar host configuration information, plus detailed information on requesting and installing server and client certificates used by StarPipes.

See below for detailed information on the following topics:

Solution

Configuring StarSQL for SSL

To create a StarSQL ODBC data source utilizing SSL, select SSL/TLS from the Network Options drop-down menu and enter the appropriate host SSL port on the Network panel. Use the Test Connection button to verify the connection is working.


If you are configuring StarSQL with a DSN-less connection string rather than an ODBC data source, a typical connection string would look like:

"Driver={StarSQL (64-bit)};Server=MYDB;NetLib=SQSSL.DLL; HostName=myhost;Port=448;PkgColID=STARSQL"

Restricting the StarPipes listener to TLS 1.2

To restrict the StarPipes SSL listener to TLS 1.2 protocol:

  1. Create a registry key HKEY_LOCAL_MACHINE\SOFTWARE\StarQuest\SSA\Server.
  2. Create a DWORD entry SchannelEnabledProtocols set to 0xC00.
  3. Restart the StarPipes service.

Requiring the Certificate Authority (CA) certificate

With the goal of implementing easy traffic encryption out-of-the box, StarSQL/Window's default configuration does not require importing the host's Certificate Authority certificate into the local certificate store, and will accept the server certificate received from the host during initial negotiations.

However, many users wish to configure SSL to require importing the CA certificate (or the use of a public certificate authority) to avoid exposure to a possible man-in-the-middle attack. This section describes how to accomplish this using the following steps:

Configure StarSQL to require the presence of the CA certificate

Use Regedit to create the registry key HKEY_LOCAL_MACHINE\SOFTWARE\StarQuest\SSA\Client

Create a DWORD value SchannelFlags and set it to the hex value 0xA34. See below for an explanation of this value.

After configuring the registry value, StarSQL connections will fail with the following expected error:

[StarSQL][StarSQL ODBC Driver]Unexpected Windows Sockets error 2146893019

and the following Schannel errors will appear in the System Event Log:

The following fatal alert was generated: 48. The internal error state is 552.

The certificate received from the remote server was issued by an untrusted certificate authority. Because of this, none of the data contained in the certificate can be validated. The SSL connection request has failed. The attached data contains the server certificate.

Exporting the CA certificate (DB2 for i)

To configure a System i host system to use the Secure Sockets Layer (SSL) protocol you must have the following components:

  • Digital Certificate Manager - option 34 of 5722-SS1 (v5r4), 5761-SS1 (6.1), or 5770-SS1(7.x)
  • TCP/IP Connectivity Utilities - 5722-TC1(v5r4), 5761-TC1 (6.1), or 5770-SS1 (7.x)
  • IBM HTTP Server - 5722-DG1 (v5r4), 5761-DG1 (6.1) or 5770-DG1 (7.x)

Following are general procedures for configuring SSL on the IBM i host. Refer to your IBM documentation for details..

  1. Start the Admin HTTP instance. To verify that it is running, enter WRKACTJOB JOB(ADMIN). If it is not running, start it with STRTCPSVR SERVER(*HTTP) HTTPSVR(*ADMIN).
  2. Use a browser and the URL https://myas400:2001 to connect to the Digital Certificate Manager. On i 6.1 and later, this URL will redirect you to IBM Navigator for i, running on port 2005; from there, select IBM i Tasks Page to see the previous version of the 2001 port tasks, which includes the Digital Certificate Manager. You can also connect directly to DCM at http://myas400:2001/QIBM/ICSS/Cert/Admin/qycucm1.ndm/main0.
  3. Create a local Certificate Authority or obtain a certificate from a public Internet Certificate Authority.
  4. Create a *SYSTEM certificate store.
  5. Use Manage Applications to assign a server certificate to the OS/400 DDM/DRDA server.
  6. Select Install Local CA Certificate on Your PC from the left column of tasks. You may need to return to the main IBM Navigator for i page and re-enter DCM before Install Local CA Certificate to your PC is visible.
  7. Select Copy and paste certificate; this will display the CA certificate in Base64-encoded ASCII data format. Select the contents of the certificate (all of the text from -----BEGIN CERTIFICATE----- through -----END CERTIFICATE-----) and save it in a text file.

Exporting the CA certificate (DB2 for LUW)

Assuming that the GSK certificate store has been created with the following commands:
C> cd C:\Program Files\ibm\gsk8\bin\
C> gsk8capicmd_64 -keydb -create -db "mydbserver.kdb" -pw "mypassword" -stash
C> gsk8capicmd_64 -cert -create -db "mydbserver.kdb" -pw "mypassword " -label "SelfSigned" -dn "CN=myhost.mydomain.com,O=MyCompany,OU=CustomerSupport,L=California,ST=ON,C=CA"

Then export the certificate to a text file using:
C> gsk8capicmd_64 -cert -extract -db "mydbserver.kdb" -pw "mypassword" -label "SelfSigned" -target "MYHOSTserver.arm" -format ascii -fips

Exporting the CA certificate (DB2 for z/OS)

To generate and export a CA certificate in RACF, use the RACDCERT GENCERT and EXPORT commands with the CERTAUTH option:

RACDCERT CERTAUTH GENCERT etc.
RACDCERT CERTAUTH EXPORT etc.

Copy the resulting ASCII file to the Windows system.

For more information, see Chapter 11.2 Protecting data through DB2 SSL with digital certification of the IBM Redbook Security Functions of IBM DB2 10 for z/OS SG24-7959.

 

Importing the CA certificate into the Windows certificate store

Depending on how you plan to use StarSQL, you may choose to import the certificate into the Trusted Root Certificate Authorities section of the certificate store of either the Current User, Local Computer, or a Remote Computer. For this example, we will assume that StarSQL is being used by a service such as SQDR and use the Local Computer store.

  1. Start mmc (Microsoft Management Console) by typing mmc in a command window, in the Search programs and files box, or the Run.. box.
  2. On the File menu, click Add/Remove Snap-in.
  3. Under Available snap-ins, double-click Certificates.
  4. Select Computer account, and then click Next.
  5. To manage certificates for the local computer, select Local computer, and then click Finish.

  1. Expand the Certificates tree.
  2. Select Trusted Root Certificate Authorities, right click and select All Tasks/Import...


  1. This will start the Certificate Import Wizard. Click Next. On the File to Import screen, use the Browse button to identify the text file containing the CA certificate exported from the host. If the filename includes an extension other than one of one of the extensions expected by the MMC certificate plugin, select All Files.

  1. Click Next. Accept the default of Place all certificates in the following store Trusted Root Certificate Authorities and click Next.


Using TLS 1.2 from Windows Server 2008R2/Windows 7 client

Issue:
Connecting from a Windows Server 2008R2/Windows 7 client to a host that requires TLS 1.2 may fail with the following error:

szSqlState = "08S01", *pfNativeError = 0, *pcbErrorMsg = 148, *ColumnNumber = -1, *RowNumber = -1
MessageText = "[StarSQL][StarSQL ODBC Driver]Unexpected Windows Sockets error 2146893018."

This is because TLS 1.2 is disabled by default in Win7/Server2008R2; refer to the chart in the Microsoft document Solving the TLS 1.0 Problem, 2nd Edition.


Solution:

To enable TLS 1.2 in Win7/Server2008R2, use regedit to create the registry entry:

Registry location: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client
DWORD name: DisabledByDefault
DWORD value: 0

For more information, see the Microsoft document Update to enable TLS 1.1 and TLS 1.2 as default secure protocols in WinHTTP in Windows.

Issues connecting to IBM i 7.4 from older Windows systems

Issue: Connecting from Windows Server 2012R2/Windows 8.1 or earlier to an IBM i host running i 7.4 fails with the following error:

szSqlState = "08S01", *pfNativeError = 0, *pcbErrorMsg = 148, *ColumnNumber = -1, *RowNumber = -1
MessageText = "[StarSQL][StarSQL ODBC Driver]Unexpected Windows Sockets error 2146893018."

This error occurs because the list of enabled Cipher Suites in IBM i 7.4 has been reduced to a very limited set of 7 (compared to the 29 that were enabled in IBM i 7.3), none of which are available on Windows systems older than Windows 2016.

Solution:

  • Use Windows Server 2016, Server 2019, or Windows 10.

OR

  • Enable cipher suite RSA_AES_128_CBC_SHA256 on i 7.4. This has to be done both at the OS level (with WRKSYSVAL) and at the LIC (Licensed Internal Code) level using SST..

At the OS level:

use WRKSYSVAL to:
Change QSSLCSLCTL from *OPSYS to *USRDFN
Add *RSA_AES_128_CBC_SHA256 to the list in QSSLCSL

Note that this enables the deprecated cipher suite for all applications; if you wish to restrict the use of RSA_AES_128_CBC_SHA256 to only the DDM/DRDA service, use Digital Certificate Manager to modify the application definitions of all applications except DDM/DRDA service. For more information, see Cipher Suite Configuration in the IBM i 7.4 documentation.

At the LIC level:

Use SST (System Service Tools) to run TLSCONFIG and add RSA_AES_128_CBC_SHA256 to the list of enabled Cipher Suites:

  1. STRSST
    logon as QSECOFR/<password>
    Note that the SST QSECOFR user is upper case and the password may differ than the OS QSECOFR user. Resetting the password of the SST user may involve halting the system and entering Dedicated Service Tools (DST).
  2. Select Display/Alter/Dump.
  3. Select Display/Alter storage.
  4. Select Licensed Internal Code (LIC) data.
  5. Select Advanced analysis. (You must page down to see this option.)
  6. Page down until you find the TLSCONFIG option. Then, place a 1 (Select) next to the option and
  7. Press Enter. You are now on the Specify Advanced Analysis Options window. The command shows as TLSCONFIG.
  8. Enter '-h' to display the help.
  9. Enter: -eligibleDefaultCipherSuites:YF,YG,YB,YC,YD,YE,YH,3C
    3C is the code for RSA_AES_128_CBC_SHA256.

It may also be necessary to restart the DRDA/DDM server:
ENDTCPSVR *DDM
STRTCPSVR *DDM

In addition, IBM i 7.4 accepts only TLS 1.2 (and 1.3) connections, so Win7/Server2008R2 clients will need to enable TLS 1.2 as described above .

Explanation of the suggested SchannelFlags value 0xA34

0xA34 sets the following flags:
SCH_CRED_AUTO_CRED_VALIDATION
SCH_CRED_IGNORE_NO_REVOCATION_CHECK
SCH_CRED_NO_DEFAULT_CREDS
SCH_CRED_NO_SERVERNAME_CHECK
SCH_CRED_REVOCATION_CHECK_CHAIN

The default SchannelFlags value (subject to change in future versions) used by StarSQL and StarPipes is:
SCH_CRED_NO_DEFAULT_CREDS
SCH_CRED_MANUAL_CRED_VALIDATION

Appendix: Registry keys for overriding SCHANNEL protocol settings

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\StarQuest\SSA]

[HKEY_LOCAL_MACHINE\SOFTWARE\StarQuest\SSA\Client]
"SchannelEnabledProtocols"=dword:00000000
"SchannelFlags"=dword:00000000
"SchannelMinimumCipherStrength"=dword:00000000
"SchannelMaximumCipherStrength"=dword:00000000
"SchannelSessionLifespan"=dword:00000000

[HKEY_LOCAL_MACHINE\SOFTWARE\StarQuest\SSA\Server]
"SchannelEnabledProtocols"=dword:00000000
"SchannelFlags"=dword:00000000
"SchannelMinimumCipherStrength"=dword:00000000
"SchannelMaximumCipherStrength"=dword:00000000
"SchannelSessionLifespan"=dword:00000000

These values are optional and should only be entered into the registry to completely override value(s) in the internal SCHANNEL_CRED structure which controls the parameters for the SSL session. These values are applicable on the client and server side of the connection.

EnabledProtocols

Optional. A DWORD that contains a bit string that represents the protocols supported by connections made with credentials acquired by using this structure. If this member is zero, Schannel selects the protocol.

The global system registry settings take precedence over this value. For example, if SSL3 is disabled in the registry, it cannot be enabled using this member.

This member can contain any of the following flags.

SP_PROT_PCT1_SERVER 0x00000001
Private Communications Technology 1.0 server side. (Obsolete.)

SP_PROT_PCT1_CLIENT 0x00000002
Private Communications Technology 1.0 client side. (Obsolete.)

SP_PROT_SSL2_SERVER 0x00000004
Secure Sockets Layer 2.0 server side. Superseded by SP_PROT_TLS1_SERVER.

Important: Secure Sockets Layer 2.0 and Transport Layer Security 1.2 flags are mutually exclusive.

SP_PROT_SSL2_CLIENT 0x00000008
Secure Sockets Layer 2.0 client side. Superseded by SP_PROT_TLS1_CLIENT.

Important: Secure Sockets Layer 2.0 and Transport Layer Security 1.2 flags
are mutually exclusive.

SP_PROT_SSL3_SERVER 0x00000010
Secure Sockets Layer 3.0 server side.

SP_PROT_SSL3_CLIENT 0x00000020
Secure Sockets Layer 3.0 client side.

SP_PROT_TLS1_SERVER 0x00000040
Transport Layer Security 1.0 server side.

SP_PROT_TLS1_CLIENT 0x00000080
Transport Layer Security 1.0 client side.

SP_PROT_TLS1_0_SERVER SP_PROT_TLS1_SERVER
Transport Layer Security 1.0 server side.

SP_PROT_TLS1_0_CLIENT SP_PROT_TLS1_CLIENT
Transport Layer Security 1.0 client side.

SP_PROT_TLS1_1_SERVER 0x00000100
Transport Layer Security 1.1 server side.

SP_PROT_TLS1_1_CLIENT 0x00000200
Transport Layer Security 1.1 client side.

SP_PROT_TLS1_2_SERVER 0x00000400
Transport Layer Security 1.2 server side.

Important: Secure Sockets Layer 2.0 and Transport Layer Security 1.2 flags are mutually exclusive.

SP_PROT_TLS1_2_CLIENT 0x00000800
Transport Layer Security 1.2 client side.

Important: Secure Sockets Layer 2.0 and Transport Layer Security 1.2 flags
are mutually exclusive.

SchannelFlags

Contains bit flags that control the behavior of Schannel. This member can be zero or a combination of the following values.

SCH_CRED_AUTO_CRED_VALIDATION 0x00000020
SCH_CRED_CACHE_ONLY_URL_RETRIEVAL_ON_CREATE 0x00020000
SCH_DISABLE_RECONNECTS 0x00000080
SCH_CRED_IGNORE_NO_REVOCATION_CHECK 0x00000800
SCH_CRED_IGNORE_REVOCATION_OFFLINE 0x00001000
SCH_CRED_MANUAL_CRED_VALIDATION 0x00000008
SCH_CRED_NO_DEFAULT_CREDS 0x00000010
SCH_CRED_NO_SERVERNAME_CHECK 0x00000004
SCH_CRED_NO_SYSTEM_MAPPER 0x00000002
SCH_CRED_REVOCATION_CHECK_CHAIN 0x00000200
SCH_CRED_REVOCATION_CHECK_CHAIN_EXCLUDE_ROOT 0x00000400
SCH_CRED_REVOCATION_CHECK_END_CERT 0x00000100
SCH_CRED_USE_DEFAULT_CREDS 0x00000040
SCH_SEND_AUX_RECORD 0x00200000
SCH_SEND_ROOT_CERT 0x00040000

See the Microsoft MSDN SCHANNEL_CRED documentation for details.

SchannelMinimumCipherStrength

Minimum bulk encryption cipher strength, in bits, allowed for connections.

If this member is zero, Schannel uses the system default. If this member is –1, only the SSL3/TLS MAC–only cipher suites (also known as NULL cipher) are enabled.

SchannelMaximumCipherStrength

Maximum bulk encryption cipher strength, in bits, allowed for connections.

If this member is zero, Schannel uses the system default.

If this member is –1, only the SSL3/TLS MAC–only cipher suites (also known as NULL cipher) are enabled. In this case, dwMinimumCipherStrength must be set to –1.

SchannelSessionLifespan

The number of milliseconds that Schannel keeps the session in its session cache. After this time has passed, any new connections between the client and the server require a new Schannel session. Set the value of this member to zero to use the default value of 36000000 milliseconds (ten hours).