Solved

Perl SQL connection string needs to be encrypted

Posted on 2014-10-28
4
259 Views
Last Modified: 2014-10-28
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

0
Comment
Question by:Amkick
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 26

Expert Comment

by:wilcoxon
ID: 40408599
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
 
LVL 2

Author Comment

by:Amkick
ID: 40408708
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
 
LVL 26

Accepted Solution

by:
wilcoxon earned 500 total points
ID: 40408820
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
 
LVL 2

Author Comment

by:Amkick
ID: 40409430
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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
As a financial services provider, your business is impacted by two of the strictest federal regulations on record: the Sarbanes-Oxley Act and the Gramm-Leach-Bliley Act. Correctly implementing faxing into your organization to provide secure, real-ti…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question