SQL Error SQL72045 When Importing a BACPAC

Problem

You receive the following error when trying to import a BACPAC that was generated on a different server:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Could not import package.
Warning SQL0: A project which specifies Microsoft Azure SQL Database v12 as the target platform may experience compatibility issues with SQL Server 2014.
Error SQL72014: .Net SqlClient Data Provider: Msg 12824, Level 16, State 1, Line 5 The sp_configure value 'contained database authentication' must be set to 1 in order to alter a contained database.  You may need to use RECONFIGURE to set the value_in_use.
Error SQL72045: Script execution error.  The executed script:
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET CONTAINMENT = PARTIAL 
            WITH ROLLBACK IMMEDIATE;
    END

Error SQL72014: .Net SqlClient Data Provider: Msg 5069, Level 16, State 1, Line 5 ALTER DATABASE statement failed.
Error SQL72045: Script execution error.  The executed script:
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET CONTAINMENT = PARTIAL 
            WITH ROLLBACK IMMEDIATE;
    END

 (Microsoft.SqlServer.Dac)

Solution

Execute the following T-SQL on your local instance:

sp_configure 'contained database authentication', 1;  
GO  
RECONFIGURE;  
GO  

Background

Initially I thought this error was arising due to enabling Transparent Data Encryption (TDE) on SQL Azure databases, but in actual fact it was down to switching to database level authentication rather than relying on server level logins.

A side-effect of this change was that the database is now considered to be (partially) contained, i.e. it has no hard dependencies on master. Read more on Contained Databases here. Support for contained databases is disabled by default as they have security implications. From the contained database authentication Server Configuration Option MSDN entry:

When contained databases are enabled, database users with the ALTER ANY USER permission, such as members of the db_owner and db_accessadmin database roles, can grant access to databases and by doing so, grant access to the instance of SQL Server. This means that control over access to the server is no longer limited to members of the sysadmin and securityadmin fixed server role, and logins with the server level CONTROL SERVER and ALTER ANY LOGIN permission. Before allowing contained databases, you should understand the risks associated with contained databases.

TypeLoadException when using SqlColumnEncryptionAzureKeyVaultProvider

Problem

An exception is thrown when trying to use the SqlColumnEncryptionAzureKeyVaultProvider for working with Always Encrypted columns in SQL Azure.

An unhandled exception of type 'System.TypeLoadException' occurred in Hyak.Common.dllInheritance security rules violated by type: 'System.Net.Http.WebRequestHandler'. Derived types must either match the security accessibility of the base type or be less accessible.

Solution

There’s an issue with the 4.1 version of System.Net.Http assembly. Downgrade to version 4.0.

Background

I’m working on a data migration project that involves moving encrypted data over to Always Encrypted columns. The first part of getting that up and running is to get the Always Encrypted SQL database driver up and running. I’ve worked a fair bit with the Azure Key Vault so was surprised to see the exception detailed above when first running my application. The stack trace wasn’t overly helpful:

   at Microsoft.Azure.Common.Platform.HttpTransportHandlerProvider.CreateHttpTransportHandler()
   at Hyak.Common.ServiceClient`1..ctor()
   at Microsoft.Azure.KeyVault.Internal.KeyVaultInternalClient..ctor()
   at Microsoft.Azure.KeyVault.Internal.KeyVaultInternalClient..ctor(KeyVaultCredential credentials)
   at Microsoft.Azure.KeyVault.KeyVaultClient..ctor(AuthenticationCallback authenticationCallback)
   at Microsoft.SqlServer.Management.AlwaysEncrypted.AzureKeyVaultProvider.SqlColumnEncryptionAzureKeyVaultProvider..ctor(AuthenticationCallback authenticationCallback) in d:\DsMain\DS_Main\Sql\mpu\shared\Security\AzureKeyVaultProvider\SqlColumnEncryptionAzureKeyVaultProvider.cs:line 56
   at AlwaysEncryptedKeyVaultIssue.Program.InitializeAzureKeyVaultProvider() in C:\Dev\AlwaysEncryptedKeyVaultIssue\AlwaysEncryptedKeyVaultIssue\Program.cs:line 21
   at AlwaysEncryptedKeyVaultIssue.Program.Main(String[] args) in C:\Dev\AlwaysEncryptedKeyVaultIssue\AlwaysEncryptedKeyVaultIssue\Program.cs:line 44
   at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
   at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
   at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()

After a fair bit of Googling, I eventually came across up a fairly fresh Microsoft Connect ticket detailing the issue.

The issue really is not obvious and hats off to ‘lucavgobbi’ for diagnosing the root cause! Luckily it’s quite simple to remedy, although there are a few caveats.

For the purpose of this post, I created a simple test project that demonstrates the issue. It’s very easy to create using the following steps.

  1. Create a new console application (targeting 4.6.1)
  2. Install the following 2 Nuget packages:
    • Install-Package Microsoft.SqlServer.Management.AlwaysEncrypted.AzureKeyVaultProvider
    • Install-Package Microsoft.IdentityModel.Clients.ActiveDirectory
  3. Copy this example Program.cs from the demo source.
  4. Run the app and watch it explode!

On running the application, you’ll be presented with the lovely exception from above. So what do?

As described in the Microsoft Connect issue, we need to switch the version of System.Net.Http from v4.1 to v4.0. It’s important to note that this dependency is added via Nuget as a dependency of which is the first gotcha. Run the following command to downgrade the package:

Update-Package System.Net.Http -Version 4.0.0

Run the application again and BOOM, it still fails… Ok…. The problem here lies in your app / web confg (App.config or web.config). An assembly binding redirect is added when the package is installed, redirecting to version 4.1.0, totally subverting our package downgrade. This is again simple to fix, change the dependency from this:

<dependentAssembly>
    <assemblyIdentity name="System.Net.Http" publicKeyToken="b03f5f7f11d50a3a" culture="neutral" />
    <bindingRedirect oldVersion="0.0.0.0-4.1.0.0" newVersion="4.1.0.0" />
</dependentAssembly>

To look like this:

<dependentAssembly>
    <assemblyIdentity name="System.Net.Http" publicKeyToken="b03f5f7f11d50a3a" culture="neutral" />
    <bindingRedirect oldVersion="0.0.0.0-4.1.0.0" newVersion="4.0.0.0" />
</dependentAssembly>

Now you’re good to go!

Browse the demo source code on GitHub.

Source Code for the application’s Program.cs:

namespace AlwaysEncryptedKeyVaultIssue
{
    using Microsoft.IdentityModel.Clients.ActiveDirectory;
    using Microsoft.SqlServer.Management.AlwaysEncrypted.AzureKeyVaultProvider;
    using System;
    using System.Collections.Generic;
    using System.Data.SqlClient;
    using System.Threading.Tasks;

    public class Program
    {
        private static ClientCredential clientCredential;

        static void Main(string[] args)
        {
            InitializeAzureKeyVaultProvider();
        }

        public static void InitializeAzureKeyVaultProvider()
        {
            var clientId = Guid.NewGuid().ToString();
            var clientSecret = "ITS_A_SECRET";

            clientCredential = new ClientCredential(clientId, clientSecret);

            var azureKeyVaultProvider = new SqlColumnEncryptionAzureKeyVaultProvider(GetToken);

            var providers = new Dictionary()
            {
                { SqlColumnEncryptionAzureKeyVaultProvider.ProviderName, azureKeyVaultProvider }
            };

            SqlConnection.RegisterColumnEncryptionKeyStoreProviders(providers);
        }

        public async static Task 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;
        }
    }
}

Azure Storage Explorer – Crash When Removing Account

The Azure Storage Explorer tool is great for when you need to work with an Azure Storage Account. I’m running version 6, preview 3 (6.0.3.1) and recently ran in to a problem.

Having deleted and re-created a storage account in a new subscription, I needed to update the access key in Storage Explorer. Pretty simple right? There’s certainly a missing ‘Manage Connections’ view, or perhaps there is and I wasn’t able to find it yet.

First you have to select the storage account to remove, wait for it to fail to connect, then click on the ‘Remove’ button. The trouble is that once you click on ‘Remove’ the app quits. Hmmm.

It turns out that the storage accounts are stored in a single file:

C:\Users\{UserName}\AppData\Roaming\Neudesic\AzureStorageExplorer\{Version}\AzureStorageExplorer6.dt1

NOTE: You should probably take a backup of this file before attempting to remove it / change it.

I didn’t have the time to figure out if I could edit this file to remove the troublesome string, so took the nuclear option and deleted it. On starting the app I was now back to an empty connection list! Superb! Whilst I had to add all of my account details in again, I was at least able to continue on my merry way.

If you have a neater way of handling this, please share it with me!

Durable Task Framework – Testing a TaskActivity

Problem

You’ve created an orchestration using the Service Bus Durable Task Framework that makes use of several tasks, but you want to be able to test out each task in isolation.

Solution

The Durable Task Test Framework provides you with a utility class that makes creating and executing a new orchestration with a single task painless and easy. Whilst this isn’t a unit test by any stretch of the imagination, the MSTest framework is perfect for running your tasks one by one and making sure everything is as it should be.

How it Works

Creating and configuring an orchestration is relatively simple, but involves quite a few lines of code. If you’re just testing a single TaskActivity, there’s no point in doing that over and over again, so I created a simple helper class to take care of the grunt work!
Continue reading

Durable Task Framework – Complex Types as Task Input Parameters

In my last post, Using Unity With The Durable Task Framework, I mentioned that I had originally overlooked the fact that the data passed to and from your TaskActivity objects in an orchestration are serialized and sent over the wire. If you’re using simple types then this isn’t a problem, but usually we want to be able to pass in something with a little more information.

Continue reading

Using Unity With The Durable Task Framework

The Durable Task Framework, created by¬†Abhishek Lal over at Microsoft, is fantastic. It allows you to leverage the Windows Azure Service Bus to create long running, durable workflows (orchestrations) comprised of one or more sub-tasks (activities) or even sub-workflows. If you’re not familiar with the framework, take a look at it first before continuing. The above link contains a download with some documentation and samples for using the framework.

It didn’t take long for me to get up and running with an orchestration and a few tasks, although I quickly realised that I had overlooked one key point. The input and output objects for each task are serialized and transmitted over the Azure Service Bus. Initially my inputs were complex types with a unity container reference so I could share a single instance of a unity container with all of my orchestrations and tasks. Serializing the unity container is out of the question, but it wasn’t obvious to me how I could use the framework to allow me to inject my unity container in to the orchestrations and tasks.

In this post, I present a solution to configuring your setup such that you can provide your task orchestrations and activities with whatever extra data you need.

Continue reading

Azure 1.8 Upgrade and WaIISHost.exe.config woes

Having finally upgraded my Azure solution to 1.8, I immediately encountered a crash when one of my web roles was starting. My unity configuration section had disappeared and could not be loaded! I hadn’t changed anything else so had no idea what was causing the problem. All the configs looked right and were in the right place. Looking at the config URI of the config host for the ConfigurationManager, it was pointing to ‘csxDebugrolesMvcWebRole1basex64WaIISHost.exe.config’.

Finally after looking at the path for the config file and checking the contents, I saw that it was just using a standard WaIISHost.exe.config! So something changed with 1.8. A quick search led me to this helpful blog post, detailing a workaround.

When running locally, to get any settings that you needed you would have to add them to a file called WaIISHost.exe.config and off you go. But this doesn’t work for 1.8. Instead you need to name your debug config file the same as the project it’s in (or at least the name of the generated assembly of that project). So if you’re creating an assembly called ‘MyWebRoleAssembly.dll’ then the config file should be ‘MyWebRoleAssembly.dll.config’.

Now the config URI is pointing to csxDebugrolesMvcWebRole1approotbinMvcWebRole1.dll.config. Much better!

Serving .JSON Files in Azure gives a 404

I just came across a problem when implementing some lovely localization in Javascript. Using the i18next library, it requires that all of your translations be stored in .json files. No problem! Everything runs fine locally (of course) but once I had pushed to my staging environment, those .json files couldn’t be found! When accessing the files directly, I was greeted with a 404. Because everything worked locally I knew the paths were just fine, so after a quick Google this looked to be a MIME issue. IIS didn’t know what to do with these static files so just threw up a 404.

The first solution was to add the missing MIME types to my web.config, like so:

<system.webServer>
  <staticContent>
    <remove fileExtension=".json" />
    <mimeMap fileExtension=".json" mimeType="application/json; charset=UTF-8" />
 </staticContent>
</system.webServer>

Redeploy, refresh. Now I had a bunch of errors with other scripts that were working fine, almost every script was throwing a 500 error. More Googling and cussing and publishing and refreshing and nothing. Eventually I spotted a small note on someone’s post on StackOverflow where they mentioned changing the BuildAction for the .json files to ‘Content‘ instead of ‘None’. I’m not sure what affect this has and will look in to it another time, but for now it solves my problem and hopefully yours too!