Decrypting Stored Procedures with SQL Compare

Posted on August 21, 2023 by way0utwest

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.

This is part of a series I have on SQL Compare from Redgate Software. It’s an amazing piece of software that you should try if you haven’t. Download an eval today.

Decrypting a Batch

The setup is in the previous post, and its the same here. Once that is complete, connect to SQL Compare and set up a project with the databases that have the encrypted procedures as the source. The destination doesn’t matter. You could pick any empty database.

Once that’s done, run the comparison. You ought to get results like this. Notice on this SQL 2017 instance, both procs are decrypted.

2023-07-21 12_43_26-SQL Compare - E__Documents_SQL Compare_SharedProjects_(local)_SQL2017.SimpleTalk

2023-07-21 12_43_21-SQL Compare - E__Documents_SQL Compare_SharedProjects_(local)_SQL2017.SimpleTalk

These do contain the WITH ENCRYPTION text, as this is an exact decryption. If I deploy this to another database, the procedures will be decrypted there.

Moving Procs Without Encryption

For many of us, if we were copying this procs, we want them decrypted. Fortunately, SQL Compare makes this easy. There are project options for this. Notice below I’ve searched for options related to encryption. I selected the “Decrypt encrypted objects” to allow SQL Compare to show the code above.

2023-07-21 12_49_16-(local)_SQL2017.SimpleTalk_1_Dev v localhost.SimpleTalk_1_Dev.scp_

When I check the “Ignore WITH ENCRYPTION”, this will give me the code without that option. I check this and refresh my project and notice the code below. No WITH ENCRYPTION added.

2023-07-21 12_50_04-SQL Compare - E__Documents_SQL Compare_SharedProjects_(local)_SQL2017.SimpleTalk

I can now decrypt a lot of procedures in a batch, and do this as needed since if some are encrypted by a developer, likely you’ll find lots of random places as that developer tried to protect code in every database they touched./

I tested this on SQL Server 2017/2019/2022 and it worked. SQL Compare is a fantastic product for simplifying work and it does so much more than this. Give it a try if you own it or download an eval today.

Posted in Blog | Tagged , , , | 3 Comments

A Real World Clash with AIs

Posted on August 21, 2023 by way0utwest

A group of people are protesting more cars in San Francisco by disabling self-driving cars with a cone on their hood. It is likely that the cone disables some sensors and won’t let the cars move. For truly autonomous cars, this could be an issue if people could prevent them from working with small hacks. I could imagine a strip of duct tape or some other smaller obstacle that might also work and be less noticeable to anyone looking at the vehicle.

Ignoring the humor here, or the more-cars-in-cities debate, this is an interesting place where computing systems can fall down. People do find ways to hack sensors or other ways in which our software gets data from the real world, which can force behaviors we didn’t expect. We might allow for these, or we might not. In this case, I don’t know that the developers could do anything different, other than perhaps detect and alert the owners that the vehicle can’t move until someone clears the obstacle. They could add more sensors, but I suspect hackers would just block those.

The bigger issue is that as we depend on computing devices that interact with and need to work in the real world, we’ll have more problems like this. We might see simple hacks, or even everyday events that can interfere with the machines completing their tasks. If we get very dependent on these AIs, then these hacks become more than just vandalism. Imagine getting in this vehicle in an emergency to go to the hospital, and it won’t move because a sensor is blocked? That could be more than inconvenient.

We certainly have similar issues with people in the real world. People flatten tires (how does an autonomous car change a tire), they padlock doors and gates, and they might do any number of things that interfere with others’ ability to live their lives. Humans can often adapt to these issues with small behavior changes, but what about computing devices? Will a new class of jobs come about to correct issues like this? Will we see “fix-it” robots that autonomously move around and remove cones from hoods or change tires?

It’s hard to even think about all the crazy permutations of how autonomous computing devices can evolve and the types of interactions we might have. I’m not even sure if I think that the way we are approaching AI and autonomous systems is good. We’re often applying similar frameworks to them that we do for other humans, which I’m not sure if the best way to approach these decisions.

The future is looking very strange, and it’s coming quickly. I’m not even sure what to think about issues like this.

Steve Jones

Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

Posted in Editorial | Tagged | Comments Off on A Real World Clash with AIs

Using AI with Data Tasks

Posted on August 19, 2023 by way0utwest

The hot new technology of the year is AI. Between ChatGPT, Copilot, and generative AI, it seems that this is invading the world of computing at an incredible rate. Whether this becomes really useful and valuable or not is something that we will seem over time. There have been plenty of trends in this area in the past that haven’t become as ubiquitous as the hype would lead you to believe.

I’ve done some light experiments with AI on my blog. To date, I haven’t found this to be that useful, other than a few cases where I basically used an AI to search the web for me. Rather than read a bunch of SSC or Stack Overflow results, the AI summarized things.

Somewhat.

I definitely had to test and verify the code more than I feel I’ve done with code posted in a forum. Of course, I do less experimenting because the AI results were a little more targeted to what I needed, rather than my cobbling knowledge and partial solutions together. I’m also not sure which I prefer.

I would like to use AI for data work, and there is an article that talks about some of the ways that we’ve used AI in the past. Data profiling has made sense, and I can see value here. For data security, I’m not sure how helpful AIs have been. I’ve looked at some products, and I don’t know that I think any of them do a great job of identifying data. They do make it easy for whoever is assigned the task by doing some of the work, but they aren’t a panacea. They make mistakes, just like humans do.

I do think data observation and looking for anomalies is a place where AI can really shine, but that’s not the data work that many of us do. It matters, but for most of us, this isn’t something we deal with.

The future of AI was more interesting. The idea of data homogenization, taking data from different sources, and fitting it to a data model is interesting. Of course, the AI can’t make too many mistakes, or the time correcting might overwhelm the time saved. I think we see that now with humans who we ask to ETL data. If they aren’t good at it, or make lots of mistakes, those of us overseeing them might just do the entire job ourselves.

I know that AIs are still new and immature, and while there is a lot of potential, they feel like junior staffers now, needing more handholding and micromanaging than I like to do. Perhaps they will change our careers and the way we work, but I don’t know how quickly, or even how deeply. Already I find lots of companies putting restrictions on what their employees can do with AI, which makes me think this might be more a targeted, niche technology more than a general, use-it-everywhere solution.

Steve Jones

Posted in Editorial | Tagged , , | Comments Off on Using AI with Data Tasks

The DevOps Roadshow(s) from Redgate

Posted on August 18, 2023 by way0utwest

I’m back from an office trip this week to Pasadena. I had the chance to go to the office, do some internal training for people, and spend time with our US reps and SEs. It’s valuable for me to get to know people better when I’m remote and I’m glad I get to the offices a time or two a year each.

However, now I’m prepping for more travel. In case you haven’t seen, I’m taking a roadshow this fall. Not just me, but Grant and Ryan as well.

2023-08-09 11_58_44-Photo - Google Photos

We’re going around the country, and here are the dates I know about:

  • Houston – Aug 24 (Steve)
  • Chicago – Aug 30 – (Steve and Ryan)
  • Manchester, UK – Sep 4 (Steve)
  • Bristol, UK – Sep 6 (Steve)
  • New York – Sep 7 (Ryan or Grant)
  • London – Sep 8 (Steve)
  • Boston – Sep 20 (Steve)
  • Atlanta – Oct 11 (TBD, not Steve)
  • Jacksonville – Oct 19 (Grant)
  • San Diego – Oct 25 (Grant)
  • San Francisco – Nov 1 (Steve)
  • Seattle – Nov 13 (Steve)

It’s a busy travel schedule for me, but not a lot of work prepping, which is usually the hard part. Last week and this one are prepping (and likely next week), so it’s a little busy now.

I’m excited to visit these cities. I’m a little bummed I won’t make Jacksonville or San Diego as I have friends there, but those are vacation days for me to see my daughter at university. Maybe I’ll get back to those cities next year.

We don’t have all the links up yet (some are above), but they should be coming soon. Keep an eye on the Events page.

If you’re coming to the PASS Data Community Summit and don’t have a pre-con on Tuesday, consider coming to see me. Hopefully the reg will go up soon.

Posted in Blog | Tagged , , , | 2 Comments