Linked server questions

Question

Linked server questions

dbursey

SSC Eights!

Points: 831
More actions
April 16, 2013 at 11:53 am

#276504

Hello, all - I am trying to understand linked servers and trying to find a couple of answers.
We have a server named like server1\server. I'm trying to create a link to this server from another one called (for the example) server2. The link appears to have worked, but when I look at the catalog, there's nothing there but "default" and "system." How do I get all the db catalogs to appear? And is there anything wonky because of the "server1\server" name? I'm pretty sure it needs to have both parts, because I could not get this to work/connect using just "server1."
I'm not getting any error messages, I'm just not seeing the db's/catalogs I expect to see. I've tried looking at other linked servers, but of course, they're all on server1/server, so I don't have any good examples of a linked server coming in.
Any information would be greatly appreciate - thank you!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

As far as i know, only ONE catalog will ever appear for a SQL server linked server...and that is the list of objects in the database the linked server points to;

the proc sp_tables_ex (which is what the GUI calls

) only returns the current database context

if you leave off the @catalog parameter, you'll be dropped into the default database of the login you are using, probably master;

it as a default of master, there's probably not much to see there.

try this:

EXEC master.dbo.sp_addlinkedserver

@server = N'My2012Server',

@srvproduct = N'',

@datasrc = N'DEV223\SQL2012',

@provider = N'SQLOLEDB';

EXEC master.dbo.sp_addlinkedserver

@server = N'My2012ServerV2',

@srvproduct = N'',

@datasrc = N'DEV223\SQL2012',

@provider = N'SQLOLEDB',

@catalog = 'SandBox';

EXEC sp_tables_ex My2012Server

EXEC sp_tables_ex My2012ServerV2

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

dbursey SSC Eights! Points: 831 More actions · Answer 2

Should I delete the existing linked server before running the system sp's?

Thank you for your reply -

Donna

Lowell SSC Guru Points: 323518 More actions · Answer 3

dbursey (4/16/2013)
Should I delete the existing linked server before running the system sp's?
Thank you for your reply -
Donna

what i posted was a proof of concept; if you need to add the catalog to an existing linked server, then yes, you'll need to drop and recreate.

if you are creating new linked servers for testing (my examples) no need to drop the existing.

you most likely have code like this as well, so consider that in your scripting as well:

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'MyLinkedServer',

@useself = N'true',

@locallogin = NULL,

@rmtuser = N'sa',

@rmtpassword = 'NotARealPassword';

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 4

When you setup a linked server - you also have to define the security context for each connection using the linked server. It sounds to me like you have not done this - which will then only show the system catalogs that are available to public.

To validate, go to the security tab in the linked server properties and see what security has been defined. If you have the item 'Not be made' or 'Be made without using a security context' then you would not see the databases you have access to.

If you check the item 'Be made using the login's current security context' - be aware that this will fail from a client if you do not have Kerberos authentication setup and configured correctly.

You can define a specific security context for all users - and specific users. For example, if you select the last item in the list that will be the default security context. In the upper pane, you can then specify specific credentials to be used for specific logins. For example, you could use a default read only for all connections - and specify for specific logins on that server a mapping to a login on the linked server that has read/write access.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

dbursey SSC Eights! Points: 831 More actions · Answer 5

Aha! I think this is the crux of the matter...right now it's the third option for my login, but I think I need to make it the fourth option and put in my sql server auth account, which has all the rights known to humans and beyond...thank you, I'll try that out.