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
asp.net-mvc entity-framework
azure azure-keyvault always-encrypted edited Nov 21 '18 at 20:27
marc_s
582k13011231269
asked Nov 21 '18 at 18:26
mintmint
2,054103148
add a comment |
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
asp.net-mvc entity-framework
azure azure-keyvault always-encrypted edited Nov 21 '18 at 20:27
marc_s
582k13011231269
asked Nov 21 '18 at 18:26
mintmint
2,054103148
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 |
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
asp.net-mvc entity-framework
azure azure-keyvault always-encrypted edited Nov 21 '18 at 20:27
marc_s
582k13011231269
asked Nov 21 '18 at 18:26
mintmint
2,054103148
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
asp.net-mvc entity-framework
azure azure-keyvault always-encrypted asp.net-mvc entity-framework
azure azure-keyvault always-encrypted edited Nov 21 '18 at 20:27
marc_s
582k13011231269
asked Nov 21 '18 at 18:26
mintmint
2,054103148
edited Nov 21 '18 at 20:27
marc_s
582k13011231269
asked Nov 21 '18 at 18:26
mintmint
2,054103148
edited Nov 21 '18 at 20:27
marc_s
582k13011231269
edited Nov 21 '18 at 20:27
marc_s
582k13011231269
edited Nov 21 '18 at 20:27
marc_s
582k13011231269
582k13011231269
asked Nov 21 '18 at 18:26
mintmint
2,054103148
asked Nov 21 '18 at 18:26
mintmint
2,054103148
asked Nov 21 '18 at 18:26
mintmint
2,054103148
2,054103148
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
2
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
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
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
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
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
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
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
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
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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