Perl SQL connection string needs to be encrypted

IT security people are asking me to not have unencrypted username/passwords on disk any longer. But I use several DSN files and need to be able to indicate what connection string to use. Is there a way to connect to a SQL database using the .NET encrypted version of a connection string? Or are there other means to handle this?

I have connections strings such as:

Provider=SQLOLEDB.1;Data Source=MYSERVER;Initial Catalog=MYDATABASE;User Id=MYUSERID;Password=MYPASSWORD

Open in new window

LVL 2
AmkickAsked:
Who is Participating?
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.

wilcoxonCommented:
I don't know of any way to do this directly.  Which DBD are you using?

I can think of two ways that may work:
You could patch the DBD module to allow an encrypted string.  This may or may not be easy.
You could store the encrypted string in the code in a variable and then unencrypt it in code before using it as the connection string.  If anyone knows how to read the code then they could get the unencrypted string but at least it's not stored on disk in unencrypted form.
0
AmkickAuthor Commented:
DBD? I use

	
my $conn = CreateObject Win32::OLE "ADODB.Connection" || die "CreateObject: $!";
$conn->{CommandTimeout} = 240;
$conn->Open("$dsn");

Open in new window


where $dsn is the connection string as written above. (I hope that answers your question.)
0
wilcoxonCommented:
Odd.  It looks like you're using Win32::OLE to establish an ADODB connection.  Is there some reason why you're not using DBD::ADO (via DBI) to establish an ADODB connection?  That would be the much more normal way.

When doing that the code would look like:
my $dbh = DBI->connect("dbi:ADO:$dsn", $usr, $pwd, $att ) or die $DBI::errstr;

Open in new window

with the $dsn being the connection string you provide minus the user and password clauses (though it might work even with those embedded in the string rather than separated out).

Then again, that's really a tangent as it doesn't fix the encrypted connection string issue...

Another option that you could use is to have the username and password stored outside of the code and read in by the code.  It could be stored in another database or in a file or something else (and could be encrypted and decoded by your code before being used to connect).
0

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
AmkickAuthor Commented:
no reason, just bad habbits, I guess...

Storing the username and password elsewhere was my workaround too. Thanks for confirming that this is the best option here.

By the way: another option is to use integrated authentication in SQL, which does present the drawback that the account that is running the perlscript should be a SQL user too. In our case that is not OK, so I will go with your suggestion.
0
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
Perl

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.