Configuring a Data Source on Linux

The ODBC driver (libogodbc.so) provided by oGRAC can be used once it is configured into a data source. Configuring a data source requires setting up two configuration files, odbc.ini and odbcinst.ini (generated when compiling and installing unixODBC and placed in the /usr/local/etc directory by default), and performing configuration on the server.

Procedure

Obtain the unixODBC source package.
The unixODBC version must be 2.3.6 or later. Taking unixODBC-2.3.6 as an example, you can download it directly: unixODBC-2.3.6.tar.gz.
Install unixODBC. If an earlier version of unixODBC is already installed on the machine, uninstall it first or directly overwrite the existing installation.
Taking unixODBC-2.3.6 as an example, run the following commands on the client to install unixODBC. By default, it is installed to the /usr/local directory, the data source files are generated in the /usr/local/etc directory, and the library files are generated in the /usr/local/lib directory.
shell
```
tar zxvf unixODBC-2.3.6.tar.gz
cd unixODBC-2.3.6
./configure --enable-gui=no
make
# The installation may require root privileges.
make install
```
Replace the client ODBC driver.
Copy the ODBC driver (libogodbc.so) provided by oGRAC to the /usr/local/lib directory, and verify its integrity.
shell
```
ldd /usr/local/lib/libogodbc.so
```

Configure the data source.

Configure the ODBC driver file.

Append the following content to the /usr/local/etc/odbcinst.ini file.

shell

[OgracMPP]
Driver64=/usr/local/lib/libogodbc.so
setup=/usr/local/lib/libogodbc.so

Table 1 describes the configuration parameters in the odbcinst.ini file.

Table 1 Configuration parameters in the odbcinst.ini file

Parameter	Description	Example
[DriverName]	Driver name, which corresponds to the driver name in the data source DSN.	[OgracMPP]
Driver64	Path to the driver dynamic library.	Driver64=/usr/local/lib/libogodbc.so
setup	Driver installation path, which must be the dynamic library path specified in Driver64.	setup=/usr/local/lib/libogodbc.so

Configure the data source file.

Append the following content to the /usr/local/etc/odbc.ini file.

shell

[OgracDB]
Driver=OgracMPP
Servername=127.0.0.1
Port=1611
Username=test
Password=test123

Table 2 describes the odbc.ini file configuration parameters.

Table 2 odbc.ini file configuration parameters

Parameter	Description	Example
[DSN]	Data source name.	[OgracDB]
Driver	Driver name, which corresponds to DriverName in odbcinst.ini.	Driver=OgracMPP
Servername	Server IP address.	Servername=127.0.0.1
Username	Database user name.	Username=test
Password	Database user password.	Password=test123 Note: The ODBC driver itself has already cleared the password from memory to ensure that the user password is not retained in memory after the connection is established.
Port	Server port number.	Port=1611
sslmode	Enables SSL mode.	sslmode=VERIFY_CA
sslca	Root certificate file that issues certificates for ODBC. The root certificate is used to verify the validity of server certificates.	sslca=/usr/test/certificate/ca.crt
sslcert	ODBC-side certificate file, which contains the client's public key.	sslcert=/usr/test/certificate/client.crt
sslkey	ODBC-side private key file, which is used for digital signing and for decrypting data encrypted with the public key.	sslkey=/usr/test/certificate/client.key

For details about the allowed values of the sslmode options, see the following table:

Table 3 sslmode options

sslmode	SSL Encryption Enabled	Description
VERIFY_CA	Yes	An SSL secure connection must be used, and it must be verified that the database has a certificate issued by a trusted certificate authority.
DISABLED	No	No SSL secure connection is used.
PREFERRED	Possible	If supported by the database, an SSL secure encrypted connection is recommended, but the authenticity of the database server is not verified.
REQUIRED	Yes	An SSL secure connection must be used, but only data encryption is performed, without verifying the authenticity of the database server.
VERIFY_FULL	Yes	An SSL secure connection must be used. In addition to the verification scope of verify-ca, it also verifies whether the host name of the database host matches the certificate content.oGRAC does not support this mode.

Use SSL mode.

Generate the server certificate.

Perform the following operations on the server where the database is installed:

shell

# Generate the CA certificate ca.crt and private key ca.key on the host.
openssl req -newkey rsa:3072 -passout pass:12345678 -keyout ca.key -x509 -days 365 -out ca.crt -subj "/C=CN/ST=BJ/O=huawei/OU=huawei/CN=CA/emailAddress=123456@xxx.com"
# Generate the private key mes.key and certificate request mes.csr on the host.
openssl req -newkey rsa:3072 -nodes -keyout mes.key -out mes.csr -subj "/C=CN/ST=BJ/L=BJ/O=huawei/OU=huawei/CN=Server/emailAddress=123456@xxx.com"
# Generate the certificate mes.crt on the host.
openssl x509 -req -days 365 -in mes.csr -CA ca.crt -CAkey ca.key -CAcreateserial -out mes.crt

Configure server parameters.
Create a directory /opt/ograc/data on the server where the database is installed, place the generated certificates ca.crt, ca.key, ca.srl, mes.crt, mes.csr, and mes.key into this directory, and modify the permissions on the folder and certificates:
shell
```
# Modify the folder permissions, where ogracdba is the database installation user.
chmod 700 /opt/ograc/data
chown ogracdba:ogracdba /opt/ograc/data

# Modify the certificate permissions.
chmod 400 /opt/ograc/data/*
chown ogracdba:ogracdba /opt/ograc/data/*
```

Add SSL parameters to enable SSL mode for the database.

shell

# Log in to the database.
ogsql test/test123@127.0.0.1:1611 -q
# Execute the following SQL statements to add parameters.
alter system set SSL_CA = '/opt/ograc/data/ca.crt';
alter system set SSL_CERT = '/opt/ograc/data/mes.crt';
alter system set SSL_KEY='/opt/ograc/data/mes.key';

# Restart the database.
cms res -stop db
cms res -start db

After the restart, execute the SQL command: show parameter ssl. If the query result shows that the value of HAVE_SSL is TRUE, SSL has been successfully enabled.

Configure an SSL connection on the ODBC client.
Create an arbitrary directory /usr/test/certificate on the client where ODBC is located, and use scp to transfer the ca.crt file generated on the server to the client where ODBC is located. Then perform the following operations:
shell
```
mkdir -p /usr/test/certificate
cd /usr/test/certificate
scp root@xxx.xxx.xx.xx:/opt/ograc/data/ca.crt .
```
Generate the private key client.key and the certificate request client.csr on the client:
shell
```
openssl req -newkey rsa:3072 -nodes -keyout client.key -out client.csr -subj "/C=CN/ST=BJ/L=BJ/O=huawei/OU=huawei/CN=Server/emailAddress=123456@xxx.com"
```
Use scp to transfer client.csr to the /opt/ograc/data directory on the server where the database is located:
shell
```
scp client.csr root@xxx.xxx.xx.xx:/opt/ograc/data/
```
Generate the client certificate.
Generate the certificate client.crt in the /opt/ograc/data directory on the server where the database is located, and transfer it to the client:
shell
```
cd /opt/ograc/data
openssl x509 -req -days 365 -in client.csr -CA ca.crt -CAkey ca.key -CAcreateserial -out client.crt
```
Transfer the certificate to the client.
Transfer the generated certificate client.crt to the client:
shell
```
cd /usr/test/certificate
scp root@xxx.xxx.xx.xx:/opt/ograc/data/client.crt .
```

Configure ODBC parameters on the client.

Grant permissions to the certificate:

shell

chmod 400 /usr/test/certificate/*

Add the following parameters in the odbc.ini file:

shell

sslca=/usr/test/certificate/ca.crt
sslcert=/usr/test/certificate/client.crt
sslkey=/usr/test/certificate/client.key
sslmode = VERIFY_CA

Configure environment variables on the client.
shell
```
vim ~/.bashrc
```
Append the following content to the configuration file:
shell
```
export PATH=/usr/local/bin:$PATH
export LD_LIBRARY_PATH=/usr/local/lib:$LD_LIBRARY_PATH
```
Run the following command to apply the settings:
shell
```
source ~/.bashrc
```

shell

odbcinst -i -d -f /usr/local/etc/odbcinst.ini

Testing the Data Source Configuration

After installation, run the isql command to verify the connection, that is, isql OgracDB -v test test123.

If the following information is displayed, the configuration is correct and the connection is successful.

shell

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

If ERROR information is displayed, a configuration error exists. Check whether the above configuration is correct.

Configuring a Data Source on Linux ​

Procedure ​

Testing the Data Source Configuration ​

Configuring a Data Source on Linux

Procedure

Testing the Data Source Configuration