Link to home
Start Free TrialLog in
Avatar of Roxanne25
Roxanne25

asked on

Pass Login Info to Linked Server?

Hi there!  I have a question about utilizing a linked server.  I am in a pretty controlled environment and there is a linked server that exists already on our production server.  The server connects to an Oracle database.  I would like to automate some data pulls from the oracle database into our SQL Server but it requires me to have two different linked servers.  There are 2 Oracle logins that I need to use but, from my knowledge, I can only set up 1 username and password in the Linked Server security property.  

There are different tables in the oracle database for each login that I need to use... the only way I can do it now is to edit the linked server each time I need it and put the different credentials in it before I run the queries.

Is there a way to either pass credentials to the linked server on executing openquery statements or to set up the linked server to have 2 different username and passwords and then I can tell it which one I want to use?

Thanks in advance.  BTW - the actual database is running in 2000 but I'm using 2005 to connect to it.
SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
1st - you need 2 linked servers for 2 logins
2nd you can try to use opendatasource
http://msdn.microsoft.com/en-us/library/ms179856.aspx

or
OPENROWSET
http://msdn.microsoft.com/en-us/library/ms190312.aspx

see example and possible problems resolutions from
http://mssql.meetholland.com/message/97381.aspx


SELECT *
FROM OPENDATASOURCE(
'OraOLEDB.oracle',
'Data Source=MyOracleDB;User ID=user;Password=password'
).MyOracleDB.schema.table


Avatar of Roxanne25
Roxanne25

ASKER

Hi thanks, @EugeneZ, I would love to have 2 linked servers but I work for the military and getting another linked server on the server is posing quite a big challenge.

@Angel, I'm curious to your last comment about using the owner syntax and having the linked server set up for 2 schemas... how would I do that?
Also, does openrowset take complicated select statements or just an object name?
you know that you can write:

select * from table_owner.table_name
in oracle, the same as you write it in sql server (dbo.table_name, usually)
Yes, but that would imply that the account you're using has access across the schemas... which I do not.  Each login has access to its own tables and not across the schemas.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks EugeneZ!  That is what I was looking for and has given me a few ways to attack this that I think will work. :)

Much appreciated to both you and Angel.
you are very welcome!