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.
Roxanne25Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
>, I can only set up 1 username and password in the Linked Server security property.  
yes, in this context, you are correct: per usage of the linked server, only 1 permission set is possible.

either you use OPENROWSET instead (where you can define the credentials 'ad hoc'), or you must use the OWNER.TABLENAME syntax in your oracle query, using a security in the linked server that can query from both OWNER's (or SCHEMA's ) ...
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
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


Roxanne25Author Commented:
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?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Roxanne25Author Commented:
Also, does openrowset take complicated select statements or just an object name?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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)
Roxanne25Author Commented:
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.
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
openquery is using regular connection string

see the examples

http://support.microsoft.com/kb/240340
--------------
\"getting another linked server on the server is posing quite a big challenge.
"if need - you will get 2nd
--
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
check


sp_addlinkedsrvlogin

http://msdn.microsoft.com/en-us/library/ms189811(v=SQL.90).aspx

see local server login to remote server mapping in linked server security options:
Security for Linked Servers
http://msdn.microsoft.com/en-us/library/ms175537.aspx


e.g. how to add 2 logins

USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'TEST', @locallogin = N'test2', @useself = N'False', @rmtuser = N'tvv', @rmtpassword = N'**cc*'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'TEST', @locallogin = N'test3', @useself = N'False', @rmtuser = N'vvvv', @rmtpassword = N'     '
GO

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roxanne25Author Commented:
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.
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
you are very welcome!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.