Decrypting Always Encrypted Column ASP.NET MVC App

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

edited Nov 21 '18 at 20:27

marc_s

577k12911141259

asked Nov 21 '18 at 18:26

mint

2,029103048

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<T>().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

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

add a comment |

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

edited Nov 21 '18 at 20:27

marc_s

577k12911141259

asked Nov 21 '18 at 18:26

mint

2,029103048

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<T>().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

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

add a comment |

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

edited Nov 21 '18 at 20:27

marc_s

577k12911141259

asked Nov 21 '18 at 18:26

mint

2,029103048

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

asp.net-mvc entity-framework azure azure-keyvault always-encrypted

edited Nov 21 '18 at 20:27

marc_s

577k12911141259

asked Nov 21 '18 at 18:26

mint

2,029103048

edited Nov 21 '18 at 20:27

marc_s

577k12911141259

asked Nov 21 '18 at 18:26

mint

2,029103048

edited Nov 21 '18 at 20:27

marc_s

577k12911141259

edited Nov 21 '18 at 20:27

marc_s

577k12911141259

edited Nov 21 '18 at 20:27

marc_s

577k12911141259

asked Nov 21 '18 at 18:26

mint

2,029103048

asked Nov 21 '18 at 18:26

mint

2,029103048

asked Nov 21 '18 at 18:26

mint

2,029103048

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<T>().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

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

add a comment |

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<T>().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

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

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<T>().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

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

add a comment |

0

active

oldest

votes

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53418402%2fdecrypting-always-encrypted-column-asp-net-mvc-app%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

0

active

oldest

votes

0

active

oldest

votes

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu