Extracting data from SQL Server linked server

Question

I am using MS SQL Server 2012, and I am trying to extract data from a Linked Server database.

In SQL Server management studio, I can see the linked server. I can browse the database on the server. I can see all tables in database.

When I try and query a linked table

select * from openquery(somedatabase,'select top 4 * from test')

I get error:

The OLE DB provider "MSDASQL" for linked server "somedatabase" indicates that either the object has no columns or the current user does not have permissions on that object.

When I try to script the table.. right_click on the linked table, Script table as, Select to, New Query Editor window, I get same error.

Any help would be greatly appreciated.

I referred to following posts but it did not help.

http://www.sqlservercentral.com/Forums/Topic1036585-391-1.aspx

Error Querying MySQL server linked to SQL Server 2008

http://wiki.servicenow.com/index.php?title=Using_ODBC_Driver_in_SQL_Server#gsc.tab=0

Thanks.

• Hannah Vernon: so, you don't have access to the table. Check the login used by the linked server to see if it has been GRANTed access.
• Harleen: Yes. I am using admin login

Answer 1

Check the link server setup and see HOW the users connect to the link server. This can be done by going to Server Objects in SSMS -> Linked Servers -> [Link server name] -> right click then select Properties -> security tab. It can be done without a security context, using the current login's security context, or it can be done using a specific login. Find out which account is being used and then make sure that account has access to the table on the linked server.

• Harleen: Thanks for replying Eric. I tried using without a security context and administrator login. But I get same error.
• Eric S: Do you have windows authentication set up for both servers or do you have the same login information for both? If you do, try using current security login context. One other thing I can think of is make sure the two servers can see each other so try pinging the server you are connecting to from the server you are trying to connect from.