Voice of the DBA

Tag: security

SQL Server 2016: The Data Protection Version

I’ve spent a few years working with SQL Server encryption and security, trying to educate people on the various capabilities involved. There are some nice features available in the platform that can help you run a more secure SQL Server, but there has been a lot of room for improvement across the last few versions. The openness of the platform is somewhat tempered by the need for each of us to write a fair amount of code to properly secure our databases. That is the hard part as many developers don’t write secure code.

That is changing a bit. I am really excited about the SQL Server 2016 release in that there are a number of data protection mechanisms that are going to greatly expand how we can protect our data moving forward. From Row Level Security to Always Encrypted to Dynamic Data Masking, the number of new features that will help make security a bit easier is growing dramatically.

I certainly think Microsoft is definitely encouraging and supporting better, more secure coding practices. If you look in Books Online at MSDN, there is now a Security Center that covers a number of topics and organizes information in a much better way. Security has seemed like an afterthought in past documentation. From Encryption to SQL Injection to Auditing to even Metadata Visibility, it seems like the next version of SQL Server on premise, and in Azure, really is taking security more seriously.

I look forward to watching the platform evolve and security increasing over time. It does seem as though SQL Server has the fewest security holes in the platform, but I hope that many of us can take advantage of the changes in SQL Server over time and build applications that will be viewed as being secure as well.

Steve Jones

The Voice of the DBA Podcast

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

February 24, 2016
Data for Ransom

Imagine you come into work and all of your database columns are encrypted. You have no idea what key was used, and your application can’t access the data. You receive a message that for a $100,000 payment, the decryption key will be sent to you. What do you do?

Well, depending on your business, and how quickly you can restore backups, maybe your organization pays. I read a story recently about a hospital that found a number of their files encrypted by a virus, with a demand to pay a ransom for the decryption key. They did, received the key, and opened their files. However that set a dangerous precedent.

Apparently attacks by ransomware are on the rise. Those of us with database software are not likely to be too affected as we (should) have regular backups we can restore. However the interruption to businesses can be costly, and this can result in more organizations paying the ransom, which then encourages more attacks.

The new Always Encrypted capability in SQL Server 2016 is great, and I’m excited by it. However, we want to be sure a malicious user doesn’t enable this feature. I could certainly see that as an attack vector for web based systems. An attacker gets sysadmin privileges, enables Always Encrypted and places the certificate on the client web server. Some weeks later, they remove the client certificate, and suddenly no one can decrypt the data. Potentially, depending on backup procedures, there might not even be a capture of the certificate from the web server in any backup files.

The world is becoming more dangerous, and more troublesome for our data. As with most things, vigilance and monitoring of security segments of our applications is important. While ransomware is unlikely to strike databases, one never knows, and you should ensure that you have good backups in the event that you are attacked.

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.

February 23, 2016
Forcing Strong Passwords

It seems that every week we have some sort of data breach that results in emails and passwords for users being released. That alone is a good reason why you should not have passwords shared across sites or systems. I can’t stress this enough, and if you do this, please go get a password manager today and stop. Really, I mean it.

However is it just the users that create poor passwords? In many cases, sure. However as the people that develop software, we can help. In fact, we should. Security should be on the mind of everyone that writes software.

I ran across a piece on The Hacker News that says far too many sites don’t require users to choose strong passwords. I think this is changing, but far, far too many sites don’t check a user’s password for strength, or reuse. Some even force users to choose less than ideal passwords. I’ve run into plenty of sites that don’t accept the characters in my strong passwords for some reason. Often the length or addition of any special characters if prohibited.

I’ve said it before, but I’ll note it here. Most of us aren’t competent at writing authentication routines and should just use one that’s already written and been vetted. More importantly, we should be sure the code, application and database, is modular enough and can be replaced if we find that our application needs stronger security. Because it will. We are constantly racing Moore’s law to implement better security.

Steve Jones

The Voice of the DBA Podcast

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

February 17, 2016
Limiting Database Permissions for DLM Dashboard
I was talking with some of our support people recently about permissions on DLM Dashboard. A client was having issues, and we weren’t sure what was wrong. As a result, I decided to dig in a bit and see how limited I could be with permissions for the login/user that is used to track changes.

My first step was to create a new login in SQL Server, giving the public server role and then granting very limited permissions in master and the Redgate database. Those permissions were:
- master – VIEW ANY DEFINITION
- master – execute on dbo.RG_SQLLighthouse_ReadEvents
- RedGate – SELECT ON SQLLighthouse.DDL_Events
That’s a nice, limited set of permissions. You do need sysadmin for setup, but after that, you can set these permissions for the user that you’ve configured in DLM Dashboard. The permissins are documented on the DLM Dashboard documentation site.

In my case, I have a login/user, DLMDashUser, configured in the tool.

I then went to add a new database on my local instance. However since this login isn’t mapped to a user, nor has any high server privileges, I got an error.

To fix this, I connected to my instance and modified the user. Scripting is a better way to do this, and in my case, I used this script:

USE Puzzles
GO
CREATE USER DLMDashUser FROM LOGIN DLMDashUser;
GO
GRANT SELECT ON sys.sql_expression_dependencies TO DLMDashUser
GO

This grants the necessary permissions to a new user in this database. You can save this script, which is especially handy for production systems where we don’t want monitoring tools to have elevated permissions.

Now when I go to add the database, I click add and it works.

And I can then see the database in my monitoring dashboard.

The principle of least privilege should apply everywhere, certainly in production, but also in development. If you limit permissions in development, you might cause a few headaches, but you’ll understand the issues and solve them early on. More importantly, if you have security flaws, they aren’t in production systems where data is exposed.

SQL Server security isn’t that hard, but it can be cumbersome. Set it up properly in development, keep your scripts (even from the GUI), and then use those scripts for your production systems.

NOTE: Typically I’d create a role for this system, which is perhaps what I should do. Having a role like this would make switching users in DLM Dashboard at some point much easier.

CREATE ROLE Monitoring
GO

GRANT SELECT ON sys.sql_expression_dependencies TO Monitoring

GO
ALTER ROLE Monitoring ADD MEMBER DLMDashUser

In fact, I just changed to use this role, and added the role to the other databases so that my dev system is propery set up.
February 11, 2016