title: How best to store a password in SQL Server so that the password can be retrieved
body: <p>So I'm creating an application that logs into a long list of FTP sites, checks for new files and downloads any new files it finds.</p>
  
  <p>I'm using SQL Server 2016 SP1 to store my table of files that I've already downloaded, so when I connect to the FTP and check against that table, I can tell if a file is "new" or not.</p>
  
  <p>I'm also using SQL Server to store my list of FTP sites, usernames and... PASSWORDS! My application loops through every FTP site in my FTP table one by one and uses the credentials in the table to connect to them.</p>
  
  <p>Right now I have the passwords stored as plan varchars, since I need to pass them into the FTP application I'm using.</p>
  
  <p>What I'd like to do is store them as encrypted data, so that I only ever decrypt the password when I'm about to use it. Ideally I'll pass the decrypted password to my FTP client in memory only and never actually write it to disk as a varchar.</p>
  
  <p>Every guide or webpage I come across talks about encrypting passwords so you only get the hash, effectively giving me a way to check if I have the correct password, which is useful for running a website with a password, or checking if a user has entered a password correctly, or whatever.</p>
  
  <p>What I need is a way to store the password encrypted, so that it can be DE-crypted, by me, so I can use it. Whatever I search for along these lines, I always keep coming back to answers that give me a hash check, not a way to decrypt it.</p>
  
  <p>Is what I want to do possible? Is there even any point? Since if <em>I</em> can decrypt the passwords, then surely anyone with access to the database could? Or is there some way of effectively locking them down to only be decrypted by certain UserIDs?</p>
  
  <p>Advice welcomed.</p>