Voice of the DBA

Tag: security

The Combinations of Software

Security issues seem to be appearing more frequently, not less. I’d expect that we would be getting better at writing software, and I think many of us are. The problem is that more and more people are writing software and we still haven’t found a way to better train developers early in their careers. Perhaps the one good thing is that more and more developers are using frameworks, which create more consistent software. If issues are discovered, a patch can ensure a large swath of systems can be patched.

The bad news is that far too many development groups build systems quickly, but don’t patch them in an expedient manner. They may be afraid or just not bother.

A short while ago there was a loss of data from Ticketmaster ticket sales. Apparently a chatbot was used to steal information. As soon as Ticketmaster discovered the issue, they disabled the software. There is some disagreement as to who is at fault here. The chatbot vendor says their JavaScript chatbot should not have been running on a secure payment page.

The specifics here aren’t important, but it is a concern that more and more often we are assembling applications from pieces of software. We often use plugins on websites and other building blocks when we put together a system. In more and more cases, we will be connecting this software to our data stores. That wasn’t the case here, but often there is some data access, and since we may keep both secure and non secure data in the same database, any vulnerabilities in one building block can cause security issues in others. The weakest link in the chain saying applies here.

I wonder how many of you worry about issues with the assembly of whole pieces of software. The pieces should be more secure, or at least, more easily patched. There should be more incentive and resources to patch software used by many people, though many times vendors become hesitant to do any more than absolutely necessary.

I’m not sure if it’s better to build out of pre-written pieces of code, but I do know that security is a shared responsibility and I wish it was more of a priority for all developers. The security of our application can depend on that weakest link.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.9MB) podcast or subscribe to the feed at iTunes and Libsyn.

July 19, 2018
Storage that Expires

Whether you like the idea of the GDPR (and the new California law), I’m sure you agree that these laws will likely change our data handling in business. Even if they are weakened through legal challenges, many companies have already started to comply and change some of their practices.

I’ve written about the GDPR plenty of times this year, and I like the law. I hope the law stands strong and resists most challenges. While I’m sure there will be plenty of spurious or silly requests and complaints, I do think these laws are asking for the good data handling practices that most data professionals have advocated for years. These include not only security but also integrity. How often have many of us advocated for corrections to problematic data and been told no? How many times have we complained about security practices?

One area that I think has been neglected too long in most industries is the area of retention. Most companies I’ve worked for have retained data indefinitely, without any thought or policy. In my mind, we ought to explicitly think about how long we hold data, and remove older data that isn’t needed for our organization’s operation. I feel more strongly about this over time as we find that data beaches become more and more prevalent.

Azure has started a preview of immutable storage, essentially WORM (Write Once, Read Many) drives as an Azure container. I’ve used WORM storage, but it’s often been viewed as a way of keeping information forever. that can change with this new Azure storage, as you can set a lifecycle management period. The blobs will be removed after this time, which removes one management headache from administrators.

I could see quite a few uses for this type of storage. If it’s inexpensive enough, what about storing backups here? We could have policies set to remove files after some limited period. I’m sure there are plenty of other uses for storage the is immutable, but also contains lifecycle management options. What creative use would you have for this type of expiring WORM storage?

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.5MB) podcast or subscribe to the feed at iTunes and Libsyn.

July 18, 2018
Code I Can’t Live Without–T-SQL Tuesday #104

Bert Wagner has a good invitation this month, a T-SQL Tuesday question about code, specifically code you can’t live without. I’ve got my thoughts below.

This is a monthly blog party, and you can participate. Write a post on this topic, and publish it on your blog. Drop a comment on Bert’s post. You can read the rules on his invitation, but you can really post anytime. It’s fun and good for your brand.

The Critical Code

I’ve managed lots of instances in my career. Some mission critical, some just important, some not important to me or the business, but important to someone.

One thing I’ve found is that there are plenty of common things I do on most systems. I’ve had lots of code that I’ve written to manage all aspects of DBA work, from backups to maintenance to monitoring. I’ve had routines that handled all sorts of security or auditing.

The code that I can’t live without, isn’t my code, but I’ve used it on probably every instance I’ve managed at some point. The code is actually from Microsoft, and it’s indispensable.

The code is sp_helprevlogin from this support article.

While I tend to be distrustful of keeping passwords the same for too long, and I usually don’t attempt to recover them in any way, just reset them. There are cases, however, when we’re moving an app, failing over to DR, or recovering some system and we need to keep the password the same.

At least in the short term. When systems are down, I need them back up, and I can argue about a password reset later.

This has been the most useful piece of code for me, and one that I think most of you could use as well.

July 10, 2018
Setting Up Pi-Hole.Net

I saw a tweet a short time ago from Drew Furgiuele about pi-hole.net. He noted this was a near use of a Raspberry Pi, and I wanted to give it a try. I got one awhile ago, and used it for a few things. We started with a media streaming device, but when I got a Fire Stick, this wasn’t needed or used. Then I tried a Bluetooth audio bridge to the stereo. It wasn’t as reliable as I’d like, and it added complexity to turning this on, letting it boot, etc.

As a result, it’s been sitting a bit as I thought of some other project. The pi-hole seemed like something to try, so I decided to give it a go. It was pretty easy, and it blocks some ads. Not all of them, but

First, I grabbed the latest release, Stretch, of Raspbian. I chose the lite version, since this will be headless. I downloaded that and also grabbed Etcher to flash the card. This took a bit, but I let it run in the background as I did other things.

From there, I used Explorer to connect to the drive and create an empty “ssh” file. I read this tutorial, and ended up just creating a new text file in Windows, and removing the extension.

Once this was done, I took the device up and plugged it into the router, both the Ethernet and the USB cables. This booted and connected the device. Back at my machine, I used Putty to connect. I did have to upgrade Putty to get the proper encryption level, but if you get a new copy, you’ll be fine.

A few people pointed out ssh is in Windows now. Great, that works, I like Putty.

Once connected, I ran the curl command to get pi-hole installed. It was fairly simple, and once installed, walks you through some basic config and networking. I use the 192.168.1.0/24 network that most people do, but my DHCP is set to give out addresses from 2 to 199. 200 and above are statics for me, so I configured this to use one.

After that, I got a password for the device and I went to my static IP/admin to see the Pi-hole dashboard. I logged in and I could see traffic. This is from the next day.

It’s an interesting project and I’ll see how this goes. I could add a other software to this device, and I may. We’ll see.

I’m not against advertising, but there are some shady sites and some data gathering. Right now, a lot of blocks are for some telemetry service, which is fine. I can’t get info on it, so I’m guessing it’s not legitimate.

In case you are wondering, there are lots of queries to telemetry.servers.getgo.com that are being blocked.

If you want to try it, this is a cheap experiment, and you can also play with Linux, some hardware, and SSH.

July 6, 2018