DevOps and Security

DevOps is a buzzword these days, and like many of the hyped concepts written about, it has a lot of meanings. There is this idea releasing software more often, using automation, having various groups talk to each other, and more, all of  which we might see as common sense. However other DevOps ideas, such as releasing more often (with potentially less review), being willing to break applications and fix them quickly, having developers able to release code to live, production environments, these don’t seem to be ideas that would enhance security for most organizations.

However, that’s not necessarily the case. Security and DevOps practices aren’t necessarily mutually exclusive. I ran across a piece from the security perspective, looking at some of the ideas in DevOps that can actually enhance security.

Writing more code, especially around the configuration and infrastructure, allows versioning, auditing, and more that can ensure we have fewer mis-configured systems. Adding some Desired State Configuration (DSR), with some automated testing of this code, could ensure that the changes made don’t open up security holes. Or, at least, allow us to determine who made the change and when the issue appeared. These are important for understand security risk.

There are also the ideas of measurement, metrics, and feedback, which are important for ensuring security. After all, anomalous behavior should be investigated, as this could be a sign of intrusion. For databases, it’s especially important with the large number of clients that connect to our systems. Adding DevOps style monitoring can allow us to determine if a workload is normal, or perhaps a sign of intentional, accidental, or malicious data query activity.

I enjoyed the piece, and I’d recommend you read it. Plus, whenever I see “snowflake” in an article, I think of Grant and want to read further to see how someone else has used the same analogy he does.

Steve Jones

The Voice of the DBA Podcast

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

The Penalty for a Data Breach

Many of us that work with data are somewhat insulated from the effects of a data breach. Each of us is responsible for writing software, managing the database platform, perhaps even in charge of configuring security. However, in the event of an attack or loss of data, many of us would likely be responsible for the technical aspects of analyzing the weakness or cause of the incident, determining what data might be lost, or perhaps helping to change systems to prevent future incidents.

Would many of us deal with the penalties or financial liabilities of data loss? Perhaps indirectly, but not much. However our employers (or more likely, their insurance companies) will deal with penalties. Right now there isn’t a lot of liability for companies that lose data. Minor penalties and credit monitoring, but many people are getting upset, which might result in changes.

What does a company owe you if your information is lost? It’s debatable, and this is likely to become a bigger issue over time as more and more people are affected by this issue. I expect that penalties will increase over time, this will also mean that the requirements and work each of us as data professionals need to do will grow.

While encryption and other measures can help, they don’t completely protect data. Good security comes about from a number of layers, each of which is designed to thwart, or limit the success of, certain attacks. Those of us that work with data, whether in development or administrative roles, need to educate ourselves and continue to improve our ability to implement secure applications.

Steve Jones

The Voice of the DBA Podcast

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

Paying for Data

Would you recommend your organization pay a ransom to get the key to decrypt data that’s become encrypted on your database systems? What if the data were encrypted in backups going back two weeks?

It’s an interesting question, and one I might expect we will have to deal with at some point. This week I saw an announcement that another organization, this time a university, paid a ransom to recover data. Before you react with “of course, not,” think about this quote from the University Vice-President:

“We are a research institution,” she was quoted as saying. “We are conducting world class research daily and we don’t know what we don’t know in terms of who’s been impacted and the last thing we want to do is lose someone’s life’s work.”

That’s a powerful statement, and certainly one that I could see salespeople, accountants, and more making as an appeal to upper management. What’s interesting in the piece is that the university acknowledges that using the decryption keys is a complex endeavor, requiring IT to get systems operational again. I hope that at the least they also made new backups of the encrypted data before using the keys. Who knows what sort of results come about from criminals that have encrypted, and the offered to provide reversal instructions and keys.

I expect the ransomware efforts to become more prevalent across the next few years, especially as people have success. Even if fewer people pay ransoms, I bet plenty of people will look to disrupt organizations with these actions. Perhaps even a competitor will look to introduce ransomware into your company? Maybe by offering a job to an individual that leaves such a surprise behind as they get ready to move to a new employer.

There are all sorts of issues here, and potential attack vectors. The fact that ransomware (or other malicious software) could be introduced to remain idle, or even transparently encrypt and decrypt systems until a date passes and the keys delete themselves, is horrifying. At the very least, I think this brings to mind the idea that we need multiple versions of backups, so mirroring a file system to another location, or some cloud service like Azure, Dropbox, etc., isn’t enough. Perhaps we should also have checks in place that look for changes. After all, can you imagine a rouge process implementing TDE and letting it run for a month before deleting the certificates from your system and truncating all the tables a week later?

Steve Jones

Half Baked Features

I gave a talk recently on some of the data protection features being added in SQL Server 2016, along with a few that have been available for some time. I think the talk went well, but I point out lots of potential issues with the features themselves. I had a question from someone that noted Always Encrypted, Dynamic Data Masking and more aren’t really full developed, are they?

The question threw me, not because I agree, but because I think that there is a complex answer. Encryption and data security features are easy to use. The features really are easy to implement, especially encryption. Most encryption is just another function call for the developer, which is something almost all of us can write. The mechanics of using these features are fairly easy.

However the architecture, the decisions on how to manage keys and where to deploy features, those are hard. We deal with those relatively infrequently, but when we must make those decisions, we should carefully consider the ways in which our systems might be attacked or mis-used.

Some of the restrictions that I see in various features are unavoidable. At least, I can’t see a way to avoid them. In some sense, things like a binary collation are almost required because of the nature of how encryption must operate on data. Perhaps there are ways to mitigate issues, but I’m not sure. No security mechanism is perfect and all encryption can be broken. However I think the way these features work is good enough in many situations.

There are some things, such as allowing Dynamic Data Masking on Always Encrypted columns, which can be implemented. However, the changes are more complex, and involve not just SQL Server changes, but probably also ADO.NET changes. Making the decision on how to actually implement these changes shouldn’t be taken lightly, and I’m happy to have a working feature that might be enhanced in later versions over not getting any options at all in this version.

Steve Jones

The Voice of the DBA Podcast

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

The Proliferation of Roles

The best practice guidance for SQL Server security is to use roles for permissions, instead of granting rights to users. I’ve always followed this guidance in my career. I’ve learned that if one person needs access, sooner or later someone else will. Even if it’s an automated process, I’ll use a role so that I can build a test login to ensure I’ve configured things correctly.

In small companies, or in relatively static applications, this makes perfect sense and few people argue with the rule. That is, until they get some requirement that only one account will ever be used. Then they want to just grant rights to a user. After all, why add the role for one person. See my thought above. Sooner or later, someone else will want access.

However I also have had people complain that if each new required position or process needs their own role, sooner or later we’ll have this proliferation of dozens of roles.


I worked in a large Fortune 100 company and we had thousands of groups in our AD forest, and easily dozens of roles in many databases. While that might seem complex and confusing, it wasn’t bad. We named roles to match AD groups or job functions, and adding in new users was simple as we usually mapped them to the same roles as a previous user. If a new system or person needs access, usually their access is the same as some other account.

However, I know they can be complex, so I’m wondering if you have any tips, tricks, hints, or even gotchas for using roles. I’d also be curious if you think there are cases where roles don’t make sense.

Steve Jones

The Voice of the DBA Podcast

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

Am I a sysadmin?–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I was doing some security testing and wondered if I was a sysadmin. There are a few ways to check this, but I thought there should be a function to tell me.

There’s this code, of course:

ServerRole =,
PrincipalName =
FROM sys.server_role_members rm
Inner JOIN sys.server_principals rp
ON rm.role_principal_id = rp.principal_id
Inner JOIN sys.server_principals SP
ON rm.member_principal_id = SP.principal_id
where = SUSER_SNAME()
and = ‘sysadmin’

That lets me know if my login is a sysadmin. However, there is a function that you can use. IS_SRVROLEMEMBER() is a function that you can use, passing in a server role as a parameter. The code I’d use to check on sysadmin membership is this:


If I run this, I get a 1 if I’m a member, or a 0 if I’m not.

2016-04-12 11_38_34-Settings

Using this function in your code allows you to make decisions based on role membership for the users involved, and perhaps alert them of needs for certain rights.


This was a quick one, really about 10 minutes to organize and write. Most of the time was writing the code to join system tables. If you tackle this subject, talk about how you  might use this, or where this type of check could come in handy in your code (maybe before taking some action).

The Pen Test

We need something like the Air Force project seeking network vulnerabilities for our database systems. While there are tools that can help from the outside, but we need SQL Server specific tools. Why don’t we have a Best Practices Analyzer for SQL Server 2014 (or 2016)? It seems as though this is one of those tools that we should have available for checking against instances and determining if there are obvious vulnerabilities. Being able to add custom extensions for particular needs would also be nice.

I guess if this is important to your organization, you’ll pay for a tool (or a pen test), but the problem is far, far too many organizations just don’t take security seriously. It seems that despite the huge costs incurred for not properly securing computer systems, the penalties aren’t nearly enough to get every company to patch their systems and write secure code. Most just hope they’re never attacked, or if they are, no one finds out.

I’d really like to see some tools, or maybe just a best practice process that would allow system administrators to test their own systems. I found this post from PenTestLab, and I think it’s great. The article is a good start for what to look for and what issues might exist. However even better than this might be a series of automated items we can run that showcase to management, developers, and DBAs when they’ve made a mistake that potentially opens a system to attack. Maybe best of all would be getting software vendors to actually run these types of tools against their applications and provide some proof they’ve built secure code.

I don’t hold out hope that security will improve anytime soon, but I do hope that more system administrators learn about what issues potentially exist and check their systems for potential vulnerabilities before someone else discovers the issues exist. Lastly, I hope they all share what they learn.

Steve Jones

The Voice of the DBA Podcast

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

Changing the sa Password with SQLCMD

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I wanted to make a quick note on changing the sa password, as this is a sensitive account, and the password should be changed if you ever suspect it is compromised. I’d also recommend you change this if anyone that knows the password leaves your group.

I wrote about using SSMS, but that’s not always convenient. If you need to change this remotely, perhaps in a hurry, SQLCMD is a quick way to do this.

SQLCMD is a command line tool, so open a command prompt.

2016-04-06 12_47_33-Photos

Run SQLCMD and connect to your instance as a sysadmin. If you have any doubt, you can enter the query from my previous post to check your connection.

Once you’ve connected, you can issue this code:

ALTER LOGIN [sa] with PASSWORD = N‘Sup#rAmaz!ngP@$$w0rd’

This is the code that will change the password for the login specified, even if I’ve logged in with a different account.

Once I’ve done this, test the sa login from a new session and verify it works.


Make sure you know how to do this. It’s a basic skill, so learn it, blog about it, and use it where appropriate. Maybe write about why you’d do this in your own post.




Crack that Encrypted Data

Ransomware appears to be gaining some traction as a new trend. For awhile in my career, it was virus programs designed to send to all your contacts. Then it was infections to use your computer as part of a bot net. Now it’s encrypting your files and demanding a payment to get the password.

I’m starting to think that a)I need to ensure I have solid, better backups on all devices, and b) I should pay attention and be aware of decryption programs. I’d love to say that I could build a decryption program, like someone did, but as much as I’m interesting in encryption and study it, that’s a little out of my skillset wheelhouse.

I’m actually starting to think that this might be a way that people in communities, like the SQL Server community, can help each other. We can be aware of potential ransomware threats, like the one that hit this hospital, and potentially share ways to recover from the incident, or even decrypt the drives. In fact, I suspect it might be worth keeping a system handy to practice decryption techniques, if you can determine the attack vector.

I’m sure many organizations wouldn’t want to share details of attacks and infections, but this is exactly the type of information that we, as data professionals, should be sharing. It’s incredibly difficult to keep up with all the threats and attacks, not to mention the techniques to recover systems. I’d urge all of you to ask your employers if you can at least help others, even if you can’t disclose how or where you gained the knowledge. If nothing else, the information needs to be shared more publicly to allow us to better protect our systems and be effective custodians of data.

Steve Jones

The Voice of the DBA Podcast

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

Getting Started with Encryption in SQL Server 2016

The release of SQL Server 2016 gives developers a number of ways in which they can securely encrypt and protect their data. In this introductory session, you will learn about the encryption options in SQL Server 2016, watching Always Encrypted, TDE, Row Level Security, Dynamic Data Masking, and server side column encryption can secure and protect your data from unauthorized users.

Length: 60-75 minutes

Code: Github

Powerpoint deck: Getting Started with Encryption in SQL 2016.pptx