Category: tsqltuesday.com

How to protect your Intellectual Property in SQL server

This is a blogpost inspired by T-SQL Tuesday invitation #167 by MATTHEW MCGIFFEN.

Imagine a scenario where you need to deploy your application, which contains a significant amount of valuable know-how, to a SQL server that is not under your control or is managed by other DBAs. In most cases, you are deploying your application to a customer, and naturally, you want to protect your intellectual property (IP). Is there a way to achieve this? The short answer is no, there isn’t. While there may be some security measures in place, I firmly believe that there is no foolproof method to safeguard data from users with sysadmin access and server admin access. Just a note, Always Encrypted was not a solution in our case, although considered.

So, how did I approach this problem? I decided to add additional layers of security to minimize the risk of unauthorized access to sensitive data.

On the SQL server itself, I created a symmetric key using a password to encrypt the data. The next question was where to store the password required to open the symmetric key. One obvious option was to embed it in a stored procedure and encrypt the stored procedure itself. Even sysadmins do not have the ability to alter encrypted stored procedures from SQL Server Management Studio (SSMS). However, they can potentially use free third-party tools to decrypt the stored procedure and retrieve the original code. This taught me that stored procedure encryption, while helpful, is not entirely foolproof.

Since my application relies on SQL data, another option was to embed the data directly into the application and compile it. This might seem like a bulletproof solution, as the code would be in binary form, making it unreadable. However, in reality, it’s relatively easy to decompile, for instance, C# code using free tools. Reverse engineering has become more accessible than it used to be.

In the end, the level of security you can achieve depends on various factors. If possible, dividing your solution and not centralizing everything can be beneficial. While you might encounter a curious DBA, they typically lack the development experience required to easily access your application code. You can also consider obtaining sensitive data from external resources and storing them only in the memory of the running application or in temporary objects within the SQL server. However, as mentioned earlier, it’s challenging to guarantee that someone won’t hijack your process and access and save the data elsewhere. Ultimately, the level of protection you need depends on the value of your data and whether others can make use of it without understanding its context.

Extended events

This post about SQL server extended events was written based on the invitation 166 from scarydba.com. Still not sure why I haven’t noticed the invitation on the official web of tsqltuesday.com but found it by #tsql2sday

They have been around since SQL 2012, what has changed? Microsoft have done some enhancements to the impact on performance, they added new events, but what has not changed is the user unfriendliness. I feel this is the biggest downside of extended events, especially the need to process results from XML.

There has been 0 development in SSMS, but you have the option to use an extension for Azure data studio, which is handy and provides a similar interface to SQL profiler. Damn SQL profiler deprecated since when 2016? For how long it will be present? We know all the downsides of this tool, but wasn’t it handy at least once in our lives?

Btw. Azure data studio – why is majority DB users still using SSMS, is it a force of habit? Do they think it is only for Azure? Well, I do use SSMS more often, because it is available on instances where I am connecting, but sometimes I blame myself of being to lazy to drive the change. With this thought I found a nice article comparing features of ADS vs SSMS, feels like SSMS got a lot of point for features that are not used or at least not often used.

Another thing with extended events is that they are not always cheap and they can have an impact on the server. As a result rule no. 1 should be to set up your filters properly and be careful with predicated that can be evaluated after the event is actually collected, these won’t help to optimize collection performance.

Recently I came across a great library XESmartTarget that helps you read extended events externally and save data in a better structure than XML, e.g.: directly into DB table, there a lot of options that this library offers and we are going to use it for new features in Server Monitoring and Tuning tool (SMT).

Thank you for this topic and I look forward to reading posts of others.

My new SQL 2022 favourite feature?

My favourite SQL 2022 feature? Just from introductions and blogposts I really look forward to PSP (not a gaming console) and in general Query store changes to help optimize queries. It sounds like Microsoft got the confidence about Query store to enable it by default, which will definitely force more DBAs to keep it enabled and hopefully to use it as I feel that among DBAs it is still not a staple tool or feature.

Especially with Parameter Sensitive Plan optimization, it will be interesting how is it going to work in real life among different systems and workloads, but for sure a step forward.

On top of that as a T-SQL developer I look forward to changes in TSQL itself, although to be fair, some of them just follow the ANSI standard, so it is not like Microsoft came up with everything on their own.

The thing with new features makes me wonder how hard would it be to get them into older and still supported versions and if Microsoft actually wants to do that. I understand that the SQL version has to be upgraded (and a new licence paid) at the end of the lifecycle, although how much support from Microsoft itself you have ever gotten? Getting new features to supported versions would make changes to the community of SQL server so much faster, just imagine that as a dev working on a product, that has to wait on all supported versions you have to wait a few years until the 20222 version will be the oldest supported and then you can start using new T-SQL commands otherwise your code would not work on older versions.

I understand it’s not easy and it is not just adding a new T-SQL command/function into CU and that the changes are happening across the product – like it is not just the interpreter but also the query optimizer etc. I am just saying that this is keeping the process of adopting new features quite slow. On the other hand, does not Microsoft like this, we know that the development especially with big additions like Columnstore indexes, Query store etc. is gradual and stretches over a few versions maybe it is just for good that only those how are really keen and have the option start using everything once it is released, Microsoft could get relevant feedback from all the “testers” and then make it steady for the rest of us.

And on the topic of new year’s resolutions? I don’t like them, I don’t do them. I believe that if you want to make a change, you should start any day a year, ideally immediately, not making drastic decisions and be consistent with small changes to your life and making the “resolutions” your new lifestyle, like eating healthy food, exercise etc. is much better and on a long time scale has a better chance for success.

Hope you liked my first T-SQL Tuesday attendance and I already look forward to #160.

Jiri D.

This blog was written based on tsqltuesday.com invitation #159

© 2024 sqlkoala.com

Theme by Anders NorenUp ↑