How to create a SQL Server linked Server with Local SQL Server Authentication User to a remote Windows Authentication



  • I need to create a Linked Server from Server A that uses a SQL Server Authentication to a Server B that uses a Windows Authentication.

    Is the "impersonation" possible?

    If "YES" what options, config should I pick?

    enter image description here



  • As documented, this is not possible.

    If you do not want to do impersonation (using the locally logged-in user on Server A to authenticate to Server B), you must specify a SQL Server Authentication login, you cannot authenticate using Windows Authentication.

    https://docs.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine?view=sql-server-ver15

    Remote User
    Use the remote user to map user defined in Local login. The Remote User must be a SQL Server Authentication login on the remote server.

    https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addlinkedsrvlogin-transact-sql?view=sql-server-ver15#arguments

    [ @rmtuser = ] 'rmtuser'
    Is the remote login used to connect to rmtsrvname when @useself is FALSE. When the remote server is an instance of SQL Server that does not use Windows Authentication, rmtuser is a SQL Server login.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2