SQL Server accesses Oracle through a linked server

Database: SqlServer2005, Oracle 11gR2

System environment: Windows Server 2008R2 32-bit.

When you are working on a project recently, you need to access Oracle through a linked server in SQL. SQL access to SQL before, it feels quite simple, think access to Oracle is similar, actually did not find a lot of gaps.

Steps:

1. SQL Server-side windows requires a corresponding version of the Oracle client. (I access ORACLE11GR2 via PL/SQL after I install the oracle9i client, no problem, but cannot be queried via linked server access)
2. Create a service name in Oracle Net Manager and test the connection through.
3. Verify that SQL Server side Windows can access remote Oracle through Sqlplus.
4, create the link server, the following code.

/****** object: LinkedServer [hztmvqd] Script Date: 01/06/2015 11:02:50 ******/exec master.dbo.sp_addlinkedserver @server = N ' Hzt Mvqd ', @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 ' goexec master.dbo.sp_serveroption @server = N ' Hztmvqd ', @optname =n ' collation compatible ', @optvalue =n ' false ' goexec master.dbo.sp_serveroption @server =n ' HZTMVQD ', @optname =n ' data access ', @optvalue =n ' true ' goexec master.dbo.sp_serveroption @server =n ' hztmvqd ', @optname =n ' Dist ', @optvalue =n ' false ' goexec master.dbo.sp_serveroption @server =n ' hztmvqd ', @optname =n ' pub ', @optvalue =n ' false ' goexec Master.dbo.sp_serveroption @server =n ' hztmvqd ', @optname =n ' RPC ', @optvalue =n ' false ' goexec master.dbo.sp_ Serveroption @server =n ' hztmvqd ', @optname =n ' rpc out ', @optvalue =n ' false ' goexec master.dbo.sp_serveroption @server = N ' Hztmvqd ', @optname =n ' Sub ', @optvalue =n ' false' Goexec master.dbo.sp_serveroption @server =n ' hztmvqd ', @optname =n ' Connect timeout ', @optvalue =n ' 0 ' goexec Master.dbo.sp_serveroption @server =n ' hztmvqd ', @optname =n ' collation name ', @optvalue =nullgoexec master.dbo.sp_ Serveroption @server =n ' hztmvqd ', @optname =n ' lazy schema validation ', @optvalue =n ' false ' goexec master.dbo.sp_ Serveroption @server =n ' hztmvqd ', @optname =n ' query timeout ', @optvalue =n ' 0 ' goexec master.dbo.sp_serveroption @server = N ' Hztmvqd ', @optname =n ' use remote collation ', @optvalue =n ' true '

Places to be aware of:

1, the data source to write Ip+sid For example: 172.22.75.111/vqdv3mid, otherwise it may not be normal query. You can also write the Vqdv3mid service name directly.

2. SQL Server link Oracle can be accessed through two interfaces: "Msdaora" and "oraoledb.oracle". The "oraoledb.oracle" Access interface is provided by Oracle's Oracle Probider for OLE DB driver and must be installed by the Oracle client. The "Msdaora" provider is provided by Microsoft OLE DB Provider for Oracle and comes with a SQL database.

The error encountered

News
Executed as user NT Authority\System. The data source object for the OLE DB provider "OraOLEDB.Oracle" of the linked server "HZTMVQD" could not be initialized. [SQLSTATE 42000] (Error 7303) The OLE DB provider "OraOLEDB.Oracle" of the linked server "HZTMVQD" returned the message "ORA-01017: Invalid username/password; Login is denied. " [SQLSTATE 01000]  (Error 7412). The step failed.

Solution:
1. Linked server--HZTMVQD Right-click Property--Security--Add an account in the mapping of local branch login to remote server login. For example:
Local login SA, remote user vqd, remote password vqd123 login mapping with SA account
Local login NT AUTHORITY\SYSTEM, remote user vqd, remote password vqd123 login mapping with NT AUTHORITY\SYSTEM account

2. If you cannot access it after adding it, please add the corresponding account in the security-login name and assign the permissions

Well, for the time being summed up so much, next encounter new problems in the summary. January 13, 2015 17:02:52 Peanuts

SQL Server accesses Oracle through a linked server