Voice of the DBA

Author: way0utwest

Color Coding a Database on All Servers with SQL Prompt

SQL Prompt has some nice color coding features, but sometimes I want a database to keep the same color, regardless of instance. I don’t know that I’d recommend this, but since I work in demo environments across versions, this is a handy trick for me.

In the SQL Prompt menu, there is an options item.

Clicking this brings up a dialog with a number of sections along the left and details on the right. Near the bottom is the color menu.

When I decide to color code a database, I click “add” and I get this view. The cursor defaults to the Server/Group edit box, and I’ve typically entered a server name here.

However, I can skip this and just enter a database.

In this configuration, every time I open a query window to a database called SimpleTalk_1_Dev, no matter which instance, it will be colored purple.

I can see this when connecting to my default local instance.

I also test things on a v14 (SQL 2017) instance, and if I connect there, I see the same thing.

In some sense, this is an edge case. I demo similar things across instances and versions, and it’s nice to keep colors simple rather than entering dozens of instance names. However, it’s not something many people do, as they often use the same database name on different instances. That’s a more common scenario.

That’s supported as well. Notice here I’ve entered just a server with no database.

Now every database I connect to on this instance will be colored green. Note that the purple still overrides because it is specific to a database. The third tab below is connected to the SimpleTalk_1_Dev database.

SQL Prompt has some amazing features, but color coding is one that helps me keep track of which databases and instances I’m connected to in a query window. If you‘ve never configured this, give it a try.

If you’ve never tried SQL Prompt, it’s incredible. Download an eval today and see what you think.

July 22, 2020
Hacking the Admin

Recently Twitter had a security breach, with a silly scam. At least, I’d think it was silly. I saw a tweet from Elon Musk noting that he’d return $2k in bitcoin for every $1k anyone sent to him. He was feeling generous.

While that might seem silly, a number of other high profile accounts were breached and seemed to lend some level of veracity to the offer. I saw a few news reports that the hackers made off with over US$100,000, so apparently at least a few people were fooled. Twitter locked down verified accounts for a bit while it investigated, removed some tweets, and tried to close the security hole.

What is disturbing here is that apparently the hack took place through Twitter syadmins, with privileged accounts. As of this writing, it isn’t clear if this was social engineering or a sysadmin worked with hackers, but there were internal tools allowing Twitter employees to post tweets on behalf of users.

I have no idea how this happened, but I’m assuming this is some sort of data change made to their system. If this were an RDBMS with a “tweets” table, this would be adding a row to the table with links to the verified accounts’ linkage. Not a hard change in the SQL Server world, and certainly the type of change that most admins could make.

The question might be should they be allowed? Many of us have made ad hoc data changes to systems to correct an issue, and some of us do this regularly.

This reminds me of some customers whose DBAs aren’t allowed to directly connect from SSMS (or other clients) to production and make changes. All changes, including ad hoc data changes, must be submitted to some sort of pipeline, where the change is logged, and perhaps approved by someone else. A different sort of two factor authentication.

Should this be a more common pattern of access to production systems? Limiting access by everyone, even admins? I know we need to trust administrators, but what happens when administrators get fooled by social engineering? A thorny attack vector that we ought to be considering in our architectures.

Steve Jones
Listen to the podcast at Libsyn, Stitcher or iTunes.

July 22, 2020
Daily Coping 22 Jul 2020

I’ve 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.

Today’s tip is to write your worries down and save them for a specific “worry time”.

What am I worried about? Not much, and my worries are really small. I worry my daughter won’t get to experience college this fall, including sports, and I won’t get to watch her play. I’m worried my middle kid won’t get a job when he graduates next year. I’m worried my oldest will get sick, or stuck, in Spain this year where he’s supposed to teach. Or that he won’t go.

Mostly I worry that I won’t be able to get back to my way of life, speaking, travel, coaching, and snowboarding. This is a good way to write a few things down, and I’ll worry about these on the weekend, when I can have a drink with my wife and complain about things.

July 22, 2020
Adapting Privacy

Lawyers will be lawyers.

That’s a quote from a piece at Ars Technica on how and why search engine startup Neeva changed their privacy policy. Their business model relied on users paying Neeva to keep their data private. The goal being a secure platform that considered users privacy. An admirable goal, and one that I might have been welling to fund. There is a lot of data out there, and while I don’t mind companies improving their service to me with data about me, I don’t like them sharing it.

In this case, Neeva didn’t read their terms of service very well. I don’t think this was malicious, but it was a mistake. I’m sure they used a legal group that someone recommended to them, and the lawyers were trying to craft a document that was structured to protect the company and allow for flexibility. Sharing data with affiliates and advertisers would seem to go against their model, but it’s something lawyers might think is fine. If you read the article, you can see how Neeva has adjusted their terms.

As the world grows more digital, and we all deal with an ever increasing number of organizations, data privacy will become more important to some, perhaps many people. The ideas of reducing data retention, limiting transfer of data, and giving users more control is an idea that may take hold, especially if a few companies start to succeed with this business model. We’ve seen pressure on companies throughout this year cause them to shift their previous stances, sometimes dramatically.

For those of us that work with data, this might mean easier administration in some ways. Certainly we will have to alter our systems for archival or more flexibly data retention policies. We might even need to adapt our data models to include more fields that allow us to easily decide what data to keep, remove, or obfuscate things.

In fact, I wonder if that’s the future of privacy. We de-link old users by randomly setting values for PII data and keeping other information like sales values or URLs clicked as useful information for aggregation without any risk of disclosure.

Steve Jones

Listen to the podcast at Libsyn, Stitcher or iTunes.

July 21, 2020