sp_addlinkedsrvlogin

Creates or updates a mapping between a connection on the local instance of Lyftron and a security account on a remote data source.

Lyftron supports two types of authentication:

  • standard (SQL Server) authentication mechanism, where user and password are stored locally and presented to the remote data source
  • Windows Integrated authentication, where a domain account is used to grant permissions on a remote data source and Lyftron authenticated using its service account

Note: Lyftron does not support logins as known in SQL Server. All login user credentials are stored with a connection.

Syntax

    sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'   
         [ , [ @useself =     ] 'TRUE' | 'FALSE' | NULL ]   
         [ , [ @locallogin =  ] 'locallogin' ]   
         [ , [ @rmtuser =     ] 'rmtuser' ]   
         [ , [ @rmtpassword = ] 'rmtpassword' ]

Arguments

[ @rmtsrvname = ] 'rmtsrvname'

Is the name of a connection that the login mapping applies to. rmtsrvname is sysname, with no default.

[ @useself = ] 'TRUE' | 'FALSE' | 'NULL'

Determines whether to connect to rmtsrvname by explicitly submitting a login and password. The data type is varchar(8), with a default of TRUE. A value of TRUE specifies that Lyftron will authenticate to rmtsrvname using its service account security context (WIA), with the rmtuser and rmtpassword arguments being ignored. FALSE specifies that the rmtuser and rmtpassword arguments are used to connect to rmtsrvname for the specified locallogin. If rmtuser and rmtpassword are also set to NULL, no login or password is used to connect to the data source.

[ @locallogin = ] 'locallogin'

Is a login on the local server. locallogin is sysname, with a default of NULL. NULL specifies that this entry applies to all local logins that connect to rmtsrvname. If not NULL, locallogin can be a standard login or a Windows login. The Windows login must have been granted access to Lyftron either directly, or through its membership in a Windows group granted access.

[ @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. rmtuser is sysname, with a default of NULL.

[ @rmtpassword = ] 'rmtpassword'

Is the password associated with rmtuser. rmtpassword is sysname, with a default of NULL.

Remarks

When a user logs on to the local server and executes a distributed query that accesses a table on the linked server, the local server must log on to the linked server on behalf of the user to access that table. Use sp_addlinkedsrvlogin to specify the login credentials that the local server uses to log on to the linked server.

Permissions

Requires ALTER ANY LOGIN permission.

Example

The following example creates the myConnection, sets the datasrc to "mysql", tests the connection, lists the updated connection using the sys.servers view, and drops the connection.

exec sp_addlinkedserver 'myConnection', 'MySql';
exec sp_updatelinkedserver 'myConnection', @datasrc='mysql:3306';

exec sp_addlinkedsrvlogin 'myConnection', 'FALSE', @rmtuser='root', @rmtpassword='root';

exec sp_testlinkedserver 'myConnection';
select * from sys.servers where name = 'myConnection';
exec sp_dropserver 'myConnection';

See Also