RE: DBLINK from Oracle to SQLserver
Date: Tue, 1 May 2012 17:09:09 +0000
Sorry, forgot to add other useful information.
Oracle 9iR2 18.104.22.168 on w2003
SqlSever 2008 on W2008.
I followed all of the steps I found in the white papers. The only difference was that I used NT authentication when I setup the ODBC connection (using my credentials) and then a supplied username and password from the Sqlserver side for the link creation.
I've not used the database owners credentials yet, but that may be the next setup. Gonna recreate the ODBC DNS entry with the same credentials as the dblink and see what happens.
I'll look at oracle gateway but don't think we are going to have the budget right now for another licensed option in Oracle...
From: Taylor, Chris David [mailto:ChrisDavid.Taylor_at_ingrambarge.com] Sent: Tuesday, May 01, 2012 12:01 PM
To: 'merchanti_at_hotmail.com'; Storey, Robert (DCSO) Cc: 'oracle-l_at_freelists.org'
Subject: RE: DBLINK from Oracle to SQLserver
Oracle Gateway is a separately licensable option I think (I know it used to be).
"Quality is never an accident; it is always the result of intelligent effort." -- John Ruskin (English Writer 1819-1900)
Any views and/or opinions expressed herein are my own and do not necessarily reflect the views of Ingram Industries, its affiliates, its subsidiaries or its employees.
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Imtiaz Merchant Sent: Tuesday, May 01, 2012 11:58 AM
Subject: Re: DBLINK from Oracle to SQLserver
Looks like you are using the generic ODBC driver. Install the Oracle gateway to SQL Server. I just did that a couple of weeks ago myself and it works like a charm. The documentation that comes along with the product is good too. It is the 5th CD in the 11g CD Pack.
On May 1, 2012, at 11:01 AM, "Storey, Robert (DCSO)" <RStorey_at_DCSO.nashville.org> wrote:
> Morning folks.
> This shouldn't be difficult, but as usual, it is. Creating a link between an Oracle DB and a Sql server DB.
> 1) I setup the System DSN and tested it. Works
> 2) Modified the TNSNAMES.ORA file - check
> 3) Modified the Listener file and reloaded - check
> 4) TNSping of the TNSnames entry successful - check
> 5) Created the inithsodbc.ora file - check
> 6) created a dblink putting the username/password in "" and the odbc
> service in ' ' - check
> 7) link created successfully - check
> 8) select * from table_at_linkname -ERROR
> Got the error back
> ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
> [Generic Connectivity Using ODBC][Microsoft][ODBC SQL Server
> Driver][SQL Server]Cannot open database "test" requested by the login. The login failed.
> (SQL State: 37000; SQL Code: 4060)
> ORA-02063: preceding 2 lines from BOBLINK
> Dropped the link and recreated it using just a junk username and password. Got the same error. Metalink has gone to sleep on me.
> I'm missing something simple here but not sure what it is. Do I have to use a username with DBA privs on the sql server?