SQL Server 2008 R2 链接 Oracle 10g

  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.


2、“OraOLEDB.Oracle” 访问接口是由oracle 的Oracle Probider for OLE DB
驱动提供的。它解决了两个数据库类型不一致的的问题。而且如果需要使用分布式事务,必须使用它来创建链接服务器。后文会有详细介绍。 在创建之前,在SQLSERVER中,链接服务器->访问接口->OraOLEDB.Oracle->右键属性,选中
“Allow inprocess”
(中文为:允许进程内) 这一步是使我们选择的OraOLEDB.Oracle接口打开执行操作。如未设置会报如下错误: “无法初始化链接服务器
“null” 的 OLE DB 访问接口 “OraOLEDB.Oracle” 的数据源对象”

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

On the next page, enter the name and description for the group; then
select the specific rights to be assigned to the group.
图片 1

  9:Reboot the server.


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


(2) Click on Edit Site Group
Permissions, the rights for the entire group are to be changed.

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



图片 2

On that page, you can click on Add
Users to add a new user. On the next page, click on Advanced permissions
to switch to advanced permissions view, where you can control more fine


  • 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
  • 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.
  1. 配置”OraOLEDB.Oracle”属性->启用项“允许进程内”;

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

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

  4. 填写产品名称->Oracle

图片 3

At first, navigate to Customize
Documents and Libraries page. Then Click on Change Permissions for
this document library

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

首先sqlserver 链接oracle可以通过两个访问接口:


(2) From Top-level Site
Administration page, click on Manage site groups link under Users
and Permissions section.

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





“MSDAORA” 和“OraOLEDB.Oracle”

图片 4

1: Add a custom site group



  1. 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
    图片 5

Security is used in multiple ways in
SharePoint. This article provides an overview of the security features
that can be used when managing access to documents and libraries, which
is also a supplementary to my previous posting, <<SPS中Shared
Documents的访问权限管理>> .

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

Oracle 的 透明网关(transparent Gateway)
也可以实现 Oracle 可以与 其它异构数据库的互联


3. The rights assigned to site
groups can also be modified.

  11:Open the properties page of

  1. 服务器上需要安装Oracle

如果此事务已明地或暗地被确认或终止 问题还没有解决可以参考以下设置:

In summary, please note that if
members belong to other groups. For example, if Rickie is a member of
Contributor Group and Custom Group, he will inherit rights from both

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



(5) You can click on custom site
group name. On the next page, you can add a new member to the group.
That is to say, you assign that member the specific rights, which the
custom site group provides.

  2:Open the properties page of

1、“MSDAORA”访问接口是由Microsoft OLE DB Provider for
链接服务器””的 OLE DB 访问接口 “MSDAORA” 返回了消息 “发生了一个 Oracle
错误,但无法从 Oracle 中检索错误信息。”。 链接服务器””的 OLE DB 访问接口
“MSDAORA” 返回了消息 “数据类型不被支持。”。 消息 7321,级别 16,状态
2,第 1 行 准备对链接服务器 “” 的 OLE DB 访问接口 “MSDAORA”
执行查询”select * from SYS_MESSAGE”时出错。”



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


2.需要打开分布式的端口,端口号是135,或者添加System32下msdtc.exe的例外(netsh firewall set
allowedprogram %windir%/system32/msdtc.exe MSDTC
enable )。

图片 6

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

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


In some situation, those default
groups can’t meet your requirements to restrict access control. So
custom site groups can be created with rights selected by the creator
the group. Please follow the following steps to create a custom site

  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.

安装:Windows Server2008 R2下安装Oracle


(3) Click on Add a Site Group to
add a new group.

  8:Close out of “regedit”.


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”.



Site groups control the overall
actions that users can perform on the site. When users are granted
permission to access the site, they must be assigned to at least one
site group. The default groups are Reader, Contributor, Web Designer and

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

图片 7

2. Create a custom site

图片 8

图片 9

(1) Navigate to top-level web site,
and click on Site Settings on the top navigation bar, and then from
that page, click on Go to Site Administration link under
Administration section.

  19:Reboot the server.

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

4. Provide access control to a
specific Documents and Libraries

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



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


2: Advanced permissions view


图片 10

Keep in mind, what you did over there
will only have an effect on access right of the specified Documents and
Libraries and not affect other lists or sections.


  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
  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,

  9. Close Registry Editor and restart the computer.


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

  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 等选项,具体设置如下图。
    图片 11
  4. 以上的截图是在win8上的,如果你的系统是xp或是2003则有些变化.展开至
    Component Services –> Computers –> My Computer
    右键,选择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)
    图片 12
  5. 确认后会要求重新启动Service。注:如果在后面的步骤做完后,还是无法解决问题,可能需要重启电脑。
  6. 确认DTC不会被你的防火墙阻止。


  4:Close out of “dcomcnfg”.


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



In my previous posting, there are
detailed steps showing you how to do it.

在SQL SERVER 2008 R2下用Windows


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:

This is done as follows:

1. Managing Site Security with Site

(1) Click on the group to be changed.
The names of the people assigned to that group are then

Security for documents is important
for most organizations. Some organizations need to restrict access to
specific documents.