cx_Oracle doesn't connect when using SID instead of service name on connection string

Question

I have a connection string that looks like this

con_str = "myuser/[email protected]:1521/ora1"

Where ora1 is the SID of my database. Using this information in SQL Developer works fine, meaning that I can connect and query without problems.

However, if I attempt to connect to Oracle using this string, it fails.

cx_Oracle.connect(con_str)

DatabaseError:  ORA-12514:  TNS:listener  does  not  currently  know  of  service  requested  in  connect  descriptor

This connection string format works if the ora1 is a service name, though.

I have seen other questions that seem to have the reverse of my problem (it works with SID, but not Service name)

• Using Oracle Service Names with SQLAlachemy
• Oracle SID and Service name; connection problems
• cx_Oracle & Connecting to Oracle DB Remotely

What is the proper way to connect to Oracle, using cx_Oracle, using an SID and not a service name? How do I do this without the need to adjust the TNSNAMES.ORA file? My application is distributed to many users internally and making changes to the TNSNAMES file is less than ideal when dealing with users without administrator privileges on their Windows machines. Additionally, when I use service name, I don't need to touch this file at all and would like it keep it that way.

Saldivar

Answer 1

I a similar scenario, I was able to connect to the database by using cx_Oracle.makedsn() to create a dsn string with a given SID (instead of the service name):

dsnStr = cx_Oracle.makedsn("oracle.sub.example.com", "1521", "ora1")

This returns something like

(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle.sub.example.com)(PORT=1521)))(CONNECT_DATA=(SID=ora1)))

which can then be used with cx_Oracle.connect() to connect to the database:

con = cx_Oracle.connect(user="myuser", password="mypass", dsn=dsnStr)
print con.version
con.close()

Ketosis

The documentation is very clear. Just do cx_Oracle.makedsn("oracle.sub.example.com", "1521", service_name="ora1"), explicitly using the keyword service_name to differentiate it from the third argument (which is sid). – Fertile

Very useful, landed here after 2 days of search and it worked in less than 30 seconds. Terrific. Thanks a lot @Andreas Fester. – Eileneeilis

A mere upvote is not enough to express my gratitude. – Reinstate

Spent the whole day figuring out why i was not able to write to oracledb. Some issue connecting. This answer from 10 years ago is a lifesaver. Works like a charm! Thank you so much @andreas!!! – Kirbie

Answer 2

For those looking for how to specify service_name instead of SID.

From changelog for SQLAlchemy 1.0.0b1 (released on March 13, 2015):

[oracle] [feature] Added support for cx_oracle connections to a specific service name, as opposed to a tns name, by passing ?service_name=<name> to the URL. Pull request courtesy Sławomir Ehlert.

The change introduces new, Oracle dialect specific option service_name which can be used to build connect string like this:

from sqlalchemy import create_engine
from sqlalchemy.engine import url

connect_url = url.URL(
    'oracle+cx_oracle',
    username='some_username',
    password='some_password',
    host='some_host',
    port='some_port',
    query=dict(service_name='some_oracle_service_name'))

engine = create_engine(connect_url)

Retard

This is question about cx_Oracle not sqlalchemy – Serotherapy

Answer 3

If you are using sqlalchemy and ORACLE 12, the following seems to work.

from sqlalchemy import create_engine
con='oracle://user:password@hostname:1521/?service_name=DDDD'
engine = create_engine(con)

Note, you have to use the service name and not the SID. I don't know why, but the simple connection string that uses SID does not work.

Leach

Answer 4

It still may not work. You need to take the output of dsnStr and modify the string by replacing SID with SERVICE_NAME and use that variable in the con string. This procedure worked for me.

Beggarly

Answer 5

SID's may not be easily accessible or you might not have it created for your database.

In my case, I'm working from the client side requesting access to a cloud database so creating an SID didn't really make sense.

Instead, you might have a string that looks similar to this:

"(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = something.cloud.company)
(PORT = 12345)) (ADDRESS = (PROTOCOL = TCP)(HOST = something.cloud.company)
(PORT = 12345)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = 
something.company)))"

You can use it in replacement of the SID.

connection = cx_Oracle.connect("username", "pw", "(DESCRIPTION = (ADDRESS = 
                (PROTOCOL = TCP)(HOST = something.cloud.company)(PORT = 12345)) (ADDRESS = 
                (PROTOCOL = TCP)(HOST = something.cloud.company)(PORT = 12345)) 
                (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = something.company)))")

Dorettadorette

Answer 6

I thought during a while that I would not be able to use Magic SQL (%sql, %%sql) because of service name issue in connection that would force to use the alternative way described above with cx_Oracle.connect(), cx_Oracle.makedsn()... I finally found a solution working for me: declare and set a variable for the service name first and then use it in the command (since not working if literal string for service name put in the command !)

import cx_Oracle

user='youruser'
pwd='youruserpwd'
dbhost='xx.xx.xx.xx'
service='yourservice'

%load_ext sql
%sql oracle+cx_oracle://$user:$pwd@$dbhost:1521/?service_name=$service

output (what you get in successful connection):

u'Connected: youruser@'

Tenacious

Answer 7

If someone is looking to set oracle.jdbc.proxyClientName property for cx_oracle, to connect using proxyClient, they can use -

cx_Oracle.init_oracle_client("../../oracle_local_client", config_dir= "../../oracle_local_client/network/admin")

connectDsn = cx_Oracle.makedsn('db.svr.net', 'portNumberHere',service_name="TEST_READWRITE")
#replace all prams above

pool = cx_Oracle.SessionPool(externalauth=True, homogeneous=False, dsn = connectDsn)
connection = pool.acquire(user="[PROXY_CLIENT_NAME]")

Notice the use of '[' braces to depict that the user is proxyClient. I am using Kerberos authentication for this and my SQLNET.ora file contains the below properties.

NAMES.DIRECTORY_PATH=(TNSNAMES,HOSTNAME,EZCONNECT)
SQLNET.AUTHENTICATION_SERVICES = (BEQ,KERBEROS5PRE,KERBEROS5)
SQLNET.AUTHENTICATION_KERBEROS5_SERVICE=oracle
SQLNET.KERBEROS5_CC_NAME=OSMSFT:
SQLNET.KERBEROS5_CONF_MIT=TRUE
SQLNET.KERBEROS5_CONF=I:\projects\poc\resources\krb5.conf # krb5 config file complete path.

For more information, refer the video embedded in this article.

Burkett

Answer 8

I also met this issue. The solution is:

1: get the service name at tnsnames.ora
2: put the service name in
con_str = "myuser/[email protected]:1521/ora1"

Paniculate