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


BUT
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

OpenEdge Development - Forum

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.

Thanks

Ray

This thread is closed