SQL Linked Server Notes (including to an IP)

02-Aug-1313 Leave a comment

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]
GO
EXEC master.dbo.sp_dropserver @server=N'', @droplogins='droplogins'
GO
EXEC master.dbo.sp_addlinkedserver @server = N'', @srvproduct=N'SQL Server'
GO
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=''

.

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

Filed under SQL Configuration Tagged with

SQL Linked server – Change of password

12-Jul-1111 Leave a comment

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

http://msdn.microsoft.com/en-us/library/ms175537.aspx

“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.”

Filed under SQL Configuration, SQL Server, Troubleshooting Tagged with ,