Windows Application as a Service that monitors a folder and executes scripts that arrive in folder
1. Create small application that can encrypt SQL scripts (for SQL Server) hosted on a local machine.
2. Create Agent application as a service on a remote machine that monitors folder for arrival of files and executes when found.
Please see attached word document for more details.
Please feel free to make notes that that document and I will respond accordingly.
User creates SQL script file(s) (a .sql MS Sql Server File) and place them in a folder on local machine.
The user opens the encryption application in (1) above and encrypts the SQL files in the folder above.
Then once these encrypted files have been copied to the remote site (not part of this solution), an Agent program that runs as a Windows Service (created in C#?) (see (2) above) will be monitoring a specified folder location real time for the arrival of the encrypted SQL scripts.
The Agent will also monitor the folder for the arrival of a DLL assembly, a SQL CLR Stored Procedure Class Library with a specified Digital Signature.
If this Assembly is valid, i.e. the name matches the expected DLL file name and the Assembly’s Digital Signature matches what the Agent expects, then it needs to drop and re-register the CLR Assembly in SQL Server first before the execution of the SQL scripts.
NOTE: the Agent needs to know and store the settings that tell it which SQL Server Instance, database, and SQL Account credentials to use in order to establish a connection to the SQL Server.
Of course these settings also need to be stored in an encrypted configuration file.
The encryption key that is used to encrypt the SQL scripts is the same key that will be used by the Agent to decrypt the scripts.
Before SQL scripts are executed, they need to be validated -- .i.e. checked for SQL errors.
Also as an added security feature, a simple key needs to be present at the first line of each script as a comment line. This key is also hard coded inside the Agent, and must match the SQL key.
After the DLL is registered in SQL Server, it gets deleted from the folder.
After the SQL scripts have been executed, they also get deleted.
It would be nice if there was a simple log file that could trace the events.
It would be even better if this log file is encrypted so that it can only be viewed through a special viewer. Perhaps the same utility on the local machine that was used to encrypt the SQL scripts in the beginning?