Voice of the DBA

Tag: security

  • It’s Time to Patch and Upgrade

    I don’t want to be chicken little here, but the Meltdown/Spectre bugs have me concerned. I don’t know the scope of the vulnerabilities, as far as exploits go, but I do know the lax ways in which humans interact with machines, including running code, opening untrusted documents, and just making silly mistakes. No matter how careful you think you are, can you be sure everyone else in your organization is just as careful? Are you sure they won’t do something silly from a database server? Or do something from a server (or workstation) that has access to a database server? Or use a browser (yes, there’s an exploit)

    PATCH your system, soon.

    Vulernabilities in hardware are no joke, and even if you think you’re fairly safe, it’s silly to let this one go by and assume you won’t get hit. The advent of widely deployed scripting tools, botnets, and more mean that you never know what crazy mechanism might end up getting to your database server. Is it really worth allowing this when you can patch a system? This is a no brainer, a simple decision. Just schedule the patches. With all the news and media, I’m sure you can get some downtime approved in the next few weeks. After all, your management wouldn’t want to explain to their customers any data loss from this any more than you’d want to explain it to your boss.

    We’ve got a page at SQLServerCentral that summarizes the links I’ve found for information, patches, etc. I’m sure things will change rapidly, and I’ll update the article as I get more information. The important things to note are that not all OSes have patches yet, and there are situations where you might not need to change anything. That’s good, as there are some preliminary reports of patches causing issues with performance (degrading it) for PostgreSQL And MongoDB systems. I did see this tweet about no effects on SQL Server, which is good, but YMMV.

    Most of us know patching matters, and we need to do it periodically (even if it’s a pain), however, many of you are like me in that you rarely upgrade systems. Once they work, and because I have plenty of other tasks, I don’t look to necessarily upgrade a database platform for years. One downside to that is that a major vulnerability like the Meltdown/Spectre attacks is that patches likely won’t come out for old system and versions of SQL Server. That is the case here.

    That means that if you’re on SQL 2005-, or even on older Windows OSes, you might really consider planning an upgrade. Even if you aren’t overly worried about this exploit, you won’t want a vulnerability to live for a long time in your environment. You never know when a firewall will change, server will move, or some malware will slip through (did I mention the browser exploit?). Plan on an upgrade. I’ve started asking about accelerating our upgrade plans, and you might think about that as well. I know management doesn’t want to spend money unneceesarily, but this feels necessary, and a good time to refresh your system to a supported version.

    In general I like to delay my patches slightly from the world and not be on the bleeding edge. That’s fine, but don’t wait too long with this one. I would hope that most people get systems patched in the next month. If not, don’t expect any sympathy if you lose data.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Sharing Data

    One of the things that’s written into various computer laws is that unauthorized access of a computer system with another’s credentials is a crime. That’s been drilled into me at various organizations where no one was supposed to share any credentials. That is something many sysadmins drill into users, while sharing their own system level credentials for accounts like root, sa, system, and more. I’ve seen no shortage of these accounts in use across multiple services or multiple people from those that would admonish a manager for sharing their password with an assistant.
    This week Troy Hunt wrote about politicians sharing passwords, and the problems with doing so. It’s an interesting read, and certainly points out that the expediency of having users share a workload has plenty of downsides in accountability and auditing of actions. I think there’s little excuse for sharing security credentials in UK gvoernment as there are other solutions to handle this issue. I am more sympathetic in real time environments, like hospitals, where the login process might literally cause a death in the event of a delay.
    Leaving aside the authentication aspect, we often share data among individuals inside of an organization. In fact, outside of sysadmins, there might not be many people that really understand who should have access, let alone who has access, to some data. In fact, over time it seems that most organizations tend to lean towards allowing an ever-growing number of people to access data in file shares. While we might prevent database access and grant/revoke this at times, the output from our systems often ends up in Excel sheets or other files and people that might not have direct access still see the data.
    The real physical world is just as bad, since many people may leave data lying around on desks or tacked to a wall. Just like credentials on a post-it, we have lots of data available for others to read, though physical access is required. However, have you thought about how many people have physical access? It’s not just your co-workers, but also janitorial staff, tradespeople, and others likely wander regularly through your office spaces.
    Security is a tough battle, one that is interesting in that most of the time we don’t need much more than good passwords. Most people don’t have the time or inclination to deal with their own data, much less yours. However, when an attack is targeted on your organization, from outside or within, it’s extremely difficult to ensure data won’t get lost.
    I don’t have a great solution, but I do think that there are good reasons to limit access to data on our systems, not the least of which is auditing and accountability. Beyond that, however, we have to hope that our users have some judgment about with whom they may share reports and other data.
    Steve Jones

  • Changing the Default DB for a Login–#SQLNewBlogger

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

    Recently I got into a bit of a pickle. I was detaching some databases for a demo, which is something I do periodically to make it easier for someone to see what’s doing on. The database detached fine, and I ran my demo.

    Then, as a sysadmin, I right clicked to attach a database back.

    2017-10-23 15_59_17-SQLQuery6.sql - (local)_SQL2014.SimpleTalk_1_Development (PLATO_Steve (57))_ - M

    and all of a sudden I get this error:

    2017-10-23 15_59_08-Microsoft SQL Server Management Studio

    Interesting. I could have attached the database back from the command line, or with dbatools, but I thought this was interesting. As I go to the attach dialog, a new connection is made. However, in this case, the default database for my sysadmin account was the one I’d detached.

    Fortunately, this is easy to fix. First, I opened a query window with master as the specific database:

    2017-10-23 16_01_20-Connect to Database Engine

    Then, I have a couple options to reset my default database. For old SQL Servers, many of you might have used sp_defaultdb. That’s marked as a deprecated procedure, so ALTER LOGIN is the new way. The syntax uses the WITH to include various options. In my case, I needed the DEFAULT_DATABASE item. This was my code:

    ALTER LOGIN [PLATO\Steve] WITH DEFAULT_DATABASE = MASTER

    If you are on an older version, something like this will work:

    exec sp_defaultdb @login = ‘Steve’, @defaultdb = ‘master’

    Once that was done, the GUI dialog worked. A quick and easy fix in this case.

    SQLNewBlogger

    As soon as I found the error, I knew what was happening. Resetting the default database took less than a minute, but I decided to spend 10  grabbing a few screenshots and putting this post together.

    You could do the same thing. Show that you can recover from errors.

  • A Tour of SQL Server Security Features

    Abstract

    Protecting data from unauthorized access becomes more important all the time. SQL Server includes a number of features that make data protection and security easier for developers and DBAs with a framework for protecting data. Come learn how Always Encrypted, TDE, Row Level Security, Dynamic Data Masking, and column level encryption can protect your systems.

    You will learn:

    • About the different encryption and security features in SQL Server
    • Understand the code changes required for encryption mechanisms
    • Gain a basic understanding of RLS and DDM, which do not require code changes to help protect data

    Level: 100

    Demos

    This talk includes the following demos

    • Always Encrypted setup and data access
    • Row Level Security setup and use
    • Dynamic Data Masking for users
    • Column Level Encryption implementation
    • TDE setup and verification