SQL Enlight for Build Machines - Setup and Configuration Guide

Introduction

SQL Enlight for Build Machines is a dedicated edition designed to execute static code analysis in automated build pipelines and Continuous Integration (CI) environments. Unlike the developer edition, it operates entirely via the command-line interface (CLI) and does not require a user interface, making it ideal for headless build agents.

Prerequisites

Before setting up SQL Enlight for Build Machines, ensure your environment meets the following requirements:

• Operating System: Windows OS is required.
• SQL Enlight Binaries: The SQL Enlight for Build Machines binaries must be unzipped and available on the build agent machines.
• Valid License: Each build machine running SQL Enlight requires a valid license for SQL Enlight for Build Machines.
• Required CI/CD Plugins (e.g., Jenkins):
  • JUnit Plugin: For publishing test results and integrating analysis violations (usually pre-installed).
  • HTML Publisher Plugin: For publishing and viewing HTML reports within the CI interface.

Installation Process

Unlike SQL Enlight for Developers, the Build Machines edition does not have a traditional installer. Installation is straightforward:

1. Download: Obtain the SQL Enlight for Build Machines archive (e.g., sqlenlight-cicd_1.1.10.27.zip).
2. Unzip: Extract the contents of the archive.
3. Copy: Copy the extracted folder to a permanent location on your build machine (e.g., C:\sqlenlight-cicd_1.1.10.27\).
4. Repeat: Perform these steps on any additional build machines (e.g., Build Machine 1 and Build Machine 2).

The main executable will be located in the Cli subfolder (e.g., EnlightForCI.exe ).

License Activation and Configuration

Each build machine must have its license activated locally. The license activation process generates an activation response file (or license data), which is then reused during analysis runs.

Activate the License on Build Machine 1 & 2

On each build machine, open a command prompt and navigate to the SQL Enlight CLI folder. Run the licenseActivate command.

You can either activate online directly or generate a request file for email activation if the machine is offline.

Online Activation:

Enlight licenseActivate --licenseKey=XXXX-XXXX-XXXX-XXXX

Generate Activation Request File (Offline):

Enlight licenseActivate --licenseKey=XXXX-XXXX-XXXX-XXXX --requestOutputFile="C:\path\to\activation-request.xml"

Note: Email the generated activation-request.xml to Yubitsoft to receive your activation response file.

Generate Activation Response File (Online):

Enlight licenseActivate --licenseKey=XXXX-XXXX-XXXX-XXXX --requestOutputFile="C:\sqlenlight-cicd\license\activationResponse.txt"

Use the License Data in CI/CD

During analysis via the CLI, you must pass the generated activation response file path using the --licenseData parameter (specific to the Build Machines edition). This ensures proper authorization during automated builds.

Enlight analyzeFiles --licenseData="C:\sqlenlight-cicd\license\activationResponse.txt" [other parameters...]

Key CLI Commands

SQL Enlight for Build Machines is controlled entirely through CLI commands. Below are the primary commands and their parameters.

AnalyzeFiles

Runs analysis of T-SQL script files and generates a report.

Usage:

Enlight analyzeFiles
      --inputPath=<paths>
      --licenseData=<license data or activation response file path>
    [ --reportOutput=<reportPath>]
    [ --reportStylesheet=<stylesheetpath>]
    [ --contextFile=<contextfilePath>]
    [ --templateFile=<analysisTemplateFile>]
    [ --exitCodeOnViolations= <exitCode>]
    [ --qualityGate=<qualityGatePath>]
    [ --failOn=QualityGateFail|QualityGateFailOrWarn|AnyIssue]
    [ ... additional parameters ]

Build Machines Specific Parameters:

Parameter	Description
licenseData	Required. License data or activation response file path.
exitCodeOnViolations	Returns a custom exit code if analysis rule violations are found. Useful for failing builds.
reportPerTarget	Creates a separate report file for each analyzed file or SQL module.
demoMode	Runs analysis in a demo mode with limited results.

AnalyzeServer

Analyzes SQL Server stored procedures, functions, triggers, and other database objects directly.

Usage:

Enlight analyzeServer
      --objectName=<objectName>
      --objectType=<objectType>
      --connectionString=<connectionString> | --server=<servername> --database=<databasename>
      --licenseData=<license data or activation response file path>
    [ --reportOutput=<reportPath>]
    [ --exitCodeOnViolations= <exitCode>]
    [ --qualityGate=<qualityGatePath>]
    [ --failOn=QualityGateFail|QualityGateFailOrWarn|AnyIssue]

AnalysisContext

Generates and outputs the context information (metadata) of a given database to a file. This file can be reused in subsequent analyzeFiles or analyzeServer runs for faster execution.

Usage:

Enlight analysisContext
        --includeDatabases=<list of databases names>
        --outputFile=<outputFile>
        --connectionString=<connectionString> | --server=<servername> --database=<databaseName>

Report

Transforms an existing XML analysis report by applying an XSLT stylesheet. Ideal for converting raw XML results into HTML, JUNIT, CSV, or VSTESTS formats.

Usage:

Enlight report
        --reportXmlFile=<reportFilePath>
        --reportStyleSheet=<reportStyleSheetPath> (HTML, JSON, JUNIT, CSV, VSTESTS)
        --reportOutput=<reportOutputFile>

Analysis Settings and Quality Gates

Quality Gates

Quality Gates provide a configurable way to enforce SQL coding standards by combining metrics, policies, and weights. The gate evaluates the analysis and produces an overall quality score (A-F grade) and a Pass/Warn/Fail status.

CLI Integration: You can specify a Quality Gate definition file (JSON or XML) and control failure conditions:

Enlight analyzeFiles
        --inputPath="E:\MyProject\SQL Script\*.sql;"
        --qualityGate="E:\SqlEnlight-Settings\MyGate.json"
        --failOn=QualityGateFail

• QualityGateFail: Fails the build only if the gate fails.
• QualityGateFailOrWarn: Fails the build on warning or failure.
• AnyIssue: Fails the build if any analysis issue is found.

Configuring Analysis Context

You can control the level of detail loaded from the database via the analysis context.

• Basic Mode: Loads commonly used schema information (tables, views, triggers, indexes, etc.).
• Full Mode: Loads all basic schema information plus additional objects (Service Queues, Routes, Certificates, Extended Properties).

Whitelists

To exclude certain databases, schemas, objects, or file paths from the analysis, configure a whitelist. Whitelist entries use regular expressions to match names and paths. You can pass a whitelist file using the --whitelistFile parameter.

Target Repository Configuration

To make your SQL Enlight analysis portable and consistent across different environments, it is highly recommended to store your configuration files within your source control repository. This ensures that all team members and CI/CD pipelines use the same analysis rules, quality gates, and context.

1. Add a .sqlenlight Folder

Create a new folder named .sqlenlight at the root of your target database repository. This folder will serve as the central location for all SQL Enlight configuration and generated artifacts.

/YourDatabaseProject
  ├── .sqlenlight/
  ├── Scripts/
  └── ...

2. Generate Analysis Context

If your repository targets a specific database, generating an analysis context file can significantly speed up the analyzeFiles execution by caching the database metadata.

Use the SQL Enlight CLI analysisContext command to generate this file from your target database, and save it directly into the .sqlenlight folder.

Usage:

C:\sqlenlight-cicd\Cli\EnlightForCI.exe analysisContext ^
  --server="YourServerName" ^
  --database="YourDatabaseName" ^
  --outputFile=".\.sqlenlight\analysis-context.xml"

Documentation Reference: You can learn more about the analysisContext command and its parameters in the Command Line Tool - AnalysisContext Documentation.

3. Configure Analysis Template

An Analysis Template allows you to enable or disable specific rules, adjust rule parameters, and add custom rules tailored to your project’s standards.

1. Open the SQL Enlight For Developers extension in SQL Server Management Studio (SSMS) or Visual Studio.
2. Navigate to the SQL Enlight Options/Settings to configure your analysis rules.
3. Once configured, export the Analysis Template to an XML file.
4. Save the exported XML file into the .sqlenlight folder in your repository (e.g., .\.sqlenlight\analysis-template.xml).

Note: If you do not have a license for SQL Enlight For Developers, you can use the trial version to perform this configuration. Documentation Reference: For information on configuring analysis rules and exporting analysis templates, refer to the Manage Analysis Rules Documentation and Analysis Template Import/Export Documentation.

4. Configure Quality Gate

Quality Gates enforce minimum quality standards by producing an overall quality score and a Pass/Warn/Fail status based on rule violations.

1. Open SQL Enlight For Developers in SSMS or Visual Studio.
2. Access the Quality Gate configuration settings.
3. Define your metrics, policies, and weights to meet your project’s quality standards.
4. Export the Quality Gate definition to a JSON or XML file.
5. Save the exported file into the .sqlenlight folder in your repository (e.g., .\.sqlenlight\quality-gate.json).

Note: The trial version of SQL Enlight For Developers can also be used for configuring Quality Gates. Documentation Reference: For more information on setting up Quality Gates, see the Quality Gates and Quality Gate Configuration.

Using the Configuration in CI/CD

Once your repository is set up with the .sqlenlight folder and the necessary configuration files, you can easily reference them in your CI/CD pipeline commands:

C:\sqlenlight-cicd\Cli\EnlightForCI.exe analyzeFiles ^
  --licenseData="C:\sqlenlight-cicd\license\activationResponse.txt" ^
  --inputPath="C:\Source\Database\**\*.sql" ^
  --contextFile="C:\Source\Database\.sqlenlight\analysis-context.xml" ^
  --templateFile="C:\Source\Database\.sqlenlight\analysis-template.xml" ^
  --qualityGate="C:\Source\Database\.sqlenlight\quality-gate.json" ^
  --failOn=QualityGateFail ^
  --reportOutput="%WORKSPACE%\SqLEnlightAnalysisReport.xml"

CI/CD Integration (Jenkins Example)

Integrating SQL Enlight with Jenkins is achieved using standard Windows batch command build steps.

Step 1: Run Analysis and Generate XML Report

Add an “Execute Windows batch command” step in your Jenkins job:

C:\sqlenlight-cicd\Cli\EnlightForCI.exe analyzeFiles ^
  --licenseData="C:\sqlenlight-cicd\license\activationResponse.txt" ^
  --inputPath="C:\Source\Database\**\*.sql" ^
  --contextFile="C:\Source\Database\.sqlenlight\analysis-context.xml" ^
  --templateFile="C:\Source\Database\.sqlenlight\analysis-template.xml" ^
  --qualityGate="C:\Source\Database\.sqlenlight\quality-gate.json" ^
  --failOn=QualityGateFail ^
  --reportOutput="%WORKSPACE%\SqLEnlightAnalysisReport.xml" ^
  --exitCodeOnViolation=5555

Note: If the ERRORLEVEL (exit code) matches 5555, Jenkins will mark the build as unstable or failed.

Step 2: Configure Quality Gates (Optional)

To enforce minimum quality standards (if not already passed via the combined command above):

C:\sqlenlight-cicd\Cli\EnlightForCI.exe analyzeFiles ^
  --licenseData="C:\sqlenlight-cicd\license\activationResponse.txt" ^
  --inputPath="C:\Source\Database" ^
  --reportOutput="analysis-report.xml" ^
  --qualityGate="C:\Source\Database\.sqlenlight\quality-gate.json" ^
  --failOn=QualityGateFail

Step 3: Transform XML into HTML and JUnit

Add another batch step to generate both HTML and JUnit formats from the XML:

:: Generate HTML Report
C:\sqlenlight-cicd\Cli\EnlightForCI.exe report ^
  --reportOutput="%WORKSPACE%\SqLEnlightAnalysisReportHtml.html" ^
  --reportStylesheet=HTML ^
  --reportXmlFile="%WORKSPACE%\SqLEnlightAnalysisReport.xml"

:: Generate JUnit Report
C:\sqlenlight-cicd\Cli\EnlightForCI.exe report ^
  --reportOutput="%WORKSPACE%\junit-report.xml" ^
  --reportStylesheet=JUNIT ^
  --reportXmlFile="%WORKSPACE%\SqLEnlightAnalysisReport.xml"

Step 4: Publish JUnit Results

1. Add the Publish JUnit test result report post-build action.
2. Set “Test report XMLs” to junit-report.xml.
3. Check “Retain long standard output/error”.

Step 5: Publish HTML Reports

1. Add the Publish HTML reports post-build action.
2. Configure the report directory and index page to point to your HTML output.
3. Important - CSP Configuration: Jenkins’ default Content Security Policy (CSP) blocks inline CSS/JS needed for HTML reports. Run this in the Jenkins Script Console (temporary) or startup script (permanent):

System.setProperty(
  "hudson.model.DirectoryBrowserSupport.CSP",
  "sandbox allow-scripts; default-src 'self'; style-src 'self' 'unsafe-inline'; script-src 'self' 'unsafe-inline';"
)

Best Practices and Recommended Configurations

• Run analysis on every commit: Catch issues early by integrating SQL Enlight into pull request validation builds.
• Cache Analysis Context: Use the analysisContext command to generate a metadata file and reuse it with the --contextFile parameter in analyzeFiles to significantly speed up analysis of T-SQL scripts.
• Use Include/Exclude Patterns: For large codebases, use --includeFilesPattern and --excludeFilesPattern to limit analysis to relevant files.
• Enforce Quality Gates: Configure Quality Gates with --failOn=QualityGateFail rather than just failing on any minor issue. This allows teams to focus on critical technical debt and security violations.
• Archive Reports: Always archive both HTML and JUnit reports as build artifacts for historical tracking and auditing.
• Centralize Settings: Store your settings.xml, qualityGate.json, and custom analysis templates in source control (e.g., inside a .sqlenlight folder). Pass them via the --settings, --qualityGate, and --templateFile parameters to ensure consistency across all builds and environments.
• Whitelist Legacy Code: Use the whitelist feature to ignore third-party scripts or legacy modules that are outside the current scope of refactoring, preventing them from failing the Quality Gate.

Troubleshooting

• License not activated: Ensure the license activation file exists and the --licenseData parameter points to the correct path. Verify activation using the licenseActivate command.
• Builds failing unexpectedly: Check the --exitCodeOnViolations and --failOn parameters. Ensure Jenkins is configured to recognize the custom exit code.
• HTML reports look broken in Jenkins: The Jenkins Content Security Policy (CSP) is likely blocking CSS. Apply the CSP configuration mentioned in the Jenkins integration section.
• Missing JUnit results in Jenkins: Ensure the JUnit plugin is installed and the “Test report XMLs” path correctly matches the --reportOutput value of your JUnit report generation step.
• Analysis taking too long: Check your database connection context. If analyzing offline scripts, pre-generating an analysisContext XML file will drastically reduce runtime.