Linked Servers enable your SQL Server instance to
execute commands against OLE DB or ODBC compatible data sources. Linked
Servers can be used in the following scenarios.
Accessing data from heterogeneous data sources like MS Access, Oracle, Sybase, and MySQL
From
Linked Servers, having the ability to issue distributed queries,
updates, commands, and transactions on heterogeneous data sources
|
Users will often
raise questions about user privileges you need to manage in order to add
or drop Linked Servers. As Linked Servers are added to the SQL Server,
you need to have server roles. As is obvious, the sysadmin server role can add or drop Linked Servers because sysadmin can do everything in the server, whereas the setupadmin server role is tailored to adding and dropping Linked Servers.
Users
also often face the dilemma of when to use Linked Servers in SSIS as
both options are supporting heterogeneous databases. As this is not a
place to discuss features and facilities of SSIS, we will simply discuss
where Linked Servers can be used.
Linked Servers can be
used to update the remote server within your application. For example,
if you need to update a remote server table from a trigger where the
trigger is fired after an insert to the SQL Server table, Linked Server is the option.
You can use Linked Servers in your Web application when you need to access a remote server other than SSIS.
Also, if you want to analyze data in a remote server, most professionals prefer to use the Linked Server option because it will give you the luxury of tweaking data, group, Union, and so on, the way you want.
|
How a Linked Server Works
When a client application executes a distributed query via a Linked Server, SQL Server sends requests to OLE DB. The rowset request may be in the form of executing a query against the provider or opening a base table from the provider.
For a data source to
return data through a Linked Server, the OLE DB provider (DLL) for that
data source must be present on the same server as the instance of SQL
Server.
When a third-party OLE
DB provider is used, the account under which the SQL Server service runs
must have read and execute permissions for the directory and for all
subdirectories in which the provider is installed.
Configuring a Linked Server
You can configure Linked
Servers in two different ways. The easiest way is to use SQL Server
Management Studio because it provides you with the relevant screen to
enter Linked Server parameters.
Here is a list of the parameters you need to configure Linked Servers in Figure 1.
Provider Select
an OLE DB data source from the available list box. The OLE DB provider
is registered with the given PROGID in the registry.
Product name The product name of the OLE DB data source to add as a Linked Server. This should be a nonempty value.
Data source Name of the data source as interpreted by the OLE DB provider. In case of MS Access, Data Source is the.mdb file, and in Excel it is the Excel file.
Provider string Unique programmatic identifier (PROGID) of the OLE DB provider that corresponds to the data source. Table 1 lists the parameters needed for different databases.
Table 1. Parameters Needed for Databases
| Remote OLE DB Data Source | OLE DB Provider | Provider_Name | Data_Source | Provider String |
|---|
| SQL Server | Microsoft SQL Native Client OLE DB Provider | SQLNCI | SQL Server Instance Name | Database Name |
| Oracle | Microsoft OLE DB Provider for Oracle | MSDAORA | SQL*Net alias for Oracle | |
| Oracle, version 8 and later for the Oracle database | Oracle Provider for OLE DB | OraOLEDB. Oracle | Oracle database Alias | |
| MS Access | Microsoft OLE DB Provider for Jet | Microsoft.Jet. OLEDB.4.0 | Full path of MS Access file | |
| ODBC data source | Microsoft OLE DB Provider for ODBC | MSDASQL | System DSN of ODBC data source | ODBC connection string (If no DSN available) |
| File system | Microsoft OLE DB Provider for Indexing Service | MSIDXS | Indexing Service catalog name | |
| Excel | Microsoft OLE DB Provider for Jet | Microsoft.Jet. OLEDB.4.0 | Full path of Excel file | Excel 5 |
| IBM DB2 Database | Microsoft OLE DB Provider for DB2 | DB2OLEDB | | DB2 database Catalog name |
Security
is a very important configuration in Link Servers. As the remote server
is a different server, there is a probability that users in the SQL
Server server do not exist in the remote server. Therefore, you need to
map users between SQL Server and the remote server. Figure 2 shows the Security tab of the Linked Server.
The login map will either pass the user along without transiting 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. 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.
The default connection options for a user not mapped are as follows:
Not be made
This option restricts the ability to run distributed queries to those
users in the user mapping list. If a user not on the user mapping list
attempts to run a distributed query, he will receive an error.
Be made without using a security context
This option is for non-SQL Server external data sources and is not
useful for SQL Server. SQL Server will attempt to connect as the user
SQL without the password. If a user not on the server mapping list
attempts to run the distributed query, he will return an error.
Be made using the logins current security context When
the local SQL server connects to the external server, it can delegate
security, meaning that the local SQL server will connect to the local
users’ login of the SQL server. Using this method is similar to listing
the user and selecting the impersonate
option except that this uses security delegation, and to pass the
security context, the login must be the exact same account and not just
the same login and password.
Be made using the security context
This option simply assigns every nonmapped local user to a hardcoded
external SQL Server login. Although this may be the simplest method, it
allows every local user the same access to external SQL Server. Using
this option should not violate any responsible security regulations.
Server options can
be set, but most of the time you can get away with default settings.
Here is a list of these default settings:
Collation Compatibility Set this option to true if the two servers are using the same collation.
Data Access If this option is set to false, it disables distributed queries to the external server.
RPC If this option is set to true, remote procedure calls may be made to the external server.
RPC Out If this option is set to true, remote procedure calls may be made from the external server.
Use Remote Collation
If this option is set to true, distributed queries will use the
collation of the external SQL server rather than that of the local
server.
Collation Name This option specifies a collation for distributed queries. It cannot be chosen if collation compatibility is set.
Connection Time-out The connection timeout is in milliseconds.
Query Time-out The distributed query timeout is in milliseconds.
Exercise 1
will take you through the steps to create Linked Servers using SQL
Server Management Studio. In this exercise, we will discuss how to
create a Linked Server for Microsoft Access mdb.
Exercise 1. Creating Linked Servers Using SQL Server Management Studio
Start SQL Server Management Studio from Program Files| SQL Server 2008. Select Database Engine as Database Type and the SQL Server Database instance name
and provide the necessary user credentials to log in to SQL Server. If
you supply the correct information, you will be taken to the SQL Server
Management Studio. If the Object Explorer is not visible, select the Object Explorer menu option from the View menu or press F8. Expand Server Object in the Object Explorer and select Linked Servers. Right-click Linked Servers and select New Linked Server as shown in Figure 3.
After selecting the name of the option, you will be taken to Figure 9.1, which is the general page of the Linked Server configuration. Specify a Linked Server Name, which is the name of the Linked Server used when accessing the server. Select the Other data source option. Select Microsoft Jet OLEDB 4.0 Provider from the provider drop-down list. Enter any text for the product name. Data Source should be the file path to the Microsoft Access.mdb file. Leave the other two tabs with default values. Click OK to confirm the changes.
|
You can drop the Linked Servers if you follow the steps in Exercise 2.
Exercise 2. Dropping a Linked Server in SQL Server Management Studio
Start SQL Server Management Studio from Program Files | SQL Server 2008. Select Database Engine as Database Type and the SQL Server Database instance name
and provide the necessary user credentials to log in to SQL Server. If
you supply the correct information, you will be taken to SQL Server
Management Studio. If the Object Explorer is not visible, select the Object Explorer menu option from the View menu or press F8. Expand Server Object in the Object Explorer and select Linked Servers. Right-click the Linked Server you want to delete and select Delete. Press OK to confirm the deletion of the Linked Server.
|
Similarly, you can create or drop Linked Servers using T-SQL scripts. Exercise 3 explains how to get the template of the Linked Server.
Exercise 3. Opening Template for Linked Server in SQL Server Management Studio
Start SQL Server Management Studio from Program Files | SQL Server 2008. Select Database Engine as Database Type and the SQL Server Database instance name
and provide the necessary user credentials to log in to SQL Server. If
you supply the correct information, you will be taken to SQL Server
Management Studio. If the Template Explorer is not visible, select the Template Explorer menu option from the View menu or press CTRL+ALT+T. In the Template Explorer, navigate to the Linked Server as seen in Figure 4.
The Add Linked Server Access MDB template has the template to add Linked Server of MS Access MDB file to the SQL Server. Add Linked Server Simple template has the template for To add a Link Server. Drop Linked Server template has the template to drop a Linked Server. Double-click the required template from the Template Explorer to enter the relevant parameters to suit your environment. Execute the script by clicking the Execute button.
|
Apart from creating Linked Servers from SQL Server Management Studio, you have the option of creating them using T-SQL in Exercise 4.
Exercise 4. Creating Linked Servers Using T-SQL
Start SQL Server Management Studio from Program Files | SQL Server 2008. Select Database Engine as Database Type and the SQL Server Database instance name
and provide the necessary user credentials to log in to SQL Server. If
you supply the correct information, you will be taken to SQL Server
Management Studio. Select the New Query button, which is most probably below the main menu. You will have a fresh query screen. The following query will create a Linked Server named CUSTOMERACCESS, which is linked to Customer.MDB MS Access. -- ========================================
-- Add Linked Server Access MDB
-- ========================================
EXEC sp_addlinkedserver
@server = N'CUSTOMERACCES',
@provider = N'Microsoft.Jet.OLEDB.4.0',
@srvproduct = N'OLE DB Provider for Jet',
@datasrc = N'C:\LinkSrv\CUSTOMER.MDB'
GO The following query will set up login mapping between SQL Server and MS Access. -- Set up login mapping using current user's security context
EXEC sp_addlinkedsrvlogin
@rmtsrvname = N'CUSTOMERACCES',
@useself = N'TRUE',
@locallogin = NULL,
@rmtuser = N'dinesh', --Remote User Name
@rmtpassword = N'pa$$w0rd' –Remote user password
GO After creating the Linked Server, you can verify by listing the tables in the Linked Server. EXEC sp_tables_ex N'CUSTOMERACCESS'
GO
|
You have the option of dropping the Linked Server using T-SQL as shown in Exercise 5.
Exercise 5. Droping a Linked Server Using T-Sql
Start SQL Server Management Studio from Program Files| SQL Server 2008. Select Database Engine as Database Type and the SQL Server Database instance name
and provide the necessary user credentials to log in to SQL Server. If
you supply the correct information, you will be taken to SQL Server
Management Studio. Select the New Query button, which is most probably below the main menu. You will have a fresh query screen. The following query will drop the Link Server from SQL Server. -- ==============================
-- Drop Linked Server template
-- ==============================
-- Drops a linked server reference to a Database Engine instance
-- Related logins will also be dropped in this example
EXEC master.dbo.sp_dropserver @server=N'CUSTOMERACESS',
@droplogins='droplogins'
GO
|
Provider Options
In addition to configuring
Linked Server, you can also configure various options for each
provider. These options are applied to all Linked Servers configured
from the given provider. You can configure providers through the
following steps in Exercise 6.
Exercise 6. Configuring Provider Options
Start SQL Server Management Studio from Program Files| SQL Server 2008.
Select Database Engine as Database Type and the SQL Server Database instance name and provide the necessary user credentials to log in to SQL Server. If you supply the correct information, you will be taken to SQL Server Management Studio.
If the Object Explorer is not visible, select the Object Explorer menu option from the View menu or press F8.
Expand Server Object in the Object Explorer and expand to Providers in the Linked Servers further as shown in Figure 5.
Double-click the Provider on which you want to set the configuration options and you will see Figure 9.6.
6. You can select the option from the list.
7. Also, you can see the configured Linked Server using this provider.
