Linking a SQL Server 2005 (Windows Authentication) to a SQL Server 2008 (Mixed Mode)

  • I am trying to link a production database (SQL Server 2000) that is on Windows authentication to a test database (SQL Server 2008) that is on mixed mode authentication.

    I have already tried the code below from this link:

    DECLARE @LinkName SYSNAME SET @LinkName = 'PRODUCTIONSERVER'

    DECLARE @SrvName SYSNAME SET @SrvName = 'PRODUCTIONSERVER'

    DECLARE @LocalLogin SYSNAME SET @LocalLogin = 'sa' --login on test db

    DECLARE @RmtLogin SYSNAME SET @RmtLogin = 'DOMAIN\UserName' --win auth login on prod db

    DECLARE @RmtPwd SYSNAME SET @RmtPwd = 'password' --win auth password for the login on prod db

    IF NOT EXISTS (SELECT * FROM Master..Sysservers WHERE IsRemote = 1 AND SrvName = @LinkName)

    BEGIN

    EXECUTE sp_addlinkedserver

    @server = @LinkName,

    @srvproduct = '',

    @provider = 'SQLOLEDB',

    @datasrc = @SrvName

    EXECUTE sp_addlinkedsrvlogin

    @rmtsrvname = @LinkName,

    @useself = 'false',

    @locallogin = @LocalLogin,

    @rmtuser = @RmtLogin,

    @rmtpassword = @RmtPwd

    END

    ...a linked server is successfully added but when I try a query like:

    SELECT COUNT(*) FROM PRODUCTIONSERVER.ProdDatabase.dbo.ProdTable

    I get an error:

    Msg 18456, Level 14, State 1, Line 0

    Login failed for user 'DOMAIN\UserName'.

    I have also tried logging into the prod database using the mentioned windows authentication credentials (my personal login) and I can get through.

    The reason for the linking is because I want to test the merging of live data (from sql 2000) into the test database (sql 2008).

    Any thoughts on how I can link the servers?

    Thank you very much. 🙂

  • Are you able to log into the destination server (2008), using Remote Desktop, and run SSMS?

    Then you can set security on the linked server to: 'Be made using the login's current security context'.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply