Linked Server not returning tables or views

[bhp]

I have set up a linked server OK and it displays the icon for views and tables but there are none there when you click on them, has anyone else found this?
I have created a server login as suggested by books online with necessary permissions!
Many thanks for any help!!!

 

[osjohnm]

Right click on the linked server, properties, security, check that the use this security context is checked and filled in, click apply, refresh the linked server by right clicking and then see if you can see them.

If not then post which version you are using, how you set it up etc

John

 

[bhp]

John, Thanks
Have already done this.
Am trying to link a SQL2000 to SQL7 server the sql200 server is using ony NT authorisation.
Am trying to link with server type "SQL Server"

Does this help?
Many Thanks - James

 

[osjohnm]

Hi James

Does the NT Login that you are using exist on both servers?
Secondly do you have sysadmin rights?
Lastly are you creating the linked server on the sql 2000 server or sql 7 server?

John

 

[bhp]

The NT login does exist on both boxes, I am trying to set up from the 2000 server (which uses NT authentication.
When I try and set this up the NT login fails when you try and select the table or view

 

[osjohnm]

Hi James

Our linked servers all use sql logins so I've been fiddling around with NT logins and in Books Online it mentions that WIndows Authentication is only supported if security account delegation is on both the servers. Security Account Delegation makes use of Windows Active Directory and only works on Windows 2000 Servers, so if the SQL 7 box is NT Server I think that windows won't work.

If the sql server 7 box uses mixed mode then setup a sql login on the 7 server and then uses that as the login for the linked server setup in the 2000 instance.

This comes from BOL:

If security account delegation is not available on the client or sending server, or the linked server/provider does not recognize Windows Authentication Mode, then self mapping will not work for Windows Authenticated logins. Therefore, you need to set up a local login mapping from a Windows Authenticated login to a specific login on the linked server. In this case, the remote login will be a SQL Server Authenticated login if the linked server is an instance of SQL Server.


Hope that helps

John

 

[bhp]

I have done as you said.

I have set up the NT login on the SQL Server 7 box.
One question, what security context should you use as I ma getting a failed login?
Thanks - James

 

[osjohnm]

James

Does the sql server 7 box use sql authentication as well as NT Authentication?

If it does, rather use a sql login, there appears to be to many issues revolving aorund windows authentication with regards to linked servers

John

 

[bhp]

Yes it does, but all the logins on that box are sql!

 

[bhp]

If I use just sql security then I just get the icon for tables and views as in my first posting with no errors!
Maybe there is no answer to this!!!!!

 

[osjohnm]

James

Trust me there is an answer to this, we have linked servers between sql 2000, sql 7, sql 6.5, Oracle etc and we also had trouble like this but usually it was a login problem or setting etc.

What permissions does the SQL Server login on the SQL Server 7 box have? Make sure that it has atleast select rights on the tables.

Once you have checked the permissions, go back to the linked server> properties, security> select 'Be made with this security context'> Fill in the SQL 7 SQL user and its password as it is on the SQL 7 server> Go Okay.

Then right click on the linked server> refrsh> then see if the tables are visible?

Also try disconnecting complete from the sql server in EM and then connect again and see if you can see them.
EM in SQL 2000 doesn't always refresh the new objects properly.

John

 

[bhp]

Got it working from sql to nt will have a go the other way, thanks very much for your help this is not the best explained area of SQL!!!
Many thanks - James

 

[osjohnm]

Glad you came right

John