Linked Server Progress to SSQL Server via ODBC

CEF

New Member
I want to link a Progress DB to MS SQL Server. I use the guide on:
Progress KB - How to link to a Progress / OpenEdge database from Microsoft SQL Server using a Progress / OpenEdge ODBC Driver ?

Local on my Windows 8 notebook, I have also installed a MS SQL Management Studio.

"1) Setup an ODBC connection to the Progress eatabase in the ODBC Administrator in the System DSN. Make sure the connection successfully establishes."

So I looked under System-DNS and find it. It is called pA52_T2_ODBC. 64-Bit. Progress OpenEdge 10.2B Driver. When I test the connection, it says "Connection established!".

So I think step 1 is done.

"2) Bring up SQL Server Management Studio, connect to Object Explorer. Under Server Objects -> Linked Servers, Right click -> New Linked Server. 3) Provide the information to following options:
Linked server - Provide the name you will use to refer to this linked server. Server Type - Select "Other data source" as server type. Clicking this option activates the options below it. Provider - Microsoft OLE DB provider for ODBC driver. Product name - it could be anything but better to use progress since it is a progress database. Data source - ODBC System DSN name. The rest of the options can be left blank".


And now it doesn't work! I get an Error-Msg: The linked server has been updated but failed a connection test... Error: 7303.

Why works the connection test in step 1, but not with step 2?
Any ideas?

And I tried to import a table from progress to MS Access. I use the pA52_T2_ODBC, type in my loginname and password. Then a window opens and I can chooese all the progress tables. But when I choose one and click Ok then I get this error:

[DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Access denied (Authorization failed) (7512)(#-20228).


Why can I pass first with my login data, but in second step it failed about this!?

PLEASE HEPL ME :)
 

Cringer

ProgressTalk.com Moderator
Staff member
Have you done a GRANT on the tables you want to access for your user?
 

CEF

New Member
Hello :)

No I dont. Do you mean that:
GRANT (Transact-SQL)

How is exactly the Syntax?

1) 10.2B (pA 5.2)
2) Windows Server 2016 / 64-Bit+ SQL Server 2016 Express (but also tried with SQL Server 2008 R2 Data Center)
3)See above
4) See above (Access denied)

THX. I will try GRANT tommorow.
 

CEF

New Member
I try to use GRANT, but I don't know how :-( And I don't understand the KB article about security...
F^^k. Why is that so complicated???
 

CEF

New Member
1515150842631.png

You see, this works. But Access denied when I make a Test Connect on SQL Server... WHY?
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
This may be a difference between authentication (being allowed to connect and having your credentials validated) and authorization (being allowed to access objects, based on your identity). SQL uses a default-deny model. You will only be allowed to access tables for which you have been granted at least the SELECT permission.
 

RealHeavyDude

Well-Known Member
Please don't get me wrong, but, doing security right is not an easy task and probably never will be. That's maybe also why we read so much about security flaws and so many software fails to implement security correct or does not implement security at all. There always will be a tradeoff between security and ease of accessibility. At least that's the way IT works right now and we have not come up with a better way of making things secure yet ...

The Progress OpenEdge database is not a native SQL database in the first place. It supports SQL. In that it differs to SQL databases like MSSQL and others which are. The Progress OpenEdge database is a native ABL database (where acronym ABL stands for the Progress language which is a 4GL language). There is a discrepancy between in philosophy also: Native ABL is revoke whereas SQL is grant. That means that you need to setup the SQL securitiy on any SQL database I know before you are able to access anything. Plus, as Rob already mentioned, there is a vast difference between authentication and authorization.

The only difference is that the Progress database does not come with a built-in SQL account out-of-the-box. It just sort of does in that the OS account under which the database was created per default is granted with DBA privileges. But nobody I know uses that. Most I know create a SYSPROGRESS user with the ABL data administration tool and go from there.

Nevertheless the KB articel perfectly explains how to setup the SQL security on a Progress OpenEdge database.
 
Top