This document will guide you through creating a SmartAssembly.settings file as part of the Azure build pipeline to establish a database connection for SmartAssembly.

Connecting SmartAssembly to a database will allow you to access information about each build, even after the build agent is discarded. It will also make it possible to store and access error and feature reports from multiple computers.

Prerequisites

Before following this instruction you should have SmartAssembly already installed in your Azure Pipeline.

Make sure to first read Using SmartAssembly with Azure Pipelines.

Prepare Azure SQL database

If you don't have it already, you need to configure an SQL Server on Azure.

You can also use any other SQL Server instance, as long as it allows for connections both from your local computer (for viewing error reports), and Azure Pipelines (to store details about each build).

Configuring Azure Pipelines

Option 1: Configure using the azure-pipelines.yml file

Securely storing database password

1. Sign-in to Azure DevOps: https://dev.azure.com/.
2. From the main screen choose a project you’re working on.
3. From the left-side menu choose Pipelines, then choose an existing pipeline and click Edit in the top-right corner.
4. Click Variables in the top-right corner, then a  button to add a new variable.
5. In the Name field type SA_DB_PASSWORD. This name will later be used to set the connection string without ever disclosing the password.
6. In the Value field provide your database password.
7. Check Keep this value secret to prevent the password to be shown in Azure GUI and build logs (it will be replaced with ***).
8. Click OK.

Set the connection string for SmartAssembly

Add the following task after previously added SmartAssembly installation task.

The task will create a SmartAssembly.settings file with your connection string.

- task: PowerShell@2
  displayName: 'SmartAssembly.settings'
  inputs:
    targetType: 'inline'
    script: |
      ########################
      $saDbHost = "YOUR_DATABASE_HOST"; # replace with host of your database server
      $saDbUser = "YOUR_DATABASE_USER"; # replace with your database username
      $saDbName = "SmartAssembly";      # database will be created if it doesn't exist
      ########################
      
      $saDbPass = "$(SA_DB_PASSWORD)";
      "<SmartAssembly.Settings><Options><Database ConnectionString='Server=$saDbHost;Initial Catalog=$saDbName;User ID=$saDbUser;Password=$saDbPass' Server='$saDbHost' /></Options></SmartAssembly.Settings>" | Out-File -FilePath "$($env:ProgramData)\Red Gate\SmartAssembly 7\SmartAssembly.settings" -Verbose

Option 2: Configure using the classic editor (without YAML)

Securely storing database password

1. Sign-in to Azure DevOps: https://dev.azure.com/.
2. From the main screen choose a project you’re working on.
3. From the left-side menu choose Pipelines, then choose an existing pipeline and click Edit in the top-right corner.
4. Go to Variables tab and make sure Pipeline variables tab is selected on the left-side menu.
5. Click Add button below the variables table.
6. In the Name column type SA_DB_PASSWORD. This name will later be used to set the connection string without ever disclosing the password.
7. In the Value column provide your database password.
8. Click on the padlock icon on the right side of the Value column to change variable type to secret, preventing the password to be shown in Azure GUI and build logs (it will be replaced with ***).
9. Click Save button located on a toolbar above the variables table.

Set the connection string for SmartAssembly

1. On the pipeline edit screen make go to Tasks tab.
2. From the list on the left-side of the screen select an agent job responsible for building your project.
3. Click on the  icon next to the agent job's name.
4. Search for PowerShell task, then click Add button. The task will now be added to the tasks list as PowerShell Script.
5. Drag the PowerShell Script task and drop it after previously added SmartAssembly installation task.
6. With the task selected, change its Name to SmartAssembly.settings.
7. Change the Type to Inline.

8. In the Script field, paste the following PowerShell script:

   ########################
   $saDbHost = "YOUR_DATABASE_HOST"; # replace with host of your database server
   $saDbUser = "YOUR_DATABASE_USER"; # replace with your database username
   $saDbName = "SmartAssembly";      # database will be created if it doesn't exist
   ########################
   
   $saDbPass = "$(SA_DB_PASSWORD)";
   "<SmartAssembly.Settings><Options><Database ConnectionString='Server=$saDbHost;Initial Catalog=$saDbName;User ID=$saDbUser;Password=$saDbPass' Server='$saDbHost' /></Options></SmartAssembly.Settings>" | Out-File -FilePath "$($env:ProgramData)\Red Gate\SmartAssembly 7\SmartAssembly.settings" -Verbose

9. Click Save button located on a toolbar above the tasks list.