Pivot Table Connection String - How to hide it?

[fijieddie]

Hi

We have several pivot table reports (Excel 97) that we distribute to our
users. The reports connect to an SQL Server 2000 database. Some of the
reports use trusted connection (windows) and others use non-trusted (SQL
login). These reports can easily be opened using VBA or in Excel 2007 to view
the connection string (settings). How can we protect or hide these settings?
(especially the username and password). Any help would be appreciated.

Thanks

 

[GSnyder]

I haven't ever tried to do this, as it's the policy at our company to never
embed user IDs or passwords into the connection string. I don't recall ever
coming across anything that will allow you do to that, though.

Is there a reason you can't just let the user authenticate their own UID and
Password? I would imagine that would be much more secure.

Good luck!

 

[fijieddie]

Thanks for the reply. We do not want the user to enter user and password for
reports as it would become less user friendly. There are numerous
applications and a lot more reports. As for the user id and password,
previously we had Microsoft Office 97 as the default and then it was Office
2003. It was harder to get to the connection properties. In Excel 2007 (which
we are moving slowly towards), there is a simple button there that allows you
to view the connection string. Also, due to management wanting to tighten
security, we are reveiwing all these "loopholes".

I guess hiding the connection settings is not normally done?

 

[GSnyder]

Fiji,

A sneaky way I've used to get around this in the past is to bring up a form
using VBA with a User ID and Password box (formatted as password, so it shows
up as ***** when they type into it). I then added a GO button that updates
all of the pivots, getexternaldata or whatever. Inside that VBA code, then I
programatically set the connection strings and set the UID= Forms1!UserID and
the PWD= Forms1!Password.

So, I've not stored the password in any files and the only way to get to the
password is if the user has access to that machine when it is running the
code (which means they must already have the logon and password). It makes
it easier for the user because they only need to type everything in once
rather than for each different data source.

If you're interested in more info about that loophole, I can post some
further code snippets for you.

Happy calculating!

 

[fijieddie]

I understand your method and actually its a very logical way of handling it.
But unfortunately - it won't do for our environment

I am getting convinced that this is a rare request, judging from the answers
I have found (or not found) on the internet...