Hi I'm hoping this isn't too vague to get some help with!
We've searched for several days now and cannot get an answer that works. We use Advanced Installer for different projects and need a sensible way to encrypt the connection strings for sites that do NOT use windows authentication into the sql server.
We've tried encryption after installation, running custom DLL's in Advanced Installer and all sorts. Nothing works because of Microsoft's frustrating permissions fortress around Program Files folder.
Does anyone know of a decent way to do this that does not involve Windows Authentication on the database?
Any help would be very warmly appreciated. As you can imagine, this request is made as a last ditch attempt to find a good solution.
I'm rather shocked that there isn't one that is fairly standard - but if you don't use Windows Authentication on SQL server, I don't see one as far as I can tell.
Thank you in advance for any help offered!
Warmest regards Richard
Edit: From what I understand the encrypt/decrypt can only be done on the same machine - preventing me from shipping an encrypted app.config. The key is based on a machine.config that differs by machine.
I finally found the key to solving this with Advanced Installer - it can't run DLLs that are built in managed code, so you need to use another toolset called Wix to create a DLL that Advanced Installer can use. The instructions for that can be found here: https://www.advancedinstaller.com/user-guide/qa-c-sharp-ca.html
For the encryption we're after, this is the function I created as a custom action in the Wix project (for clarity, minus the try-catch, file check and logging that our actual code has):
<CustomAction()>
Public Shared Function Encrypt(session As Session) As ActionResult
Dim config As Configuration = ConfigurationManager.OpenExeConfiguration(session.CustomActionData("ExecutablePath"))
Dim section As ConnectionStringsSection = TryCast(config.GetSection("connectionStrings"), ConnectionStringsSection)
If section.SectionInformation.IsProtected Then Return ActionResult.SkipRemainingActions
section.SectionInformation.ProtectSection("DataProtectionConfigurationProvider")
If Not section.SectionInformation.IsProtected Then Return ActionResult.Failure
config.Save()
Return ActionResult.Success
End Function
When you build the project, it generates two DLL files: a normal .dll and a .CA.dll. In the Advanced Installer project, add the .CA.dll to the included files (preferably as a temporary file since it's only needed during installation). Then go to custom actions, add a new "Call Function From Attached Native DLL" action, point it at the same .CA.dll file as you selected to add to the project, and then set up the rest of the action like this: (attached image)
...where [#ConfigEncryptionTestProgram.exe] should be replaced with your executable!
The important bits here are the position of the action in the sequence (just before Finish Execution), the execution time (commit), and the execution option that makes it run with privileges so that it can get around Microsoft's restriction on modifying files in the Program Files folder.
