TCPS support

[vanimesh]

Hi,

First of all, thank you for bringing this implementation. We are using this for a long. But recently we have implemented TCPS, secured TCP. Not sure if the implementation works with the above change on the port. Or any changes need to be done in the scripts

[ikzelf]

For which database? For oracle you should be able to use everything that sqlnet provides.

[vanimesh]

Yeah it's for Oracle. Where can I see what sqlnet provides? Also how would I use the certificates with this implementation?

[ikzelf]

Use in the same way as for other apps. Make sure that your sqlnet.ora is correct and known by the environment that zbxdb.py has. In the db_url specify protocol tcps and the correct port. You can copy the complete tns alias description in there.

https://docs.oracle.com/en/database/oracle/oracle-database/19/dbseg/configuring-secure-sockets-layer-authentication.html#GUID-28A27D34-D642-45AB-BA0F-F20D4494FB38

[vanimesh]

Do file sqlnet.ora reside on Zabbix proxy? I couldn't see any such file in Zabbix proxy(is it because we have not used tcps till date ?). Also can you please help me with a sample of db_url where I can specify tcps?

[ikzelf]

Yes, you should put it there. Put it in $ORACLE_HOME/network/admin/ You can copy from other applications in your organization. Get an oracle dba involved to help you with the setup.

[vanimesh]

thanks @ikzelf , but the setup which we use by python scripts doesn't have client installed right. Sorry if I am asking you dumb questions :-)

I was just checking the rpms installed I found the below oracle-instantclient18.3-sqlplus-18.3.0.0.0-1.x86_64 oracle-instantclient18.3-odbc-18.3.0.0.0-1.x86_64 oracle-instantclient18.3-basic-18.3.0.0.0-1.x86_64

[ikzelf]

It will be there since cx_Oracle needs it. You can also put sqlnet.ora in $TNS_ADMIN/ which you can create at any convenient location, as long as you make sure that the zbxdb.py script has this variable setup in its environment. Get your local oracle dba involved.

Groeten, Ronald Rood

Op 5 apr. 2022 om 07:02 heeft vanimesh @.***> het volgende geschreven:

 thanks @ikzelf , but the setup which we use by python scripts doesn't have client installed right. Sorry if I am asking you dumb questions :-)

— Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you were mentioned.

[vanimesh]

Sure Ronald, thank you very much for your quick response, I will get the dba involved. But I also need to understand some points as I am a bit new to the environment and this implementation :-). --Are you speaking about the /etc/zabbix/python35_venvs/cx_oracle/bin/activate which I found in the zbxdb_starter script? --Also, once we prepare the sqlnet.ora, we will have to set the env variable so that the script identifies the file, but how does the python script know to use the sqlnet.ora file for the hosts which have the TCPs enabled? Because we have a mix of hosts, some of them are TCP and some of them are TCPs.

[ikzelf]

If you setup as I described, zbxdb_starter is launched from the crontab. To load eventual extra variables zbxdb_starter also loads the ~/.bash_profile so put the export of TNS_ADMIN=/where/ever/you/put/it/ in the ~/.bash_profile. zbxdb.py does not know anything about the oracle specific variables, cx_Oracle, the driver does.

you can setup a db_url like: db_url: (DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=OFF)(FAILOVER=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=node1-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=node2-vip)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=+ASM)(SERVER=DEDICATED))) (see etc/zbxdb.oracle-cluster1-ASM.cfg for example)

If you need protocol tcps, specify tcps instead of tcp for a specific database.

[vanimesh]

If you setup as I described, zbxdb_starter is launched from the crontab. Yes it does To load eventual extra variables zbxdb_starter also loads the ~/.bash_profile so put the export of TNS_ADMIN=/where/ever/you/put/it/ in the ~/.bash_profile. zbxdb.py does not know anything about the oracle specific variables, cx_Oracle, the driver does.

you can setup a db_url like: db_url: (DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=OFF)(FAILOVER=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=node1-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=node2-vip)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=+ASM)(SERVER=DEDICATED))) (see etc/zbxdb.oracle-cluster1-ASM.cfg for example)

Sure but I was checking our implementation and found that the cfg file is something like below [zbxdb] db_url = //ora1dbprime:1522/SMT username = getstatsro password = db_type = oracle db_driver = cx_Oracle instance_type = rdbms role = normal out_dir = /etc/zabbix/zbxdb/log/zbxora_out hostname = ora1dbprime checks_dir = etc/zbxdb_checks password = verysecret

If you need protocol tcps, specify tcps instead of tcp for a specific database.

[ikzelf]

See https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9540278000346887751 for use of tcps in ezconnect. That works from instant client 19c. When using an older client, you could test it, if it works, it works, if not use the syntax I showed (the complete tns alias description in db_url) or install a newer express client (or full client).

[ikzelf]

Also: are you using the latest version? see: "Written in python, tested with python 3.6 Ready for python 3.9.2 since python 3.6 hits EOL in 2021-12 Using drivers available for python purpose is monitoring any database in an efficient way. Using zabbix_sender to upload data from crontab By popular demand: password fields are encrypted to password_enc fields during startup when a password value is detected. Not a simple hash but with strong key, complete with key rotation, if a key has been generated using "bin/zbxdb.py -c one_of_your.cfg -g". This will generate a key and place in in the keys directory for use of all your cfg's that are in the same directory."

[vanimesh]

Also: are you using the latest version? see: "Written in python, tested with python 3.6 Ready for python 3.9.2 since python 3.6 hits EOL in 2021-12 Using drivers available for python purpose is monitoring any database in an efficient way. Using zabbix_sender to upload data from crontab By popular demand: password fields are encrypted to password_enc fields during startup when a password value is detected. Not a simple hash but with strong key, complete with key rotation, if a key has been generated using "bin/zbxdb.py -c one_of_your.cfg -g". This will generate a key and place in in the keys directory for use of all your cfg's that are in the same directory."

We are using python3.5 we have some plans to upgrade it in future. But for now only TCPs is required that too only for some hosts its required

[ikzelf]

OK, just be careful pasting your password_enc here ...... that is in your case a simple reversible base64 hash. That is why I made the -g option to generate strong keys so pasting password_enc here does no longer hurt your security because the key is also needed to decrypt the password. So I edited your message and took it out.

[vanimesh]

Thanks Ronald... i have already changed the password before I shared the info :-)

[vanimesh]

So on a high level below are the steps which I understood based on our discussion

1)Create a sqlnet.ora file with appropriate details 2)Make the entry into .bash_profile 3)Edif the cgf file to tcps 4)Run the script

[ikzelf]

yes and check for connection problems. What I do in such a case is to manually start zbxdb.py to see the connection status and eventual error messages. Connection status will be uploaded into zabbix but you might want to check this manually after such changes. I hope you get it working - I expect so - if not, check with your dba first. They should know how to interpret the error messages and probably also how to fix them. Reading https://cx-oracle.readthedocs.io/en/latest/user_guide/connection_handling.html it should just work if you export TNS_ADMIN in the environment.

[vanimesh]

In .bash_profile what should be the env variable name?

[ikzelf]

The environment variable pointing to the directory containing the sqlnet.ora that should be exported is TNS_ADMIN

[vanimesh]

Hi Ronald, i tried with the same steps and i still get some error. I checked with connectivity and it's working fine. Can you please help

I tried with both url styles.

Do we need to install new client libraries? We have 18.3 now

[ikzelf]

If you can show the connect string and the logging of the error[s] that you receive, I might be able help. (show the full log, from the start, up until the error messages)

[vanimesh]

The below is the error

2022-04-08 05:27:13 start python-3.5.1 zbxdb-0.77 pid=4887 Connecting for hostname loc-vu-smtuat.uat.company.com... 2022-04-08 05:27:13.743123 zbxdb found db_type=oracle, driver cx_Oracle; checking for driver <module 'cx_Oracle' from '/u01/zabbix/python35_venvs/cx_oracle/lib64/python3.5/site-packages/cx_Oracle.cpython-35m-x86_64-linux-gnu.so'> 2022-04-08 05:27:13.747176 zbxdb driver cx_Oracle loaded 2022-04-08 05:27:13.749567 zbxdb driver drivererrors for cx_Oracle loaded 2022-04-08 05:27:13.750630 zbxdb dbconnections for oracle loaded <module 'dbconnections.oracle' from '/u01/zabbix/zbxdb/bin/dbconnections/oracle.py'> <module 'drivererrors.cx_Oracle' from '/u01/zabbix/zbxdb/bin/drivererrors/cx_Oracle.py'> 2022-04-08 05:27:13.750722 out_file:/u01/zabbix/zbxdb/log/zbxora_out/zbxdb.loc-vu-smtuat.uat.company.com:1522SMTUAT.company.COM.zbx 2022-04-08 05:27:13.772168: (1.1)connection error: [12154] ORA-12154: TNS:could not resolve the connect identifier specified for zabbix_mon@"(DESCRIPTION =(ADDRESS = (PROTOCOL = TCPS)(HOST =loc-VU-SMTUAT.UAT.company.COM)(PORT = 1522))(CONNECT_DATA =(SERVICE_NAME =SMTUAT.company.COM)))"

[vanimesh]

connection string is same as in the log above

[ikzelf]

You are using a very old version (0.77 more than 3 years old), currently 3.00. Your connection string looks good. It'd a bit hard for me to work with that old version. I hate to ask it but can you upgrade to the latest version? I can not guarantee it will work using the latest version but at least I can better help you testing/debugging this.

[vanimesh]

3.0 ? Which version are you referring to? But regarding upgrade i don't think we have planned anything very soon. But this is an urgent requirement can you please look into it? It would be really helpful for us

[vanimesh]

Also how complex is it to upgrade? And how much time does it take to do so? Any steps we can have for the same?

[ikzelf]

Currently you use 2022-04-08 05:27:13 start python-3.5.1 zbxdb-0.77 The upgrade is not very hard, mostly the same as a fresh install. If you are up to it, also upgrade to python 3.9 since even 3.6 id already at EOL since december 2021. Follow the documentation the I wrote, nothing very special, apart from adding the logger module in v2 plus verbosity options for log/debug and encryption for the passwords (using keys) in v3. Once you have the latest version I can hopefully help you and with a little luck, it already works for you. Don't forget the prepare the logging.json and the logging_sender.json (as mentioned in the docu).

[vanimesh]

Is there anyway we can use it with present version?

[ikzelf]

Do you happen to have specified this: db_url = "(DESCRIPTION =(ADDRESS = (PROTOCOL = TCPS)(HOST =loc-VU-SMTUAT.UAT.company.COM)(PORT = 1522))(CONNECT_DATA =(SERVICE_NAME =SMTUAT.company.COM)))" ? if so, change to: db_url = (DESCRIPTION =(ADDRESS = (PROTOCOL = TCPS)(HOST =loc-VU-SMTUAT.UAT.company.COM)(PORT = 1522))(CONNECT_DATA =(SERVICE_NAME =SMTUAT.company.COM)))

(remove the double quotes).

[ikzelf]

Did you get rid of the ora 12154 now?

[vanimesh]

Hi @ikzelf , apologies for keeping you waiting... I had some P1 issues to attend to. Let me try now and update you. Really appreciate your help :-)

[vanimesh]

Hi @ikzelf , i am following the below steps please let me know if I am doing something wrong

1)Updated the .bash_profile with the below

   TNS_ADMIN=/etc/zabbix/zbxdb/tcpsConfig
   export TNS_ADMIN

2)The above path has the wallet and sqlnet.ora files 3)Update the db URL without " from as you suggested 4)Killed the process of zbxdb.py which takes the configuration file 5)Checked the logs

and it works fine :-) I will try for couple of other hosts and let you know :-)

[ikzelf]

Glad to see that it works as designed.

[vanimesh]

OK, just be careful pasting your password_enc here ...... that is in your case a simple reversible base64 hash. That is why I made the -g option to generate strong keys so pasting password_enc here does no longer hurt your security because the key is also needed to decrypt the password. So I edited your message and took it out.

Hi @ikzelf , how will I know the original password? I will have to provide it to my DBA to create a user and password in the database.

[ikzelf]

https://github.com/ikzelf/zbxdb/blob/master/doc/encryption.md

Use the -p option as described.

[vanimesh]

I am getting the below error. Traceback (most recent call last): File "zbxdb.py", line 24, in import configparser ImportError: No module named configparser

[vanimesh]

Will the above command give the actual password which is encrypted in the file right? It doesn't disturb the other files and existing monitoring

[ikzelf]

you should use the same environment as the monitoring does. Yes, the -p xxxx command only reads the config file and in case of 'password' it decrypts the password and puts it on stdout in a readable form.