Question Allowing blank user connections while allowing SQL connections

gbrown

New Member
I am trying to connect from MSSQL to OE 11.3 using the DataDirect ODBC connector. I tried following the Progress KB article for creating an "Implicit SQL user that does not need a password" (P125338) and "How to configure a SQL connection without maintaining a user list" (P69145). Neither solution seemed to work and I'm trying to find out what I did wrong.

Steps I've taken:
I've created a user in _user and verified I can connect via ODBC and query the MFGPRO database from sqlexp. This tells me my ODBC connection is working.
I've created a testdb from empty and followed the steps from the KB (add sysprogress - connect via SQL - grant privileges to "implicit user" user1 - remove sysprogress) and tried to attach to the database as user1. Authentication failed.

The OE databases are running on RHEL6 (if that matters) and I'm testing my connections from the same server and from a Windows Server 2012 box.
 

Cringer

ProgressTalk.com Moderator
Staff member
Are you who I think you are? Nice to "see" you if so. :)

Could you post the exact error you're getting in ODBC please? Sometimes the errors here can be useful in pinpointing the issue.

Did you set up a broker to accept SQL connections? The recommendation is to add a secondary broker for SQL btw as having a mixed broker can cause resource issues.
 

gbrown

New Member
Thank you for your response. I'll look into the secondary broker angle but the KB didn't make it sound like it was required.

The .pf for the test database is:
-B 2000 -spin 10000 -L 80000 -Ma 32 -Mn 5 -Mi 1 -n 129 -Mf 3 -H XXXXXXXXX -S XXXXX -N tcp -ServerType SQL

In sqlexp.log the message is:
[6/23/21 9:38:32 AM]=== Start logging. Local time: 6/23/21 9:38:32 AM. ===
[6/23/21 9:38:32 AM]=== Progress Version 11.3.03.000 1461 ===
[6/23/21 9:38:32 AM]
[6/23/21 9:38:32 AM] [3] [SQLExplorer] Local /devl1/db/SQLExplorer.properties file will be used. (SQLMsg036)
[6/23/21 9:38:32 AM] [3] [SQLExplorer] Setting Connect to jdbc:datadirect:eek:penedge://XXXXXXXX:XXXXXX;databaseName=testdb
[6/23/21 9:38:32 AM] [3] [SQLExplorer] === SQLExplorer starting. ===
[6/23/21 9:38:32 AM] [3] [SQLExplorer] ### ARGS: /devl1/db/testdb -S XXXXXX -H XXXXXXXXX -user user1 -password xxx
[6/23/21 9:38:32 AM] [3] [SQLExplorer] Connecting user "user1" to URL "jdbc:datadirect:eek:penedge://iac-qdbdl02:29045;databaseName=testdb"... (8920)
[6/23/21 9:38:32 AM] [3] [SQLExplorer] Loading JDBC driver com.ddtek.jdbc.openedge.OpenEdgeDriver.
[6/23/21 9:38:32 AM] [0] [*UnexpectedError*] * recorded as exception #1 in file /devl1/db/SQLExplorer.exceptions.
[6/23/21 9:38:32 AM] [3] [SQLExplorer] === SQLExplorer ending. ===

In SQLExploer.exceptions the message is:
====== Start exception logging == "/devl1/db/SQLExplorer.exceptions" opened == Wed Jun 23 09:38:32 EDT 2021 ======


**** 1 ****

Exception at Wed Jun 23 09:38:32 EDT 2021: java.sql.SQLException
Message (throw): ### Connect stack trace. ###
Message (excp): [DataDirect][OpenEdge JDBC Driver][OpenEdge] Access denied(Authorisation failed)
Stack Trace:
java.sql.SQLException: [DataDirect][OpenEdge JDBC Driver][OpenEdge] Access denied(Authorisation failed)
at com.ddtek.jdbc.openedge.client.ddd.aw(Unknown Source)
at com.ddtek.jdbc.openedge.client.ddd.j(Unknown Source)
at com.ddtek.jdbc.openedge.OpenEdgeImplConnection.j(Unknown Source)
at com.ddtek.jdbc.openedgebase.BaseConnection.b(Unknown Source)
at com.ddtek.jdbc.openedgebase.BaseConnection.k(Unknown Source)
at com.ddtek.jdbc.openedgebase.BaseConnection.a(Unknown Source)
at com.ddtek.jdbc.openedgebase.BaseConnection.a(Unknown Source)
at com.ddtek.jdbc.openedgebase.BaseDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(DriverManager.java:571)
at java.sql.DriverManager.getConnection(DriverManager.java:215)
at com.progress.sql.explorer.SQLConnectServer.call(SQLConnectServer.java:41)
at com.progress.common.rmiregistry.TryIt.run(TryIt.java:221)

The .pf for the connection is:
:
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
I've created a testdb from empty and followed the steps from the KB (add sysprogress - connect via SQL - grant privileges to "implicit user" user1 - remove sysprogress) and tried to attach to the database as user1. Authentication failed.

Message (excp): [DataDirect][OpenEdge JDBC Driver][OpenEdge] Access denied(Authorisation failed)
Stack Trace:
java.sql.SQLException: [DataDirect][OpenEdge JDBC Driver][OpenEdge] Access denied(Authorisation failed)
Initially you said you got "authentication failed", which would be a login error. Then the error you showed was "authorization failed", which means you logged in successfully but were not authorized for the data you tried to access.

Were these two separate incidents? Or was it actually an authorization error? If the latter, your credentials are fine and you have not been GRANTed the appropriate privileges for the tables you are trying to access.

The .pf for the connection is:
:
Did you mean to include something?
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
By default, a SQL user is denied all privileges on all tables until they are GRANTed specific per-table privileges by a DBA user. The exception is when the user in question is a DBA. In that case they have all privileges on all existing tables and and new tables that are added later. But for non-DBA users, the security model is default-deny.

This model can be inconvenient; there is often a trade-off between security and convenience. But it is more secure than the default-allow model for ABL object security. Some people are tempted to "solve" the SQL inconvenience problem by turning ordinary users into DBAs, so they don't have to learn what the privileges are, or how to grant privileges, and they don't have to remember to do it for new objects after a schema addition. Needless to say, this is the wrong solution.

More info:
Progress KB - Basic Guide to Defining Progress SQL-92 Database Permissions & Security
 

gbrown

New Member
Thank you Rob. The "Access deined (Authorisation failed)" error is correct.

I appreciate the point you are making about the GRANT statement. As part of the Progress KBs P125338 and P69145 workarounds, one creates the "implict" SQL user by granting DBA and RESOURCE permission to the "implicit user". As you pointed out, granting DBA should address any "Authorisation failed" errors and yet that's the error I get.

Would anyone on the board be willing to follow the steps in P125338 / P69145 and just confirm that the problem lies with me and not with the instructions? It's should be as simple as copying sports, following the KB, and then trying to connect through ODBC. My gratitude will automatically be upgraded to extreme if the test is done against OE 11.3.x. ;)

Or, if someone running QAD & ODBC has a more perfect way of dealing with OE log files and Promon displaying the blank user, I'm would eagerly test other solutions!
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
I'll try for the "upgrade" later tonight. :)
Better late than never... Testing on 64-bit 11.3.0 on Linux.

P125338 (Knowledge Article):
This KB lists two access methods:
  • using the default DBA privilege of the database creator account;
  • creating an implicit account and granting DBA privilege to it.
I can get both to work, though the KB instructions are a little lacking.

Option 1:
  • Create a db (logged in to Linux as user "rob").
    prodb sp sports
  • "rob" can connect to the SQL broker and log in and will be a DBA.
  • Start the DB.
    proserve sp -S 4444 -ServerType SQL
  • Connect via sqlexp, with any value as password.
    sqlexp sp -S 4444 -user rob -pass pass
  • Query the list of DBAs.
    sqlexp> select * from sysprogress.sysdbauth; (succeeds)
  • Query some data.
    sqlexp> select * from pub.customer; (succeeds)
Option 2:
  • Log in as an existing DBA; first start the DB if it's currently down.
    sqlexp sp -S 4444 -user rob -pass pass
  • Grant DBA privilege to the new implicit user (note the case).
    GRANT DBA to user1;
  • Commit the transaction!
    commit work;
  • Log out
    sqlexp> exit
  • Log in as the new implicit user.
    sqlexp sp -S 4444 -user user1 -pass pass
  • Query the list of DBAs.
    sqlexp> select * from sysprogress.sysdbauth; (succeeds)
  • Query some data.
    sqlexp> select * from pub.customer; (succeeds)
This should work for you so I haven't tried the other KB article. Let me know if you need me to try that or if the above doesn't work for you.

Note: these approaches leave your database insecure, especially if multiple people are sharing these credentials. If these databases are copies of production databases, that data could be at risk.
 
Top