Solved

SQL 2008 Linked Server "Access Denied"

Posted on 2011-02-27
12
2,796 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:CoreyAnt
  • 4
  • 4
  • 3
  • +1
12 Comments
 
LVL 17

Expert Comment

by:dbaSQL
ID: 34994176
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
 
LVL 17

Expert Comment

by:dbaSQL
ID: 34994205
0
 
LVL 3

Expert Comment

by:KetGuru
ID: 34996214

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
 

Author Comment

by:CoreyAnt
ID: 34997299
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
 
LVL 3

Expert Comment

by:KetGuru
ID: 34997482

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
 

Author Comment

by:CoreyAnt
ID: 34998734
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 3

Expert Comment

by:KetGuru
ID: 34999009

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
 
LVL 17

Accepted Solution

by:
dbaSQL earned 500 total points
ID: 34999077
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
 

Author Comment

by:CoreyAnt
ID: 35002452
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
 
LVL 17

Expert Comment

by:dbaSQL
ID: 35002509
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
 

Expert Comment

by:mellerbeck
ID: 35515828
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
 

Author Closing Comment

by:CoreyAnt
ID: 36913001
Partially correct.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now