qgis is A Free and Open Source Geographic Information System. It can use several spatial backends, and Oracle is just one of them. From what I see it is quite often used with other databases like PostgreSQL or even SpatiaLite. And even Oracle is quite well supported, there sem to be some loose ends, especially for complex situations.
One of these complex situations is the connection to an Always Free Autonomous Database. Oracle tries to secure the database access, so by default it does not allow a simple connection with host, port, service_name and username/password. Instead a wallet is provided to secure the network access before providing the credentials.The zip contains several files which can be used for OCI or JDBC connections. In it's tnsnames.ora the aliases are slightly more complex than normally expected:
db1234_medium =
(description=
(retry_count=20)(retry_delay=3)
(address=
(protocol=tcps)
(port=1522)
(host=adb.eu-frankfurt-1.oraclecloud.com)
)
(connect_data=
(service_name=pxyz_db1234_medium.adb.oraclecloud.com)
)
(security=
(ssl_server_cert_dn="CN=adwc.eucom-central-1.oraclecloud.com, OU=Oracle BMCS FRANKFURT, O=Oracle Corporation, L=Redwood City, ST=California, C=US")
)
)
On the other hand, qgis (3.24.1) provides these input fields:
Name, Database, Host and Port define the connection endpoint. But there is no field available for additional details like protocol=tcps or ssl_server_cert_dn=.... Also the specific wallets location can not be provided.
Name, Database, Host and Port define the connection endpoint. But there is no field available for additional details like protocol=tcps or ssl_server_cert_dn=.... Also the specific wallets location can not be provided.
With this information, I managed to generate these values:
Database | pxyz_db1234_medium.adb.oraclecloud.com?ssl_server_cert_dn="CN=adwc.eucom-central-1.oraclecloud.com, OU=Oracle BMCS FRANKFURT, O=Oracle Corporation, L=Redwood City, ST=California, C=US"&wallet_location=C:\tns_admin |
Host | tcps://adb.eu-frankfurt-1.oraclecloud.com |
Port | 1522 |
Here are 3 important details: in the Database field, you can add information about the certificates DN and the wallet location. In the Host field, the prefix tcps:// defines the specific protocol used.
This alltogether generates an URL
tcps://adb.eu-frankfurt-1.oraclecloud.com:1522/pxyz_db1234_medium.adb.oraclecloud.com?ssl_server_cert_dn="CN=adwc.eucom-central-1.oraclecloud.com, OU=Oracle BMCS FRANKFURT, O=Oracle Corporation, L=Redwood City, ST=California, C=US"&wallet_location=C:\tns_admin
which is sufficient to connect to my ATP Database
∎
∎
Keine Kommentare:
Kommentar veröffentlichen