Let me start off by saying that DBATools is a great tool that is immensely helpful to the community, if you haven’t heard about it or tried it, please give it a shot! I personally believe that the SQL Server community is truly the best out there, with so many people helping each other and developing great, free, tools that benefit all. Having said that, I’ve been asked quite a few times to look into a “SQL Server Bug” decrypting data.
This is not an issue with SQL Server nor is it a bug/feature/whatever. It is, in fact, a bug in the source material for DBATools. There is a function Get-DecryptedObject which attempts to reverse the decryption of items such as credential passwords, which can then be used for whatever people are going to use them to do. This is based on Antti Rantasaari’s work on reversing certain types of encrypted data on a SQL Server instance (specifically linked servers). Antti did a great job, clearly a very smart individual, but it makes certain assumptions about decrypting and the decrypted data, as Antti stated can and may change based on version of SQL Server.
I’m writing this for two main reasons, the first is that I keep being involved in these same questions/situations so I wanted to raise some public awareness on the matter. I’ve been asked/told multiple times that this is a bug in SQL Server where SQL isn’t storing the right passwords or isn’t decrypting the data correctly. This is not true.
The second, and honestly more important reason, is that this shows how easy it can be to get certain types of encrypted information from a system. This isn’t a knock on SQL Server per se, but more of a knock on the security of the systems that utilize this feature. Truly if these passwords need to be known or must have programmatic access to them, should exist in a hardened vault or store. There are various enterprise options available which also bring a host of other great features, such as password rotation upon use (i.e. single-use passwords), better encryption options than SQL, programmatic access, fully logged environmental use, etc., and this should be utilized.
I’m all for digging into things to understand them, I do it myself, and I hope people continue to do these things. I believe this makes the product (and community) better. However, let’s secure our systems properly.