Using X.509 Certificates for ODBC SQL TLS Connection

Virtuoso can be configured to authenticate SQL logins, including ODBC with a single X.509 certificate. The certificate must be registered server side for this purpose and may contain an additional custom attribute for the users SQL account name. In this way all login information is held in the possibly password protected certificate. The user specifies the certificate path in the place of the user name and the certificate encryption password as the password. This works with the login dialog of the ODBC driver or within a SQLDriverConnect login string.

It is also possible to set up the ODBC client to check for the server's X.509 certificate by specifying a client side CA (Certificate Authority) list.

An X.509 certificate can be used to authenticate a SQL User account associated with Virtuoso server a TLS connection. In this usage scenario, the ODBC client will use an X.509 certificate that contains a SQL username incorporated via custom attribute or extension that created at certificate generation time. Any valid object ID (OID) can hold the username, but it important to note that any new object identifier must not conflict with existing OIDs (for example, we currently use 2.16.840.1.NNNNNN). Ultimately, It will be best to have one's own registered OID for this purpose.

Attribute-Based vs Role-Based Access Control

Role-Based Access Control (RBAC) maps a SQL User Account to a Public Key (which is the part of an X.509 certificate that's imported into the SQL User's profile, just like placing a certificate into an LDAP entry for a User). Alternatively, you can place the SQL Username in the OID of an X.509 certificate.

Attribute-Based Access Control (ABAC) doesn't map a Public Key directly to a SQL User Account; instead, you have a WebID that's associated with an ACL within a Realm (HTTP or SQL). As the ABAC ACL system, VAL includes a mapping to a SQL Role Account, which is what interacts with the Graph Security System.

Web ID Protocol ODBC Login

Virtuoso supports the WebID Protocol for ODBC Login, for which the INI parameter X509ClientVerify accepts the following values:

• 0 - do not require
• 1 - require trusted certificate
• 2 - request but do not require certificate; if certificate is given, it will be verified (and must be trusted)
• 3 - request but do not require certificate; any certificate, even self-signed, will be accepted

If a certificate contains a WebID in the SAN, the ODBC login will use it. A WebID may be mapped to a particular SQL account via the Conductor's admin interface.

Client Certificate Generation Using "openssl" Tool

To generate X.509 certificates, one can use the openssl tool freely obtainable from openssl.org . The openssl.cnf configuration file must be edited to include the new extension for sqlUserName, but first we have to find the hexadecimal representation of the SQL Username in question. Then you can use openssl to create and confirm the certificate. Follow the steps below:

1. Find the HEX representation of the SQL Username - You can work this out from ASCII codes or use the hexdump utility found on most Unix platforms. The following command would be sufficient, replacing <user_name> with the actual username:
   

   
   $ echo -n "<user_name>" | hexdump -e '":" 1/1 "%02X"'
   

   
   For example, the username 'dba' would be :64:62:61 .
   
   
2. Edit the OpenSSL config file, openssl.cnf - Open the file in a text editor of your choosing, and add the following, replacing :NN:NN..... (keeping the preceding DER!) in the sqlUserName value with the hexadecimal representation of the username we discovered in the previous step.
   

   
   oid_section  = new_oids                   # section where new oids are defined
   
   [ CA_default ]
   x509_extensions = usr_cert                # The extensions to add to the cert
   
   [ new_oids ]
   sqlUserName=2.16.840.1.NNNNNN.1
   
   ...
   [ usr_cert ]
   ...
   sqlUserName = DER:NN:NN:NN:NN:NN.....
   

   
   
3. Generate a Private Key
   

   
   openssl genrsa -des3 -out server.key 1024 -config ./openssl.cnf 
   

   
   
4. Make New Certificate Request - Make new certificate request using the command:
   

   
   $ openssl req -new -key server.key -out server.csr -config ./openssl.cnf 
   

   
   The tool will ask for certain details. Once answered, it will also generate a private key.
   
   
5. Generate Certificate - Assuming the role of CA, generate a certificate using the request from the previous step, using the following command:
   

   
   $ openssl ca -in server.csr -config ./openssl.cnf 
   

   
   The tool will print the details of the request and will ask you to sign and commit the certificate into the CA repository.
   
   
6. Generate a .pfx/.p12 certificate file
   

   
   $ openssl pkcs12 -export -out server.p12 -inkey server.key -in server.pem
   

   
   
7. Verify New Certificate - Check that the certificate contains the right SQL account name by using the following command to obtain a text dump of the certificate:
   

   
   $ openssl x509 -in server.pem -text -noout
   

   
   Now we can scan the contents of the output for the extension entries we added earlier:
   

   
   >>>>>
   X509v3 extensions:
   ....
   2.16.840.1.NNNNNNN.1:
   <SqlAccountName>
   ^^^^^^ this should match the new OID ^^^^^
   >>>>>
   

   
   

Virtuoso Server Configuration

The Virtuoso server can handle SSL connections in two modes:

• basic - The connection is encrypted, with no certificate verification. The client is not required to have its own trusted certificate.
• trusted - Additional mechanisms are enabled to check the client's certificate. In this case, the client is required to have a trusted certificate.

To allow Virtuoso to check the client's certificates, the Virtuoso INI file must contain the following entries in the [Parameters] section:

; Basic session encryption-only parameters
SSLServerPort          = 1113
SSLCertificate          = server.pem ; server's certificate
SSLPrivateKey          = server.key  ; server's private key, must match the certificate
; Trusted operation parameters
X509ClientVerify        = 3
X509ClientVerifyCAFile  = server.pem         ; CA list; file containing certificates of acceptable CA
X509ClientVerifyDepth   = 15                ; dependent of type of certificate checking can be >1
X509ExtensionOID          = 2.16.840.1.NNNNNNN.1 ; the OID value, same as that used to make client's certificates

All certificate/key files must be in PEM format (but need not use the .pem filename extension).

The server needs an SSLCertificate and SSLPrivateKey to begin listening on the SSLServerPort. These are essential for secure operations. Furthermore, the certificate must match the private key; if these do not match, the server will not start, and an error will be logged. The private key is required to guarantee that the certificate's claim is true. The server certificate is used by the client to identify the server. The client can retrieve and verify this key and choose whether the server an be trusted depending on circumstances.

Note: Basic (encryption-only) SSL/TLS cannot be used to identify a client through its certificate.

The X509ClientVerifyCAFile is a file containing CA (Certificate Authority) certificates, that the server can use to verify client certificates. The X509ClientVerify parameter enables (or disables) client certificate verification in general. The X509ClientVerifyDepth parameter specifies the degree to which the server will trust the client's certificate. The lower the value (with a minimum of 1), the higher the restriction levels. This means that the server will look in the CA certificates to find which has issued the client certificate. If there is no matching CA entry, the connection will be rejected; if there is a matching entry, Virtuoso will then verify the issuer chain; if the issuer chain is longer than X509ClientVerifyDepth, the connection will be rejected.

All file paths above must be either absolute or set relative to the server working directory.

Note: If the primary key file is encrypted, then the server must be started in foreground mode, such that the server can prompt for a password to be used to open the file.

Registering Keys with Virtuoso

To enable a client certificate to be used for authorization, the DBA must register it's MD5 fingerprint (checksum) in the database. Registered certificates may subsequently be removed from the system. Two functions exist for the purposes: USER_CERT_REGISTER() and USER_CERT_UNREGISTER().

The function prototypes are:

USER_CERT_REGISTER ( user_name , certificate , password , type );

USER_CERT_UNREGISTER ( user_name , certificate , password , type );

Both functions rely on the MD5 checksum of the certificates being registered or un-registered, therefore you have the option of supplying these functions with the certificate file or the MD5 checksum directly.

The fingerprints of registered certificates are kept in the DB.DBA.SYS_USERS table as vectors of strings under the LOGIN_CERTIFICATES user option value (U_OPTS column). The data stored there can be retrieved using a third function:

USER_GET_OPTION()

For example, one might invoke:

USER_CERT_REGISTER ('DBA', 'file:/server.pem', '', 'PEM');

Note: the file:/ URL prefix is needed to designate that the certificate is in a file on the file system.

The above action can also be performed without supplying the certificate itself, if the MD5 fingerprint is known:

USER_CERT_REGISTER ('DBA', 'D9:6D:47:D7:67:47:D7:3C:2C:E0:89:91:F3:BC:E7:59');

and

USER_CERT_UNREGISTER ('DBA', 'D9:6D:47:D7:67:47:D7:3C:2C:E0:89:91:F3:BC:E7:59');

Virtuoso Client Configuration

The following connection options control the client's SSL-related behavior:

• Encrypt - specifies the type of secure connection to use
• ServerCert? - (optional) to specify which certificate(s) are to be used to verify server certificates.

Encrypted SQL SSL Connection

The Encrypt option can be set to '1' to specify a basic secure connection; no server identity verification will be performed. Note that basic connections will only succeed if the server is also set to make basic SSL connections.

isql may be used to make such connections by including the -E = 1 in the launch arguments, as shown:

isql <hostname>:<port> <uid> <pwd> -E = 1

Any ODBC connect string can achieve the same with the ENCRYPT=1 parameter and value.

connectString = "HOST=<hostname>:<port>;UID=<uid>;PWD=<pwd>;ENCRYPT=1"

ODBC Data Source Configuration (Windows or macOS)

1. Choose the OpenLink Virtuoso ODBC Driver in the ODBC Administrator.
   
   
   
   
2. Specify an ODBC data source Name, and ensure The server requires a secure connection (SSL) check box option is ticked.
   
   
   
   
3. Tick the Connect to the Virtuoso Server to obtain default settings for the additional configuration options check box, enter a valid Username and Password, and click Next button; a secure connection to the database will be attempted.
   
   
   
   
4. If successful, the configuration dialog for additional database options will be presented.
   
   
   
   
5. Click the Finish button to complete the DSN creation.

Server Certificate SQL TLS Connection

To ensure the server's identity the Encrypt option must be set to full or relative (to application working directory) path to the file containing client's certificate and private key. This file can be encoded in PKCS#12 or PEM format. The certificate and private key contained may be generated using the steps outlined above.

When the Encrypt option is set to point to a certificate file, the ServerCert option must be set to the full or relative path to file containing list of CA certificates in PEM format. The content of this file will be used to verify server's certificate.

When using these options the UID connect option must be set to an empty string to enable certificate authorization. The PWD connection option will be used to open the private key.

Using isql this can be done as follows:

isql <hostname>:<port> "" <keysecret>  -X server.p12 -T server.pem

Using ODBC connect string this can be done as follows:

connectString = "HOST=<hostname>:<port>;UID=;PWD=<keysecret>;ENCRYPT=server.p12;SERVERCERT=server.pem"

If client's certificate does not contain a user name for SQL login, the server will try matching the certificate fingerprint against registered certificates. If any SQL account has a matching certificate registered, that account will be used for login; otherwise, login will be rejected.

ODBC Data Source Configuration (Windows or macOS)

1. Choose the OpenLink Virtuoso ODBC Driver in the ODBC Administrator.
   
   
   
   
2. Specify an ODBC data source Name and ensure The server requires a secure connection (SSL) check box is ticked.
   
   
   
   
3. Tick the Connect to the Virtuoso Server to obtain default settings for the additional configuration options check box, and:
   • for Authentication method, select the Public key authentication protocol from the drop-down menu
   • for Certificate, browse to the filesystem location of the .p12 file
   • enter the password for the .p12 file
   • optionally, tick the Require the server to authenticate itself with the public key check box, and browse to the public key file in your local filesystem.
     
     
     
     
4. If successful the configuration dialog for additional database options will be presented.
   
   
   
   
5. Click the Finish button to complete the DSN creation.