Press "Enter" to skip to content

Category: Security

When CHECKDB Fails

Published 2015-11-18 by Kevin Feasel

Andy Galbraith has a tale of woe and a cautionary message:

Paul’s blog post “Issues around DBCC CHECKDB and the use of hidden database snapshots” discusses the need to have certain permissions to be able to create the snapshot CHECKDB uses.  I checked the DATA directory and the SQL Server default path and found that the service account did have Full Control to those locations.

What happened next ultimately resolved my issue, and it reflects something I constantly tell people when they ask me how I research things relatively quickly (most of the time anyway :)) – whenever you read a blog post or article about a subject, MAKE SURE TO READ THE FOLLOW-UP COMMENTS!  Sometimes they are nothing beyond “Great Article!” but quite often there are questions and answers between readers and the author that add important extra information to the topic, or just “Don’t Forget This!” style comments that add more detail.

Indeed.

Comments closed

Always Encrypted

Published 2015-11-13 by Kevin Feasel

Kenneth Nielsen takes a look at Always Encrypted:

The way Microsoft have implemented this always encrypted feature, is to let all the data in the tables be encrypted. The application that needs to look at data will have to use the new Enhanced ADO.net library, which will give your application the methods to de/encrypt data.

This way, the only way to insert data into a table, which contains encrypted columns, is to use parameterized insert statements from your application. It is not even possible to insert data from SQL Server Management Studio, if we try, the statement will fail.

This way we ensure that only the persons using the application will be looking at un-encrypted data, thus reducing the number of people with a direct access to sensitive data.

If you go down this route, it looks like the only method available for modifying data is going through ADO.NET, although that could change later.  My biggest concern here is how much of a performance hit—if any—systems will take.

Comments closed

SQL Server 2016 IFI

Published 2015-11-12 by Kevin Feasel

Nic Cain has an outstanding blog post on enabling Instant File Initialization in SQL Server 2016, specifically wondering what happens when group policy explicitly prohibits setting Perform Volume Maintenance Tasks privileges:

Much to my surprise the virtual SQL account showed up in the PVMT secpol setting. I had no idea how it got there. Reviewing the setting I was able to confirm that the account I used for install was not able to make any adjustments and yet somehow the permissions were set.

I’m happy to hear why I’m wrong, but I’d consider this a reasonable instance of privilege escalation:  I may not want the DBA to be able to perform volume maintenance tasks on just any server, but I do want him to do it on SQL Server instances.

Comments closed

Back Those Things Up

Published 2015-11-11 by Kevin Feasel

Brian Krebs reminds us to back up all the things:

The tools for securely backing up computers, Web sites, data, and even entire hard drives have never been more affordable and ubiquitous. So there is zero excuse for not developing and sticking with a good backup strategy, whether you’re a home user or a Web site administrator.

PC World last year published a decent guide for Windows users who wish to take advantage of the the OS’s built-in backup capabilities. I’ve personally used Acronis and Macrium products, and find both do a good job making it easy to back up your rig. The main thing is to get into a habit of doing regular backups.

There are good guides all over the Internet showing users how to securely back up Linux systems (here’s one). Others tutorials are more OS-specific. For example, here’s a sensible backup approach for Debian servers. I’d like to hear from readers about their backup strategies — what works — particularly from those who maintain Linux-based Web servers like Apache and Nginx.

This article doesn’t directly relate to SQL Server, but it does act as a nice reminder:  go make sure you have good backups.  Of everything.

Comments closed