Voice of the DBA

Category: Editorial

  • 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.

  • 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.

  • Interesting Data Centers

    I’ve spent a couple decades in technology working with lots of companies. As an employee, a consultant, or a sales engineer, I’ve had the chance to visit quite a few data centers over the years, some of which were quite interesting. A few impressive, and perhaps a few more that were quite scary.

    When I started working in technology, the “server” was often just one box that lived on a desk in the office. In a few of my early small business clients, our server was often used by the secretary for Wordperfect typing while also serving applications and acting as a file server. In a few larger clients, including at a state government agency, multiple servers were piled on shelves in a closet.

    Over time, lots of small businesses, and a few larger ones, learned that hot closets don’t work for multiple servers, nor does a lack of clean, UPS power serve them well.  I’ve worked with quite a few companies to upgrade their facilities to include better power and cooling, often racing to keep up with the proliferation of server systems.

    As time progressed I found many companies that didn’t want to invest in a data center, including us here at SQLServerCentral. In the early 2000s, companies began to trust co-location facilities, those professional data centers built and run as a business. I toured many and saw some well built environments, and some not so well built. One small company SQLServerCentral visited had two corners of an office building downtown, on separate floors, with cool hoses (about 2ft in diameter) run outside, up the side of the building.

    As we look to move to the cloud, which is the next evolution of the co-location facility, I expect that more and more of us may never visit a data center in our lives. Some small startup companies don’t even own servers, outsourcing their email, VCS, build systems, and more to some vendor.

    That’s not for everyone, but it is becoming more commonplace. Vendors are even trying new ideas to lower their costs, while still maintaining the level of service we expect. There are data centers being built in mines, which provide cheaper cooling than traditional buildings. There are container based systems, and while electricity and water don’t usually mix, Microsoft is looking to try putting data centers in the ocean. Of course, not all ideas are good ones, as the ill-fated Sealand showed.

    The future of data centers and where and how we run server hardware will certainly be interesting. I wonder how many future DBAs may never physically touch the actual hardware that contains all their bits and bytes.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Power BI Week

    This past week was T-SQL Tuesday #75, with the topic of Power BI. There were some interesting posts, and we’ve included a few in this week’s newsletter. This was also the week where the winners of the Power BI report contest were announced, and it was a good entry. This was the one I actually thought was the best one. If you haven’t seen it, take a few minutes and go look at the entry and play with the data. It’s interactive, so be sure to see how well the Denver Nuggets have competed against Mr. Curry.

    I first looked at Power BI last summer, watching a presentation and being intrigued at the ability of the tool to easily get data from web pages. I then talked with a friend that works with Power BI regularly, and they showed me how to loop and gather information from many pages, easily assembling the data into a dashboard that was interactive. I was very impressed, and I’m looking forward to doing a bit more work on the platform myself.

    Quite a few people have been concerned that the development of tools like Power BI mean that there is less of a need for IT staff. I find myself thinking quite the opposite. There will always be data quality issues with something like Power BI, and there is a certain level of programming to build more complex reports. Not to mention the refresh and update process is slow, which can make the tool feel cumbersome.

    However users will find some reports are more useful, or more important, and need some help from technology professionals. Whether that’s developing more DAX formulas and measures to enrich displays or just providing stable, clean data sources that consolidate data from various places, there will be plenty of work for IT staff that support companies using Power BI.

    To me, Power BI is the type of tool that empowers users and lets them experiment a bit on their own. Once they determine what they really want, then they can engage me to build a more robust, reliable, and useful tool that helps the business.

    Steve Jones