1

Currently I have a SQL database on a server. The access and data query is currently done from Excel. A user table with properties is also created on the SQL database so that each user only gets this data and is allowed to get it.

That works so far everything very well. Now my problem is that the password is in plaintext in the VBA code (in the connection string), which is not so secure. What would be a safe solution now? Optimal would be a small login frontend (username/password), which establishes a connection to the database and checks the user in the usertable. Then the frontend opens the Excel file and passes user/password variables to the VBA code in the Excel file. So that no username/password is stored in the Excel file, but is compiled in the frontend login.

However, apart from VBA (good knowledge) I have no real programming knowledge. Is there a recommendation? Could such a "small" logintool with a username/password query be realized? What is the best way, to use .NET, C# ? Is a user interface in Access more secure? (The VBA password protection in Excel is unfortunately not so secure).

Jiri Tousek
  • 12,211
  • 5
  • 29
  • 43
Mr.Shazam
  • 89
  • 8
  • Maybe i am missing something, but why don't you display a VBA form with a text field (user id) and masked field (password). Here is an example [vba password entry](https://stackoverflow.com/questions/28189864/excel-vba-input-box/28195548) – GMc May 17 '19 at 07:23
  • Possible duplicate of [Excel VBA Input box](https://stackoverflow.com/questions/28189864/excel-vba-input-box) – GMc May 17 '19 at 07:25
  • Also, i think it will be more complicated to have a front end (e.g. java, C or whatever) to accept credentials and pass them to Excel in a secure manner than it would be to simply ask for them directly from an Excel VBA form. – GMc May 17 '19 at 07:28
  • @GMc Thanks for your advice. yes, that would be a safe option in any case. But you would have to enter the password at each start what i actually wanted to avoid (because people forgett this password, or i have to store it in the filesystem in a .txt .dll maybe). But basically it would be safer than having written the password in the code. – Mr.Shazam May 17 '19 at 07:31

1 Answers1

0

Writing a front end in C or Java or whatever won't solve the problem of people forgetting their password. If that is the problem use a password store (ideally one with autotype - so it will automatically "type" the credentials into an application for them). If you are keen to do one of these your self (i.e. build a system to store user paswords securely), many of them are open source, so have a look at how they work. Maybe you could convert it to VBA.

If you really want to never ask the user to enter a password, use these tools as a guide as to how to encrypt the password and store it securely. When the excel file is openned, you can decrypt it. If you go down this path, I would suggest using a language such as C to compile some functions (including the encryption key) into a DLL that excel can access to get the password. Note that this is still not overly secure as anyone who can access the DLL could probably access the password(s).

I use a tool called password safe, it has many passwords in it for all sorts of applications (web, database, terminal, cloud and more).

you should still prompt for the credentials in Excel as per Excel VBA Password Input

Other options might include a Single Sign on. This basically means that the database "trusts" your users' login to the client and will just grant it a session. This can be open to exploits, so you probably really do not want to do this.

GMc
  • 1,764
  • 1
  • 8
  • 26
  • Thank you for your reply! Forgetting the password is not the main problem. Currently 20 people are using the file/tool. My main problem is that the Excel file contains the access to the SQL server in VBA code.The safest solution would probably be, as you said, to enter a password in a user form every time you start, and then use it as a variable.Maybe this could only be asked at the first login and then excel/VBA saves the password in a file hidden in the directory? The only important thing for me is that if someone unauthorized gets the file, he won't get the password from the database. – Mr.Shazam May 17 '19 at 08:58
  • I don't know if you can set something like an authentication on the server (mariadb sql). so that you create users on the server and they don't need a password at all, the authentication is done via domain/username. I don't know if this idea works and if it is a safe one to protect the data on the SQL server. – Mr.Shazam May 17 '19 at 08:59
  • I agree, but it is a method that will meet your needs. Basically, the server (mariadb) "trusts" that your windows login is secure. Not all DBMS servers support it. As for security, it really depends upon your environment. If it is your local PC and your local LAN and your PC is always locked when you leave it, it might be OK. If it is a corporate environment where people do all sorts of random things, maybe not so. That is why most applications require users to login each time - maybe you could do a hybrid in your VBA form (enter UserID + Password + a "remember me" option)? – GMc May 17 '19 at 23:03