asked on

No databases are displayed under a linked server

I have a bunch of linked servers created on SQL Server Server3. Each one of them, when I expand, has a folder "Catalogs", then when I expand that folder, i see the list of databases on that linked server. But one server - Server2, when I expand it, I see folder Catalogs but when I expand it, I only see "System catalogs" and "dafault' but not the list of all databases.When I log in to that server directly, I see all the databases, but not via linked server.

Can someone help me troubleshoot the issue?

COANetwork

Make sure you are using a login in the linked server definition that has access to your user databases. You can impersonate, use a network logon, etc. Your issue is permissions.

YZlat

ASKER

COANetwork, I already checked, I am able to login to the target server using the login that is in the linked definition. When loging in directly, i am able to login and see all the databases. Why does not it allow me to do the same thing through the linked server?

I also tried expanding "default" folder and there were "tables" and 'views", and if I click on tables or views, I get 'error received when attempting to access linked server is "Login failed for user 'test_user' Error: 18456"

I know test_user exists on target server and if i login using test_user, I am able to see all the databases

Randy Knight, MCM

The previous commenter is right. This is permission. Double check the password. Try scripting out the linked server and dropping and re-creating that way. This way you can see the password right in the script so you know it is right.

ASKER CERTIFIED SOLUTION

YZlat

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

YZlat

ASKER

found the solution myself