SQL 2008 Linked Server "Access Denied"

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
or
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?
C WilliamsDBAAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
dbaSQLConnect With a Mentor Commented:
As long as you have a user mapping within the Progress database, I would think db_datareader is more than adequate.  If, of course, you are only reading the table data.  Confirm your db user privileges:
http://www.progress.com/progress/products/documentation/docs/database/dgr/dgr.pdf
0
 
dbaSQLCommented:
Don't think it has anything to do with your query, or the data in the table.  Both errors suggest authentication failure.  
Looks like they had the same problem:
http://www.progresstalk.com/showthread.php?118350-Problem-linking-to-Open-Edge-10.2B-from-64-bit-Sql-Server-2005
0
 
dbaSQLCommented:
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
KetGuruCommented:

can you please create a sample table with dbo rights and openquery like this. it is very authentication issue.

select column1 from openQuery(Sandbox, 'select column1 from mfgDatabase.dbo.SampleTable')


0
 
C WilliamsDBAAuthor Commented:
I would agree with both of you that security isn't set correctly someplace...I'm just having an issue of where.  I tried your script KetGuru to generate the sql code, then place that code into sql to try to set the correct permissions, but I still get a permission error there:

Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'userFile', because it does not exist or you do not have permission.

Your second link, KetGuru...I tried different options for my provider string, adding or deleting a user id within and it made no difference.

Below is my provider string, does this seem sufficient:

Provider=MSDASQL.1;Persist Security Info=False;User ID=serviceUser;Data Source=Sandbox
0
 
KetGuruCommented:

1. Can you please check Server Objects --> Linked Servers --> right click on linked provider and properties --> Security  and see if it is proper.
2. can you please Server options at same place as well and see if DataAccess is True.

Thanks,

0
 
C WilliamsDBAAuthor Commented:
Yes, to both of your questions; Data Access is set to True and the security is set to a user that has access to the database.

I guess my next question is, which is in relation to what you guys are suspecting is my issue, the user that is being used for connection to the Progress database...what are the permissions that should be set on the Progress database.  The user that I'm using has select rights to the table that I'm trying to access...but are there other permissions for that database that need to be set?
0
 
KetGuruCommented:

If you are using both same sql server than why donot you try Server type sql server and work without connection. db_datareader permission that is minimum requirement for an account to perform this task.
if it is possible go specific database and under security add this service user with atlease db_datareader user mapping .
you may try this as well

select * from openquery( SANDBox,'select getdate()') to confirm if you need to specify some schema specific  permission. i tried all the option with domain account and sql account it seems work for me with choosing type sql server omiiting requirement to define connection requirement.
0
 
C WilliamsDBAAuthor Commented:
I would like to clarify something...I was able to create the ODBC connection successfully, I was able to create the linked server successfully, and am able to see the linked servers tables without a problem...but should I see the linked server in the object explorer as I would see any other database?  Because I don't.  I only see the database in the 'linked servers' section of the object explorer, no where else.

So with that said, if I go into the Security section of SQL, I'm unable to give security priviledges to a user for the linked server as I would with any other database.  It doesn't show up there.  Nor does it show up in the drop listing of the different servers in the current SQL instance.

One last thing, KetGuru, I'm using SQL to connect/link to a Progress database.
0
 
dbaSQLCommented:
No, if it is not a SQL Server, you will not see it within the SSMS object explorer.  Please see sections 10.1 and 10.2 of the pdf I attached in my last post.  It tells you details regarding the progress user authentication.
0
 
mellerbeckCommented:
CoreyAnt, not related to this question but one of your other questions looks like you were dealing with Adage TEE files :) anyways just wondering what sort things you had done in that regard. Ping me sometime to collaborate michaelellerbeck.com
0
 
C WilliamsDBAAuthor Commented:
Partially correct.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.