SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Linked Server connects, but not usable
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mkenyon2
Starting Member

USA
5 Posts

Posted - 10/29/2013 :  15:47:30  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 10/29/2013 :  16:10:10  Show Profile  Reply with Quote
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
Go to Top of Page

mkenyon2
Starting Member

USA
5 Posts

Posted - 10/29/2013 :  16:26:26  Show Profile  Reply with Quote
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.
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 10/29/2013 :  16:40:04  Show Profile  Reply with Quote
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
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
311 Posts

Posted - 10/30/2013 :  07:15:42  Show Profile  Reply with Quote
What does the FROM clause look like in the select statement?

djj
Go to Top of Page

jiluthej
Starting Member

2 Posts

Posted - 10/30/2013 :  08:48:52  Show Profile  Reply with Quote
Try this query,

select * from openquery(linkedservername,'select * from <databasename>.<tablename>')
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000