• Flyway
• Configuration, Authentication

Storing Credentials Securely on a Windows-based Flyway Installation

Any script on a Windows machine that needs to access data that is outside the logged-in user’s Windows domain will be required to identify itself, via credentials. For a long time, Windows-based applications and scripts have stored such credentials, encrypted, in the user area, which is a secure storage area that is protected by the user’s Windows account credentials. It isn’t just credentials that you can store there but any information that needs to be protected. The added encryption provides an extra layer of security to make it much more difficult for anyone to access the data in the file directly from the drive.

It is easy for an application to use this method of storing credentials, but it not at all easy for the users to maintain all their passwords. Windows Credential Manager adds a facility to allow the user to list credentials and to update passwords as they inevitably change. The Windows method of storing credentials or other secrets makes most sense if the workstation user requires a strong password and biometric user access or ‘access dongle’ (a.k.a. security key), in order to verify the user and also forbids all remote access with your user ID!

I’ll show how to use a couple modules provided with PowerShell to access credentials stored securely in Credential Manager. We then pipe the credentials directly into Flyway, using either the STDIN or PowerShell ‘splatting’ technique, both of which I’ve described previously, in Pipelining Configuration Information Securely to Flyway. This article also demos other, more general, approaches to managing secure information, such as the use of GPG (GNU Privacy Guard), which don’t rely on the Operating System, and which provide a complete cryptographic solution for sensitive data.

Overview of Windows Credential Manager

Windows Credential Manager allows users to securely store and manage any login credentials, such as usernames and passwords, they need to access network resources, websites, and applications. This allows users to connect these remote resources, or to run scripts that do so, without repeatedly having to supply their password. It also makes it easier for users to change their passwords much more easily.

As it is a Windows feature, there is a possible downside of Credential Manager that anyone with administrative access can access your saved credentials. However, if the workstation is secure, with all the usual security best practices in place, then this method is fine for credentials that are for workstation-bound applications. It is the obvious place for storing credentials used by PowerShell scripts. The applications and scripts that you run can access Credential Manager for the stored credentials of the logged-in user and use the saved passwords.

The Credential Manager user interface in Windows 10 can be accessed from the classic Control Panel (Control Panel | User Accounts | Credential Manager). It is possible to import or export credentials, and you can set up two-factor authentication with a hardware security key or other such device via ‘Windows Hello’ to add a level of security.

Once the user has saved the credentials, Windows Credential Manager securely stores them on the user’s computer and retrieves them when they are needed. You can do an encrypted backup of these credentials and, if necessary, restore it. The categories include:

• Web Credentials: usable only from Microsoft browsers
• Windows Credentials: The sub-categories are:
  • Windows Credentials: used only by Windows but important to know about if using network resources that use ID/Password credentials.
  • Certificate-based Credentials: used just with certificates.
  • Generic Credentials: These can be used by applications and scripts to manage credentials.

Generally, the only ones you’ll need to use are the Generic Credentials. These provide a system that returns a User and a ‘blob’ of secret stuff, usually a password, when you provide a unique string that is used as a key for the information.

There is a snag for DOS scripters, though. For running DOS batch scripts, or using Flyway interactively from the command line, you can only use the “cmdkey” command, the only CLI utility provided by Windows, to add, list and delete credentials from Windows Credential Manager. It cannot be used to retrieve a password. This is as useful as selling a knife without a blade. It is relatively easy to write a command file to do this, but I won’t go into the details here.

Using Credential Manager with PowerShell

If you are scripting, it is easy to switch to Credential Manager, because PowerShell provides a library (Credman.ps1) containing a CredentialManager module to access passwords in the credential store. We identify the credential either by server and username, or by specifying the name, a ‘token’, assigned to the credential. If using PowerShell, we then simply pass credentials to Flyway securely via one of two methods: STDIN or ‘Splatting’ parameters. Either way, credentials are never saved to a variable or file.

We use the CredentialManager module to get the credentials. If they don’t already exist, we elicit from the user what the password should be and save it within Credential Manager. The only slight awkwardness is that the module has to be loaded separately. To get around that, I’ve written a function called Get-WindowsCredential.ps1 that uses a type written in C # (not by me, sadly). This type uses advapi32, which provides advanced Windows API functions related to Windows, including access to the Credential Manager. My modest part was to write the PowerShell to access the credentials to get the password and query the user to set the password if the password can’t be found within Credential Manager. We can use Get-WindowsCredential to keep our secret stuff out of the file system, in a place easily administered by the user and encrypted.

Here it is being used in a PowerShell script, using Flyway’s pipe command (only some versions have this) to pass in the credentials securely, via STDIN. It identifies the credential by username and server name. Flyway will grab all the other required configuration details from the Flyway.conf file.

By using the STDIN technique, or ‘splatting’, we can pass a whole lot of parameters such as an Oracle ‘wallet’ connection, stored maybe in the user area as a file, that should be kept away from prying eyes.

If we’re just passing in the required credentials via command line parameters, we have to decide beforehand which parameters will be used, like this:

Now, it could be that you would wish to identify a Windows credential by a token rather than by user and server. That’s cool. We can deal with that:

If you’re like me, you’d want to save some of that typing:

Then, you could set the right credentials from credential store in a one-liner:

OK. After repeating that a few times you might still yearn for something even simpler. If you are confident with the idea of using an environment variable in a PowerShell session, you can do this, which sets up the values in the session’s ‘Environment’.

Flyway can then read these special environment variables for all subsequent commands in the session. Then you can make all the typing far simpler. Now you are picking up your config information from file, and just adding the User and password values to it. This will work happily:

You should take the precaution of deleting the variables as soon as you’ve finished the process or shut down the PowerShell instance.

What about using Credential Manager to store any other sort of configuration information? You might think of using it in the same way as GPG, but the system doesn’t explicitly support the storage of anything other than passwords and security keys.

Using Credential Manager with Flyway in a DOS batch script.

The most obvious way of using Credential Manager is to use cmdkey for everything but the most common requirement, which is to get the user and password. I generally evoke PowerShell from DOS to do this, but you really don’t want to do this too often because it is slow. The whole of PowerShell has to be heaved into place for just this simple task. However, if you do this in order to set the User ID and password as environment variables, it generally only needs to be once in a script.

C:\ >PowerShell -file "%tmp%\myProg.ps1" -User Phil_Factor -Server Philf009
powershell -file "%tmp%\myProg.ps1" -User Phil_Factor -Server Philf008|flyway info -configFiles=-

The script that does this is like a function in a file. The source is here, as ExtractCredential. This can easily be rewritten in C# or Go, thereby unlocking its use when using Flyway in DOS.

Conclusion

Windows Credential Manager is a very useful part of the Windows system, and it is so much easier to use than it used to be. It meets our objective of securing the user and password values for accessing a database via Flyway. It isn’t, however, a complete alternative to other systems.

The trouble comes from cloud database systems with more complicated credential-based connection systems. Effectively, Credential Manager only currently supports variations of ID/password. If you have to access more complex systems that cannot be accessed via MSA, AD or AAD, and need more than ID and passwords, then a more generic file-level encryption system that can use a dongle for two-factor authentication is better, because it can store structured information such as XML and JSON and do it safely and responsibly. I describe this more general solution, that can even include an entire config file, in another article: Using Windows Security and Encryption with Flyway.

Tools in this post

Flyway

DevOps for the Database

Find out more