Linked Server connects, but not usable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Linked Server connects, but not usable

Author

Topic

mkenyon2
Starting Member

5 Posts

Posted - 2013-10-29 : 15:47:30

In my scenario we have two DBs on two different servers. InternalProd and CustomerProd, both running on SQL 2008 R2.

The Link was created on InternalProd like so:
sp_dropserver 'LnkCustomerProd', 'droplogins'
GO

EXEC sp_addlinkedserver
@server = 'LnkCustomerProd' -- Name used in queries
, @provider = 'SQLNCLI' -- SQL Native Client
, @srvproduct = ''
, @datasrc = '168.xxx.yyy.zzz' -- IP Address of the CustomerProd server
GO

sp_addlinkedsrvlogin 'LnkCustomerProd' , 'false', NULL, 'SqlUserName','SomePassword'
GO

When I right click on the LinkedServer object and click 'Test Connection' it comes back that it succeeded. When the DBA expands 'Catalogs' from his system, he sees the appropriate databases.
When I try to expand 'Catalogs' I get the error message: Failed to retrieve data for this request. The EXECUTE permission was denied on the object 'xp_prop_oledb_provider', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)

When I try to run a SELECT statement against the Linked Server I get the message that the object name is invalid.

I can get myself GRANTed EXECUTE permissions to that sp, but what would be the correct practice here?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-10-29 : 16:10:10

whatever you want to SELECT or EXEC you need to grant those privs to SqlUserName. did you do that?

Be One with the Optimizer
TG

mkenyon2
Starting Member

5 Posts

Posted - 2013-10-29 : 16:26:26

quote:
Originally posted by TG

whatever you want to SELECT or EXEC you need to grant those privs to SqlUserName. did you do that?

Yes, we did.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-10-29 : 16:40:04

I assume the login [SqlUserName] is a sql login. Try connecting to the remote server (from new query window) using sql authentication with that user/password. whatever you can and can't do from there should be the same as when anyone connects from the linked server.

Be One with the Optimizer
TG

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-10-30 : 07:15:42

What does the FROM clause look like in the select statement?

djj

jiluthej
Starting Member

2 Posts

Posted - 2013-10-30 : 08:48:52

Try this query,

select * from openquery(linkedservername,'select * from <databasename>.<tablename>')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources