Voice of the DBA

Tag: syndicated

  • Daily Coping 7 Jun 2021

    I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag. 

    Today’s tip is to stay positive in your conversations with others.

    This is a good tip for me this time of year. As we wind down volleyball this season, there are some tough conversations to have with kids about their performance and things they need to work on for next year. There are also talks with kids (and parents) that won’t get offered a spot to stay with the team for next year.

    One of the important things I am trying to remember when having hard talks is that I want to emphasize positive things that have happened, note what has worked, and frame issues as opportunities to improve in the future. These are areas of focus that will help improve athletes in the future.

    It can be hard, but it is important to be clear and honest about success and failure, as well as the ways to improve for the future.

  • Daily Coping 4 Jun 2021

    I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag. 

    Today’s tip is to help nature in some way, plant something, pick up trash, make a small difference in the world.

    I don’t have a green thumb, and other than cutting the grass, I don’t do a lot of nature work. However, we’ve been composting this last year and trying to take better care of the nature around here.

    I saw this and took some time to walk around, cleaning up trash that’s gotten loose on the property. I also decided to get a small tree and plant it in the yard. I’m hoping that this will survive, thrive, and grow, but we’ll see if how things go across the next year.

  • Daily Coping 3 Jun 2021

    I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag. 

    Today’s tip is to exercise outside today in some way.

    I wrote this a few days ago because exercise today may be tough. I’m flying to Ft Lauderdale this morning and driving to Key West, so hopefully things will go smooth and I’ll have time to go for a walk by the ocean tonight.

    However, when I saw this tip, I decided to go back to one of my routines from lockdown last year. I did yoga outside, taking a break from work and setting up in the sunshine. A little hot, but being out in nature is very refreshing.

  • Creating a Symmetric Key–#SQLNewBlogger

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

    This is a series on working with the various encryption technologies in SQL Server.

    One of the encryption technologies in SQL Server is using keys to encrypt or decrypt data. This post looks at the symmetric key, which is part of the way that you can do the actual encryption of your data in SQL Server. I have a post the discusses how this works, but this post just looks at the use of creating the key.

    Note: You may need to create a database master key first, and you can follow the link to do that. If you need an overview of encryption, read A Basic Encryption Primer for SQL Server.

    The CREATE Statement

    There is DDL For symmetric keys in the form of:

    There are also the OPEN and CLOSE commands. For this post, we will look only at the CREATE statement.

    The basic statement requires a name, an algorithm, and an encryption mechanism. You cannot create a key that is unprotected in some way. Each of these has different possible values.

    You also can optionally add a KEY_SOURCE and an IDENITY_VALUE, which are used to recreate this key if it is removed (or in another database). You can also use a provider, if you have an EKM provider configured. Your CREATE would use the EKM provider and the name of the key from the provider to use in operations. If I want someone else to own this key, I can provide a user name or an application role.

    Here is the minimum key create (and the select and drop to check it).

    CREATE SYMMETRIC KEY SteveKey
  WITH ALGORITHM = AES_128
  ENCRYPTION BY PASSWORD = 'sdfs'
GO  
SELECT * FROM sys.symmetric_keys AS sk
GO
DROP SYMMETRIC KEY SteveKey
GO

    This uses a specific algorithm, which I must provide. There are a number of choices, but in terms of practical choices in 2021, likely really only the AES ones make sense. For the encryption scheme, that depends on what you’ve set up in your system, but you can choose from

    • password
    • symmetric key
    • asymmetric key
    • certificate

    You choose the type and enter it, with the name of the object or the = with a password.

    That’s really the extent of creating a symmetric key. Any details with an EKM provider really come from the name used in the CREATE PROVIDER command.

    SQLNewBlogger

    This is something I’ve done a few times to learn how it works and practice implementing encryption. Ultimately, the key management makes most of the column level encryption seem silly, and really I’d do this in the application layer to ensure communications are protected.

    I took 20 minutes to write this up, copy some links, and showcase this. If you want to work in this area, do this as well. Practice this and write about what you’ve learned, the good, the bad, and the problems.