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.
| Author |
Topic |
|
dbradish
Starting Member
24 Posts |
 Posted - 2009-07-09 : 16:08:22
|
| I have several linked SQL 2008 servers, but on one I can only see a few of the catalogs. I'm sure I'm missing the obvious, but if someone would enlighten me, I'd appreciate it. I don't see a difference in security between the catalogs I can see and those I cannot.--Both servers are SQL 2008 10.0.2531.0--The linked server w/ the missing catalogs is STD (Server2), the "parent" server is ENT (Server1)--I've refreshed, dropped and recreated the linked server--we use Windows Auth only on all our SQL Servers; logins are "made using the login's current security context"--Provider being used is "Microsoft OLE DB Provider for SQL Server"--If I remote into Server1 w/ an admin Windows login, or if I access Server1 from my desktop -- regardless of who I'm logged in as, 8 of 11 linked Server2 catalogs are not listed.--I can connect to the database engine via MSSMS and see all catalogs/databases on all serversThanks for reading and giving my request your consideration.~dbradish |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-07-10 : 04:08:50
|
| What account is your SQL Server running under?Does the windows admin on Server1 have the same level of access as windows admin on server 2. Do you log into windows locally with your own account and if so, do you have full access to both servers? |
 |
|
|
dbradish
Starting Member
24 Posts |
Posted - 2009-07-10 : 09:48:32
|
| Q1A: Different accounts.Resolution: With the correct account running both SQL Servers, my linked server list is as expected. Thanks for pointing me in the right direction! |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-07-10 : 09:58:41
|
| No problem - You can run them off different accounts, but you need to make sure the permissions are correct on each. |
|
|
|
dbradish
Starting Member
24 Posts |
Posted - 2009-07-10 : 11:25:46
|
| Which might now be my next problem. Unless I'm directly on Server1 or Server2, I cannot see any catalogs without adding "anonymous logon" as a database user. |
|
|
|
dbradish
Starting Member
24 Posts |
Posted - 2009-07-10 : 13:28:49
|
| Final note: Because of the double-hop issue and reports which needed to access both servers simultaneously, I 1.) changed my servers to allow SQL authentiction, 2.) created a "LinkedServer" user with limited rights, and 3.) switched my linked servers to "be made using this security contxt".I'd love to see SQL Server find a solution to the double-hop problem, other than Kerberos. I get bit by this "SQL feature" so often (SSRS --> DB1, DB1 --> DB2, SharePointDB --> ...) Hopefully the SQL 2008 concept of server groups will expand.Kind Regards, |
|
|
|
sorgfelt
Starting Member
1 Post |
Posted - 2012-07-26 : 14:52:51
|
| I had this problem with one database not showing. I fixed it by noticing that although the username I was using in the linked server existed in the missing database, it was not associated with the same username in the server for the database. I had to run the usual query to associate an orphaned username with a login name in that database:sp_change_users_login 'UPDATE_ONE', 'username', 'loginname', 'optionalpassword' This usually happens when one restores the database from a different server. |
|
|
|
|
|
|
|
|
Missing Catalogs in Linked SQL 2008 Server