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:
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
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.