A Fine Slice Of SQL Server
I had a client that was struggling with some encrypted stored procedures. They needed to decrypt them, which I know is a pain in the #@$%@#$@#$#@. I had to do this one. This post shows how I sent them some code to do this.
In a previous post I set up some procedures and then showed code to decrypt them. Here, I’ll use SQL Compare 15, which makes this easy.
Read on to see how it works.
Comments closedSteve Jones breaks the connection:
I had a client that was struggling with some encrypted stored procedures. They needed to decrypt them, which I know is a pain in the #@$%@#$@#$#@. I had to do this one. This post shows how I sent them some code to do this.
Note, SQL Compare 15 does this easier and simpler. If you own it, I’d use that instead. A future post will show how easy that it.
Stored procedure encryption is one of the more annoying features in SQL Server. The idea was, if you wanted to prevent end users from reading your code, you could encrypt the procedures. But in order to use the procedures, SQL Server needed to decrypt them and you needed this to work on restored backups, so the decryption keys needed to be available to that SQL Server. The infrastructure is a bit different from how Microsoft eventually landed Transparent Data Encryption, enough so that it turned out breaking these procedures is trivial, as Steve shows.
I didn’t know that SQL Compare did decryption. The couple of times I needed to do this, I had used a standalone tool which was released in the 2005 timeframe, so it’s good to see something still supported which does this.
Comments closedChad Callihan provides solid advice:
Everyone has an opinion on naming stored procedures. One opinion that can be agreed upon is that starting a stored procedure with “sp_” is not the way to go. Using “sp_” can only do harm. Let’s take a look at why that is.
Read on for Chad’s reasoning.
I don’t like prefixes at all for stored procedures (or tables). It’s a common misunderstanding of Hungarian notation, as you don’t provide any new and relevant information in the object name: of course it’s a stored procedure; I’m using “EXEC” to execute it, so what else could it be?
The exception to a very good rule is something which you intend to put in the master database and execute from any database context. The best example of this is sp_whoisactive.
Comments closedBen Johnston attacks masked strings in different formats:
Clearly this is NOT a suggestion for how you might break the text but is far more of an exercise to show you how a bad actor may attempt to look at your data in ways that would generally not cause red flags.
It is especially important to reinforce the sentiment that Dynamic Data Masking less of a security tool to prevent attacks, but more to hide data from general viewing, and as a tool for building applications where the data still is accessible in some scenarios and not others.
Click through for several examples. As I like to say (over and over), dynamic data masking only works until users get access to write arbitrary queries against a system. If they’re accessing data through an app or via stored procedure calls only, then it be a reasonable part of a broader security posture.
Comments closedChad Callihan doesn’t have time for the UI:
If you need to make changes to multiple servers, you may want to avoid the GUI approach and all of the clicks that come with it. In that case, msdb contains a stored procedure called sysmail_update_account_sp that might be a more efficient approach. Let’s take a quick look at sysmail_update_account_sp and what it can do for you.
Read on to see how the procedure works and what you can do.
Comments closedGreg Dodd has an Extended Events session for that:
Do you ever have a stored procedure that you know is performing badly and needs tuning, but you’re struggling to capture when it is run or what parameters were passed in?
I had this problem recently, I knew that a particular stored procedure was running slowly for some parameters, but figuring out what the bad combination was proved to be very difficult. What if, instead of trying to guess what the parameters were, I just captured all of the time that the stored proc ran, along with the run time?
This isn’t something you’d want to run for everything at all times, I’m sure, but this can be quite important when you’re fighting one poorly-performing stored procedure.
Comments closedJoe Obbish resolves a specific type of plan cache issue involving shared temp tables:
The problem can be observed with a simple repro script. The child stored procedure performs a series of updates on a temp table. The parent procedure creates the temp table, calls the child stored procedure, and returns data from the temp table.
Click through to see an example of the issue, as well as one technique to mitigate the problem.
Comments closedJay Robinson defends the highly defensible:
The point I’m trying to make is that the database code is treated as part of the solution. It is in the repos just like the C# code. It is deployed just like the C# code. I even include unit testing, just like the C# code.
I also keep all T-SQL in the database. There is no T-SQL code inside the C# code. Stored procedures are used for all functionality, even basic CRUD functions. In fact, probably 80–90% of the stored procedures in my systems are essentially CRUD functions. The only operation that doesn’t go through stored procedures is bulk insertion, and I use SqlBulkCopy for that.
Jay makes what I often consider the most important point from the standpoint of developers: that stored procedures act as an interface.
Comments closedSoheil Bakhshi calls a stored procedure:
From time to time, Excel users require to get the data from a SQL Server stored procedure. The stored procedures usually accept some input parameters and return the results. But how can we dynamically pass values to the stored procedures from cells in Excel to SQL Server?
Read on for two approaches to the problem. Like Soheil, I think the second approach is much smoother, in part because it isn’t 30-something steps long.
Comments closedKenneth Fisher shares an opinion:
I was asked a rather interesting question during an interview recently. It went something like this:
Your resume says you’ve implemented security standards. What do you do if a development team comes to you and says “We have to have a SQL Server Id in production (or other restricted permission) because that’s the way we tested it and we are going into production now.”?
And in general my response is “You give it to them. It’s too late to stop them at that point.”
Kenneth goes on to elaborate.
I prefer a compromise solution, which requires more than 2 environments. Dev is a sandbox. Developers don’t know what they need and generally need more space to work than the final product itself will need. A wide-open dev environment is something I don’t mind. In some organizations, a local dev environment may be sufficient for this task—that is, SQL Server (or whatever) installed on the developer’s personal machine or brought in via Docker container.
However, there must be at least one pre-production environment in which developers have prod-level rights. Ideally, it would not be the last step of integration prior to prod but instead something like a QA environment which is part of a CI/CD process.
I should also point out that module signing helps with this problem: stored procedures may contain rights that devs shouldn’t have but are necessary to the way an application functions. Access to execute those stored procedures is something the DBA team can control and put some limits around, so in Kenneth’s example, truncation could be handled via stored procedure. That procedure handles the truncation operation and opens the door to further checks, such as a list (or table) of tables which the app is allowed to truncate, logging of operations, etc.
Comments closed