Import securely from SQL to Excel over web?

[mlep]

Hi all,
I've been tasked with automating a spreadsheet for the boss that needs
to pull SQL data from one of our DB servers, and then format and
import into Excel. The mechanics of doing so aren't the problem.

I want to know how I can securely do this over the web. We'll have
multiple users of this sheet all over the country, and ideally they
would:

1) open the .XLS file
2) press the button I've got a macro tied to
3) the macro goes out and runs the SQL query, and dumps the data into
the sheet

The problem is, the macro contains a credential set for a SQL account,
and while the VB code is password protected in the sheet, the sets
will be traversing the web, open for anyone who cares to look for it.

We've an SSL VPN I can have the users manually initiate and
authenticate to, but I'd like to keep this as easy for everyone as
possible (except for me, it seems). I suspect I may be able to script
the VPN connection, open the XLS file and then continue to monitor the
open state of the XLS file then then terminate the VPN, but that seems
awfully complex...

Oh, and of course, I'm a network guy who's been tasked with all of
this! I'll gladly trade some Active Directory help for some Excel
help!!!

Thanks, any ideas/thoughts would be greatly appreciated.

 

[Don Guillett]

How is this question different from your other post of a few minutes ago?

 

[mlep]

How is this question different from your other post of a few minutes ago?

Thanks for the astute, concise and generally helpful answer to my
question (be it the original or a slightly edited version thereof), I
appreciate it.

 

[MH]

This is not really an Excel issue, I suggest you try a group dedicated to
your database software.

MH

 

[Don Guillett]

You are entirely welcome.

 

[Kurt]

Actually, scripting a VPN connection is quite easy and, although not
directly related to Excel, you could launch a simple script from Excel that
would start a VPN. I'd suggest Windows built-in VPN client for your database
clients, using PPTP - mainly because it's so easy to set up. Then you can
script a connect/disconnect using "rasdial". Launch it from excel using the
"shell" command. Rasdial can connect or disconnect a VPN (or other) session.
Here's a link to rasdial info.

http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/rasdial.mspx?mfr=true

I think this would be a fairly easy to implement solution for automating
secure connections. You have to create the VPN connections first (from the
add network connection wizard).

....kurt

 

[mlep]

Thanks for the pointer and link, Kurt -- I'll take a look into that.