Solved

SQL 2008 Linked Server "Access Denied"

Posted on 2011-02-27
12
2,837 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
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!

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
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…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

770 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