Voice of the DBA

Author: way0utwest

Removing Weak Security from SQL Server

I was checking some arguments in the RESTORE command for SQL Server and saw that the MEDIAPASSWORD option was deprecated and marked as being removed at some point. That made sense, and I assumed that PASSWORD was the option to be used moving forward. However, that option is also marked as deprecated.

What should we do?

Well, we need to better secure our backup files for sure. Disk encryption and limits on AD/directory permissions ought to be set. Of course, we need to use care when handling or moving these files, especially when they cross the secure boundary in production systems to other dev/test/etc. networks.

The security section of the document explains the reasoning here. The protection provided is weak and isn’t intended to protect data. You can still read data in the backup file. This is mainly to prevent an incorrect restore when using tools, meaning the human picking the wrong file. This isn’t to protect your data.

I suspect most people dealing with SQL Server backups that use either of these options don’t know this. They think the password secures their data. I know because I’ve seen people use this to send a backup file through email or file transfer to another party. However, if you’ve ever opened a backup file in XVI32 or another editor, you will see that your data is in plain text. If you’ve never done that, give it a try today and search for strings that you know are stored in the database.

Some security is better than no security and layers of security that build on each other are useful. However, depending on weak security isn’t good. It leads people to count on something that doesn’t work and ignore more serious issues.

I’m glad that Microsoft is (supposedly) removing these options. I understand that backwards compatibility and preventing existing scripts from failing are important. At the same time, we need to move forward. I’d like these password items to become a no-op, and not cause errors, but I would hope that their use in scripts would also generate a message to the user that these options don’t work and need to remove removed. Perhaps with a direct message and a note in the error log, we’d start to see people embracing other security practices that will provide more protection.

Steve Jones

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

August 25, 2023
A New Word: mahpiohanzia

mahpiohanzia – n. the frustration of being unable to fly, unable to stretch out your arms and vault into the air, having finally shrugged off the burden of your own weight, which you’ve been carrying your entire life without a second thought.

Isn’t everyone other than Michael Jordan and a few others feeling this? I know the kids I coach in volleyball dream of flying, not for long, but always more than they can do.

I’ve felt this as well when I was in my 20s and trying to dunk a basketball. I did it a couple times, but then I struggled as I got even slightly older (and heavier). AS I moved to volleyball, I used to wish this. Now I worry about landing on these 50+ year old knees, so I don’t think about mahpiohanzia.

From the Dictionary of Obscure Sorrows

August 25, 2023
The Redgate DevOps Roadshow–Houston

I’m heading to Houston today for the Redgate Database DevOps in a Day workshops. This is the first of many on the US tour. I’ll be at 5 of them, but there will be another 5 where Ryan, Grant, or our Solutions Engineers will run the show.

If you’ve registered, please say hi, introduce yourself, and ask lots of questions. Hopefully you’ll enjoy the day.

Just a quick trip for me, coming back Friday, which is good. I head to the Chicago DevOps in a Day workshop next week.

August 23, 2023
ADS Copilot Experiments with XML

I sent some code to a customer recently to help them decrypt some stored procedures. I sent a quick and dirty set of code, noting at the bottom that the results were in XML and needed to be extracted.

The customer wrote back that my code produced XML and the procedure code had to be copied and pasted into SSMS to create a decrypted procedure.

Before I spent time on this, I decided that I should play with Copilot here. This is the perfect place and it’s what I’d hope at some point if I sent that code, the other person would use some sort of AI to help them fix things.

Maybe not, but here’s what happened.

This is part of a series of experiments with the ChatGPT and other AI systems. Lots of Copilot lately.

Asking for Help

I could look up the syntax for working with XML, but what about Copilot? Let’s see what happened.

An interesting suggestion. One problem: when I run this, the result isn’t great.

If I cast this as XML, or declare it, things work. At least, they don’t produce errors. But they don’t do what I wanted.

I wasn’t sure what to do, so I opened the completions panel for Copilot and saw other suggestions.

Let’s try these.

Suggestion 1 looks good. If I change my declaration to be XML, this works (or cast things).

Some points for Copilot here, and I’d hope a junior would get to change the declaration or ask how to convert the variable to XML.

Suggestion two doesn’t work.

Suggestion three looks like a copy paste from a forum somewhere. However, the code works. I don’t know how this gets into the suggestions, but I am interested to know what’s happening here.

Four and fix aren’t great. They look like repeats. However, six accounts for my declaration.

Does the code work? It does.

I accepted solution 6 to see what happens. The code was added to the query window.

A Repeat

I’ve given feedback, so let’s try again. I went back to the prompt and got the same suggestion again, however when I opened the panel, I saw different items.

One doesn’t make any sense and doesn’t work. Two and three are nonsense. Four is really interesting, but not useful.

I don’t think Copilot learned anything, and I’m not even sure it is all that well trained. I don’t know if it doesn’t recognize it’s in a database editor or what. The type of language for the ADS file is SQL, so I don’t know what X# and Java are added.

I’m out of patience today. I know how to do this, so I’ll just write the code.

August 23, 2023