A BICEP file to deploy Snipe IT to Azure - see the comments below for usage details

Readme.md

This GIST shows getting Snipe-IT running on Azure using a Docker container.

Notes:

• This is a simple configuration to get it working, it can obviously get a lot more complex with the setup of VNETs etc.
• This post documents the manual process, best practice and the next step will be to get it all automated with BICEP/ARM template - For an example of this see this GiST

Create an Azure MySQL PaaS instance

1. Open the Azure Portal
2. Create a new 'Azure Database for MySQL flexible server' in a new resource group
   • Provide a name for the instance
   • Set your region
   • Workload Type - for this test I use the lowest 'for development or hobby projects'
   • Set the MySQL username and password
   • For networking pick 'allow public access' and 'allow public access for any Azure service'

   You can add your Client IP address to the firewall rules if you want to be able to connect to the DB from your local machine, but this is not essential. I had enabled this to do some testing with a locally hosted Docker instance.

3. When the instance is created open it in the Azure Portal
4. Go to the Networking tab and download the SSL certificate, follow the documented process to download the certificate and convert it to the PEM format. You need the file namaed DigiCertGlobalRootCA.crt.pem
5. Go to the Database tab and create a new empty DB snipe-it

Create an Azure Storage Account

1. Open the Azure Portal
2. Create a new 'Storage Account' in same resource group as used for the MySQL
   • Provide a name for the instance
   • Set your region
   • For networking pick 'allow public access'
3. When the resource is created open it in the Azure Portal
4. In storage explorer create a new File Share called snipeit
5. Upload the SSL Cert DigiCertGlobalRootCA.crt.pem to this share
6. In storage explorer create a new File Share called snipeit-logs

Create an Azure Web App

1. Open the Azure Portal

2. Create a new 'Web App' in same resource group as used for the MySQL

   • Provide a name for the instance
   • Pick the publish type to be Docker Container
   • Set your region
   • Create a pricing tier, I used a Linux Basic B1 for this test
   • For Docker settings I picked the follow (though we override these later with a compose file)
     • Single Container
     • Docker Hub
     • With the image name snipe/snipe-it:latest

3. When the resource is created open it in the Azure Portal

4. In the configuration Path Mappings I added a new Azure Storage Mount for the cert and other local storage

   • Name - snipeit
   • Mount Path - /var/lib/snipeit
   • Type - Azure Files using the previously created file share

5. In the configuration Path Mappings I added a new Azure Storage Mount for the logs

   • Name - snipeit-logs
   • Mount Path - /var/www/html/storage/logs
   • Type - Azure Files using the previously created file share

6. In the configuration Application Settings I added the following new Application Settings

   • MYSQL_DATABASE - snipeit matching the MySQL DB name
   • MYSQL_USER to the username for the MySQL instance
   • MYSQL_PASSWORD to the password for the MySQL instance
   • DB_CONNECTION to mysql
   • MYSQL_PORT_3306_TCP_ADDR to the name of the MySQL instance <my-instance>.mysql.database.azure.com
   • MYSQL_PORT_3306_TCP_PORT to 3306
   • DB_SSL_IS_PAAS to true
   • DB_SSL to true
   • DB_SSL_CA_PATH to /var/lib/snipeit/DigiCertGlobalRootCA.crt.pem matching the path to the SSL cert
   • APP_URL to the URL of the Web App https://<my-instance>.azurewebsites.net
   • APP_KEY to a unique ID in the form base64:6M3RwWh4re1FQGMTent3hON9D7ZJJDHxW1123456789=. If you don't set this and start the container, whilst watching the log stream, you will see the new key generated which you can use
   • MAIL_DRIVER to smtp
   • MAIL_ENV_ENCRYPTION to tcp
   • MAIL_PORT_587_TCP_ADDR to smtp.sendgrid.net
   • MAIL_PORT_587_TCP_PORT to 587
   • MAIL_ENV_USERNAME to apikey
   • MAIL_ENV_PASSWORD your SendGrid API Key
   • MAIL_ENV_FROM_ADDR to the email SNipe IT notifications should come from
   • MAIL_ENV_FROM_NAME to Snipe IT or whatever you want the email to be from
   • You can also set the APP_DEBUG to true or false. If true this means more detailed error messages are shown in the Snipe-IT UI that do not appear in the log stream

7. In the deployment center I picked Docker Compose and provided the follow config to mount the storage

   version: "3"
   
   services:
     snipe-it:
       image: snipe/snipe-it:latest
       volumes:
         - snipeit:/var/lib/snipeit
         - snipeit-logs:/var/www/html/storage/logs
   
   volumes:
     snipeit:
       external: true
     snipeit-logs:
       external: true

8. Restart your Web App

9. And that should be it, the container should start and you should be able to access the Snipe-IT UI based setup Wizard via the URL of the Web App, as per the product documentation

Comments & Tips

MySQL SSL Certificate

In my case my initial problems were down to the MySQL certificate. A mixture of initially not setting the environment variable, then setting the wrong one and finally not having correctly mounted the storage to present the file. The problem was in all cases you get the same unhelpful error message in the Snipe-IT UI

SQLSTATE[HY000] [2002]  (trying to connect via (null)) (SQL: select * from information_schema.tables where table_schema = snipeit and table_name = migrations and table_type = 'BASE TABLE')

... and there was nothing more useful in the Web App Log Stream (the container output). So I had to work out what was wrong by trial and error until I set setting APP_DEBUG to true. After which I started to see more useful error messages about invalid file paths in the UI.

MySQL Initial Migration

I also wasted time trying to get the initial DB creation migrations to work. I was getting the following error in the UI when the container was trying to create the DB Tables

Note: It appears that the tables are actually being created when the container starts, not when the button is pressed. The create tables button seems more of a checking tool.

SQLSTATE[42000] Syntax error or access violation 1068 Multiple primary key defined

If I used the MySQL Workbench to connect to the MySQL instance I could see that a few tables had been created, not not the complete set.

After much trial and error, and manually comparing the setup of two MySQL instances, the fix was to set the following MySQL Server Parameter in the Azure Portal to OFF

• sql_generate_invisible_primary_key

After setting this parameter to OFF you need to delete the incorrectly created database, create a new empty database of the same name and rerun DB migration.

I have no idea why my first Azure MySQL instance had these values set to OFF and my other instances had them set to on ON. I guess I am lucky at least one was set to OFF so I could work out what was wrong.

Logs Files

The best place to check for logs is in the Web App Log Stream, this is where you will see the container output.

Also you have the same information the laravel.logs file created in an Azure File Share, so you can look at these to see if there are any errors.

DockerCompose.yml

version: "3"

services:
   snipe-it:
       image: snipe/snipe-it:latest
       volumes:
          - snipeit:/var/lib/snipeit
          - snipeit-logs:/var/www/html/storage/logs

volumes:
    snipeit:
       external: true
    snipeit-logs:
       external: true

SnipeIT.bicep

param SnipeITStorageAccount_name string
param SnipeITMySQLServer_name string
param SnipeITDB_name string
param SnipeITServerFarm_name string
param SnipeITWebsite_name string
param SnipeITMySQLLogin string
@secure()
param SnipeITMySQLPassword string
param SnipeITAppKey string
param SendGridAPIKey string

var dockerFile = loadFileAsBase64('./DockerCompose.yml')
var DockerComposeString = 'COMPOSE|${dockerFile}'
var azFileSettings = true

// Deployment of the storage account
resource SnipeITStorageAccount 'Microsoft.Storage/storageAccounts@2022-09-01' = {
    name: SnipeITStorageAccount_name
    location: resourceGroup().location
    tags: {
        Project: 'SnipeIT'
    }
    sku: {
        name: 'Standard_LRS'
    }
    kind: 'StorageV2'
    properties: {
        minimumTlsVersion: 'TLS1_2'
        allowBlobPublicAccess: true
        allowSharedKeyAccess: true
        largeFileSharesState: 'Enabled'
        networkAcls: {
            bypass: 'AzureServices'
            virtualNetworkRules: []
            ipRules: []
            defaultAction: 'Allow'
        }
        supportsHttpsTrafficOnly: true
        encryption: {
            services: {
                file: {
                    keyType: 'Account'
                    enabled: true
                }
                blob: {
                    keyType: 'Account'
                    enabled: true
                }
            }
            keySource: 'Microsoft.Storage'
        }
        accessTier: 'Hot'
    }

    // Nested deployments implicitly depend on parent resource

    // Nested deployment of file services for the storage account
    resource SnipeITStorageAccountFileServices 'fileServices' = {
        name: 'default'
        // Nested deployment of specific file shares
        // SSL cert and app data file share
        resource SnipeITCertFileShare 'shares' = {
            name: 'snipeit'
            properties: {
                accessTier: 'TransactionOptimized'
                shareQuota: 102400
                enabledProtocols: 'SMB'
            }
        }
        // Logs file share
        resource SnipeITLogsFileShare 'shares' = {
            name: 'snipeit-logs'
            properties: {
                accessTier: 'TransactionOptimized'
                shareQuota: 102400
                enabledProtocols: 'SMB'
            }
        }
    }
}

resource SnipeITMySQLServer 'Microsoft.DBforMySQL/flexibleServers@2022-09-30-preview' = {
    name: SnipeITMySQLServer_name
    location: resourceGroup().location
    tags: {
        Project: 'SnipeIT'
    }
    sku: {
        name: 'Standard_B1s'
        tier: 'Burstable'
    }
    properties: {
        administratorLogin: SnipeITMySQLLogin
        administratorLoginPassword: SnipeITMySQLPassword
        storage: {
          storageSizeGB: 20
          iops: 360
          autoGrow: 'Enabled'
          autoIoScaling: 'Enabled'
        }
        version: '8.0.21'
        backup: {
            backupRetentionDays: 7
            geoRedundantBackup: 'Disabled'
        }
        replicationRole: 'None'
        network: {
            publicNetworkAccess: 'Enabled'
        }
    }
}

// MySql settings required else the initial migration fails
resource MySqlServer_innodb_buffer_pool_dump_at_shutdown 'Microsoft.DBforMySQL/flexibleServers/configurations@2022-01-01' = {
  parent: SnipeITMySQLServer
  name: 'innodb_buffer_pool_dump_at_shutdown'
  properties: {
    value: 'OFF'
  }
}

resource MySqlServer_innodb_buffer_pool_load_at_startup 'Microsoft.DBforMySQL/flexibleServers/configurations@2022-01-01' = {
  parent: SnipeITMySQLServer
  name: 'innodb_buffer_pool_load_at_startup'
  properties: {
    value: 'OFF'
  }
}

resource MySqlServer_sql_generate_invisible_primary_key 'Microsoft.DBforMySQL/flexibleServers/configurations@2022-01-01' = {
  parent: SnipeITMySQLServer
  name: 'sql_generate_invisible_primary_key'
  properties: {
    value: 'OFF'
  }
}

resource MySqlServerFirewallRules_AzureIps 'Microsoft.DBforMySQL/flexibleServers/firewallRules@2022-01-01' = {
    name: 'AllowAllWindowsAzureIps'
    parent: SnipeITMySQLServer
    properties: {
        startIpAddress: '0.0.0.0'
        endIpAddress: '0.0.0.0'
    }
}

//Deployment of the server farm
resource SnipeITServerFarm 'Microsoft.Web/serverfarms@2022-03-01' = {
    name: SnipeITServerFarm_name
    location: resourceGroup().location
    tags: {
        Project: 'SnipeIT'
    }
    sku: {
        name: 'B1'
        tier: 'Basic'
        size: 'B1'
        family: 'B'
        capacity: 1
    }
    kind: 'linux'
    properties: {
        reserved: true
    }
}

// SQL Database deployment
resource SnipeITDatabase 'Microsoft.DBforMySQL/flexibleServers/databases@2022-01-01' = {
    name: SnipeITDB_name
    parent: SnipeITMySQLServer
    location: resourceGroup().location
    properties: {
        charset: 'utf8mb4'
        collation: 'utf8mb4_general_ci'
    }
}

resource SonarQubeWebSite 'Microsoft.Web/sites@2022-09-01' = {
    name: SnipeITWebsite_name
    location: resourceGroup().location
    dependsOn: [
        SnipeITDatabase
        SnipeITStorageAccount
    ]
    tags: {
        Project: 'SnipeIT'
    }
    kind: 'app,linux,container'
    properties: {
        enabled: true
        serverFarmId: SnipeITServerFarm.id
        siteConfig: {
            appCommandLine: ''
            linuxFxVersion: DockerComposeString
            acrUseManagedIdentityCreds: false
            alwaysOn: true
            scmType: 'None'
        }
    }
    resource SnipeITWebsiteAppSettings 'config@2021-02-01' = {
        name: 'appsettings'
        properties: {
            APP_DEBUG: 'false'
            APP_KEY: SnipeITAppKey
            APP_URL: 'https://${SnipeITWebsite_name}.azurewebsites.net'
            DB_CONNECTION: 'mysql'
            DB_SSL: 'true'
            DB_SSL_IS_PAAS: 'true'
            DB_SSL_CA_PATH: '/var/lib/snipeit/DigiCertGlobalRootCA.crt.pem'
            MYSQL_DATABASE: SnipeITDB_name
            MYSQL_USER: SnipeITMySQLLogin
            MYSQL_PASSWORD: SnipeITMySQLPassword

            MYSQL_PORT_3306_TCP_ADDR: '${SnipeITMySQLServer_name}.mysql.database.azure.com'
            MYSQL_PORT_3306_TCP_PORT: '3306'

            DOCKER_REGISTRY_SERVER_URL: 'https://index.docker.io/'
            DOCKER_REGISTRY_SERVER_USERNAME: ''
            DOCKER_REGISTRY_SERVER_PASSWORD: ''
            WEBSITES_ENABLE_APP_SERVICE_STORAGE: 'false'
            
            MAIL_DRIVER: 'smtp'
            MAIL_ENV_ENCRYPTION: 'tcp'
            MAIL_PORT_587_TCP_ADDR: 'smtp.sendgrid.net'
            MAIL_PORT_587_TCP_PORT: '587' 
            MAIL_ENV_USERNAME: 'apikey' 
            MAIL_ENV_PASSWORD: SendGridAPIKey
            MAIL_ENV_FROM_ADDR: 'alerts@mydomain.com'
            MAIL_ENV_FROM_NAME: 'Snipe IT'
        }
    }
    resource SnipeITWebsiteConfig 'config@2021-02-01' = if (azFileSettings == true) {
        name: 'web'
        properties: {
            azureStorageAccounts: {
                'snipeit': {
                    type: 'AzureFiles'
                    accountName: SnipeITStorageAccount.name
                    shareName: 'snipeit'
                    mountPath: '/var/lib/snipeit'
                    accessKey: SnipeITStorageAccount.listKeys().keys[0].value
                }
                'snipeit-logs': {
                    type: 'AzureFiles'
                    accountName: SnipeITStorageAccount.name
                    shareName: 'snipeit-logs'
                    mountPath: '/var/www/html/storage/logs'
                    accessKey: SnipeITStorageAccount.listKeys().keys[0].value
                }

            }
        }
    }
}

Strange, I had not seen these problems, but great that the Docker compose file got it sorted for you.

A factor maybe, in our instances we let the Web App use it's default URL and provide a 'real domain name' via Frontdoor

Thanks, Richard. Curious what your use-case is for Frontdoor?

The key reason we use Frontdoor as it allows us to centralise the management of the publicly accessible Azure hosted systems. Specifically the domain are managed in Frontdoor not on each separate service and it also automatically manages short lived SSL Certificates.

Might be important to others, but it is a bonus for us that it also provides global load balancing and CDN caching.

So the quick answer is easier admin

Whether I build the solution manually or try to use your Bicep template (which deployed perfectly), I cannot get past this DB error. I get the same error message on all types of installs and I cannot figure out what is the root cause.

I know that the UN/PW are correct for MySQL. I have the certificate uploaded. The only difference I notice is that the Certificate is in CER format instead of PEM when I download it from Microsoft. Do I need to encode the certificate in a different way?

I am no expert on Docker or SnipeIT, all the notes I took when setting up our instance are in this GIST and this blog post

Looking back at these notes, I would tend to agree that the certificate format would appear to be the most likely issue.

Do you need to convert the format as documented in the Azure MySQL Download SSL documentation?

openssl x509 -in certificate.crt -out certificate.pem -outform PEM

Yep. That was it. Needed the certificate converted to pem. Might be a good note to add to the guide and GIST. Thanks for your help!

Great glad you are unblocked, in my defense I think the way you export the certificate has changed since I wrote the post. I will update this GIST and supporting notes as you suggested