Creating Linked Servers Using Windows Authentication

• Arul Manoj

  SSC-Addicted

  Points: 440

  More actions

  April 16, 2009 at 11:01 pm

  #220235

  I am using SQL 2005. Now i want to create linked server for another SQL 2005 server using windows authentication.

  How to do this. Any help will be more useful.

  Thanks,

  Manoj

• Ramesh Saive

  SSC-Insane

  Points: 24275

  More actions

  April 17, 2009 at 6:55 am

  #979611

  Here is the script that I use to create linked servers...

  DECLARE @strLinkedServer NVARCHAR(100)

  SELECT @strLinkedServer = 'Prod2005Server'

  EXECUTE master.dbo.sp_dropserver

  @server = @strLinkedServer,

  @droplogins = 'droplogins'

  EXECUTE master.dbo.sp_addlinkedserver

  @strLinkedServer,

  'SQL Server'

  EXECUTE master.dbo.sp_addlinkedsrvlogin

  @rmtsrvname = @strLinkedServer,

  @useself = N'False',

  @locallogin = N'SomeLocalUser', -- add local login

  @rmtuser = N'SomeDomain\SomeWindowsUser', -- add the remote login

  @rmtpassword = '****' -- add the remote login password

  --Ramesh
  

  ---