Author: carstent

Hertz Club DK medlem?

Er du udlandsdansker, og på vej til Danmark ved jule- eller nytårstid? Så kig lige her https://hertz.viabiler.dk/udlandsdansker, og hvis du ikke allerede har modtaget en email fra Club DK, så læs nedenfor.

Vi er meget stolte over Club DK og over at have medlemmer fordelt på 149 lande.
Vi synes, at I er spændende og er nysgerrige omkring jeres eventyr ude i verden og jeres glæde ved at komme hjem.

Vi vil som noget nyt introducere Club DK interviews i filmformat.
Har du lyst til at blive interviewet om livet ude på eventyr og det helt særlige ved at komme hjem?

Hvor: Kastrup/Københavnsområdet.
Hvornår: i december/januar
Hvordan: Interview i en bil
Varighed: Ca. 30 minutter
Færdigklippet interview: Ca. 3 minutter

Som tak for deltagelse giver vi en voucher til et weekendlejemål (bilgruppe C) som kan benyttes i løbet af 2019.

Vi kontakter de personer, som er i Danmark de datoer, hvor det er muligt for os at filme.
Har du spørgsmål er du mere end velkommen til at kontakte kathrine.lodberg@hertzdk.dk. Tilmeld dig her
Advertisements

Speeding Up Search in Azure Table Storage

Azure Table Storage is cheap and in some simple uses, as good as CosmosDB. However, when searching a single table storage (Standard Performance) with millions of rows, then the key to speeding up searching for specific entities or traversal through all entities, requires you to use both the partition key and row key values, but none of the other field values. Obviously, if you have multiple different values in either or both of the partition key and row key fields, you’ll have a problem with the speed of a search. In that case, CosmosDB will be a better option.


The code below shows how to search the a storage table using the partition key and row key fields. A look up table is used for the partition key and the date is used for the row key. Obviously, the storage table, which holds the different partition keys, needs to be maintained. The “duplicated” date in the row key mimics the timestamp field, but you can use pretty much any date type and format instead, as long as you a have simple way of searching this field.


I’m adding an upload function and I’ll publish all code shortly, if you want to take advantage of a very cheap storage option, even for million of entities…


I have an Azure function that takes care of updating the partition keys table, using a timer trigger on a monthly basis. I will add this shortly.


public class AzureTableStorageReader {
    private static readonly CloudStorageAccount StorageAccount = CloudStorageAccount.Parse(
        "DefaultEndpointsProtocol=https;AccountName=xxxx;AccountKey=xxxxx==;TableEndpoint=https://xxxxx.table.core.windows.net/;");
    private static readonly CloudTableClient TableClient = StorageAccount.CreateCloudTableClient();

    public async Task<IEnumerable<TableLog>> ReadTableLogsByDateTimes(DateTime[] logsDates) {
        if (!logsDates.Any() && logsDates.Count() != 2)
            throw new ArgumentException("Log dates are not provided.");

        try {
            var storageTable = TableClient.GetTableReference("logs");
            var storagePartKeysTable = TableClient.GetTableReference("logsPartitionKeys");
            TableContinuationToken contToken = null;

            var filter = string.Empty;

            // Get all partition keys
            foreach (var apk in await storagePartKeysTable.ExecuteQuerySegmentedAsync(new TableQuery<TableLogPartitionKey>(), contToken)) {
                filter += (filter != string.Empty) ? " or " : "(";

                // Is the full partition key text stored in the RowKey?
                filter += (apk.FullText == "0") ? "PartitionKey eq '" + apk.RowKey + "'" : "PartitionKey eq '" + apk.RowKey + "%60'";
            }

            // Add start date and end date we're searching for (both inclusive)
            filter += ") and (RowKey ge '" +
                      logsDates[0].ToString("yyyy-MM-dd", CultureInfo.InvariantCulture) +
                      "' and RowKey lt '" + logsDates[1].AddDays(1).ToString("yyyy-MM-dd", CultureInfo.InvariantCulture) + "')";

            var storageTableQuery = new TableQuery<TableLog>();
            var fetchedLogs = new List<TableLog>();

            contToken = null;

            // Execute the query async and segmented, fetching rows in chunks, until the last segment is fetched
            do {
                var seq = await storageTable.ExecuteQuerySegmentedAsync(storageTableQuery.Where(filter), contToken);

                fetchedLogs.Capacity += seq.Count();
                contToken = seq.ContinuationToken;
                fetchedLogs.AddRange(seq.Select(a => a));
            } while (contToken != null);

            if (fetchedLogs.Count == 0) {
                // Log error...
            }

            // Process the rows as needed...
            return fetchedLogs;
        }
        catch (Exception ex) {
            // Log error...
            return null;
        }
    }
}

Relaxation/Exercise

I did an early run this morning in Cassiobury Park and along the Grand Union Canal towpath, as I have done a good few times before. This time it was really dark, just before 4:30, but I kind of love that. 🙂 Being on your own, meeting nothing but deers, hares, foxes and all sorts of birds is just a lovely “companion” when out there. I absolutely love these half marathon runs, they complete me to some extent. Just saying… BTW, I could do with a running partner anywhere near Watford…

Doppelgänger

Over the last few years I’ve had various people come up to me, asking me if I was High Dennis. 😱 I’m clearly not, but I’ll take it 🙂 Have you ever had that experience, that someone mistakes or thinks you resemble a celebrity? It’s odd, and I can only apologise to the man himself, he has no need for a doppelgänger. 😎

Windows Insider Programme

I’ve been part of the Windows Insider programme for quite a while now (January 14, 2015), and I have generally been very pleased with my participation. Today I installed the Windows 10 Insider Preview 18272.1000 (rs_prerelease), from my favourite Prét coffee joint in Watford.

It took a while to download, but the actual installation was very quick, and it seems MS has been working on speeding up new OS upgrades over at least the last year. I’ve been using my trusted Lenovo Flex-2 15 for most of the builds. It is nearly 5 years old, and rarely had any issues with drivers etc, so I’m well pleased with the Windows OS, in particularly the Windows Insider builds. I’m now on the Active development of Windows preview build, on the Fast ring. I am running my development tools, Visual Studio 2017, Visual Studio Code etc, on these builds and rarely have any issues. Keep it up MS!

SQL Server Cell/Column/Field level encryption handled from your application(s)

SQL Server supports a number of encryption mechanisms, including Always Encrypted. This is automatic encryption handled by the database engine, which is pretty cool. However, what if you want your applications to handle the encryption and decryption when needed? This is where cell level encryption comes in, and it is supported by SQL Server which is a good thing.

I’ve used the AdventureWorks2017 example database for the encryption shown later, which you can find here, or using a direct download link. I have enabled SQL Server and Windows Authentication on the server, and I’m using SQL Server Authentication for this post. I have a login named enc, as well as a user named enc, using the dbo schema.

SQL Server has a number of Transact-SQL commands, which you can read more about here. Now, why am I writing about this, seeing as you can just use what is shown on those pages? Using Transact-SQL is very simple, but how can you use it from your own applications? Well, I needed to use the encryption from a C# application using Entity Framework 5.0. The latter was a requirement!

Right, let’s start by enabling the database to handle the encryption/decryption. In the Sales.CreditCard table, there is a CardNumber column, which I want encrypted, to keep prying eyes from reading it. So in a query window (I’m using SSMS, SQL Server Management Studio), run the following query:

-- Connect to the right database
USE AdventureWorks2017
GO

-- Create master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SomeSillyPassword@2018'
GO

-- Create certificate
CREATE CERTIFICATE CreditCardNoCert WITH SUBJECT = 'Credit card Numbers'
GO

-- Create symmetric key
CREATE SYMMETRIC KEY CreditCardNoKey WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE CreditCardNoCert
GO

-- Grant permissions
GRANT VIEW DEFINITION ON CERTIFICATE::CreditCardNoCert TO "enc"
GO

GRANT VIEW DEFINITION ON SYMMETRIC KEY::CreditCardNoKey TO "enc"
GO

GRANT CONTROL ON CERTIFICATE::CreditCardNoCert TO "enc"
GO

-- Add new column to hold encrypted value
ALTER TABLE Sales.CreditCard
ADD CardNumberEncrypted varbinary(8000)
GO

You can encrypt the CardNumber column for all existing rows, using the below T-SQL and the symmetric key. Notice how the encrypted value is copied from the original column, CardNumber, to the newly created column, CardNumberEncrypted. Notice how the symmetric key is being opened and closed.

OPEN SYMMETRIC KEY CreditCardNoKey
DECRYPTION BY CERTIFICATE CreditCardNoCert

UPDATE Sales.CreditCard
SET CardNumberEncrypted = EncryptByKey(Key_GUID('CreditCardNoKey'), CardNumber, 1, HashBytes('SHA1', CONVERT(varbinary, CreditCardID)));
GO

CLOSE SYMMETRIC KEY CreditCardNoKey
GO

As a means of protection, you could set the CardNumber column to an empty string, or you can delete it, and rename the CardNumberEncrypted column to CardNumber.

Now that we have the encryption in place, we need to look at how you can decrypt CardNumberEncrypted.

OPEN SYMMETRIC KEY CreditCardNoKey
DECRYPTION BY CERTIFICATE CreditCardNoCert

SELECT CardNumberEncrypted AS 'CardNumberEncrypted', CONVERT(nvarchar, DecryptByKey(CardNumberEncrypted, 1, HASHBYTES('SHA1', CONVERT(varbinary, CreditCardID)))) AS 'CardNumberDecrypted' FROM Sales.CreditCard;

CLOSE SYMMETRIC KEY CreditCardNoKey
GO

So far so good, now we need to handle the encryption/decryption from an application using C#. I have created an Operations class, shown below.

using System;
using System.Linq;
using System.Text;

namespace EF-SqlEncryption {
  public class Operations {
    private AdventureWorks2017Entities _adventureWorksContext = null;

    public void EnsureEncryptedCreditCardNoColExists() {
      using (_adventureWorksContext = new AdventureWorks2017Entities()){
        try {
          _adventureWorksContext.Database.ExecuteSqlCommand("ALTER TABLE Sales.CreditCard ADD CardNumberEncrypted varbinary(128)");
          Console.WriteLine("Column CardNumberEncrypted created");
        }
        catch (Exception){
          Console.WriteLine("Column CardNumberEncrypted already exists");
        }
      }
    }

    public string GetFirstCreditCardNo() {
      string result = string.Empty;

      using (_adventureWorksContext = new AdventureWorks2017Entities()) {
        var ccs = _adventureWorksContext.CreditCards.FirstOrDefault();
        result = ccs.CardNumber + " - ";
        if (ccs.CardNumberEncrypted != null)
          result += ByteArrayStringRepresentation(ccs.CardNumberEncrypted);
      }

      return result;
    }

    public byte[] EncryptCreditCardNo(string cardNo) {
      var result = new byte[0];

      try {
        _adventureWorksContext = new AdventureWorks2017Entities();
        var dmlOpenCert = "OPEN SYMMETRIC KEY CreditCardNoKey DECRYPTION BY CERTIFICATE CreditCardNoCert;";
        var dmlCloseCert = "CLOSE SYMMETRIC KEY CreditCardNoKey;";
        var dmlEncrypt = "SELECT CardNumberEncrypted = EncryptByKey(Key_GUID('CreditCardNoKey'), CardNumber, 1, HashBytes('SHA1', CONVERT(varbinary, CreditCardID))) FROM Sales.CreditCard WHERE CardNumber = '" + cardNo + "';";
        result = _adventureWorksContext.Database.SqlQuery<byte[]>(dmlOpenCert + dmlEncrypt + dmlCloseCert).FirstOrDefault();
      }
      catch (Exception e) {
        Console.WriteLine(e.Message);
      }
      finally {
        _adventureWorksContext.Dispose();
      }

      return result;
    }

    public byte[] UpdateEncryptedCreditCardNo(string cardNo) {
      byte[] result = new byte[0];

      try {
        EnsureEncryptedCreditCardNoColExists();
        _adventureWorksContext = new AdventureWorks2017Entities();
        var dmlOpenCert = "OPEN SYMMETRIC KEY CreditCardNoKey DECRYPTION BY CERTIFICATE CreditCardNoCert;";
        var dmlCloseCert = "CLOSE SYMMETRIC KEY CreditCardNoKey;";
        var dmlUpdate = "UPDATE Sales.CreditCard SET CardNumberEncrypted = EncryptByKey(Key_GUID('CreditCardNoKey'), CardNumber, 1, HashBytes('SHA1', CONVERT(varbinary, CreditCardID))) WHERE CardNumber = '" + cardNo + "';";
        var sqlSelect = "SELECT CardNumberEncrypted FROM Sales.CreditCard WHERE CardNumber = '" + cardNo + "';";
        result = _adventureWorksContext.Database.SqlQuery<byte[]>(dmlOpenCert + dmlUpdate + sqlSelect + dmlCloseCert).FirstOrDefault();
      }
      catch (Exception e) {
        Console.WriteLine(e.Message);
      }
      finally {
        _adventureWorksContext.Dispose();
      }

      return result;
    }

    public string DecryptFirstCreditCardNo() {
      var result = string.Empty;

      try {
        EnsureEncryptedCreditCardNoColExists();

        _adventureWorksContext = new AdventureWorks2017Entities();
        var res = _adventureWorksContext.Database.SqlQuery("SELECT TOP 1 * FROM Sales.CreditCard WHERE CardNumberEncrypted IS NOT NULL");

        var cardNo = res.FirstOrDefault().CardNumber;
        var dmlOpenCert = "OPEN SYMMETRIC KEY CreditCardNoKey DECRYPTION BY CERTIFICATE CreditCardNoCert;";
        var dmlDecrypt = "SELECT CONVERT(nvarchar, DecryptByKey(CardNumberEncrypted, 1, HashBytes('SHA1', " +
"CONVERT(varbinary, CreditCardID)))) AS 'CardNumberDec' FROM Sales.CreditCard WHERE CardNumber = '" + cardNo + "';";
        var dmlCloseCert = "CLOSE SYMMETRIC KEY CreditCardNoKey;";

        // Carsten Thomsen: This works, with the combination of DML and a query. EF is closing the connection after each query or DML, meaning the Cert would be closed too, effectively preventing encryption and decryption.
        result = _adventureWorksContext.Database.SqlQuery(dmlOpenCert + dmlDecrypt + dmlCloseCert).FirstOrDefault();
      }
      catch (Exception e) {
        Console.WriteLine(e.Message);
      }
      finally {
        _adventureWorksContext.Dispose();
      }

      return result;
    }

    public string DecryptCreditCardNo(string cardNo) {
      var result = string.Empty;

      try {
        EnsureEncryptedCreditCardNoColExists();
        _adventureWorksContext = new AdventureWorks2017Entities();
        var dmlOpenCert = "OPEN SYMMETRIC KEY CreditCardNoKey DECRYPTION BY CERTIFICATE CreditCardNoCert;";
        var dmlDecrypt = "SELECT CONVERT(nvarchar, DecryptByKey(CardNumberEncrypted, 1, HashBytes('SHA1', " +
"CONVERT(varbinary, CreditCardID)))) AS 'CardNumberDec' FROM Sales.CreditCard WHERE CardNumber = '" + cardNo + "';";
        var dmlCloseCert = "CLOSE SYMMETRIC KEY CreditCardNoKey;";
        result = _adventureWorksContext.Database.SqlQuery(dmlOpenCert + dmlDecrypt + dmlCloseCert).FirstOrDefault();
      }
      catch (Exception e) {
        Console.WriteLine(e.Message);
      }
      finally {
        _adventureWorksContext.Dispose();
      }

      return result;
    }

    public static string ByteArrayStringRepresentation(byte[] ba) {
      var bytesStringRepresentation = new StringBuilder(ba.Length * 2);
      bytesStringRepresentation.Append("0x");

      foreach (byte b in ba) {
        bytesStringRepresentation.AppendFormat("{0:x2}", b);
      }

      return bytesStringRepresentation.ToString().ToUpper();
    }
  }
}

Here are the AdventureWorks2017 model and code first classes.

Bitbucket Extension for Visual Studio

I started working in a new company recently, one that uses the Atlassian Bitbucket repository for source control. I\m not a big fan, but it’s Git. Anyway, I needed to integrate it with Visual Studio 2017, so I fired up the Extensions and Updates dialog from the Tools menu. I typed in ‘bitbucket’ in the search field, and found one extension, Visual Studio Bitbucket Extension by NextIteration v. 1.37.1 at the of writing this, which I then downloaded and installed. Unfortunately I couldn’t find in the Source Control section of the Options dialog box, found on the Tools menu; it just wasn’t shown in the Current source control plug-in dropdown list. I closed the Options dialog, and switched to the Team Explorer window which didn’t show the extension either. So, I went to the Visual Studio market place and found another extension by MistyK, https://marketplace.visualstudio.com/items?itemName=MistyK.VisualStudioBitbucketExtension. Installing that, did the trick, even if this extension was also version 1.37.1, go figure…

Visual Studio 2017 Editor new line wrapping

We all have our favourite settings when it comes to setting up Visual Studio, and the editor is quite often set up differently for a group of developers working together. I like having the opening brace ({) on the same line as the class, struct or whatever the brace signals the beginning of.

I like this:

class {

as compared to this:

class 
{

I frequently use the key combination Ctrl+K, Ctrl+D to format my document after having typed some code. I dislike it, if the formatting is not to my liking. It should therefore not come as a surprise, if I tell you that while working on a new UWP app, in which I had copied across some code from a Template10 project template, the key combo seemingly didn’t work anymore. After some investigation, including opening an existing Console app, in which the key combo did work, I took a look at this code:

namespace Template10.Views
{
    public sealed partial class SettingsPage : Page
    {
        readonly Template10.Services.SerializationService.ISerializationService _serializationService;
 
        public SettingsPage()
        {
            InitializeComponent();
            NavigationCacheMode = NavigationCacheMode.Required;
            _serializationService = Template10.Services.SerializationService.SerializationService.Json;
        }
 
        protected override void OnNavigatedTo(NavigationEventArgs e)
        {
            var index = int.Parse(_serializationService.Deserialize(e.Parameter?.ToString()).ToString());
            MyPivot.SelectedIndex = index;
        }
    }
}

Because of the Page class, from which the class shown, SettingsPage, is derived has a notification stating “Base type ‘Page’ is already specified in other parts”, the class/type name Page is greyed out. This effectively blocks for the document formatting placing the opening brace { on the same line as the class declaration. If I remove : Page, and press the key combination Ctrl+K, Ctrl+D, this is what I get.

namespace Template10.Views {
    public sealed partial class SettingsPage {
        readonly Template10.Services.SerializationService.ISerializationService _serializationService;
 
        public SettingsPage() {
            InitializeComponent();
            NavigationCacheMode = NavigationCacheMode.Required;
            _serializationService = Template10.Services.SerializationService.SerializationService.Json;
        }
 
        protected override void OnNavigatedTo(NavigationEventArgs e) {
            var index = int.Parse(_serializationService.Deserialize(e.Parameter?.ToString()).ToString());
            MyPivot.SelectedIndex = index;
        }
    }
}

 

You should only open projects from a trustworthy source prompt

In Visual Studio 2017 and previous versions, if you attempt to open a project on a network share, such as one located on a Mac, that helpful, but rather annoying message prompts you. There’s a quick fix for that;

  • Open Visual Studio, on the Tools  menu, click Options.
  • In the Options dialogue box, on the list on the left, expand Projects and Solutions and click General.
  • Uncheck Warn user when the project location is not trusted, and click OK.

Simple…