October 2, 2013 at 3:20 am
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. 🙂
October 2, 2013 at 3:16 pm
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