How to not use hardcoded DB credentials within an ERP?

Question

Context: I work for a business that uses an ERP's built in macro functionality to automate tasks. It uses VBScript as its language. Previous IT Admins had hardcoded the DB connection in all of these macros. ERP clients on users machines have a directory where these macros are stored that get referenced.

Concern: In the event of a security breach, a hacker would easily be able to gain access to the database after browsing our shared network drives and finding the macros with the database credentials.

Ideas I've had: I've thought about adding the connection string to the environment variables on the ERP client machines. Also, I've thought about Integrated Windows Authentication with System DSN reference for the database instances, then adding an Active Directory group for all ERP client users with read/write privileges as a database's login (this isn't ideal either since the user's still have writing capabilities. I would've made the UPDATE/INSERT/DELETE commands into stored procedures as a solution, but there are hundreds of them).

Question: Given this dilemma, what would be a viable solution or best practice from a security standpoint?

Answer 1

Your idea with using a system DSN with Windows Account makes sense. Not sure if there is a service account you can use instead. Either way it's better than having passwords in a file/macro. If you can limit the permissions to db_datareader and db_datawriter you are already on a good path. Next I would see which tables specifically contain sensitive information and start replacing the commands accessing them with stored procs and then grant execute on those procs. Of course, then you need to start tearing the other DML permissions apart. Not sure if you can work with a separate schema and views, that depends on your DB design, which I don't know. But that's a general direction. Also, you could potentially employ application-level encryption for some of the data, so that anyone not using the App can't see the sensitive data.

Answer 2

Operating systems typically have a built-in vault for securely storing credentials. For example, there are the Kernel Key Retention Service in the Linux kernel, the iOS Keychain and the Windows Credential Manager (or the Data Protection API in older versions). If this is available to you, then you could use the following setup:

• You create one or more database roles for the scripts, keeping the role permissions at the absolute minimum. Even if an attacker manages to compromise a client and gain access to the corresponding database role, this shouldn't lead to catastrophic damage.
• On each client, you create a separate (service) account for running the scripts.
• You store the database role credentials in the client's vault and ideally restrict them to the script account, so that other applications or the standard user account cannot obtain the credentials. If the vault implementation in your Windows version doesn't have such fine-grained access control, consider putting the credentials into a client-side configuration file which can only be read by the script account.
• The clients are then able to run the scripts which in turn read the credentials from the vault (or configuration file) and connect to the database system.