TEIID D50072 when connecting via Teiid JDBC driver from Python using JayDeBeApi module

[lewis.watson]

Hello,

I've been attempting to connect to a VDB from a Python script using the JayDeBeApi (https://launchpad.net/jaydebeapi) module. However I can't seem to be able to set the username and password for the connection.

This is my code

import jaydebeapi
import jpype

jar = r'/path/to/jboss-eap-6.1/dataVirtualization/jdbc/teiid-8.4.1-redhat-7-jdbc.jar'
args='-Djava.class.path=%s' % jar
jvm_path = jpype.getDefaultJVMPath()
jpype.startJVM(jvm_path, args)

conn = jaydebeapi.connect('org.teiid.jdbc.TeiidDriver', 'jdbc:teiid:EmployeePeopleVDB2@mm://localhost:31000', 'user', 'user')
curs = conn.cursor()

This is the error message I get

Jun 02, 2014 11:16:38 AM org.teiid.jdbc.TeiidDriver connect
SEVERE: Could not create connection
org.teiid.jdbc.TeiidSQLException: TEIID50072 The username "anonymous" and/or password and/or payload token could not be authenticated by any membership domain.
    at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:135)
    at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:71)
    at org.teiid.jdbc.SocketProfile.connect(SocketProfile.java:56)
    at org.teiid.jdbc.TeiidDriver.connect(TeiidDriver.java:107)
    at org.teiid.jdbc.TeiidDriver.connect(TeiidDriver.java:55)
    at java.sql.DriverManager.getConnection(DriverManager.java:571)
    at java.sql.DriverManager.getConnection(DriverManager.java:233)
Caused by: org.teiid.net.ConnectionException: TEIID50072 The username "anonymous" and/or password and/or payload token could not be authenticated by any membership domain.
    at org.teiid.net.socket.SocketServerConnection.selectServerInstance(SocketServerConnection.java:139)
    at org.teiid.net.socket.SocketServerConnection.<init>(SocketServerConnection.java:92)
    at org.teiid.net.socket.SocketServerConnectionFactory.getConnection(SocketServerConnectionFactory.java:312)
    at org.teiid.jdbc.SocketProfile.connect(SocketProfile.java:54)
    ... 4 more
Caused by: org.teiid.client.security.LogonException: TEIID50072 The username "anonymous" and/or password and/or payload token could not be authenticated by any membership domain.
    at org.teiid.transport.LogonImpl.logon(LogonImpl.java:114)
    at org.teiid.transport.LogonImpl.logon(LogonImpl.java:82)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.teiid.transport.ServerWorkItem.run(ServerWorkItem.java:87)
    at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:269)
    at org.teiid.transport.SocketClientInstance.processMessagePacket(SocketClientInstance.java:192)
    at org.teiid.transport.SocketClientInstance.receivedMessage(SocketClientInstance.java:178)
    at org.teiid.transport.SSLAwareChannelHandler.messageReceived(SSLAwareChannelHandler.java:201)
    at org.jboss.netty.channel.SimpleChannelHandler.handleUpstream(SimpleChannelHandler.java:88)
    at org.jboss.netty.channel.DefaultChannelPipeline.sendUpstream(DefaultChannelPipeline.java:564)
    at org.jboss.netty.channel.DefaultChannelPipeline$DefaultChannelHandlerContext.sendUpstream(DefaultChannelPipeline.java:791)
    at org.jboss.netty.handler.stream.ChunkedWriteHandler.handleUpstream(ChunkedWriteHandler.java:142)
    at org.jboss.netty.channel.DefaultChannelPipeline.sendUpstream(DefaultChannelPipeline.java:564)
    at org.jboss.netty.channel.DefaultChannelPipeline$DefaultChannelHandlerContext.sendUpstream(DefaultChannelPipeline.java:791)
    at org.jboss.netty.channel.Channels.fireMessageReceived(Channels.java:296)
    at org.jboss.netty.handler.codec.frame.FrameDecoder.unfoldAndFireMessageReceived(FrameDecoder.java:462)
    at org.jboss.netty.handler.codec.frame.FrameDecoder.callDecode(FrameDecoder.java:443)
    at org.jboss.netty.handler.codec.frame.FrameDecoder.messageReceived(FrameDecoder.java:303)
    at org.jboss.netty.channel.SimpleChannelUpstreamHandler.handleUpstream(SimpleChannelUpstreamHandler.java:70)
    at org.jboss.netty.channel.DefaultChannelPipeline.sendUpstream(DefaultChannelPipeline.java:564)
    at org.jboss.netty.channel.DefaultChannelPipeline.sendUpstream(DefaultChannelPipeline.java:559)
    at org.jboss.netty.channel.Channels.fireMessageReceived(Channels.java:268)
    at org.jboss.netty.channel.Channels.fireMessageReceived(Channels.java:255)
    at org.jboss.netty.channel.socket.nio.NioWorker.read(NioWorker.java:88)
    at org.jboss.netty.channel.socket.nio.AbstractNioWorker.process(AbstractNioWorker.java:109)
    at org.jboss.netty.channel.socket.nio.AbstractNioSelector.run(AbstractNioSelector.java:312)
    at org.jboss.netty.channel.socket.nio.AbstractNioWorker.run(AbstractNioWorker.java:90)
    at org.jboss.netty.channel.socket.nio.NioWorker.run(NioWorker.java:178)
    at org.jboss.netty.util.ThreadRenamingRunnable.run(ThreadRenamingRunnable.java:108)
    at org.jboss.netty.util.internal.DeadLockProofWorker$1.run(DeadLockProofWorker.java:42)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:745)
Caused by: javax.security.auth.login.LoginException: TEIID50072 The username "anonymous" and/or password and/or payload token could not be authenticated by any membership domain.
    at org.teiid.jboss.JBossSessionService.authenticate(JBossSessionService.java:74)
    at org.teiid.services.SessionServiceImpl.createSession(SessionServiceImpl.java:157)
    at org.teiid.transport.LogonImpl.logon(LogonImpl.java:99)
    ... 35 more

Traceback (most recent call last):
  File "jdbc.py", line 9, in <module>
    conn = jaydebeapi.connect('org.teiid.jdbc.TeiidDriver', 'jdbc:teiid:EmployeePeopleVDB2@mm://localhost:31000', 'user', 'user')
  File "/usr/local/lib/python2.7/dist-packages/JayDeBeApi-0.1.4-py2.7.egg/jaydebeapi/dbapi2.py", line 269, in connect
    jconn = _jdbc_connect(jclassname, jars, libs, *driver_args)
  File "/usr/local/lib/python2.7/dist-packages/JayDeBeApi-0.1.4-py2.7.egg/jaydebeapi/dbapi2.py", line 117, in _jdbc_connect_jpype
    return jpype.java.sql.DriverManager.getConnection(*driver_args)
jpype._jexception.SQLExceptionPyRaisable: org.teiid.jdbc.TeiidSQLException: TEIID50072 The username "anonymous" and/or password and/or payload token could not be authenticated by any membership domain.

My authentication settings are pretty simple:

teiid-security-users.properties

# A users.properties file for use with the UsersRolesLoginModule
# username=password

user=user

teiid-security-roles.properties

# A roles.properties file for use with the UsersRolesLoginModule
# username=role1,role2
#user=odata,example-role

# Dashboard builder roles
#  edit mode: admin
#  read only: user

user=user
user=odata

I can connect to the VDB using the same driver + settings in SQuirreL SQL. I'm not sure if its just the way I've set things up or if its a bug in Teiid/JayDeBeApi.

Any suggestions welcome!

Regards

Lewis

[lewis.watson]

I might be using JayDeBeApi wrong!

I created another script that uses the PostgreSQL JDBC driver

import jaydebeapi
import jpype

jar = r'/home/lewis/Downloads/postgresql-8.3-607.jdbc4.jar'
args='-Djava.class.path=%s' % jar
jvm_path = jpype.getDefaultJVMPath()
jpype.startJVM(jvm_path, args)

conn = jaydebeapi.connect('org.postgresql.Driver', 'jdbc:postgresql://localhost:5432/EmployeePeople', 'user', 'password')
curs = conn.cursor()

And I get a similar error relating to the username:

Traceback (most recent call last):
  File "psycopg.py", line 10, in <module>
    cur.execute("CREATE TABLE test (id serial PRIMARY KEY, num integer, data varchar);")
psycopg2.OperationalError: TEIID31100 Parsing error: Encountered "CREATE [*]TABLE[*] test (" at line 1, column 8.
Was expecting: "foreign" | "local" | "procedure" | "trigger" | "virtual"
DETAIL:  org.teiid.jdbc.TeiidSQLException: TEIID31100 Parsing error: Encountered "CREATE [*]TABLE[*] test (" at line 1, column 8.
Was expecting: "foreign" | "local" | "procedure" | "trigger" | "virtual"

sa-cgi-ubuntu@CGI301479:~/workspace/Employees6/tests$ python psycopg.py 
sa-cgi-ubuntu@CGI301479:~/workspace/Employees6/tests$ python postgresql_jdbc.py Traceback (most recent call last):
  File "postgresql_jdbc.py", line 9, in <module>
    conn = jaydebeapi.connect('org.postgresql.Driver', 'jdbc:postgresql://localhost:5432/EmployeePeople', 'user', 'PASSWORD')
  File "/usr/local/lib/python2.7/dist-packages/JayDeBeApi-0.1.4-py2.7.egg/jaydebeapi/dbapi2.py", line 269, in connect
    jconn = _jdbc_connect(jclassname, jars, libs, *driver_args)
  File "/usr/local/lib/python2.7/dist-packages/JayDeBeApi-0.1.4-py2.7.egg/jaydebeapi/dbapi2.py", line 117, in _jdbc_connect_jpype
    return jpype.java.sql.DriverManager.getConnection(*driver_args)
jpype._jexception.SQLExceptionPyRaisable: org.postgresql.util.PSQLException: FATAL: no PostgreSQL user name specified in startup packet

I read somewhere that the Teiid JDBC driver is designed to work like the PostgreSQL JDBC driver so I tried using the psycopg2 module (http://initd.org/psycopg/) and I managed to connect!

import psycopg2

# Connect to an existing database
conn = psycopg2.connect("dbname=EmployeePeopleVDB2 user=user password=user host=localhost port=35432")

# Open a cursor to perform database operations
cur = conn.cursor()

# Query the database and obtain data as Python objects
cur.execute("SELECT * FROM EmployeePeople;")

# Make the changes to the database persistent
conn.commit()

# Close communication with the database
cur.close()
conn.close()

[rareddy]

Lewis,

I am not familiar with jaydebe code, so I can not comment on it, but seems like the issue is in there to correctly pass user and password. May be you can try another constructor on the driver with properties, if jaydebe supports it. Teiid implements Postgres binary protocol for client communications. This how Postgres ODBC driver can be used with Teiid, so are many other postgres drivers. If you found a native postgres client for python I would use that, as you did.

Ramesh..

[shawkins]

To add a little more, the first error that you are seeing as Ramesh says appears that the user name is not getting passed correctly.  Should be "user" rather than "anonymous" - which must be coming from the api layer as we do not default the user name.

Using the pg driver was somewhat successful in that it did authenticate.  However the error you seeing there means that the api or your application is attempting to create tables, which is not something that we directly allow.  The foreign tables can be defined at deployment time (either with ddl or pulled from the source metadata) or brought in on a temporary basis as a temp table.

Steve