server质量计数器难题,该友人事务管理器已经禁止了它对长距离

在SQL SE索罗德VE本田CR-V 2009 奥迪Q52下用Windows
身份认证的报到名创立了三个寻访ORACLE数据库的链接服务器xxxxx,测量试验成功,木有的时候,不过任何登入名使用该链接服务器时,报如下错误:

背景:

先是sqlserver 链接oracle能够通过八个访谈接口:

眼前再用SSIS做多少归档,里面用到了分布式事务。在开垦阶段是在一台微型Computer上运转只如果开发银行布满式服务就没怎么难题,但是后日把它配置到uat的时候蒙受标题,错误音信是:

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

     全新服务器,须要扩张属性监察和控制,发掘不可能通过powershell读取质量目的

“MSDAORA” 和“OraOLEDB.Oracle”

997755.com澳门葡京 1

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_MACHINE\SYSTEM\CurrentControlSet\Control\SecurePipeServers\winreg 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
驱动提供的。它消除了多少个数据库类型差别的的难点。何况只要急需采用布满式事务,必须利用它来创设链接服务器。后文仲有详尽介绍。 在创建在此以前,在SQLSEEscortVEEscort中,链接服务器->访谈接口->OraOLEDB.Oracle->右键属性,选中
“Allow inprocess”
(中文为:允许进度内) 这一步是使我们选取的OraOLEDB.Oracle接口张开实施操作。如未设置会报如下错误: “不能初叶化链接服务器
“null” 的 OLE DB 访问接口 “OraOLEDB.Oracle” 的数据源对象”

  1. 确认”Distribute Transaction
    Coordinator”服务在Server和本地都以运市场价格况。(如失败能够 输入:msdtc
    -resetlog (注意运营此命令时,不要试行挂起的事务)
  2. 在Server上打开 Component Services。 Control Panel –> System and
    Security –> Administrative Tools –> Component Services.
  3. 开始展览至 Component Services –> Computers –> My Computer –>
    Distributed Transaction Coordinator –> Local DTC,
    右键,接纳Properties。在弹出的Dialog中选取Tab “Security”,
    勾选Network DTC Access 等选择,具体设置如下图。
    997755.com澳门葡京 2
  4. 以上的截图是在win8上的,如若您的体系是xp或是二〇〇三则某个变化.展开至
    Component Services –> Computers –> My 计算机右键,采纳Properties。在弹出的Dialog中选用Tab “MSDTC”–>Security
    Configuration, 勾选”Network DTC Access”, “Allow Remote Client”,
    “Allow Inbound/Outbound”, “Enable TIP” (Some option may not
    benecessary, have a try to get your configuration)
    等选项,具体设置如下图。
    997755.com澳门葡京 3
  5. server质量计数器难题,该友人事务管理器已经禁止了它对长距离。确认后会须求重新启航Service。注:假使在后边的步骤做完后,依然无法化解难点,可能需求重启Computer。
  6. 确认DTC不会被您的防火墙阻止。

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

 

 请注意,倘使我们的环境和自家同一不在域里面,请选中不供给开始展览表明。否者在运作布满式事务时远程Computer会提示“此专门的职业已明地或暗地被分明或终止
”。(Computer名称绝对要能ping通,不然回报同样的不当),
注意xp系统下一般是ping不通的

997755.com澳门葡京 4

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

是因为防火墙禁止了icmp数据包.
具体查看:
初阶-调整面板-防火墙-高端-ICMP-设置.允许了就足以ping通了

 

  1. 服务器上急需设置Oracle
    62位的客户端(

997755.com澳门葡京 5

鬼子给出了八个消除这么些主题材料的手续,突出详细严峻,大概八面后珑。可以看作参照学习的好素材:

安装完结后SQL
Server的探问接口上会新添”OraOLEDB.Oracle”(安装叁拾捌个人客户端,看不到)。

ps:

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.还发现一个难题,数据库也供给打开遍及式事务的支撑:右键“服务器连接”的习性,在开发的天性窗口选取“连接”,在窗口左侧,勾选“要求将遍及式事务用于服务器到服务器的通讯”,鲜明就可以。

  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

997755.com澳门葡京 6

  2:Open the properties page of
“MSDAINITIALIZE”.

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

2.索要开采遍及式的端口,端口号是135,只怕加多System32下msdtc.exe的不等(netsh firewall set
allowedprogram %windir%/system32/msdtc.exe MSDTC
enable )。

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

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

997755.com澳门葡京 7

  4:Close out of “dcomcnfg”.

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

如上是在xp中,以下是win第88中学防火墙设置(入站准则和出站法则都急需安装)

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

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

997755.com澳门葡京 8

  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.

 

To get rid of this error just follow these steps to configure the
registry key and REBOOT the machine.

  8:Close out of “regedit”.

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

  1. Click Start, click Run, type Regedit, and then click
    OK.
  2. Locate and then click the following registry key:

  9:Reboot the server.

HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows NT

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

  1. On the Edit menu, point to New, and then click Key.

    Note If the RPC registry key already exists, go to step

  2. Type RPC, and then press ENTER.
  3. Click RPC.
  4. On the Edit menu, point to New, and then click DWORD
    Value.
  5. Type RestrictRemoteClients, and then press ENTER.

  6. Click RestrictRemoteClients.

  7. On the Edit menu, click Modify.
  8. In the Value data box, type 0, and then click OK.

    Note To enable the RestrictRemoteClients setting,
    type1.

  9. Close Registry Editor and restart the computer.

  11:Open the properties page of
“MSDAINITIALIZE”.

RESTRICTING THE
DCOM PORT RANGE

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

  1. 997755.com澳门葡京,Go to Start -> Run. Type in DCOMCNFG.
  2. Go to the properties of the My Computer node under
    the Computers folder underneath Component Services.
  3. Under the My Computer Properties look under the
    Default Protocols tab.
  4. Over there make sure that Connection-oriented TCP/IP
    is selected and then click on Properties.
  5. You will see a window like this
                           
    997755.com澳门葡京 9

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

If you don’t see a range above and the window looks
exactly like the one above, that would mean that the DCOM port range is
not configured on the machine.
You can click Add in the above window and type the range (let’s say as
5000-5100) and say Ok. Make sure it looks like this. (Both the radio
buttons should be selected for Internet Range)

  14:Close out of “dcomcnfg”.

                      
997755.com澳门葡京 10

  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.

3.在业务中,固然存在嵌套的意况,同二个政工内的兼具数据库连接对数码都是可访谈的。

  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.

 

 

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website