Friday, 26 August 2016

Auto publish Azure Web Jobs with ASP.Net Core RTM

This is an update of my original post on how to do this with ASP.Net Core RC1
At the time of this writing, there is no tooling to auto publish Azure Web Jobs with an ASP.Net Core website. You can do it with old-style websites, but not yet the new ones. The tooling is highly likely to appear eventually, but as of right now you have to take a few steps to set it up yourself, which is what I describe in this post. For clarity, what I am describing works only with source control deploy (git etc), not with publishing from Visual Studio.

This information will eventually become obsolete, probably around the RTM of the ASP.Net Core Visual Studio tooling. If that has happened and I have not updated the post, please post a comment and I'll get on it.
Note that for very simple, self-contained web jobs, there may be a simpler approach (see for some thoughts). In this post I am catering for the scenario where your webjob references another project in your solution - though it'll work just as well if it doesn't.

In summary

You have to use a custom deployment script and insert an action to publish your webjob to the path that Azure looks for webjobs in; App_Data/Jobs/Continuous for continuous jobs. Azure will automatically detect content in that folder and assume it's a webjob, so all we really have to do is make sure our webjob is copied there. And yes, it will happily overwrite a running webjob, the Kudu functionality handles that somehow.

The reason we are publishing, using dotnet publish, is to ensure we get all the dependencies. It won't re-compile so it's just a copy operation.
If we had a very simple webjob that was self contained, you could just copy the files, as long as you added a run.cmd (see link above).

In detail

Prepare a site
  1. Set up a solution with an ASP.Net 5 Web site and a webjob written as a ASP.Net 5 / Core console app.
  2. Set up source control deploy to an Azure website. It shouldn't matter which type.
  3. Wait for the initial deploy of the site. 

Set up a custom deployment script

In this example, we will download the deployment script that Azure has created. There are ways to also do this with the Azure CLI, but the generated script is not quite the same at this point in time - which may or may not matter.
  1. Get the auto generated script
    1. Log in to the web app console at https://[yoursite]
    2. Go to Tools - Download deployment script
    3. Unzip the downloaded zip file to the root of your solution folder
  2. Modify your deploy.cmd file in the following ways
    1. Add a line like this near the top - just to make it easier to check that your custom script is being used (whatever you put after echo will be output in the log file)
      echo CUSTOM SCRIPT Start
    2. Near the middle of the file you will find a series of steps that are numbered. One of the steps will look like this;
      NOTE: The indented lines will be on a single line, the line breaks are only added here for readability
      :: 2. Build and publish
      call :ExecuteCmd "%MSBUILD_PATH%" "%DEPLOYMENT_SOURCE%\MySolution.sln" 
    3. Below that insert these lines (updating the path with the actual path to your webjob in the solution)
      NOTE: The indented lines need to put on a single line, the line breaks are only here for readability
      :: 2.1 Publish webjobs
      call :ExecuteCmd dotnet publish 
               -o "%DEPLOYMENT_TEMP%\App_Data\Jobs\Continuous\MyWebJob" 
             -c Release
      If you have more than one web job, just add individual publish lines for each one

Check that it worked

Push your changes and wait for Azure to deploy, then look at the webjobs in the Azure portal. You should see your job there. In case you don't, have a look at the publish log file to see if there are any errors in there.

Thursday, 25 August 2016

InfoSec with SQL Azure

I've been using SQL Azure since 2011 and it's been a journey. One of the big problems I used to have was passing security audits from some of our clients; We deal with a  lot of data that is highly sensitive so are under a lot of scrutiny to make sure it is protected.

A lot of what you have to comply with when you start going into the areas of ISO27001 and PCI compliance is not just about technical security in the way we normally think about it, it’s just as much about people and processes. In my experience from filling in dozens of security questionnaires, I believe that SQL Azure can tick all the boxes, as long as you switch on the right features. Just to be clear, I'm not required to comply with PCI so can't vouch for that, but I do have to comply with a number of ISO27001 and Data Protection requirements.

Box ticking

Most security audits will ask you if your data is "encrypted at rest". If you understand how Azure really works and where  that requirement originally comes from, you'll know that this is a meaningless requirement when you're in Azure. But, just try to convince a security auditor of that. So go ahead and switch on Transparent Data Encryption  and you can tick that box on the questionnaire.
Incidentally, Azure also recently enabled you to do something similar for Blob storage - again mainly so you can tick the box.

Intrusion detection

SQL Azure has a neat Threat Detection facility to monitor the use of your database and alert you to anomalous behaviour. I've seen it detect potential SQL injection attacks and allegedly it will detect "unusual" behaviour, though I have yet to actually see that (thankfully :)).
This is  a useful feature to potentially detect both external and internal attacks (see below).
You have nothing to lose by switching it on.

Internal attacks

As a developer it's easy to think that once you pass penetration testing, your job is done. But, for the organisation, that's only half the battle. A proper security audit will look just as much at how you are preventing and detecting what they sometimes call "Internal Data Leakage". Basically, they are worried that people inside your organisation may access the data and leak it. This may be deliberately, it may be a result of social engineering or it may be that an external agency decided to hack a person who works for you. It's the modern equivalent of sneaking in through the kitchen entrance instead of taking a battering ram to the front gates. It's hardly surprising there is a lot of focus on this, given that most of the big attacks recently seems to have come from some kind of inside job.
You may well trust everyone in your organisation to not want to deliberately betray you and to be sensible about not having their laptops hacked - but an auditor won't, so you may as well buckle up and do the right thing. SQL Azure makes it surprisingly easy, though there are number of manual steps that could be made a lot easier with some better tooling, especially if you have many databases.

Step 1 - Give users individual logins

Your application probably uses a master username and password to log in to SQL. It's tempting to just give that to the people who need to go and look stuff up in the database, including developers, devops, support etc. But this means you have no way of knowing which actual person did what. Worse, when someone leaves the organisation you may not find it that easy to change the application's SQL Password.

You can create individual users in SQL Azure to give to the people who need access and I urge you to do so. You may want to give users read-only access while you are it. The tooling is lacking, in that you have to create the users using T-SQL from something like SQL Server Management Studio or similar; you can’t manage the users in the Azure Portal, which is quite an oversight IMO.

If you use Azure AD for authentication you can add Azure AD users to the SQL Databases. This has the benefit that user access will be automatically revoked from all databases when the user's AD Account is disabled - probably when they leave the organisation. It also means that, as of August 2016, you can use 2 Factor Authentication with the SQL logins - just download the latest version of SQL Server Management Studio 2016 and choose the Universal authentication method.
At NewOrbit, all user access to Azure and SQL  Azure is controlled through Azure AD, through our Office 365 subscription and all users are required to have 2FA enabled.

Step 2 - hide the sign-in details for the application

Once you have given users individual logins, you'll need to change the password the application uses to login to SQL. And you need to ensure it's not available to developers or others. You can do this by specifying things in the portal, if you use web apps. However, there are two other methods you may want to consider;

  • You can let your Application itself authenticate to SQL with Azure AD using a certificate - see the same link as above about using Azure AD with SQL Azure. This way there literally is no password and as long as you delete the certificate after uploading it, there is no way for anyone to log in to SQL as the application.
  • You could store the SQL password in Azure Key Vault.

Step 3 - audit all the things

SQL Azure has an Auditing facility whereby you can audit all queries made against the database. Given that you have assigned an individual user name to each of your people, you can (and should) audit exactly which queries each of your people have run - and Azure will even flag up queries that return an unusually large number of records. This is gold dust for complying with InfoSec requirements about preventing internal data leakage.

Step 4 - use data masking

SQL Azure has a facility to "mask" certain columns, such as email addresses, phone numbers and so on. I think the original design of this feature was as an aide to applications. But actually it's really useful in complying with InfoSec requirements about limiting access to sensitive data; You can set it up so your Application has normal access to all the data, but the logins your users have can automatically mask sensitive data. This means your users can write queries as normal and even see if data exists, but sensitive details that they don't need for troubleshooting will be masked in the output.
Do understand that this feature is not completely tamper proof; you can still search on the underlying data, so with patience you can triangulate your way to the real data - but then that will show up in the audit logs.


When properly configured, I believe SQL Azure offers a very compelling InfoSec story, one that should be able to satisfy most security audits you are likely to encounter.