SQL Server Performance

Linked Servers

Discussion in 'General DBA Questions' started by Jondalar, Jan 3, 2003.

  1. Jondalar New Member

    Good day all.

    I have a problem that you folks can shed some light on.

    I have two SQL Servers; SQL Server 2000 running on Windows 2000, and SQL Server 7.0 running on NT.

    Each server resides on its own physical device.

    The 2000 server uses Windows authentication.

    The 7.0 server uses Integrated security.

    Logins on the 2000 server have been duplicated on the 7.0 server.

    Both servers are located within the same domain.

    Using the Enterprise Manager on the 2000 server, I can successfully create a linked server to the 7.0 server. Additionally, when I execute sp_linkedservers from the (2000 server) Query Analyzer, the 7.0 server is returned in the dataset as a linked server. I can also "ping" the 7.0 server from the 2000 server.

    However, when I attempt to run queries against the linked server, or try to examine its tables from the 2000 Enterprise Manager, I receive the following message: "Login Failed. Reason: User Administrator not associated with a trusted SQL Server connection".

    I've created, deleted, and recreated this linked server many times using both the 2000 EM and the sproc sp_addLinkedServer from the 2000 Q.A. Additionally, logins have been created using the same tools.

    From my reading, I am doing everything correctly to set up this linked server, but apparently, I am missing something.

    Would any of you have an idea as to what that something might be?

    Thanks.

    Brian

    BTW Jondalar is the name of my malamute





  2. satya Moderator

    It sounds to me like the security on your server is set to Windows Only. This is I believe the new default for SQL2000.

    Right click server in Enterprise Mgr, choose properties, security tab, the "Authentication" needs to be set to SQL Server and Windows not Windows Only.



    Satya SKJ
  3. bradmcgehee New Member

    Did you create a login mapping? This is described under the topic "Establishing Security for Linked Servers" in the SQL Server Books Online.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  4. Jondalar New Member

    Thanks to all. I will give these suggestions a try.

    Brina

    Jondalar
  5. Jondalar New Member

    quote:Originally posted by satya

    It sounds to me like the security on your server is set to Windows Only. This is I believe the new default for SQL2000.

    Right click server in Enterprise Mgr, choose properties, security tab, the "Authentication" needs to be set to SQL Server and Windows not Windows Only.



    Satya SKJ


    Jondalar
  6. Jondalar New Member

    Satya, would this affect authentication for the entire server or just the authentication for the linked server?

    Thanks.

    Brian

    Jondalar
  7. satya Moderator

    The 2000 SQL server used.

    Satya SKJ

Share This Page