如何创建链接服务器

前几天在写一个VBA的报表时，看到里边使用了链接服务器，后来另一个报表程序也有类似的场景。就试着去在数据库管理器里创建，结果在配置服务器信息时，对里边的参数代表的含义去里雾里。虽然之前也用过，但基本上是Google一下教程，然后一步一步跟着配置，这次一定要好好梳理一下链接服务器的使用方法。不放过任何一个学习的机会，才能不断持续进步。

基本概念

链接服务器让用户可以对 OLE DB 数据源进行分布式异类查询。 在创建某一链接服务器后，可对该服务器运行分布式查询，并且查询可以联接来自多个数据源的表。 如果链接服务器定义为 SQL Server 实例，则可执行远程存储过程。

链接服务器具有以下优点：

• 能够访问 SQL Server 之外的数据。
• 能够对企业内的异类数据源发出分布式查询、更新、命令和事务。
• 能够以相似的方式确定不同的数据源。

我们可以使用 SQL Server Management Studio或sp_addlinkedserver (Transact-SQL) 语句配置链接服务器。

通常，链接服务器用于处理分布式查询。 当客户端应用程序通过链接服务器执行分布式查询时，SQL Server 将分析命令并向 OLE DB 发送请求。 行集请求的形式可以是对该访问接口执行查询或从该访问接口打开基表。

为使数据源能通过链接服务器返回数据，该数据源的 OLE DB 访问接口 (DLL) 必须与 SQL Server 的实例位于同一服务器上。

使用第三方 OLE DB 访问接口时，运行 SQL Server 服务的帐户必须具有对安装访问接口的目录及其所有子目录的读取权限和执行权限。

上面这段文字是从MSDN上Copy过来的，不过很有必须在创建之前了解我们为什么需要链接服务器。

通俗讲就是当我们需要在一个instance里边访问或者调用其他instance上的资源时，可以通过建立链接服务器的方式来达到我们的需求。

 

接下来我们先来看如何在SQL Server Management Studio中创建链接服务器的方式，然后再介绍如何透过Transact-SQL实现。

SQL Server管理器

1. 连接准备要使用链接服务器的instance

2. 选择【Server Objects|服务器对象】，选中【Linked Servers|链接服务器】，右键选择【New Linked Server…|新建链接服务器】

3. 在新打开的窗口中选择【General|常规】选项卡，设置链接服务器的参数

1. [Linked Server]: 链接服务器名

SELECT * FROM [LLSS].[DBName].[Schema].[TableName]

SELECT * FROM [SERVER134].[CompanyDB].[dbo].[Employee]

1. [Server Type]: 选择【Other data source|其他数据源】
2. [Provider]: 选择【SQL Native Client】选项
3. [Product Name]: 可以输入任何值，但不可以为空
4. [Data source]: XXX.XXX.XXX.XXX\DDSS(要在当前实例上共享的数据库实例的网络名)
5. [Provider string]: 连接字符串，这里我们可以将其设置为空，当然，你也可以直接将完整的连接字符串Copy到这里，这样我们就可以省略后面的Logon步骤了
6. [Location]: 置为空
7. [Catalog]: 默认数据库名

4. 然后打开【Security|安全】选项，选择[Be made using this security context], 设置登录用户名和密码。此步可以省略，如果设置了Provider string.

Option Name	Description
Be made using the login’s current security context	Most Secure. Uses integrated authentication, specifically Kerberos delegation to pass the credentials of the current login executing the request to the linked server. The remote server must also have the login defined. This requires establishing Kerberos Constrained Delegation in Active Directory, unless the linked server is another instance on the same Server.  If instance is on the same server and the logins have the appropriate permissions, I recommend this one.
Be made using this security context	Less Secure. Uses SQL Server Authentication to log in to the linked server. The credentials are used every time a call is made.
Local server login to remote server login mappings	You can specify multiple SQL Server logins to use based upon the context of the user that is making the call.  So if you have George executing a select statement, you can have him execute as a different user’s login when linking to the linked server.  This will allow you to not need to define “George” on the linked server.
Not be made	If a mapping is not defined, and/or the local login does not have a mapping, do not connect to the linked server.
Be made without using a security context	Connect to the server without any credentials.  I do not see a use for this unless you have security defined as public.

5. 点击OK，我们的链接服务器就设置好了。

参考：http://www.codeproject.com/Articles/35943/How-to-Config-Linked-Servers-in-a-Minute#

利用存储过程创建链接服务器(sp_addlinkedserver)

语法

sp_addlinkedserver [ @server = ] 'server'
    [ , [ @srvproduct = ] 'product_name' ]
    [ , [ @provider = ] 'provider_name' ]
    [ , [ @datasrc = ] 'data_source' ]
    [ , [ @location = ] 'location' ]
    [ , [ @provstr = ] 'provider_string' ]
    [ , [ @catalog = ] 'catalog' ]

sp_dropserver [ @server = ] 'server'
    [ , [ @droplogins = ] { 'droplogins' | NULL} ]

sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
    [ , [ @useself = ] 'useself' ]
    [ , [ @locallogin = ] 'locallogin' ]
    [ , [ @rmtuser = ] 'rmtuser' ]
    [ , [ @rmtpassword = ] 'rmtpassword' ]

具体可参考MSDN:

http://msdn.microsoft.com/en-us/library/aa259589%28v=sql.80%29.aspx

http://msdn.microsoft.com/en-us/library/aa933295%28v=sql.80%29.aspx

http://msdn.microsoft.com/en-us/library/aa259581%28v=sql.80%29.aspx

知道了这其中的用途，我们可以开始使用这两个系统存储过程来创建链接服务器，假如我们使用的是SQL Server数据库，那么可用如何代码来实现：

-- Create a demo linked server

Exec sp_droplinkedsrvlogin DEMO, Null
Exec sp_dropserver DEMO

EXEC  sp_addlinkedserver
      @server='DEMO',--链接服务器名别名
      @srvproduct='',
      @provider='SQLOLEDB',
      @datasrc='DEMOServer'   --要访问的服务器


EXEC sp_addlinkedsrvlogin
     'DEMOServer',
     'false',
     NULL,
     'sa',
     '123456'

然后我们就可以使用这个链接服务器了。

Select * from DEMO.pubs.dbo.orders

我们也可以创建通往Oracle数据库实例的链接服务器，参数设置基本类似，具体可以参考MSDN。