Hostage Data

Posted on May 19, 2021 by way0utwest

Ransomware continues to surprise me in different ways. Recently there was an incident where data was not only encrypted, but also copied back to the criminals. In this case, Apple was the target through a supplier with the ransom note saying that without a payment, the data would be auctioned off.

That’s crazy. Not only might you have business issues where you can’t access data, but now you have the stress of the data possibly being released or sold. While not many of us work in organizations where our data would be worth $50mm, it might be worth a significant amount, especially if the data were customer data. This alone might be a good reason to ensure that you have local data already encrypted without the keys present. At least then the criminals couldn’t read your data.

This doesn’t help with SQL Server and TDE. In that case, the certificate would be inside the local master database, and if someone could attach it and get access to the master database, they could read your databases. If you have an SMK and a DMK, perhaps this might offer some protection, but I don’t think so. This does mean that Always Encrypted might help, unless you have lots of servers or other machines on your network with the certificates, in which case someone might be able to piece together the keys and read data.

Attacks are becoming more numerous and creative. Having backups might have protected you against some ransomware, but not if copies of your files are sent to criminals. Perhaps the access from servers to the outside world needs to be more reigned in. Not much fun for administrators, but this might be the future of protecting systems.

The arms race between sysadmins that protect infrastructure and criminals seems to have taken a leap forward here, and I’m not looking forward to the next step.

Steve Jones

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

Posted in Editorial | Tagged | Comments Off on Hostage Data

Daily Coping 19 May 2021

Posted on May 19, 2021 by way0utwest

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag. 

Today’s tip is to try a vegetarian dish for a meal today.

My oldest is a pseudo vegetarian. It’s a social issue with him and he sticks to it very well. Once in awhile I’m making some barbacoa or carnitas and tempt him, but not too often. Usually I try to accommodate him if I’m cooking and ensure there are good meatless options. He also picks vegetarian restaurants when he celebrate his birthday, so I’ve gotten good at cooking some meals without meat.

Today I went looking for some new things and came upon this: Vegan Roasted Sweet Potato Salad. It looks good, has things the family likes, and is gluten free, which works well for my daughter. I roasted the potatoes late one afternoon while finishing work. Luckily I can hear the timer beep to get this one. I chopped a few vegetables and then assembled the salads on plates, expecting everyone to come in.

I did forego the custom salad dressing as I didn’t feel like messing with the blender and cleaning it on this day. This worked out well, overall, as everyone was busy and late for dinner. I’d assembled things at 6, but it wasn’t until 730 that my wife came in from the barn.

It was a nice, light dinner for all of us, and quite taste. The dressing (jalapeno lime vinaigrette) was spicy, though not sure I’d have been better making it from scratch.

Posted in Blog | Tagged , , | Comments Off on Daily Coping 19 May 2021

Daily Coping 18 May 2021

Posted on May 18, 2021 by way0utwest

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag. 

Today’s tip is to pause your work every hour and stand up to stretch.

I don’t get into the zone too often, but when I do I’m glad. However, I do bundle together tasks and try to get a number of related items done at once. Switching tasks seems like a good time to pause and stretch, but I don’t do that enough.

I’m making it a point to do better today. Hopefully from now on, but for today. When I switch tasks today, I’m standing up to do a few yoga stretches and deep breathing. As a backup, I’m setting a repeating 60 minute timer on my desktop to remind me.

That won’t work past today, as it will get annoying, but for a day, it might help me to think about this more often.

Posted in Blog | Tagged , , | 1 Comment

2020 Advent of Code – Day 5

Posted on May 17, 2021 by way0utwest

This series looks at the Advent of Code challenges.

As one of my goals, I’m working through challenges. This post looks at day 5. I’m going to do this one in Python here, though I did solve it in other languages in my repo.

Part 1

This is an interesting problem, and one that’s simpler than it appeared at first. I started down the path of some hash bucket thing, moving to calculate rows before I got to the end and realized this is really a binary problem.

As a result, after I loaded the data, I started here:

SELECT 
   (SUBSTRING(d.SeatCode, 1, 1) * 64) +
   (SUBSTRING(d.SeatCode, 2, 1) * 32 ) +
   (SUBSTRING(d.SeatCode, 3, 1) * 16 ) +
   (SUBSTRING(d.SeatCode, 4, 1) * 8 ) +
   (SUBSTRING(d.SeatCode, 5, 1) * 4    ) +
   (SUBSTRING(d.SeatCode, 6, 1) * 2    ) +
   (SUBSTRING(d.SeatCode, 7, 1) * 1    ) AS row,
   (SUBSTRING(d.SeatCode, 8, 1) * 4    )  +
   (SUBSTRING(d.SeatCode, 9, 1) * 2    )  +
   (SUBSTRING(d.SeatCode, 10, 1) * 1    )  AS seat
  FROM dbo.Day5 AS d

Here you can see I broke this into two binary sections. The first 7 characters get you a row code from 0 to 127. The last 3 values get you a 0 to 7 value. I should have been clued in when I saw the 0s here. In any case, this gets me the two binary values.

The seat code is the row multiplied by 8 and then adding the seat. I took the above query, wrapped it with a CTE and then ordered by seat codes. This gave me the highest value, which solved the problem.

WITH cteAirplane( ROW, seat)

AS

(SELECT 
   (SUBSTRING(d.SeatCode, 1, 1) * 64) +
   (SUBSTRING(d.SeatCode, 2, 1) * 32 ) +
   (SUBSTRING(d.SeatCode, 3, 1) * 16 ) +
   (SUBSTRING(d.SeatCode, 4, 1) * 8 ) +
   (SUBSTRING(d.SeatCode, 5, 1) * 4    ) +
   (SUBSTRING(d.SeatCode, 6, 1) * 2    ) +
   (SUBSTRING(d.SeatCode, 7, 1) * 1    ) AS row,
   (SUBSTRING(d.SeatCode, 8, 1) * 4    )  +
   (SUBSTRING(d.SeatCode, 9, 1) * 2    )  +
   (SUBSTRING(d.SeatCode, 10, 1) * 1    )  AS seat
  FROM dbo.Day5 AS d
  --ORDER BY row desc
  )
  SELECT (row * 8)+seat AS seatID
  FROM cteAirplane
  ORDER BY seatID DESC

Part 2

The second part is a different problem. Now I need the seat codes, but I’m looking for a gap here. Meaning a missing seat code.

I decided to use LAG here. I altered my first CTE to calculate the seat code directly rather than returning the row and seat. Then I added this CTE:

cteValues (SeatID, diff)

AS

(

SELECT seatid, SeatID - LAG(SeatID,1) OVER (ORDER BY SeatID) AS diff

FROM cteAirplane

)

This CTE found the difference between each subsequent Seat codes using the OVER() clause. My final query was looking for a diff > 1, which returned 1 row. That was the answer.

Posted in Blog | Tagged , , | Comments Off on 2020 Advent of Code – Day 5