Let's say I have R or Python scripts which connect to a SQL database using SQL authentication on a schedule. In the scripts, one way or another, the username/password must be passed through the functions of RJDBC (in R) or SQLAlchemy (in Python). What is the best way to protect these passwords from an outsider who gains access to the server and scripts?
If you fear that the server is compromised and the attacker gets access to the scripts you need to store the passwords outside of the scripts where the attacker has no access. If this is possible depends on the kind of attack you expect:
- If you expect only a limited attack where the attacker gets only limited access to a few data then you might store the credentials in a file where the script user has access but not the attacker.
- If instead you expect a full compromise then you need to store the credentials on a different system. But it will be hard to make the script then make automatically use these remote credentials. It might be possible to automatically feed the credentials from remote into your application but how this is done depends on your specific environment and use case.