0

I've encrypted a couple of columns in a SQL database hosted in Azure. I did the encryption (deterministic) using AzureKeyVault. I've given the App Service in Azure itself access to the KeyVault. I've also added the Column Encryption Setting=enabled to the connection string. I'm using Entity Framework 6.0 for my data layer.

However when I run a query against a table that has the encryption, I simply receive a timeout error.

Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

I've looked at adding this code and calling it in my Global.asax Application_Start method:

public class AzureKeyValueConfiguration
{
    private static ClientCredential _clientCredential;

    public static void InitializeAzureKeyVaultProvider()
    {
        string clientId = "MyClient";
        string clientSecret = "MySecret";
        _clientCredential = new ClientCredential(clientId, clientSecret);

        SqlColumnEncryptionAzureKeyVaultProvider azureKeyVaultProvider = new SqlColumnEncryptionAzureKeyVaultProvider(GetToken);

        Dictionary<string, SqlColumnEncryptionKeyStoreProvider> providers =
            new Dictionary<string, SqlColumnEncryptionKeyStoreProvider>();

        providers.Add(SqlColumnEncryptionAzureKeyVaultProvider.ProviderName, azureKeyVaultProvider);
        SqlConnection.RegisterColumnEncryptionKeyStoreProviders(providers);
    }

    private async static Task<string> GetToken(string authority, string resource, string scope)
    {
        var authContext = new AuthenticationContext(authority);
        AuthenticationResult result = await authContext.AcquireTokenAsync(resource, _clientCredential);

        if (result == null)
            throw new InvalidOperationException("Failed to obtain the access token");

        return result.AccessToken;
    }
}

protected void Application_Start()
{
        FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
        RouteConfig.RegisterRoutes(RouteTable.Routes);
        AutoMapperConfiguration.Configure();
        log4net.Config.XmlConfigurator.Configure();
        AzureKeyValueConfiguration.InitializeAzureKeyVaultProvider();
}

However, I'm not even sure if this code is firing as I can't get it to hit in the debugger. All in all, I'm a bit lost and could use some guidance.

Thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
mint
  • 3,341
  • 11
  • 38
  • 55
  • 2
    Please provide the query, that you are running. It could be that your query fetches all the encrypted data to decrypt it and that's why it takes so long. – Andrey Nikolov Nov 21 '18 at 20:43
  • Hi, my query is like this: entity = db.Set().FirstOrDefault(t => t.AccountId == id); – mint Nov 26 '18 at 14:06
  • This isn't the query, that is ran in the database :) Is AccountId encrypted? Just capture the T-SQL query, which is executed in the database by this linq query and post it here. – Andrey Nikolov Nov 26 '18 at 14:48
  • 1
    Ok after a lot of digging (EF sure hides the erorr messages) I found out it was because my App Service didn't have the 'Verify' permission to the Azure Key vault. Now, I can read the data fine... however I cannot update the data now. Thanks for helping. – mint Nov 26 '18 at 15:00

0 Answers0