SQL Server 通过链接服务器访问Oracle

        数据库：SqlServer2005 、Oracle 11gR2

        系统环境：windows Server 2008R2 32位。

        最近在做项目时，需要通过sql的链接服务器访问Oracle。以前弄过sql访问sql，感觉挺简单的，以为访问Oracle也差不多，实际做过后才发现存在很多差距。

        步骤：

        1、SQL Server端Windows需装对应版本的Oracle客户端。（我安装Oracle9i客户端后通过PL/SQL访问Oracle11gR2，没有问题，但是通过链接服务器访问就无法查询）
        2、Oracle Net Manager中创建服务名,测试连接通过。
        3、确认SQL Server端Windows可以通过SQLPlus访问远程Oracle。
        4、创建link server,如下代码。

/****** 对象:  LinkedServer [HZTMVQD]    脚本日期: 01/06/2015 11:02:50 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'HZTMVQD', @srvproduct=N'Oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'172.22.75.111/VQDV3MID'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'HZTMVQD',@useself=N'False',@locallogin=N'sa',@rmtuser=N'vqd',@rmtpassword='vqd123'

GO
EXEC master.dbo.sp_serveroption @server=N'HZTMVQD', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'HZTMVQD', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'HZTMVQD', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'HZTMVQD', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'HZTMVQD', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'HZTMVQD', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'HZTMVQD', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'HZTMVQD', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'HZTMVQD', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'HZTMVQD', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'HZTMVQD', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'HZTMVQD', @optname=N'use remote collation', @optvalue=N'true'

需要注意的地方：

1、数据源要写成ip+SID 例如：172.22.75.111/VQDV3MID,否则可能无法正常查询。也可以直接写VQDV3MID服务名。

2、sqlserver 链接oracle可以通过两个访问接口：“MSDAORA” 和“OraOLEDB.Oracle”。“OraOLEDB.Oracle” 访问接口是由oracle 的Oracle Probider for OLE DB 驱动提供的，必须安装Oracle客户端才会有。“MSDAORA”访问接口是由Microsoft OLE DB Provider for Oracle提供的，由sql数据库自带。

遇到的错误

消息
已以用户 NT AUTHORITY\SYSTEM 的身份执行。 无法初始化链接服务器 "hztmvqd" 的 OLE DB 访问接口 "OraOLEDB.Oracle" 的数据源对象。 [SQLSTATE 42000] (错误 7303)  链接服务器"hztmvqd"的 OLE DB 访问接口 "OraOLEDB.Oracle" 返回了消息 "ORA-01017: 用户名/口令无效; 登录被拒绝"。 [SQLSTATE 01000] (错误 7412).  该步骤失败。

解决方案：
1、链接服务器--HZTMVQD右键属性--安全性--在本地分服务器登录到远程服务器登录的映射中添加账户。例如：
本地登录sa，远程用户vqd，远程密码vqd123 以sa账户登录映射
本地登录NT AUTHORITY\SYSTEM，远程用户vqd，远程密码vqd123  以NT AUTHORITY\SYSTEM账户登录映射

2、添加后如果还不能访问，请在安全性--登录名里面添加对应的账户，并分配权限

好了，暂时就总结这么多，下次遇到新的问题在进行总结。 2015年1月13日 17:02:52 Peanuts