SAP SQL Anywhere
Page tree
Skip to end of metadata
Go to start of metadata

The purpose of this page is to describe the types of certificates available in SQL Anywhere and provides examples for generating and using certificates.

Certificates are the files that store the public and private keys that are used to create the two halves of a secure link. There are four main components that are stored in a certificate:

  1. The owner’s identifying information (name, address, and other biographical information).
  2. The owner’s public key.
  3. The expiry date.
  4. The digital signature. This acts like a real signature, which proves that this is the owner’s proper key and prevents modification by attackers.

A digital signature provides a means to detect whether a certificate has been altered. A digital signature is a cryptographic operation created by calculating a value, called a message digest, from the identity information and the public key.

A message digest is a calculated bit-value designed to change if any part of the certificate data changes. The algorithm used to calculate the message digest is known to all users of the certificate. The correct value is encrypted with the private key for the certificate. Since all certificate users can calculate the message digest value, detecting alteration is simply a matter of calculating the message digest value and decrypting the value in the certificate using the public key. Differing values indicate that the certificate has been altered.

SQL Anywhere has support for X.509 certificates. X.509 is a Public Key Infrastructure (PKI) specification for distributing certificates. It is highly recommended that you generate X.509 compliant certificates using the new certificate generation utilities included with SQL Anywhere 10.0.1 and later. Older versions of SQL Anywhere should continue to use the older certificate generation utilities.

When you generate a certificate, you must choose the encryption method used to generate the public and private keys. There are two types of encryption algorithms for communication supported by SQL Anywhere: Rivest, Shamir, Adleman (RSA), and Elliptic Curve Cryptography (ECC)*. RSA is included with the standard package in SQL Anywhere 10, but is an add-on component in earlier versions. ECC must be purchased as a separately licensable component in all versions of SQL Anywhere. There is also an add-on version of RSA that is compliant with the Federal Information Processing Standards (FIPS) that are used for United States and Canadian government applications.

* ECC is no longer offered as an encryption type in SQL Anywhere 16 and up.

Authentication is the process of proving your identity to someone else. From a distributed network point of view, it is almost too easy to masquerade (also known as spoofing) as someone else. To ensure that you are communicating to the correct party, a certificate authority (CA) is used to digitally sign (create the digital signature for) the certificate. The CA is responsible for ensuring the legitimacy of the certificate and the owner. All communicating parties must trust that the CA is legitimate and that it only digitally signs legitimate certificates. The CA is also ultimately responsible for ensuring that the party using the certificate is who they say they are.

A chain of trust can be created by using multiple CAs. One CA can authorize lower-tier CAs, which in turn can authorize even more lower-tier CAs. This creates a chain of CAs, each of which must be trusted by all communicating parties. Ultimately, a top-tier CA (known as a root authority) must be responsible for all of the authorities that exist below it. The root authority creates a root certificate, which is self-signed. (Self-signed in this case means that there are no higher CAs that have signed the certificate and that the chain of trust stops at this authority.)

SQL Anywhere has many options for the type of PKI that can be used for secure communication. An examination of the files that are generated by SQL Anywhere can help you understand these options (Fig. 1):

  • Public Certificate  The certificate that is distributed to connecting clients. It contains the information that up to this point has been considered “the certificate.” It contains the public key for the server, along with all of the signatures from all of the associated CAs in the chain of trust.
  • Private Key File  The matching private key that goes with the public certificate. This should be backed up to a secure location and must not be given out to the public under any circumstances. This file should be protected by a strong password consisting of a variety of alpha-numeric characters and symbols.
  • Server Identity  The concatenation of the Public Certificate and the Private Key that is used to start the server.

All SQL Anywhere certificates are generated with a default of Privacy-Enhanced Mail (PEM) encoding. If you require a Distinguished Encoding Rules (DER) encoding, you can alter the encoding certificate using the viewcert utility.

To DER encode the file (with the same password):

viewcert server.pem -d -o server.der

To PEM encode the file (with the same password):

viewcert server.der -p -o server.pem

To DER encode the file (with a new password):

viewcert -ip oldpassword server.pem -d -op newpassword -o server.der

To PEM encode the file (with a new password):

viewcert -ip oldpassword server.pem -p -op newpassword -o server.pem

There are a few questions that need to be answered before establishing a plan for a CA chain of trust. Here are some questions that you should consider:

  • Can you guarantee that the private key will be protected in a secure location?
  • Are there any industry regulations that apply to the software you are building?
  • Are you communicating across the Internet? Are you operating solely on a corporate LAN?

The answers to these questions determine whether you should generate your own certificates using the tools included with SQL Anywhere or utilize a third-party solution to act as your root CA. The higher the risk of exposure, the greater the reason to utilize a third-party CA.

There are many advantages to using a third-party CA. The first advantage is that it adds confidence in the communication system because many other people also trust the third-party.

The second advantage is that a third-party CA has the facilities to maintain and protect all of the keys it distributes, including the master root private key. This service is usually associated with a fee that is determined by the third-party vendor.

These parameters are particularly important when using a third-party CA. For example, it is not sufficient to trust a server simply because it is been signed by a particular CA since a legitimate competitor may also use that CA. By stating the expected values for these fields in the client options, you can guarantee that you are always connecting to the correct server.

Note that the values for these fields are specified when the certificate is generated:

certificate_name“Common Name”
certificate_company“Organization”
certificate_unit“Organizational Unit”

Note: In version 10.0.1 the command-line utilities were changed slightly to give more options to users for X.509 compliance. Only the version 10.0.1 and later commands are used in the examples below.

This is the simplest PKI structure, where the issuer of the certificate attests to its validity. In this scheme, there are no higher CAs to validate the validity of the certificate. This solution is appropriate when the database server can guarantee that the private key will be protected appropriately, and when the communication is happening solely over a corporate LAN within the confines of a firewall.

> createcert
SQL Anywhere X.509 Certificate Generator Version 16.0.0.2052
Warning: The certificate will not be compatible with older versions
of the software including version 12.0.1 prior to build 3994 and version 16.0
prior to build 1691. Use the -3des switch if you require compatibility.

Enter RSA key length (512-16384): 2048
Generating key pair...
Country Code: CA
State/Province: ON
Locality: Waterloo
Organization: SAP SE
Organizational Unit: Active Global Support
Common Name: testserver.sap.com
Enter file path of signer's certificate: [ENTER]
Certificate will be a self-signed root
Serial number [generate GUID]: [ENTER]
Generated serial number: 32addde9066e4672bc1f4929f3a52441
Certificate valid for how many years (1-100): 30
Certificate Authority (Y/N) [N]: [ENTER]
1. Digital Signature
2. Nonrepudiation
3. Key Encipherment
4. Data Encipherment
5. Key Agreement
6. Certificate Signing
7. CRL Signing
8. Encipher Only
9. Decipher Only
Key Usage [1,3,4,5]: 1,3,4,5,6
Enter file path to save certificate: selfpublic.pem
Enter file path to save private key: selfprivate.key
Enter password to protect private key: password
Enter file path to save identity: selfserver.pem

You can now start a MobiLink server and connect to it using a SQL Anywhere MobiLink client:

> mlsrv16 -c "..." -x tls(tls_type=rsa;identity=selfserver.pem;identity_password=password)
> dbmlsync -c "..." -e "ctp=tls;adr=trusted_certificates=selfpublic.pem;certificate_name=testserver.sap.com;certificate_company=SAP SE;certificate_unit=Active Global Support"

This is an appropriate solution for a medium-size organization that has multiple database servers that require secure communication, but does not require a third-party CA to be involved.

> createcert
SQL Anywhere X.509 Certificate Generator Version 16.0.0.2052
Warning: The certificate will not be compatible with older versions
of the software including version 12.0.1 prior to build 3994 and version 16.0
prior to build 1691. Use the -3des switch if you require compatibility.

Enter RSA key length (512-16384): 2048
Generating key pair...
Country Code: CA
State/Province: ON
Locality: Waterloo
Organization: SAP SE
Organizational Unit: Active Global Support
Common Name: SAP Enterprise Root
Enter file path of signer's certificate: [ENTER]
Certificate will be a self-signed root
Serial number [generate GUID]: [ENTER]
Generated serial number: 32addde9066e4672bc1f4929f3a52441
Certificate valid for how many years (1-100): 30
Certificate Authority (Y/N) [N]: y
1. Digital Signature
2. Nonrepudiation
3. Key Encipherment
4. Data Encipherment
5. Key Agreement
6. Certificate Signing
7. CRL Signing
8. Encipher Only
9. Decipher Only
Key Usage [6,7]: [ENTER]
Enter file path to save certificate: entpublic.pem
Enter file path to save private key: entprivate.key
Enter password to protect private key: password
Enter file path to save identity: entserver.pem

You can now use this key to sign other generated keys that can be used to start lower-tier database servers:

> createcert
SQL Anywhere X.509 Certificate Generator Version 16.0.0.2052
Warning: The certificate will not be compatible with older versions
of the software including version 12.0.1 prior to build 3994 and version 16.0
prior to build 1691. Use the -3des switch if you require compatibility.

Enter RSA key length (512-16384): 2048
Generating key pair...
Country Code: CA
State/Province: ON
Locality: Waterloo
Organization: SAP SE
Organizational Unit: Active Global Support
Common Name: testserver1.sap.com
Enter file path of signer's certificate: entserver.pem
Enter password for signer's private key: password
Serial number [generate GUID]: [ENTER]
Generated serial number: 736052f5eaa74820b42fe17a6d869946
Certificate valid for how many years (1-100): 30
Certificate Authority (Y/N) [N]: n
1. Digital Signature
2. Nonrepudiation
3. Key Encipherment
4. Data Encipherment
5. Key Agreement
6. Certificate Signing
7. CRL Signing
8. Encipher Only
9. Decipher Only
Key Usage [1,3,4,5]: 1,3,4,5,6
Enter file path to save certificate: 1public.pem
Enter file path to save private key: 1private.key
Enter password to protect private key: password
Enter file path to save identity: 1server.pem

You can now connect to a SQL Anywhere database server that is trusted via your enterprise CA's public certificate:

>dbsrv16 -ec tls(tls_type=rsa;identity=1server.pem;identity_password=password) -n demo demo.db -n demo
>dbisql -c "uid=DBA;pwd=sql;eng=demo;dbn=demo;links=tcpip;enc=tls(tls_type=rsa;trusted_certificates=entpublic.pem;certificate_name=SAP Enterprise Root;certificate_company=SAP SE;certificate_unit=Active Global Support)"

You can now start the process again to create a second server certificate signed by your enterprise CA and start a second SQL Anywhere database server with that certificate:

>dbsrv16 -ec tls(tls_type=rsa;certificate=2server.pem;certificate_password=password) -n demo2 demo2.db -n demo2

You can also connect to this server using the same public enterprise CA certificate you used to connect to the first server, but specifying different certificate details:

>dbisql -c "uid=DBA;pwd=sql;eng=demo2;dbn=demo2;links=tcpip;enc=tls(tls_type=rsa;trusted_certificates=entpublic.pem;certificate_name=testserver1.sap.com;certificate_company=SAP SE;certificate_unit=Active Global Support)"

If you reverse the certificate details for the individual database servers, the connection fails:

>dbisql -c "uid=DBA;pwd=sql;eng=demo;dbn=demo;links=tcpip;enc=tls(tls_type=rsa;trusted_certificates=entpublic.pem;certificate_name=testserver2.sap.com;certificate_company=SAP SE;certificate_unit=Active Global Support)"

[Sybase][ODBC Driver][SQL Anywhere] TLS handshake failure
Error code=-829
SQL state=08S01

>dbisql -c "uid=DBA;pwd=sql;eng=demo2;dbn=demo2;links=tcpip;enc=tls(tls_type=rsa;trusted_certificates=entpublic.pem;
     certificate_name=testserver1.sap.com;certificate_company=SAP SE;certificate_unit=Active Global Support)"

[Sybase][ODBC Driver][SQL Anywhere] TLS handshake failure
Error code=-829
SQL state=08S01

In the above example, you must be able to access the CA's private server identity, along with the password to generate a signed-certificate by a CA. Deploying this information to external clients to generate the remote sites certificates may not be feasible or secure.

Instead, a remote site can generate a request file (in PKCS10 format) that can be sent to a certificate authority site (with the appropriate geographical details and generated keys). This file cannot be used to encrypt communications with a server until it is signed by a CA. Note that the certificate name or other information given in this step cannot be changed once it has been signed by a CA.

>createcert -r
SQL Anywhere X.509 Certificate Generator Version 16.0.0.2052
Warning: The certificate will not be compatible with older versions
of the software including version 12.0.1 prior to build 3994 and version 16.0
prior to build 1691. Use the -3des switch if you require compatibility.

Enter RSA key length (512-16384): 2048
Generating key pair...
Country Code: CA
State/Province: ON
Locality: Waterloo
Organization: SAP SE
Organizational Unit: Active Global Support
Common Name: testerver3.sap.com
Enter file path to save request: 3request.req
Enter file path to save private key: 3private.key
Enter password to protect private key: password

This file can then be sent to a CA, who can then sign the certificate using the private enterprise server key:

>createcert -s 3request.req
SQL Anywhere X.509 Certificate Generator Version 16.0.0.2052
Warning: The certificate will not be compatible with older versions
of the software including version 12.0.1 prior to build 3994 and version 16.0
prior to build 1691. Use the -3des switch if you require compatibility.

Enter file path of signer's certificate: entserver.pem
Enter password for signer's private key: password
Serial number [generate GUID]:
Generated serial number: ac47bc7a9a4645c5a505f0a6ff2d94b5
Certificate valid for how many years (1-100): 30
Certificate Authority (Y/N) [N]: n
1. Digital Signature
2. Nonrepudiation
3. Key Encipherment
4. Data Encipherment
5. Key Agreement
6. Certificate Signing
7. CRL Signing
8. Encipher Only
9. Decipher Only
Key Usage [3,4,5]:
Enter file path to save certificate: 3public.pem

This file can then be sent back to remote site and used to generate the server identity in combination with the private key:

>copy 3public.pem + 3private.key 3server.pem
3public.pem
3private.key
      1 file(s) copied.

And you can now start the database server, and connect using the enterprise CA's public key:

>dbsrv16 -ec tls(tls_type=rsa;identity=3server.pem;identity_password=password) -n demo demo.db -n demo
>dbisql -c "uid=DBA;pwd=sql;eng=demo2;dbn=demo2;links=tcpip;enc=tls(tls_type=rsa;trusted_certificates=entpublic.pem;certificate_name=testserver3.sap.com;certificate_company=SAP SE;certificate_unit=Active Global Support)"

Related Content

  • No labels