I've setup a linked server on SQL Server 2008, connecting to a Progress database. Below are steps that I've taken so far, but I'm unable to make the simplest of queries on the linked database.
1. I created an ODBC connection to the Progress database: Connection tests work.
2. Using SQL manager, I created a linked server to my Progress database. Again, connection tests go well.
3. I've mapped user security, I believe correctly.
local login: a domain user with sa priviledges on the SQL server
Remote user: a Progress database local user with sa priviledges on that database
With the steps above, I can see all the tables from my Progress database in the linked server catalog...in the object explorer, or by using the sp_tables_ex N'Sandbox' stored procedure command.
So everything seems good to this point, but if I try to run a query against that database, I get an error:
OLE DB provider "MSDASQL" for linked server "Sandbox" returned message "[DataDirect] [ODBC Progress OpenEdge Wire Protocal driver] [OpenEdge] Access denied (Authorization failed)(7512)".
Msg 7306, Level 16, State 2, Line 1
Cannot open the table "MFGDatabase"."PUB"."UserFile" from OLE DB provider "MSDASQL" for linked Server "Sandbox".
The syntax of my query:
select * from Sandbox.MFGDatabase.pub.userFile
select * from openQuery(Sandbox, 'select * from mfgDatabase.pub.userFile')
As for this table...yes, I know for a fact that there should be data in this table.
If I try to view data in the table by right clicking the table in the objects explorer and go to 'Script table as | Select to | New Query Window'...I don't get an error message I get:
-- [Sandbox].[mfgDatabase].[pub].[userFile] contains no columns that can be selected or the current user does not have permissions on that object.
What am I missing?
Do I have to do another step that pulls data into these linked tables?
Am I messing up my security settings?