?
Solved

SQL 2008 Linked Server "Access Denied"

Posted on 2011-02-27
12
Medium Priority
?
2,951 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:C Williams
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:C Williams
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:C Williams
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
 
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 1000 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:C Williams
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:C Williams
ID: 36913001
Partially correct.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

777 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