SQL Linked Server Notes (including to an IP)

Create linked server when on the actual server, not as a user on another PC.
If there is a VPN connection required then the VPN connection needs to be up and running on the server, not the local PC.

USE [master]
EXEC master.dbo.sp_dropserver @server=N'', @droplogins='droplogins'
EXEC master.dbo.sp_addlinkedserver @server = N'', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'',@useself=N'False',@locallogin=NULL,@rmtuser=N'RemoteServerLogin',@rmtpassword=''
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'',@useself=N'False',@locallogin='Domain\login',@rmtuser=N'RemoteServerLogin',@rmtpassword=''


@locallogin=NULL means any local login
You cannot use Windows NT group logins, does not work. Individual window users only.

SQL Linked server – Change of password

TODO confirm this works then mark as Solved

A linked server using VFPOLEDB has worked for sometime here. Then the server related user password was changed and it all stopped working. Perhaps consider following page


“Linked server login mappings can be added by using sp_addlinkedsrvlogin and removed by using sp_droplinkedsrvlogin. A linked server login mapping establishes a remote login and remote password for a specified linked server and local login. When SQL Server connects to a linked server to execute a distributed query or a stored procedure, SQL Server looks for any login mappings for the current login that is executing the query or the procedure. If there is a login mapping, SQL Server sends the corresponding remote login and password when it connects to the linked server.”