Voice of the DBA

Tag: SQLNewBlogger

  • Setting DebugPreference for Testing–#SQLNewBlogger

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

    I was working on a PoSh script recently and needed to debug some things. Rather than have Write-Host throughout it, I wanted to log some stuff when I had issues, but not all the time. This post talks about how to do this.

    A Simple Script

    Here’s a simple script that I wrote to investigate this:

    write-host("test")
    Write-Debug("This is a debug message")
    $i = 10
    Write-Debug("I: $i")
    $i += 1
    Write-Debug("I: $i")
    $i += 1
    Write-Host($i)

    In this script, I have a few messages. If I just run this, I get this result:

    ❯ .\debugscript.ps1
    
test
    
12

    That’s pretty easy to see. However, what if I want my debug messages to print? I can add a –Debug parameter, but that doesn’t affect the script.

    ❯ .\debugscript.ps1 -Debug
    
test
    
12

    Set $DebugPreference

    Instead, what I need to to is change the debug preference, which is in the $DebugPreference variable. This is in the Preference Variable list, and defaults to SilentlyContinue.

    However, if I set this to Continue, I get the behavior I want.

    ❯ $DebugPreference="Continue"
    
❯ .\debugscript.ps1
    
test
    
DEBUG: This is a debug message
    
DEBUG: I: 10
    
DEBUG: I: 11
    
12

    If I don’t want to see these, I can set the variable back.

    $DebugPreference="SilentlyContinue"

    Using the variable with write-debug is a quick way to turn debugging on and off in your console.

    SQLNewBlogger

    I had used this before, but had to think about it for a few minutes as I hadn’t done any PowerShell lately. So I decided to add 15 minutes to my work and document this for myself.

    And for the next person that wants to interview me on how I write PoSh. You could do the same thing.

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

  • A Basic Encryption Primer for SQL Server

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

    Encryption is a function call in SQL Server, where we pass in the text to encrypt and a key. That’s really what we are doing with encryption. I pass a key and data into a function and get either encrypted or decrypted data. Here’s a short example:

    Basic Encryption Demo

    Let’s say I have a simple message, like “Let’s meet at Kunjani Coffee at 8am.”. I want to protect this message.

    DECLARE
         @data VARCHAR(500) = 'Let''s meet at Kunjani Coffee at 8am.'
       , @encrypteddata VARBINARY(500);
    
SELECT 'Plain Data', @data
    
SELECT
         'Encrypted Data'
       , ENCRYPTBYPASSPHRASE ('mysecretk#y', @data) AS EncryptedData
    
SELECT
         'Decrypted Data'
       , CAST(DECRYPTBYPASSPHRASE ('mysecretk#y', ENCRYPTBYPASSPHRASE ('mysecretk#y', @data)) AS VARCHAR(100)) AS DecryptedData
    
SELECT
         'Almost Decrypted Data'
       , DECRYPTBYPASSPHRASE ('mysecretk#y', ENCRYPTBYPASSPHRASE ('mysecretk#y', @data)) AS AlmostDecryptedData;

    In this code, I’m using EncryptByPassPhrase and DecryptByPassPhrase to encrypt data. I pass in a key, which is my passphrase. That is “mysecretk#y” i this case. I then pass in the data and get the result returned. You can see the four results below:

    2021-05-17 12_31_35-SQLQuery1.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (59))_ - Microsoft SQL Server

    This is a quick example of the functions working together. I use one to hide the data, and another to reveal it.

    The Encryption Hierarchy

    Encryption is expensive in terms of resources (time, CPU, etc.), which means we want to minimize it. Part of what we do is try to minimize the work done while maximizing protection.

    We can double encrypt things, meaning I could take the result of the second query, and use that “encrypted data” string and use that in another encryption function and provide even more protection. The encryption of each string, however, is time and CPU intensive.

    Rather than encrypt the data again, we often just encrypt the keys. One key encrypts another and then the resource cost of decrypting a key is much lower than decrypting the data over and over. If you look at the encryption hierarchy on MS Docs, you’ll see that keys are stacked on each other, each one protecting the layer below. Typically we are only encrypting the actual data with a password or a symmetric key, which are essentially the same thing and the quickest way to perform the encryption and decryption.

    Let’s lightly look at the parts of this hierarchy.

    Asymmetric Keys

    The stronger keys are the asymmetric ones. They are called this because the key used for encryption is different than the key used for decryption. Typically these are paired together, and you can give someone 1 key, so they can only perform one operation.

    Certificates are asymmetric keys, with other metadata, and have the two keys as the public and private keys. This is how we do a lot of encryption across distances where we need to exchange data on insecure channels, like the Internet.

    These are computationally intensive, meaning lots of CPU and time, so we don’t usually want to use these to encrypt lots of data. Instead, we use these to encrypt or symmetric keys.

    Note: The strangeness in the SQL hierarchy is that the SMK and DMK are symmetric keys.

    Symmetric Keys

    The Symmetric keys are used for both encryption and decryption of the data. This doesn’t mean any key works, but the key used to specifically encrypt a set of data is the key used to decrypt it. This means we need to send the key to all parties that do encryption and decryption.

    These do require lots of CPU, but much less than asymmetric keys. Typically we use these to actually encrypt the data.

    Hash Functions

    This isn’t really encryption, but some hash functions are used for things like passwords, where we can have one-way encryption. These are often used to transform the data into a hash, or representation, and then we can compare the hash together.

    Using Encryption in Practice

    Each of these ideas is just a function call, and that is how we’ve implemented encryption in SQL Server. Whether you use TDE, Always Encrypted (AE), column level encryption, or anything else, you are making function calls in some way.

    For TDE and AE, SQL Server handles much of this work for you. It gets keys, does the function call. For the code above, or any of the ENCRYPT/DECRYPT functions, you are writing code and using those in your work.

    However, keep in mind that key management is the key to protecting things. This means how you protect them, where they are, how you copy them to other places, and change them over time.

    Summary

    Encryption is just a series of function calls. We have different types of functions and different parameters, but that’s really the core of what is happening.

    We can use these functions with the outputs of one as the inputs of another, or more often, the keys used as inputs instead of text, allowing us to encrypt and protect the keys themselves.

    There is a lot more to learn, but this gives a basic look at encryption in SQL Server.

  • Computed Columns for Grouping–#SQLNewBlogger

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

    I ran into someone trying to do some grouping for an accounting report. In this case, they had a number of criteria where certain accounts were used to produce groups of values. For example, I might have these criteria:

    • Accounts from 400000 to 490000 are Operating Expenses
    • Accounts from 500000 to 502999 are Personal Expenses
    • Accounts from 503000 to 503999 are Materials and Services

    There would be other items, but if I have accounts and values, how do I sum and group in these ways?

    There are a few possibilities, but since I might move accounts around, I thought that computed columns might help with grouping here. This post looks are a way you can do this.

    Imagine I have some values like this: 

    CREATE TABLE BudgettoActual
    
(accountid INT
    
, budget NUMERIC(10,2)
    
, actual NUMERIC(10,2)
    
)
    
GO
    
INSERT dbo.BudgettoActual
         (accountid, budget, actual)
    
VALUES
         (400010, 300, 299),
         (501010, 100, 102),
         (502010, 200, 150),
         (503010, 400, 150),
         (507010, 800, 150)
    
GO

    Now I can use a SUM with a CASE, but that makes a complex query. One way to simplify this for others is to use a computed column in the table that might include my criteria. I can use a CASE statement to create my groupings.

    ALTER TABLE dbo.BudgettoActual 
    
ADD AccountGroup AS CASE 
        WHEN accountid>= 400000 AND accountid < 489999 THEN 1
        WHEN accountid>= 501000 AND accountid < 503000 THEN 2
        WHEN accountid>= 503000 AND accountid < 504000 THEN 3
        WHEN accountid>= 507000 AND accountid < 508000 THEN 4
      ELSE 5
      END

    With this, I see this in my table:

    2021-05-12 11_35_15-SQLQuery5.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (52))_ - Microsoft SQL Server

    Now I have these different groups that I can use in a query and group by them. For example, I can get a quick look at my different categories with this code. I’ve put the categories in a CASE in the column list, but it could come from another table.

    SELECT
              CASE
                  WHEN ba.AccountGroup = 1 THEN
                      'Operating Revenues'
                  WHEN ba.AccountGroup = 2 THEN
                      'Personal Expenses'
                  WHEN ba.AccountGroup = 3 THEN
                      'Materials and Services'
                  WHEN ba.AccountGroup = 4 THEN
                      'Reserves'
              END 'Object'
            , SUM (ba.budget) AS Budget
            , SUM (ba.actual) AS actual
    
FROM     dbo.BudgettoActual AS ba
    
GROUP BY ba.AccountGroup;

    This gives me a look at my financial numbers quickly.

    2021-05-12 11_36_20-SQLQuery5.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (52))_ - Microsoft SQL Server

    Rather than numbers, I could have used the title in the computed column, but that causes issues with ordering. With these numbers, I can choose numbers that are the order I need them in for a report (which can matter for financial reporting).

    I’d prefer to use a separate table mapping the AccountGroup to a title and then joining that in my report.

    This isn’t the only way to do this, but it is one way to handle complex grouping in a way that can make it easier for clients that might need to query this data.

    SQLNewBlogger

    This post took me about 10 minutes to write, but about 15 minutes to setup, which might be most of a writing session for a blog. However, it’s a good showcase of a creative way to solve an issue.

    Any of you could put together a similar post on a query issue you’ve run into and want to share.