Accessing Remote Data  «Prev  Next»

Lesson 4Adding a remote login
ObjectiveAdd a remote login and log on to the system.

Adding Remote Login

After you have added a linked server, you will need to log on to the server and resource where the data resides. By default, SQL Server will attempt to do this with your current login ID and password. If you do not have access to the requested resources on the linked server, you will need to add a remote login.

Adding a remote login

You can add a remote login with the sp_addlinkedsrvlogin system stored procedure, which follows the syntax shown in the following SlideShow:

Example of adding a remote login

To allow remote access for the following user:
Login ID amann
Account sa
Password None
Server Boston

Use this Transact-SQL code:
sp_addlinkedsrvlogin 'Boston', 'false', 'amann', 
'sa', NULL

Removing a remote login

To remove a linked server login, use the sp_droplinkedsrvlogin system stored procedure. Include the remote server and local login name, as shown below:

sp_droplinkedsrvlogin 'Boston', 'amann'

Configuring the logins

The whole point of linked servers is to enable local users to run queries that access data from other data sources. If the external data source is SQL Server, then it will require some type of user authentication, which is accomplished via mapping logins, or for those local users whose logins are not mapped, via setting the default behavior. The login map will either pass the user along without translating the login name if the Impersonate option is checked, or translate any user’s login to a remote login and password if the Impersonate option is not checked. Of course, on the external server, the login must be a valid login and must have been granted security rights in order for the link to be effective.

In the next lesson, you will learn how to use a fully qualified path to access remote data.