0

I'm looking for a way to SAFELY run daily backups using mysqldump. Safely means not storing username/password in a text file or any other unencoded format.

I know there is (apparently it isn't) a way to safely run MySQL queries: storing the credentials in an ODBC datasource within the OS.

Is there a way to force mysqldump utility to use that predefined datasource? I guess a PHP script with odbc_connect() won't do, because I can't pass the connection link to mysqldump.exe...

Or is there any other way to store username/password for use in an automated script?

I'm running Windows Server 2008 R2, MySQL 5.6

Slava
  • 201
  • 3
  • 12
  • Odbc would just store the unecrypted credentials in the registy instead of a file. Not sure why you think that would be safer. – Zoredache Aug 27 '13 at 15:34
  • @Zoredache Thank you for clarifying that. I just went one step backwards from the solution. – Slava Aug 27 '13 at 16:36
  • Just out of curiosity, why do you think you need this? If this is on the MySQL server, then anyone could bypass the credentials by just restarting the MySQL server with a certain option. If you are really worried, why not just setup bitlocker so the entire system is encrypted? – Zoredache Aug 28 '13 at 01:20
  • @Zoredache You seem to know a lot about security. Is it really that easy to get access to all MySQL databases? Because that's shocking. What exact options do you refer to? I want to know what the intruder will be capable of. Also would you mind to explain how partition encryption (bitlocker) could prevent a hacker from stealing data or messing up the mysql server? – Slava Aug 28 '13 at 13:46
  • A person with admin access can run the documented recovery procedure. http://dev.mysql.com/doc/refman/5.6/en/resetting-permissions.html A user that would have access to your stored credentials would have already compromised your server and have admin access on the system. Getting into mysql is trivial. They would probably not be spending time looking for your stored password. Full disk encyption protects you from physical attacks. An attacker with physical acces couldn't get the creds by booting from a livecd. – Zoredache Aug 28 '13 at 15:36
  • Ok. Thanks. Now I can answer your question. The encrypted storage of the MySQL credentials will protect from intruders that don't have admin access to the OS, but read files, run processes as a compromized service or a malware application. – Slava Aug 28 '13 at 16:19
  • Setting strong ACLs on the directory/file would do that do, and it is the standard method. – Zoredache Aug 28 '13 at 16:23

1 Answers1

1

There is no such way. Passwords must be stored in cleartext or reversible encoded format - client must authorize itself somehow and mysql doesn't support any other methods (unlike pgsql for example, which can use practically anything with its utilities) - it wants password.

GioMac
  • 4,544
  • 4
  • 27
  • 41
  • 1
    I can't vote down, but this is -1. Passwords in MySQL need only to be PASSED as cleartext. It has nothing to do with storage. [MySQL Workbench's Password Storage Vault](http://dev.mysql.com/doc/workbench/en/wb-manage-db-connections-vault.html) is an excellent proof of that. (this brings me to an idea to dig into the docs, maybe I can use the vault). There are also ways to use 3d party automation tools like Finalbuilder, which have password storage for things like zip archives, which can be used to temporarily extract credentials and cleanup afterwards. – Slava Aug 27 '13 at 17:04
  • Passwords are stored in reversible format. PASSWORDS ARE STORED. – GioMac Aug 27 '13 at 17:17
  • MySQL connection (both local-socket, network-based) requires passwords to be passed and there is no other way for authentication, which means that you HAVE TO store password. It doesn't support other authentication mechanisms with mysqldump. – GioMac Aug 27 '13 at 17:20
  • http://dev.mysql.com/doc/refman/5.5/en/pluggable-authentication.html – GioMac Aug 27 '13 at 17:22
  • Don't get this wrong. That is also exactly what I want to achieve: store passwords. Storing it cleartext may lead to company's bankruptcy in case of an incident. So the only option is to encrypt. Reversible encryption will of course mean that I need to store the encryption string somewhere. Programs like MySQL Password Storage Vault most certainly uses encryption string decode algorithms that are compiled using reverse engineering protection techniques, which make the extraction of the encryption string extremely difficult, even for experienced hackers. This is 'safe enough'. It's possible. – Slava Aug 27 '13 at 17:48
  • I understand... No need in reverse engineering, it stores password for workbench with Win API, anyway, it's very easy to trace everything you need, even without reverse engineering. Solution is to make something similar, to use wrapper with Microsoft CryptoAPI support. http://msdn.microsoft.com/en-us/library/ms867086.aspx this is only thing you can do with mysqldump - call it through the script, which will get password through this API. – GioMac Aug 27 '13 at 17:57
  • We are getting closer. Vault apparently uses Windows user credentials to encrypt `connections.xml` containing passwords. There is no other encryption string storage needed. The only thing we need now is a tool that can be called programmatically to store & retrieve passwords using MS CryptoAPI. – Slava Aug 27 '13 at 18:28
  • Yep, that's what I am searching now. – GioMac Aug 27 '13 at 18:29
  • http://www.askyb.com/mfc/c-encryption-and-decryption-using-microsoft-cryptoapi/ – GioMac Aug 27 '13 at 23:29
  • Thank you for the effort and for this find. So far I'm also only finding source code examples, no reliable tools. As far as I could find, CryptoAPI also can't be called through command line directly, only by function calls. If I find no reliable tool, I may setup a C++ environment (in which I never programmed) and use this example to write custom command-line tool, with all the risks and time investments. – Slava Aug 28 '13 at 14:02
  • Yes, same here. I think I need to read a bunch of books about how it works.. – GioMac Aug 28 '13 at 14:04