Options to pass encrypted creds from SQL 2012 to Oracle on Unix?

Question

Options to pass encrypted creds from SQL 2012 to Oracle on Unix?

MothInTheMachine

Hall of Fame

Points: 3251
More actions
May 29, 2014 at 6:07 pm

#291749

Hi,
I would like to establish a SQL 2012 database (for reporting) and utilize SSIS to pull data from an Oracle 10g (migrating to 11g shortly) database which is running on UNIX. The Oracle box does not have the Advanced Security Option (ASO) turned on. My supervisor is telling me that SQL Server has a means of connecting to Oracle on Unix securely without ASO active. Incidentally SQL Server is on 2008R2, if it matters.
My understanding, admittedly as a neophyte, is that the only way to connect to an Oracle/UNIX box from a SQL Server/Windows box is for Oracle to share an asymmetric key with SQL Server which is done with the ASO being on.
Am I missing something?
Thank you!
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

MothInTheMachine Hall of Fame Points: 3251 More actions · Answer 1

Lots of looks on this one. Is this really so rare a scenario? A windows based SQL 2012 server wanting to run a SSIS job to pull data from a unix based Oracle 11g DB. really? No one? Have i finally made it to the bleeding edge 😛

Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;

Lowell SSC Guru Points: 323518 More actions · Answer 2

not sure if this helps at all, but i have a similar situation.

would mapping a network drive featuring specific username/password help?

exec master.dbo.xp_cmdshell 'NET USE J: \\DEV223\c$\Data /user:mydomain\lowell NotMyRealPassword /persistent:yes'

i've got a unix box on our network that I have to copy files form (that get ETL'ed into a datawarehouse).

That connection requires a specific username and password to connect to the share. it's not actually a domain user, but some unix user they created justso I can access it

if i browse to the \\10.x.x.x ip address via windows, i get prompted for a username/password. i can enter the credentials and get to it.

I know i can do the same via a command prompt if i need to script it.

exec master.dbo.xp_cmdshell 'NET USE K: \\10.40.10.192\DataFiles /user:unixlowell NotMyRealPassword /persistent:yes'

in the situations where i'm having an automated process, i can map a drive, and i think you can also pass credentials just to access a drive without mapping, but i'd have to google the similar syntax.

if that works, then it's just file copy operations from a source .

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

MothInTheMachine Hall of Fame Points: 3251 More actions · Answer 3

Hi Lowell. Thanks for your reply. My hunch is there is no automagic encryption that SQL and Oracle share. One solution I had considered was like your. A SSIS dumps the results of a query to a shared drive in Windows and from there i can use integrated security to pick them up and pull them into SQL. The key for us is that at no point in the chain is there a username and password stored in clear text.

Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;