SQL Server accesses Oracle and sqloracle through the linked Server

SQL Server accesses Oracle and sqloracle through the linked Server

Databases: SqlServer2005 and Oracle 11gR2

System Environment: windows Server 2008R2 32-bit.

Recently, when working on a project, you need to access Oracle through the SQL link server. I used to access SQL through SQL. I thought it was quite simple to access Oracle. I found many gaps only after I did it.

Steps:

1. for SQL Server Windows, the corresponding version of Oracle client must be installed. (I have installed the Oracle9i client and accessed Oracle11gR2 through PL/SQL. There is no problem, but I cannot query it if I access Oracle11gR2 through a linked server)
2. Create a service name in Oracle Net Manager and test the connection.
3. Check that SQL Server Windows can access remote Oracle through SQLPlus.
4. Create the link server using the following code.

/***** Object: LinkedServer [HZTMVQD] script Date: 01/06/2015 11:02:50 ******/EXEC master. dbo. sp_addmediaserver @ server = n' HZTMVQD ', @ srvproduct = n' Oracle', @ provider = n' OraOLEDB. oracle ', @ datasrc = n' 172. 22.75.111/VQDV3MID 'exec master. dbo. sp_add1_srvlogin @ 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'

Note:

1. The data source must be written as ip + SID, for example, 172.22.75.111/VQDV3MID. Otherwise, the query may fail. You can also directly write the VQDV3MID service name.

2. SQL server can connect to oracle through two access interfaces: "MSDAORA" and "OraOLEDB. Oracle ". The "OraOLEDB. Oracle" access interface is provided by the oracle Probider for ole db driver of Oracle. It must be installed on the Oracle client. The "MSDAORA" access interface is provided by Microsoft ole db Provider for Oracle and comes with SQL database.

Errors

Message
Executed as user nt authority \ SYSTEM. You Cannot initialize the data source object of the ole db access interface "OraOLEDB. Oracle" of the linked server "hztmvqd. [SQLSTATE 42000] (error 7303) link server "hztmvqd" ole db access interface "OraOLEDB. Oracle" Returned message "ORA-01017: invalid username/password; login denied ". [SQLSTATE 01000] (error 7412). This step fails.

Solution:
1. Link to the server -- HZTMVQD right-click the property -- Security -- add an account in the ing from local sub-server logon to remote server logon. For example:
Log on to sa locally, remotely log on to vqd, and remotely log on to vqd123 using the sa account for login ing.
Local login nt authority \ SYSTEM, remote user vqd, Remote Password vqd123 login ing with nt authority \ SYSTEM account

2. If the account cannot be accessed after it is added, add the corresponding account in security-Logon Name and assign permissions.

Now, let's sum up so much for the moment. Next time we encounter new problems, we will summarize them. January 13, 2015 17:02:52 Peanuts