[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5625
  • Last Modified:

Connect to Progress using datadirect odbc

Hello,
I have an SQL database that connects to a Progress database using linked servers with a DSN.  The DSN uses datadirect drivers.  When in SQL, I query Progress and I get the data just fine if I use the credentials of sysprogress user.  However if I use anything but sysprogress I get an error:
OLE DB provider "MSDASQL" for linked server " returned message "[DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Access denied (Authorization failed) (7512)".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "select * from pub.abccode" for execution against OLE DB provider "MSDASQL" for linked server.

Has anyone ever seen this?

Thank you


0
mtranstech
Asked:
mtranstech
1 Solution
 
ahenschCommented:
Have you granted privledges for the table?

GRANT SELECT ON pub.abccode TO<uid> or GRANT SELECT ON pub.abcode TO PUBLIC
0
 
Lieven EmbrechtsCommented:
progress version ?
0
 
progressorCommented:
You need to set security for all tables you want to access with that user.

Simple progress code to generate the SQL for all public tables is:


HTH,
Casper.
DEFINE VARIABLE cUser AS CHARACTER  NO-UNDO.

ASSIGN cUser = 'PUBLIC'. /* change this to specific username if you don't want to give all users select permission */

OUTPUT TO VALUE('c:\temp\allfiles' + cUser + '.sql').
FOR EACH _file WHERE NOT _file._hidden NO-LOCK:
     
  PUT UNFORMATTED 'grant select on pub."' + _file._file-name + '" to ' + cUser + '~;' SKIP.
  PUT UNFORMATTED 'commit;' SKIP.
END.

Open in new window

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
progressorCommented:
1 more thing: You can only run the sql grant as DBA (that is sysprogress or other DBA acount) and no other users are accessing the database, since a table lock is required to set the permissions.

Casper.
0
 
mtranstechAuthor Commented:
Hello Progressor,

I tried your suggestion and I get the same result unfortunately.  I even tried creating another DBA user and get the same result.  It's like the users don't have permission to use the Openedge odbc driver.

I am using Progress OpenEdge 10.1B.


Thank you
0
 
progressorCommented:
What if you query as sysprogress:

select * from systabauth;

Do you see a 'y' in the column select for public?
If so: Are you sure the user exists in the _user table of the database?

Casper.
0
 
mtranstechAuthor Commented:
Hello,
I was on vacation during the Holidays so you can understand I did take my worries with me.

I was able to find part of my answer on the net. If you google :"openedge sql authorization explained" you will find a word document I used find out how to do it. I have attached a copy of the document just in case.

However I still have a problem.
I do not want to execute the grant statement some 300 times. I still need to find a script that will fix allthe permissions for me.  Since I am very green at progress I have no idea where to start.  

Thank you.
OpenEdge-SQL-Authorization-Expla.doc
0
 
progressorCommented:
Use this Progress 4GL code to generate the script:

This generates scripts to give select permissions to all users (public). If you only want to authorize a specific user then change the script acoordingly (change public for a username).

HTH,

Casper


DEFINE VARIABLE cUser AS CHARACTER NO-UNDO.

ASSIGN cUser = 'Public'.

OUTPUT TO c:\temp\allfiles.sql.
  
FOR EACH _file WHERE _file._hidden = NO:
    PUT UNFORMATTED 'grant select on pub."' _file._file-name + '" to ' cUser  ';' SKIP.
    PUT UNFORMATTED 'commit;' SKIP.
END.

Open in new window

0
 
mtranstechAuthor Commented:
I did find another way to do this. I used a file with a statements for every table in the database.  But I like this solution better as it makes for better programming.  

Thank you  
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Tackle projects and never again get stuck behind a technical roadblock.
Join Now