Author: way0utwest

  • Using a DMK in TDE

    When you setup TDE, you need to create a DMK (Database Master Key) in the master database if one doesn’t exist. This key forms the basis of a chain of protection for your data inside of the SQL Server instance. The hierarchy is:

    • The DMK exists in the master database. This protects a certificate or asymmetric key by encrypting it.
    • The certificate in turn encrypts the DEK in a database using TDE. This certificate is required to open the DEK in the database, or open the DEK during a restore operation.
    • The DEK encrypts the data.

    There is only one DMK in each database, so the DMK in master for TDE could be used to protect multiple certificates, asymmetric keys, or symmetric keys that exist in master for use by TDE or any other encryption mechanism.

    A particular DMK is not needed to restore a TDE database on another instance. Rather the new instance of SQL Server should have a DMK created in the master database. Then the backup of the certificate is restored on the other instance, protected by the DMK there. Once this is complete, the TDE database can be restored.

    If you’d like to know more about what a DMK is, I’ve written about it in a previous post.

  • The Great and Powerful Internet of Things

    Will we have the IoUT (Internet of Useful Things? As some have said, the IoS (Internet of Sh**e) is really what we have because of the poorly built, poorly secured hardware and software devices. It seems that almost every month I see new devices introduced in the consumer space, most of which aren’t well designed for widespread use, and certainly aren’t very durable. There are, of course, exceptions, including a few I’d like, but for the most part, the rush to market means that many of the IoT devices sold aren’t much better than most of the home built Kickstarter projects. That’s not to say Kickstarter (or Indiegogo or any other site) doesn’t produce good products, just that some aren’t.

    We’ve got an infographic from Website Guide today that you can examine. It’s an interesting item, which is why I published it. There are a lot of great possible applications for IoT style devices. Plenty of businesses and industries are seeing the benefits of using sensors and devices that are (semi-) smart and connected to a network. Using well written applications, companies can come up with new ways of gathering and using information. When these systems are well designed, this can result in lower costs, higher sales (or usage), happier customers, or all three.

    As you scroll down the infographic, you’ll see the dark side of IoT, which is getting more press all the time. Security is the number one concern, as far, far too many devices don’t include the necessary security to protect either the device or the end user. I did watch an IoT demo at Live!360 last year using the Azure IoT hub, and was glad there was some authentication and ability to shut down devices that might be compromised or misbehaving, but far too many IoT systems aren’t well secured.

    That’s disconcerting for me as a database professional. I expect that more and more companies will use IoT in some way. I’d even like to find time for some IoT sensors around the ranch, tracking horses and ensuring they’re all walking around every day. The thing that concerns me is what happens when someone finds ways to hijack or hack a device? Even sensors could be hijacked. What if you’re processing temperature information in dynamic SQL and someone decides to heat and cool the sensor to create certain values? Are you sure your database isn’t vulnerable?

    These concerns leave out the potential for the misuse of any data that’s collected about users, especially if users aren’t aware of what’s being collected and its use. With so many ways of collecting data now, I’m not even sure that anyone has a good handle on all the data collected about them, not to mention how it’s being stored, secured, and used.

    I think there’s incredible potential for some great IoT applications and data in the future. All the forecasts and spending seem to point towards the next 5-10 years being a period where there will be an explosion of the number of types devices built and deployed. Those devices will produce a tremendous amount of data for us to manage in databases. We will have big challenges in storing, indexing, managing streams, and certainly, the pruning of old 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

  • Speaking at SQL Konferenz 2017

    I’m heading back across the Atlantic this February for SQL Konferenz. This is an intimate SQL Server event in Darmstadt (near Frankfurt). I was fortunate to be picked to speak in 2015, but skipped 2016 as the timing wasn’t good. This year, things lined up for me, and I’ll be attending the conference after a short visit to the Redgate offices.

    I will be presenting a talk on Database Devops, updated from last year, and looking at ways in which you can get your database into a better software development process.

    There are a number of other great sessions on all aspects of SQL Server, both the core engine and BI topics. There are also a good selection of presentations in both English and German. Just for fun, I might go to a German one to see if I can follow along. Perhaps I’ll go see the Cloud Service in Azure one.

    You can register today for SQL Konferenz and come enjoy the event with me. See you in February.

  • The 10 Day Cleanse

    Disclosure: It hasn’t been 10 days, but I wanted to write something at this point, after 7 days. I know some of you are trying to be more healthy, so I’m posting some thoughts.

    Let me cut to the chase, and then some thoughts. First, this is a week for me with no coffee, no alcohol, no soda, and almost no food. I’m amazed I got here. I’ve lost 12+ pounds from when I started.

    I’ve been doing the Green Smoothie Cleanse with my wife. For the last seven days, I’ve been subsisting on

    1. green smoothies (I get 64oz/day)
    2. decaf tea (green and detox)
    3. carrots
    4. grapes
    5. nuts
    6. hard boiled eggs
    7. peanut butter (and cashew butter, which is really good)
    8. water

    That’s it. I drink about 4 of these a day:

    Photo Jan 31, 9 59 12 AM

    And these smoothies, usually in 3-4 chunks.

    Photo Jan 28, 6 21 19 AM

    Lovely, huh?

    I’m hungry often, but mostly I’m unsatisfied. It’s a mixed bag for me. I’ve lost weight. I think I’m slimmer and healthier, but I also think about food. A lot. All the time.

    I also think about how I think about food. I realize it’s comfort for me, and probably need to take less comfort and be more careful.

    It’s been an interesting week. Three days to go and then I’ll be back on a semi normal diet. There is a transition with one smoothie and then better eating. I’ll try that a bit, but I spend 10 days traveling for work, which will be tough. At least I’m more aware of what I eat and what I can tolerate with regards to hunger.