ODBC Access to Multi Tenant OpenEdge DB

Posted by maynardr on 21-Apr-2017 11:36

I am looking at using OpenEdge Multi Tenancy database for a solution that requires connections via a) a SQL Server Linked server, and b) .Net ODBC connection.

I am using Progress OpenEdge 11.6 Driver (actually 11.6.3).
Using OpenEdge Explorer with Sports2000 database, I have:

  • Enabled Multi Tenancy;
  • Created two new Tenants (Ten1 and Ten2);
  • Created two domains (Tenant1 and Tenant2);
  • Created two users Ray@Tenant1 and Ray@Tenant2;
  • Started Brokers (32 bit and 64 bit);
  • On Window (win 7) Created System ODBC sources (one for each Tenancy) Connections Tested OK with Ray@Tenant1/2

On SQL Server I can create a Linked server using ODBC DSNs, I have to supply userid and password on security tab. That works fine. I can see the Caltalogs contains SPORTS2000, Tables contains PUB.Customer etc
But when a select of this is tried like select CustNum from [SPORTSTEN2].[SPORTS2000].[PUB].[Customer]
I get this error.
OLE DB provider "MSDASQL" for linked server "SPORTSTEN2" returned message "[DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Access denied (Authorisation failed) (7512)".

Via a C# program, taken from Progress sample, I can make and open an ODBC connection to this database, but when I execute a select on PUB.Customer I get
ERROR [HY000] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Access denied (Authorisation failed) (7512)

I have looked at user permissions for users using OE explorer. It shows my Tenant Users have can-Read and can-Write on PUB.customer.

any ideas on what I am missing?

As you may have guessed I am an MS stack developer. We have used Linked servers and ODBC before for NO Multi Tenancy OpenEdge databases OK.

PS. SQL Server 2012. DotNet 4.5.2

All Replies

Posted by steve pittman on 21-Apr-2017 13:44

You need to GRANT  access privileges to your tenant users, just as you would GRANT privileges on an ordinary OE database.
For example:
              GRANT SELECT ON  pub.customer TO  Ray@Tenant1
Hope this helps,           …sjp

Posted by maynardr on 24-Apr-2017 09:27

I have connected to the database using procedure editor, via progress data administration program. Both as blank userid and also as Ray@Tenant1.

I can select from customer OK.

I can also select from _user, and can see my two users (both called Ray with different Domains and TenantIDs)

BUT the suggested GRANT SELECT ON  pub.customer TO  Ray@Tenant1 returns

Database PUB not connected. (1021)

If I remove the pub. I get

Table customer does not exist or cannot be accessed. (962)

Is this how I should execute the GRANT?

OE Explorer has "Review user data security" option, which seems to suggest that Ray@Tenant1 can read/write any column on customer.

If I am misunderstanding your suggestion please let me know what to try.



This thread is closed