Remote apps and SQL Server Security

ariel2 has asked for the wisdom of the Perl Monks concerning the following question:

Monks,

I have developed a perl GUI application for use within my corporate network. This application makes DBI connections to our MS SQL Server, but now I am realizing that I am going to have to make this app available to various 3rd parties who would run it from outside of our network (and be accessing sensitive financial data).

I'm sure that it's possible to poke a hole in our firewall and change the connect string... but somehow that seems like a horrible idea. I thought about wrapping each of the queries into SOAP calls, but that seems overly rigid... Maybe something with SOAP and HTTPS (which I have never done, but assume is possible) that would accept queries and return the results?

Basically, I really have no idea as to the best way to handle this situation. I'd love to be able to continue to handle the database activity in a DBI-ish manner, but not at the expense of a respectable amount of security for our data and our servers.

Absorbing,

-ariel2

Comment on Remote apps and SQL Server Security

Replies are listed 'Best First'.
Re: Remote apps and SQL Server Security by dragonchild (Archbishop) on May 17, 2005 at 02:38 UTC
Wrap your database calls behind an API of some sort (OO is good here) and rewrite your application to think only in terms of these business-level function calls. So, instead of looking at it as "data from tableA and tableB", think of it as "the quarterly report data". This has three benefits: Your application is more testable Your application is more agile when it comes to change You can change how the data comes to the application #3 is the relevant one here. Once all your datastore access has been changed to be business-level, you can use SOAP, XML-RPC, HTTPS ... pick your favorite remote protocol to work with. Your application doesn't care. You can also handle more than one type of protocol, if your clients need this. In general, if you think something isn't in Perl, try it out, because it usually is. :-) "What is the sound of Perl? Is it not the sound of a wall that people have stopped banging their heads against?"	[reply]

Replies are listed 'Best First'.

Re: Remote apps and SQL Server Security
by dragonchild (Archbishop) on May 17, 2005 at 02:38 UTC

This has three benefits:

Your application is more testable
Your application is more agile when it comes to change
You can change how the data comes to the application

#3 is the relevant one here. Once all your datastore access has been changed to be business-level, you can use SOAP, XML-RPC, HTTPS ... pick your favorite remote protocol to work with. Your application doesn't care. You can also handle more than one type of protocol, if your clients need this.

In general, if you think something isn't in Perl, try it out, because it usually is. :-)
"What is the sound of Perl? Is it not the sound of a wall that people have stopped banging their heads against?"

[reply]