0

I work with SQL Server 2016 and I will need to send the generated scripts in some releases to our client, so they can install the package in UAT and Production. I would like to send the scripts in DLL or EXE format, so they can't see the content. Is it possible? Do I need some third party applications? If yes, which ones?

  • 1
    Im sure DLLs and EXEs can he decompiled to expose the text string for the DDL. I hope you have a License Agreement with your client. – Michael Kutz Apr 27 at 0:35
  • Hi @MichaelKutz, thanks for the feedback. Yes, my company have a License Agreement with the client, and we have a product, so we don't want them to see or have the source code. That's the main reason of my question. Please have in mind that decompile a DLL is illegal – pncsoares Apr 27 at 0:37
  • You could probably do it with our third party product Encryptionizer. But honestly it would be overkill for you unless you were also using it to encrypt your databases. If you want to explore more, submit details on our contact page and mention DataBase Administrators Stack Exchange in the comments so it gets to me. – Neil Weicher Apr 28 at 13:15
  • The while process is rather questionable for shipping updates, but one option would be to write your update scripts in Python (which allows further conditional logic as well as interactively determining the database connection details) and then use PyInstaller with the --onefile Option to generate a executable out of it. (Or you use a obfuscated powershell script). – eckes Apr 28 at 14:30
2

send the generated scripts ... to our client ... they can't see theirs content.

This is technically impossible, but may be sort-of implemented depending on what your threat model is. The problem is that the resulting distributable must be able to unpack/decode/decrypt the scripts such that it can submit them to SQL Server so it must contain all the required algorithms and keys.

If your threat model involves trying to stop a determined snooper (a competitor perhaps, or a black-hat looking to find holes that as-yet-unpatched installations are still vulnerable to) from seeing the code then that is impossible with a simple distributable. No matter how many layers you add they can just unpick them by analysing the distributable.

Secondly, if you make it enough hassle that they don't want to do that they have another inspection route: they could just intercept the communication with SQL Server and watch the code as it flows through. Yes TDS streams are usually encrypted, but they are running the package against their SQL server so that control both the client and the server so can control (and therefore break for their own purposes) that encryption. Thirdly, they can always just look in the database, and compare the current to a pre-patch backup if that would make the changes more obvious, the only way around this is for you to host the DB perhaps running your product in a SaaS-only manner.

If your concern is merely more opportunistic snooping, then there are many installation packagers[1] out there that support creating self-extracting executables which would run a program in the archive to run the scripts also in the archive and clean up as best as is possible afterwards, and support encrypting or otherwise obfuscating the content in transit.

[1] I'll not mention specific ones as there are many (free, Free, OpenSource, and commercial), I don't know enough about any specifically to authoritatively compare them, I know nothing of your dev and production environments (other than they include SQL Server), and anyway shopping/recommendation questions/answers are discouraged on StackExchange for reasons explained in detail elsewhere.

I would recommend against using this sort of tool for this purpose though: you are adding a layer of complication that is otherwise unnecessary and might make debugging installation problems that might occur more problematical for you and therefore your client (which won't look good).

If you do use such a product, do so as a convenience: making installation a single (or at least minimal) step process for your clients in order to reduce the risk of human error, rather than as a security measure.

If your concern is more "human factors" such as bad language in code comments or other sources of embarrassment, then discipline and lack of code reviews is the problem, not distribution security, and this needs to be fixed earlier in your process.

I will need to send the generated scripts in some releases

As a side note: if by this you mean that you are generating scripts from SQL Server in order to distribute, then you may need to improve your dev process. You should be able to build everything including upgrade packages from source control rather than generating scripts from your dev databases. This may take effort to setup to run smoothly, but it could save you from a collection of failure types in future.

  • thank you very much for your opinion. I appreciate it. I liked your side note and it’s true...we generate the scripts in a very old way and I want to improve that. I have been thinking about TFS pipeline that allows to check the unit testes, build code and generate the needed database scripts...in the end the pipeline publishes the package to the final environment. Thanks. – pncsoares Apr 28 at 13:13
0

I suggest to build yourself a shell program. You will need to hardcode sql script into it and build it as exe file. So then provide a command to customer to run.

Or another way around to use Data-tier in sql server. MSDN: https://docs.microsoft.com/en-us/sql/relational-databases/data-tier-applications/data-tier-applications

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.