February 7, 2006 at 11:50 pm
Has anyone tried using Oracle 9 Hetrogeneous Services to talk to SQL Server 2005. I can access the SQL database from odbc connection on the Oracle Server but when I try to connect through Oracle I get [server]login failed for user sa (sql state 00000 sql code 18454).
This works fine for sql server 2000 so I assume it's a new security feature of 2005. Anyone know what I can try ????
February 9, 2006 at 5:43 am
Does anyone remember the first time they tried to connect an XP machine to a network?
It took me ages to find out that the bloody thing had already done it by itself, I was looking for some admin setup which just wasn't there.
Same thing happened with 2005 - Oracle, actually this is easier than connecting it to a 2000 server.
Here's my setup (actually I'm using 8i, but it did take me a while to realise how easy it was)
1) Oracle client on the server.
2) TNSNames - Standard oracle tnsnames file
3) Server Objects --> Linked Servers -->Right Click Add linked server
4) Other Data source --> OLE DB Provider for Oracle
5) Product Name --> Anything you like
6) Data Source --> TNSNAMES DB Name
7) Provider String --> TNSNAMES DB Name
8) Security --> Be made using this security context --> Remote login = db user --> With Password = db password
And that's it - there's some messing about with queries when you're qualifying the linked server to use - can't remember exactly but there's a subtle difference in the syntax, something to do with '..'
hope that helps
Rich
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply