Categories
Azure Microsoft SQL Database

Automatically generate a password for an Azure SQL database with ARM template

Do you really need to know your database password?

Creating an Azure SQL Database with ARM Template is a fairly common task. For that you’ll have to provide an admin login and password via the parameters of the ARM template. This task can be done by a single developer or in an automated flow via Continuous Deployment for example.

While a single developer may need to access the database with an application such as SQL Server Management Studio and has the need to know the database password for testing purposes, you could ask yourself if it’s relevant to save this password in an automated environment.

When a QA, Staging or Production environment is deployed who really as the need to access the database except your application? Does a person needs to access the database and know the password?

At the end when deploying to environments, you can ask yourself the following question:

Do you really need to know your database password?

Today we will discover how to automatically generate a password for an Azure SQL database via an Azure Resource Manager template. Our ARM template will be created in a new Azure Resource Group deployment project in Visual Studio.

 

Creation

Let’s declare the parameters of the ARM template:

{
  "$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "databaseCollation": {
      "type": "string",
      "defaultValue": "SQL_Latin1_General_CP1_CI_AS"
    },
    "databaseEdition": {
      "type": "string",
      "defaultValue": "Basic",
      "allowedValues": [
        "Basic",
        "Standard",
        "Premium"
      ]
    },
    "databaseMaxSizeBytes": {
      "type": "string",
      "defaultValue": "1073741824"
    },
    "databaseRequestedServiceObjectiveName": {
      "type": "string",
      "defaultValue": "Basic",
      "allowedValues": [
        "Basic",
        "S0",
        "S1",
        "S2",
        "P1",
        "P2",
        "P3"
      ],
      "metadata": {
        "description": "Describes the performance level for Edition"
      }
    }
  }
  ...
}
  • databaseCollation: the collation of the database, by default SQL_Latin1_General_CP1_CI_AS.
  • databaseEdition: the edition of the database, by default Basic.
  • databaseMaxSizeBytes: the size of database, by default 1GB.
  • databaseRequestedServiceObjectiveName: the pricing tier of the database, by default Basic.

 

Now we will declare the variables of the ARM template and here comes the interesting part:

{
  ...
  "variables": {
    "databaseName": "[concat('mydb', uniqueString(resourceGroup().id, 'E931CAC0-7259-4FA1-80B4-BD47CBA4E040'))]",
    "sqlserverAdminLogin": "[concat('l', uniqueString(resourceGroup().id, '9A08DDB9-95A1-495F-9263-D89738ED4205'))]",
    "sqlserverAdminPassword": "[concat('P', uniqueString(resourceGroup().id, '224F5A8B-51DB-46A3-A7C8-59B0DD584A41'), 'x', '!')]",
    "sqlserverName": "[concat('mysqlserver', uniqueString(resourceGroup().id))]"
  }
  ...
}

We can pay attention to several things here:

  • We use a lot the ARM template function uniqueString allowing us to create a deterministic hash string based on the values provided as parameters.
  • We are able to create a random database name, admin login, admin password, SQL server name.
  • The names will be unique for the resource group.
  • To make sure that we are compliant with the Azure SQL database policy “Your password must contain characters from three of the following categories – English uppercase letters, English lowercase letters, numbers (0-9), and non-alphanumeric characters (!, $, #, %, etc.)”, we insert one character for each category before and after the unique string.

 

And to finish we will declare the resources of the ARM template:

{
  ...
  "resources": [
    {
      "apiVersion": "2014-04-01-preview",
      "name": "[variables('sqlserverName')]",
      "type": "Microsoft.Sql/servers",
      "location": "[resourceGroup().location]",
      "properties": {
        "administratorLogin": "[variables('sqlserverAdminLogin')]",
        "administratorLoginPassword": "[variables('sqlserverAdminPassword')]"
      },
      "tags": {
        "displayName": "SqlServer"
      },
      "resources": [
        {
          "apiVersion": "2014-04-01-preview",
          "name": "[variables('databaseName')]",
          "type": "databases",
          "location": "[resourceGroup().location]",
          "dependsOn": [
            "[concat('Microsoft.Sql/servers/', variables('sqlserverName'))]"
          ],
          "properties": {
            "edition": "[parameters('databaseEdition')]",
            "collation": "[parameters('databaseCollation')]",
            "maxSizeBytes": "[parameters('databaseMaxSizeBytes')]",
            "requestedServiceObjectiveName": "[parameters('databaseRequestedServiceObjectiveName')]"
          },
          "tags": {
            "displayName": "Database"
          }
        },
        {
          "apiVersion": "2014-04-01-preview",
          "name": "AllowAllWindowsAzureIps",
          "type": "firewallrules",
          "location": "[resourceGroup().location]",
          "dependsOn": [
            "[resourceId('Microsoft.Sql/servers', variables('sqlserverName'))]"
          ],
          "properties": {
            "startIpAddress": "0.0.0.0",
            "endIpAddress": "0.0.0.0"
          }
        }
      ]
    }
  ]
  ...
}

Here nothing new as it is an usual way to declare a SQL Server and its database.

 

Example of use

The ARM template is now ready, let’s open a Windows PowerShell and try it:

.\Deploy-AzureResourceGroup.ps1 -ResourceGroupName 'MyResourceGroupName' -ResourceGroupLocation 'canadaeast' -TemplateFile '.\azuredeploy.json'

...

Resource Microsoft.Sql/servers/databases 'mysqlserverlugjlio5wdu64/mydbz3uejht5n74jk' provisioning status is succeeded

If everything goes well, you should see the same kind of message as above.

 

To go further

If you get the database connection string it will look like the following:

Data Source=tcp:'mysqlserverlugjlio5wdu64.database.windows.net,1433;Initial Catalog=mydbz3uejht5n74jk;User Id=lbkxzpxo3blcg4g@mysqlserverlugjlio5wdu64;Password=P77qwhg25nihlox!;

 

As no user needs to know the password, you could enforce the security by changing the password on every deployment using the deployment name in the ARM template function uniqueString. Change the password variable as following:

"sqlserverAdminPassword": "[concat('P', uniqueString(resourceGroup().id, deployment().name, '224F5A8B-51DB-46A3-A7C8-59B0DD584A41'), 'x', '!')]"

  

Summary

We have seen how to create an ARM template that will create an Azure SQL database with an automatically generated password.

  

Now you have a database, but you can’t access it as you don’t know the generated password. But again, you don’t to know this password while the web app you will deploy along with the database in the ARM template needs to know it. Well we will discover how together in my next article, stay tuned!

  

You can download the example solution here:

Download full sources

Or

Browse the GitHub repository

 

Please feel free to comment or contact me if you have any question about this article.

Categories
App Service Azure Microsoft WebJobs

Job Handlers and Dependency Injection in Azure WebJobs

In the series of articles about Microsoft Azure WebJob and Dependency Injection, we will learn how to process a function with a Job Handler.

In a previous article we discovered in an advanced scenario how to implement Dependency Injection and Dependency Scope per job in Azure WebJobs with Unity.

Today in this last article in the series of articles about Microsoft Azure WebJob, we will discover how to process a function using a Job Handler.

 

Creation

Let’s continue with the source code created in the previous article by creating the following interface:

using System.IO;
using System.Threading;
using System.Threading.Tasks;

namespace AzureWebJobs.JobActivatorUnity.Contracts
{
    public interface IQueueMessageJob
    {
        Task Process(CancellationToken ct, string message, TextWriter log);
    }
}

 

Now we will create the implementation of it:

using System;
using System.IO;
using System.Threading;
using System.Threading.Tasks;
using AzureWebJobs.JobActivatorUnity.Contracts;

namespace AzureWebJobs.JobActivatorUnity.Handlers
{
    public sealed class QueueMessageJobHandler : IQueueMessageJob
    {
        private readonly INumberService numberService;
        private readonly IUnitOfWork unitOfWork;

        public QueueMessageJobHandler(INumberService numberService, IUnitOfWork unitOfWork)
        {
            if (numberService == null) throw new ArgumentNullException(nameof(numberService));
            if (unitOfWork == null) throw new ArgumentNullException(nameof(unitOfWork));

            this.numberService = numberService;
            this.unitOfWork = unitOfWork;
        }

        public async Task Process(CancellationToken ct, string message, TextWriter log)
        {
            Console.WriteLine("Beginning QueueMessageJobHandler work...");

            log.WriteLine("New random number {0} from number service for message: {1}", this.numberService.GetRandomNumber(), message);

            await this.unitOfWork.DoWork(ct, message);

            Console.WriteLine("Finishing QueueMessageJobHandler work...");
        }
    }
}

As you can notice we moved the code previously in the function to the Job Handler process method.

  

Example of use

We have created the QueueMessageJobHandler and we will learn how to use it.

  

First we will register it in the Unity container:

using System;
using AzureWebJobs.JobActivatorUnity.Contracts;
using AzureWebJobs.JobActivatorUnity.Dependencies;
using AzureWebJobs.JobActivatorUnity.Handlers;
using AzureWebJobs.JobActivatorUnity.Services;
using AzureWebJobs.JobActivatorUnity.Unity;
using Microsoft.Practices.Unity;

namespace AzureWebJobs.JobActivatorUnity
{
    public class UnityConfig
    {
        #region Unity Container
        private static Lazy<IUnityContainer> container = new Lazy<IUnityContainer>(() =>
        {
            var container = new UnityContainer();
            RegisterTypes(container);
            return container;
        });

        /// <summary>
        /// Gets the configured Unity container.
        /// </summary>
        public static IUnityContainer GetConfiguredContainer()
        {
            return container.Value;
        }
        #endregion

        /// <summary>Registers the type mappings with the Unity container.</summary>
        /// <param name="container">The unity container to configure.</param>
        public static void RegisterTypes(IUnityContainer container)
        {
            container.RegisterType<IJobActivatorDependencyResolver, UnityJobActivatorHierarchicalDependencyResolver>(new ContainerControlledLifetimeManager(), new InjectionConstructor(container.CreateChildContainer()));
            container.RegisterType<INumberService, NumberService>(new ContainerControlledLifetimeManager());
            container.RegisterType<IQueueMessageJob, QueueMessageJobHandler>(new HierarchicalLifetimeManager());
            container.RegisterType<IUnitOfWork, DisposableService>(new HierarchicalLifetimeManager());
        }
    }
}

Here we register the Job Handler along with the other services.

  

In our job function we can now use the IQueueMessageJob:

using System;
using System.IO;
using System.Threading;
using System.Threading.Tasks;
using AzureWebJobs.JobActivatorUnity.Contracts;
using AzureWebJobs.JobActivatorUnity.Dependencies;
using Microsoft.Azure.WebJobs;

namespace AzureWebJobs.JobActivatorUnity
{
    public class Functions
    {
        private readonly IJobActivatorDependencyResolver jobActivatorDependencyResolver;

        public Functions(IJobActivatorDependencyResolver jobActivatorDependencyResolver)
        {
            if (jobActivatorDependencyResolver == null) throw new ArgumentNullException(nameof(jobActivatorDependencyResolver));

            this.jobActivatorDependencyResolver = jobActivatorDependencyResolver;
        }

        // This function will get triggered/executed when a new message is written 
        // on an Azure Queue called queue.
        public async Task ProcessQueueMessage([QueueTrigger("queue")] string message, TextWriter log, CancellationToken ct)
        {
            using (var scope = this.jobActivatorDependencyResolver.BeginScope())
            {
                await scope.CreateInstance<IQueueMessageJob>().Process(ct, message, log);
            }
        }
    }
}

We can pay attention to several things here:

  • The function is simplified and all the work is now done in the Job Handler.
  • The job is processed in its own scope.
  • We call CreateInstance<IQueueMessageJob>() once, all the dependencies will be automatically injected by Unity in the implementation.
  • All the instances created inside the scope will be disposed immediately before the function ends.

 

To go further

If you test the source code, the console output will be the following when the function is triggered:

Executing: 'Functions.ProcessQueueMessage' - Reason: 'New queue message detected on 'queue'.'
Beginning QueueMessageJobHandler work...
DisposableService doing work...
Finishing QueueMessageJobHandler work...
DisposableService disposing...
Executed: 'Functions.ProcessQueueMessage' (Succeeded)

Right after the Job Handler work is done, the disposable service is disposed.

 

Summary

We have seen how to create a Job Handler for a Microsoft Azure WebJob function which completes the series of articles about Microsoft Azure WebJob and Dependency Injection.

 

You can download the example solution here:

Download full sources

Or

Browse the GitHub repository

(Note that the project uses Microsoft.Azure.WebJobs version 1.1.2)

 

Please feel free to comment or contact me if you have any question about this article.