在SQL SECRUISERVE凯雷德 2009 福特Explorer2下用Windows
身份验证的记名名创造了八个走访ORACLE数据库的链接服务器xxxxx,测量检验成功,木不正常,不过其余登入名使用该链接服务器时,报如下错误:

背景:

率先sqlserver 链接oracle能够透过三个访谈接口:

消息 7302,级别
16,状态 1,第 1 行

     斩新服务器,必要扩张属性监察和控制,发掘不能透过powershell读取质量目标

“MSDAORA” 和“OraOLEDB.Oracle”

Cannot create an
instance of OLE DB provider “OraOLEDB.Oracle” for linked server
“xxxxxx”.

斩草除根措施:

1、“MSDAORA”访问接口是由Microsoft OLE DB Provider for
Oracle提供的,这里提议不应用此接口进行链接。通过该访谈接口建设构造的链接服务器在拓宽查询oracle表(带数据类型CLOB、BLOB字段)时会报那个指鹿为马“
链接服务器””的 OLE DB 访谈接口 “MSDAORA” 重返了音信 “发生了二个 Oracle
错误,但力不能支从 Oracle 中搜寻错误新闻。”。 链接服务器””的 OLE DB 访谈接口
“MSDAORA” 重回了消息 “数据类型不被补助。”。 新闻 7321,级别 16,状态
2,第 1 行 筹划对链接服务器 “” 的 OLE DB 访谈接口 “MSDAORA”
实践查询”select * from SYS_MESSAGE”时出错。”

 

  • Open the Registry Editor by going to the Start Menu and selecting
    Run…, then type “regedit”, and click the OK button.
  • Navigate to the
    HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSecurePipeServerswinreg registry
    key.
  • Right click on the “winreg” key and select Permissions. Add users or
    groups to which you want to grant Read access.
  • Exit Registry Editor and restart Windows.

2、“OraOLEDB.Oracle” 访谈接口是由oracle 的Oracle Probider for OLE DB
驱动提供的。它解决了八个数据库类型不一样样的的主题素材。而且生龙活虎旦急需利用遍及式事务,必需运用它来创设链接服务器。后文种有详细介绍。 在开立以前,在SQLSE兰德讴歌RDXVE本田UR-V中,链接服务器->访谈接口->OraOLEDB.Oracle->右键属性,选中
“Allow inprocess”
(中文为:允许进程内) 这一步是使大家选择的OraOLEDB.Oracle接口张开实行操作。如未设置会报如下错误: “无法早先化链接服务器
“null” 的 OLE DB 访谈接口 “OraOLEDB.Oracle” 的数据源对象”

那时内需在“服务器对象”——>“链接服务器”——>“访谈接口”下,找到OraOLEDB.Oracle选项,单击右键选取属性,然后在拜会接口选项下勾选“允许进度内”(Allow
inprocess)。就能够缓慢解决地点难点,有时候,也许供给重新新建此链接服务器。

 

图片 1

服务器 windows server 2008 r2 64位  或 win 7 enterprise 64位

 

  1. 服务器上急需设置Oracle
    陆15个人的客户端(

老外给出了三个消逝这么些主题素材的手续,特别详细严峻,大致百样玲珑。能够看做参考学习的好材料:

设置实现后SQL
Server的走访接口上会新添”OraOLEDB.Oracle”(安装三19位顾客端,看不到)。

Ran into this issue where the linked
server would work for users who were local admins on the server, but not
for anyone else. After many hours of messing around, I managed to fix
the problem using the following steps:

安装:Windows Server2008 R2下安装Oracle
10g

  1:Run “dcomcnfg.exe”. Navigate to
“Component Services -> Computers -> My Computer -> DCOM
Config”.

  1. 安排”OraOLEDB.Oracle”属性->启用项“允许进度内”;

  2. 新建链接服务器(名称 自定 :ORA_LINK)

  3. 填写链接服务器名称->选择访谈接口”Oracle Provider for OLE DB”

  4. 填写产品名称->Oracle

  2:Open the properties page of
“MSDAINITIALIZE”.

6.
数据源填写Oracle顾客端/服务端配置的连续几天地址的劳动名(tnsnames.ora中的服务名)

  3:Copy the “Application ID” on the
properties page.

  (listener.ora/sqlnet.ora/tnsnames.ora配置文件详整)

  4:Close out of “dcomcnfg”.

  (sqlnet.ora<->Oracle Net Manager 
概要文件)(tnsnames.ora<->Oracle Net Manager
服务命名)(listener.ora<->Oracle Net Manager 监听程序)

  5:Run “regedit”. Navigate to
“HKEY_CLASSES_ROOTAppID{???}” with the ??? representing the
application ID you copied in step #3.

7.
填写Oracle的登入账号密码:选取项->安全性->选取”使用此安全上下文创建连接”,并填入Oracle登入名与登录密码

  6:Right click the “{???}” folder and
select “Permissions”

SELECT *
FROM OPENQUERY(ORA_LINK,’SELECT * FROM
USERNAME.TABLE’);

  7:Add the local administrators group
to the permissions, grant them full control.

 

  8:Close out of “regedit”.

Oracle 的 透明网关(transparent Gateway)
也得以达成 Oracle 能够与 此外异构数据库的合力

  9:Reboot the server.

  10:Run “dcomconfig”. Navigate to
“Component Services -> Computers -> My Computer -> DCOM
Config”.

  11:Open the properties page of
“MSDAINITIALIZE”.

  12:On the “Security” tab, select
“Customize” under “Launch and Activation Permissions”, then click the
“Edit” button.

  13:Add “Authenticated Users” and
grant them all 4 launch and activation permissions.

  14:Close out of “dcomcnfg”.

  15:Find the Oracle install root
directory. “E:Oracle” in my case.

  16:Edit the security properties of
the Oracle root directory. Add “Authenticated Users” and grant them
“Read & Execute”, “List folder contents” and “Read” permissions. Apply
the new permissions.

  17:Click the “Advanced Permissions”
button, then click “Change Permissions”. Select “Replace all child
object permissions with inheritable permissions from this object”. Apply
the new permissions.

  18:Find the “OraOLEDB.Oracle”
provider in SQL Server. Make sure the “Allow Inprocess” parameter is
checked.

  19:Reboot the server.

 

 

相关文章