Hello!

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:

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 scriptDeleteVars.ps1 file on the repo.

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