Home > Articles > Data > SQL Server

  • Print
  • + Share This
Like this article? We recommend

Like this article? We recommend

Setting Up and Configuring Remote and Linked Servers

One SQL Server can interact with other SQL Servers as well as many other heterogeneous data sources. This can be done in two ways: remote servers and linked servers. When you set up a remote server, another SQL Server can execute stored procedures that reside in the remote server. Remote servers must be SQL Servers. This architecture is far more limited than the linked server architecture, which permits users to issue select, insert, update, and delete queries against the linked server. The linked server need not be a SQL Server; in fact, using linked servers, it is completely possible to join data from tables residing in SQL Server, Oracle, and Excel. In most cases, you will want to use linked servers rather than remote servers, because the linked servers are more powerful. I am going to cover remote servers as well as linked servers, however, because you may have legacy systems using the older architecture and because replication is implemented with remote servers.

Remote and Linked Server Configuration Options

Four configuration options deal with remote and linked servers. In most cases, the defaults are what you want, but you must make sure that the settings have not been changed from the default when you set up remote servers.

Options That Apply to Both Remote and Linked Servers

The following options apply to both remote and linked servers:

  • Remote Login Timeout (S). This configuration option affects both remote and linked servers. It specifies how long to wait when connecting to a remote server. If you try to connect to a remote server, and that server is down, for example, you might wait forever. The default is 0, which means that you want to wait indefinitely. You may want to set this to an interval, such as 5 or 10 seconds. (S, D).

  • General Tip

    The Remote Login Timeout option cannot be set in Enterprise Manager; you must use Transact-SQL or DMO to set it.

  • Remote Query Timeout (S). This option specifies the number of seconds that SQL Server should wait after issuing a request to a linked server or a remote server before timing out if no results have been returned. The default is 0, which means that you wait indefinitely. (S, D)

Options That Apply Only to Remote Servers

The following options apply only to remote servers:

  • Remote Access. The default is 1, which allows other servers to access this server remotely. If you are planning to use remote servers, you should leave this option alone. If you set it to 0, other servers can't access the server remotely. (S, S)

  • Remote Proc Trans. If this option is set to 1, all operations using remote servers will be distributed transactions managed by MSDTC. Because a lot of overhead is required to manage distributed transactions, you should leave this option set to 0 (the default) and explicitly request a distributed transaction only when you need one. (S, D)

  • General Tip

    You can read more about distributed transactions in the Books OnLine.

Remote Servers

Remote servers are set up in pairs. First, each server must be aware of the other's existence. Then, you must set up logins for the remote servers. These are effectively mappings between the login ID used on one of the servers to the login ID used on the other server.

Setting Up Remote Servers with Transact-SQL

This procedure must be done on both servers. First, use the following command:

sp_addlinkedserver 'server', 'SQL Server'

Suppose, for example, that you have two servers, one named ServerA and one named ServerB. On ServerA, issue the following command:

sp_addlinkedserver 'ServerB', 'SQL Server'

On ServerB, issue the following command:

sp_addlinkedserver 'ServerA', 'SQL Server'

Now, you must map the logins. The way you do this depends on whether the logins are SQL Server Authentication logins or NT Authentication logins. (See Chapter 9, "Security," for details on the different methods of authentication.)

Mapping SQL Server Authentication Logins

If the user has the same login name and password on both SQL Servers, it is not necessary to perform this step. If Tom has a login on ServerA and a login on ServerB, for example, the mapping is automatic. If the names differ, however, you must define the mappings. Assume for this example that ServerA has a login named Theresa, and you want that login to map to a login named RemoteUsers on ServerB. Use the command sp_addremotelogin, as follows:

sp_addremotelogin 'remoteserver', 'login', 'remote_name'

For example, on ServerB:

sp_addremotelogin 'ServerA', 'RemoteUsers', 'Theresa'

After this has been done, Theresa, running on ServerA, can execute any stored procedures that RemoteUsers have been given permission to on ServerB. If you want bidirectional logins (that is, from ServerB to ServerA), use a similar process on ServerA.

The final step is to specify whether a password is needed when Theresa wants to run a procedure on ServerB. In most cases, it is easiest if you do not request that the password be checked, because remote stored procedures are likely to be executed from local stored procedures and it is difficult to interact with a user for a password. You specify this with the command sp_remoteoption, as follows:

sp_remoteoption 'remoteserver', 'loginame',
'remotename', trusted, {true | false}

To continue the preceding example, the command should look like this:

sp_remoteoption 'ServerA', 'RemoteUsers', 'Theresa',
 trusted, true

Mapping NT Authentication Logins

Mapping NT Authentication logins is done differently. Use the command sp_addlinkedsrvlogin, as follows:

sp_addlinkedsrvlogin 'remoteserver', {true | false},
 'locallogin', 'remotename', 'rmtpassword'

The second argument must be false for an NT Authentication login. Assume that PINE\SHARON has an NT Authentication login on ServerA, and that you want to map this login to RemoteUsers on ServerB with no password. The command, issued on ServerB, should look like this:

sp_addlinkedsrvlogin 'ServerA', false, RemoteUsers,

Setting Up Remote Servers with Enterprise Manager

It's much easier to set up remote servers with Enterprise Manager than with Transact-SQL, but it's not possible to map NT Authentication logins in Enterprise Manager. In the Security folder in the console pane, right-click on Remote Servers and choose New Remote Server. The screen shown in Figure 3.4 displays.

Figure 3.4 Remote Server Setup screen.

It is important to check the box labeled RPC near the top of the screen. You can map all users to a single login on the remote machine or you can map them individually as shown in Figure 3.3. To skip checking passwords on the remote server, don't check the box in the Check Password column.

Setting Up Remote Servers with DMO

You work with the RemoteServers collection, the RemoteServer object, the RemoteLogins collection, and the RemoteLogins object to set up remote servers with DMO.

To add a remote server, create a RemoteServer object. Specify its Name property and add it to the RemoteServers collection of a connected SQLServer object.

General Tip

You will see some other properties in the documentation and in the list that appears in Visual Basic for the object, but they all have to do with replication. You do not need to worry about setting these for a simple remote server.

To set up remote logins, create a RemoteLogin object. Set the RemoteName, LocalName, and Trusted properties. (Set these to true if password is not to be checked; otherwise set them to false.) Add the object to the RemoteLogins collection of a RemoteServer object.

General Tip

The RemoteLogin object seems to only handle SQL Server authentication logins.

Linked Servers

Linked servers are a new feature in SQL Server 7.0. They allow direct queries against many different data sources, including Oracle, Excel, Access, and ODBC data sources. After you have set up a linked server, you can query it by just including the linked server name as part of the table name, as follows:

SELECT * from MySpreadsheet...Sheet1$ 
SELECT * from ORACLEDB..Scott.Emp

Setting Up Linked Servers with Transact-SQL

Use the sp_addlinkedserver command to set up a linked server with Transact-SQL. This is the same command I described under remote servers, but there is a lot more information that you must provide when linking a heterogeneous server, as follows:

sp_addlinkedserver 'server', 'product_name',
'provider_string', 'catalog'

Server is a name you assign to the server, such as MyLinkedServer. The following table lists possibilities for the other parameters:

Remote Data

product name

provider name

Data Source


provider string



SQLServer (default)









Database Name (optional)


Doesen't matter


SQL*Net alias for Oracle database





Doesen't matter

Microsoft. Jet.OLEDB. 4.0

Full path name of Jet database file




ODBC data source

Doesen't matter


System DSN of ODBC data source**


ODBC connection string**


File system

Doesen't matter


Indexing Service catalog name




Microsoft Excel spreadsheet

Doesn't matter

Microsoft. Jet.OLEDB. 4.0

Full name of Excel file


Excel 5.0


Text file***

Doesn't matter

Microsoft. Jet.OLEDB. 4.0

Full name of text file




If you want to set up an Excel spreadsheet as a linked server, the command should look like the following:

sp_addlinkedserver 'ExcelSpreadsheet', '', 
 '\\EXCEL:\MySpreadsheets\LastMonth.xls', '', 'Excel 5.0'

Remember that the path names are from the SQL Server's point of view; you must use share names accessible to SQL Server. Do not use your mapped drive letters. If the files do not reside on the SQL Server computer, SQL Server must be logging in with a domain account and that account must have permission to access the files.

When you set up linked servers, it's also necessary to configure logins for those servers. Use the command sp_addlinkedsrvlogin, as follows:

sp_addlinkedsrvlogin 'servername',{true | false},
 'SQL Server loginname',
 'linked server login name',
 'linked server password']

When you specify true for the second argument, the SQL Server login name is used to connect to the remote server. You can only use this if the SQL Server login is a SQL Server Authentication login. You may specify null for the SQL Server login name. In this case, all SQL Server logins will log in to the linked server with the linked server login name and password. If you provide a value, it must be a SQL Server login or a Windows NT login that has been granted access to the SQL Server. Assume, for example, that the Excel spreadsheet allows an Admin login with no password, and you want all SQL Server users to be able to log in to this linked server. The command to set this up is as follows:

sp_addlinkedsrvlogin 'ExcelSpreadsheet', FALSE, 

  NULL, 'Admin', NULL

Setting Up Linked Servers with Enterprise Manager

It's very simple to set up linked servers with Enterprise Manager. In the Security folder, right-click Linked Servers and choose New. The screen shown in Figure 3.5 displays.

Figure 3.5 Setting up a linked server.

As annoying as it is, all linked server names must be in uppercase letters. Select the OLE DB Provider from the drop-down list, and provide the other elements using the parameters table in the preceding section. Leave Collation Compatible unchecked unless you are absolutely certain that the remote data source has the same character set as SQL Server. The RPC and RPC Out boxes only make sense for SQL Servers; they specify whether remote procedure calls from and to the server are allowed.

It's also possible to set up the logins with Enterprise Manager. You do so on the Security tab illustrated in Figure 3.6.

Figure 3.6 Specifying linked server logins in Enterprise Manager.

If you choose No Security Context Will Be Used, no logins at all are used. This makes sense for things such as Excel, if the spreadsheet isn't password protected, and text files, which have no security other than file-level permissions. If you choose They Will Be Impersonated, the SQL Server login is used to authenticate the user at the linked server. This only works for SQL Server Authentication logins. Checking this makes sense if the remote server is a SQL Server with the same logins as the server on which the linked server is defined. If you choose They Will Be Mapped To, all SQL Server users will connect to the linked server with the specified username and password. You should choose They Are Not Allowed to Access if you want to specify the logins individually in the grid at the bottom of the screen.

In the grid, provide the SQL Server login. In most cases, you won't choose Impersonate, because doing so requires that the logins be the same on both servers.

Refer back to Figure 3.6. With this setup, Tigger logs in to the linked server as a user named Scott with a password of Tiger. Any other users will log in as a user named Admin with a password of secret.

Setting Up Linked Servers with DMO

Each SQL Server object has a LinkedServers collection that contains LinkedServer objects. Each LinkedServer object has a LinkedServerLogins collection that contains LinkedServerLogin objects.

To add a new LinkedServer, instantiate a LinkedServer object and provide its Name property. Use the same properties you used for setting up linked servers with Transact-SQL to set up properties for DMO. When the properties of the LinkedServer object are complete, add it to the LinkedServers collection of a connected SQLServer object.

To define linked server logins, instantiate a LinkedServerLogin object. This object has four properties.




SQL Server login


True if SQL Server login should be passed to the linked server; otherwise false


Username for linked server


Password for linked server

Then, add the LinkedServerLogin object to the appropriate LinkedServerLogins collection.

  • + Share This
  • 🔖 Save To Your Account

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.


Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.


If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.


This site is not directed to children under the age of 13.


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020