Deleting SQLCMD SetVar The Brute Force Way


Security has always been an important issue, and this is more true today than it ever has been. And one of the cardinal sins of IT Security is storing sensitive data as clear text stored in files. So with the case of sql scripts generated by SSDT, this sets an issue, as the variables are stored as clear text. I know of no way to mask variables, and so there are three options:

  1. Do something clever involving KeyVault that may well get convoluted. The use of the word “clever” immediately rules this out as an option for me.
  2. Do something elegant to obscure the values of parameters after the script is generated/executed so no one can access them.
  3. Do something that solves the issue brute force, but gets the job done.

As I had already thought of a solution that falls directly into the “brute force” category, I tried it out and got it working pretty quickly. I can then re-visit options 1 or 2 sometime when I have the time. It’s not exactly a solution made out of straw, but it does have a weakness, which we will get to in due course.

There is a repo that you can use to try the demo out your self. It is here.

In the repo there is a dacpac, a publish.xml file and 2 PowerShell scripts. One is called “script”, which will generate a deploy.sql file that includes setting the variables. This is a fairly straightforward script. No magic or incantations required. But of course the script here exists on the file system until it is deleted, which may be never. Or it may be packaged and stored as a deployment artefact, accessible to many more people than intended.

So the Brute Force way to solving this problem is simple: once the script is generated then use regular expressions to find the variables and to delete them from the file.

Simple, right?

Well, what we have to do is read the file line by line, and if it matches the regular expression then delete it. If it doesn’t then the line is written to a new file. The old file is deleted, and the new file re-named to the old file. So some magic required. This also has the potential to fail if something goes wrong during the read/write stage. Of course you could do a try/catch and then in the “finally” block delete the file, which should guarantee that the file is deleted. But there’s still a risk that you leave a file with sensitive information on the disk.

This however is still preferable to not doing anything, as this should work for pretty much and file generated by SSDT. The script that does this is in the “ScriptDeleteVar.ps1” file on the repo.

I’d like to know how other people manage this security issue.


Leave a Reply

Your email address will not be published. Required fields are marked *