Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Linked server questions Expand / Collapse
Author
Message
Posted Tuesday, April 16, 2013 11:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 2:17 PM
Points: 34, Visits: 161
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!
Post #1442901
Posted Tuesday, April 16, 2013 12:10 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:53 PM
Points: 12,896, Visits: 32,095
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1442913
Posted Tuesday, April 16, 2013 12:25 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 2:17 PM
Points: 34, Visits: 161
Should I delete the existing linked server before running the system sp's?

Thank you for your reply -

Donna
Post #1442933
Posted Tuesday, April 16, 2013 12:36 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:53 PM
Points: 12,896, Visits: 32,095
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1442941
Posted Tuesday, April 16, 2013 12:55 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 7:26 AM
Points: 4,358, Visits: 9,536
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
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1442956
Posted Tuesday, April 16, 2013 1:04 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 2:17 PM
Points: 34, Visits: 161
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.
Post #1442961
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse