The team I work with handles large amounts of consumer survey, and internal company metric data. Primarily the data is stored in a database, and we utilize various platforms and to work with that data, including MS Excel.

My peer has developed a suite of Excel/VBA files to take the raw data we get and upload it into a database. Unfortunately, after a code review we identified that the database password was displayed in the VBA code, in plain text, in several places per file. Being in a large enterprise organization we had a number of concerns about those files being available to more than just our team, and the database password being compromised.

The problem is, I feel the solution is just as bad, if not worse than the original problem. The plaintext passwords have been removed in the code of all of the VBA files, and an ancillary, helper file was created that requires the user to authenticate through our organizations LDAP system before gaining access to the database password. Being a scripting language (and being Excel), I feel there really aren't adequate precautions in place to secure the users password in the helper file. I feel that now instead of compromising access to a single database, we are potentially compromising access to the logins of anyone who uses these files.

To be fair, my peer has implemented a number of security minded techniques for securing the data in helper file. I just feel each one can easily be circumvented:

  • locking the VBA code [google search for vba password recovery]
  • using a custom "masked" password entry box [the password box may be masked, but it is still passed out of the box as plaintext]
  • not actually storing the user password in the file itself (he passes it on directly to the LDAP call) [a break point or stop statement can easily reveal the contents of what was passed]
  • setting the sheets to VeryHidden to prevent direct access [append .zip to filename, and ALL data in ALL worksheets is accessible]

As I'm not one to complain without a solution in mind, our organization already employs Active Directory/LDAP for authentication into our individual machines. My thought is that checking the logged in user (not a full user/password, just the user) against a white list of users on the database should be a sufficient alternative for authentication.

So My Question...
Am I being too paranoid about this? Is there another, more appropriate solution? I am pushing back fairly hard on this fix and I just wanted a sanity check before I start to involve others in yet another solution.

  • The obvious solution would be for the username/password never to be passed to/through excel at all - Can't you do this using AD for the user to authenticate their uid/psw direct to the server? – James Snell Aug 6 '13 at 12:24

You are being a bit paranoid, every solution to hide a password won't work against someone who really wants it, but there are better ways to solve this issue so some concern is warranted. Your idea is far worse than the current situation, with your idea everyone has access to the database that can use the scripts, any of them could easily do bad things to your database, and no one has to go through the trouble of figuring out the account used to access the database.

The best solution would be to create a small desktop app or web app that can be used to upload excel files or however your data comes, and then encrypt the connection string.

  • Thanks for the sanity check. I will look into other methods of uploading the data. – Hari Seldon May 4 '12 at 14:54

You can't stop every possible password interception technique. So you just have to think about each technique that can be used, and how likely it is to be used. If it is easy, and very likely to be used, then that is something that you probably shouldn't use. If it is hard to do, though, and unlikely that someone will do it, then that might be OK. Some security experts would probably disagree, but the reality is that you can't always fortify everything, all the time.

With that said, the idea of uploading the Excel file to a web app or desktop app that does the database load is a good one. That way you only store the password in one place.

  • 1
    I've recently taken a number of computing security classes, and I think this is probably one of those times where theory of what would be nice to do, conflicts with what is practical to do. I think I will ease up a bit – Hari Seldon May 4 '12 at 14:56
  • @HariSeldon Good idea, sometimes it is hard to balance these things. And sometimes you can't; that is part of the fun of figuring this stuff out though. :) – Alan Delimon May 4 '12 at 18:29

I would not call you paranoid. You are security conscious the steps that your peer has taken do help a bit but they are far from being enough. Still a case can be made about how much security you need.

There is a saying in Security in IT. "Given enough time any system will fall". It's just a matter of balance, how much do you care about that password database and how much is enough security (which also tends to become a burden for the user and developer).

I will say that the techniques you mention are rather weak. If you can see the plain text password then it can probably be sniffed. The code locking seems also very weak, tough I must I admit I know little of this.

Again security is a matter of balancing. What could happen if you password db got compromised?. Balance this with the price of a Security Audit for your solution or hire a security conscious developer that has some experience in the matter.


One possibility would be to use INTEGRATED SECURITY=sspi in your connection string, like this you don't have to specify user/password and access would be handled through Active Directory login and DB permissions (you'd need to setup DB access for allowed users for this to work).

I also found this other article on Stack Overflow where they explain how to create an external DLL that can be used to retrieve the connection object obscuring all details in that compiled library.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.