Wednesday, September 16, 2015

DevOps and PowerShell : Automating SonarQube installation – Part 2

This is the 2nd post of the automation SonarQube installation series. In the first part we’ve seen how to setup the infrastructure for SonarQube. In this part I’ll show how to configure SQL server for SonarQube

For SQL server, we will be doing the following
  • Enable SQL Server authentication
  • Add a new login for SonarQube
  • Create a database for SonarQube

Enable SQL Server authentication


function Set-AuthenticationMode
{
       [CmdletBinding()]
       param
       (
              [Parameter(Mandatory=$false, Position=0)]
              [string] $ServerName = "localhost",
             
              [Parameter(Mandatory=$false, Position=1)]
              [string] $AuthenticationMode = "Integrated"
       )

       Initialize-Assembly -Name 'Microsoft.SqlServer.SMO'
       if(-not(Test-AssemblyLoaded -Name 'Microsoft.SqlServer.SMO'))
       {
              throw "Unable to find the SQL server management objects assembly"
       }

       $sqlServer = new-object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName
       [string]$currentMode = $sqlServer.Settings.LoginMode

       if($currentMode -eq $AuthenticationMode)
       {
              "Current login mode is already set to $AuthenticationMode. Skipping the step" | Write-Verbose
       }
       else
       {
              switch($AuthenticationMode)
              {
                     "Integrated"
                     {
                           $sqlServer.Settings.LoginMode = [Microsoft.SqlServer.Management.SMO.ServerLoginMode]::Integrated
                     }
                     "Mixed"      
                     {
                           $sqlServer.Settings.LoginMode = [Microsoft.SqlServer.Management.SMO.ServerLoginMode]::Mixed
                     }
                     "Normal"
                     {
                           $sqlServer.Settings.LoginMode = [Microsoft.SqlServer.Management.SMO.ServerLoginMode]::Normal
                     }
                     "Unknown"
                     {
                           $sqlServer.Settings.LoginMode = [Microsoft.SqlServer.Management.SMO.ServerLoginMode]::Unknown
                     }
                     default
                     {
                           "Unable to set a login mode with name $AuthenticationMode. Skipping this step" | Write-Error
                     }
              }
              $sqlServer.Alter()


              "Restarting the SQL server service" | Write-Verbose
              Restart-Service MSSQLSERVER -Force
       }
}

Using the Set-Authentication mode method, we can now easily change the SQL server authentication as
#Set SQL authentication mode
Set-AuthenticationMode -AuthenticationMode "Mixed" -Verbose

Adding a new login for SonarQube

We now need to add a user for connecting as the SonarQube server. We’ll later use these credentials to configure JDBC to login to SQL server.

function Set-SqlLogin
{
       [CmdletBinding()]
       param
       (
              [Parameter(Mandatory=$false, Position=0)]
              [string] $ServerName = "localhost",
             
              [Parameter(Mandatory=$true, Position=1)]
              [ValidateNotNullOrEmpty()]
              [string] $Username,

              [Parameter(Mandatory=$true, Position=2)]
              [ValidateNotNullOrEmpty()]
              [string] $Password
       )

       Initialize-Assembly -Name 'Microsoft.SqlServer.SMO'
       if(-not(Test-AssemblyLoaded -Name 'Microsoft.SqlServer.SMO'))
       {
              throw "Unable to find the SQL server management objects assembly"
       }

       $sqlServer = new-object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName
       if ($sqlServer.Logins.Contains($Username)) 
       {  
              "A login with name $Username already exists." | Write-Verbose
       }
       else
       {
              "Creating a new login : $Username" | Write-Verbose
              $sqlLogin = New-Object Microsoft.SqlServer.Management.Smo.Login $ServerName, $Username
              $sqlLogin.LoginType = [Microsoft.SqlServer.Management.Smo.LoginType]::SqlLogin
              $sqlLogin.PasswordExpirationEnabled = $false
              $sqlLogin.Create($Password)
              "Added a new login : $Username" | Write-Verbose
       }
}

The Set-SqlLogin member can be used to add a new user to SQL server.

#Add user for SonarQube
Set-SqlLogin -Username "sonaruser" -Password "SonarPass@12" -Verbose

Create a database for SonarQube

Next we are going to create the target database for SonarQube. After the database is created, we need to add the sonaruser with the db_owner role to the database. The below methods are used for achieving this functionality.

function New-SqlDatabase
{
       [CmdletBinding()]
       param
       (
              [Parameter(Mandatory=$false, Position=0)]
              [string] $ServerName = "localhost",

              [Parameter(Mandatory=$true, Position=1)]
              [ValidateNotNullOrEmpty()]
              [string] $DbName,
             
              [Parameter(Mandatory=$false, Position=2)]
              [string] $RecoveryMode = "Simple"
       )

       Initialize-Assembly -Name 'Microsoft.SqlServer.SMO'
       if(-not(Test-AssemblyLoaded -Name 'Microsoft.SqlServer.SMO'))
       {
              throw "Unable to find the SQL server management objects assembly"
       }

       $sqlServer = new-object Microsoft.SqlServer.Management.Smo.Server $ServerName
       $dbExists = $false

       $sqlServer.Databases |% {
              if ($_.Name -eq $DbName)
              {
                     $dbExists = $true
              }
       }
      
       if($dbExists)
       {
              "A database with name $DbName already exists." | Write-Verbose
       }
       else
       {
              "Creating new database $DbName" | Write-Verbose

              $db = new-object Microsoft.SqlServer.Management.Smo.Database $sqlServer, $DbName
              $db.Collation = "Latin1_General_100_CS_AS"     
              if ($RecoveryMode -eq "Simple")
              {
                     "Setting database recovery mode : Simple"
                     $db.RecoveryModel = [Microsoft.SqlServer.Management.Smo.RecoveryModel]::Simple
              }
              else
              {
                     "Setting database recovery mode : Full"
                     $db.RecoveryModel = [Microsoft.SqlServer.Management.Smo.RecoveryModel]::Full
              }
             
              $db.Create()
       }
}
function Set-DbLogin
{
       [CmdletBinding()]
       param
       (
              [Parameter(Mandatory=$false, Position=0)]
              [string] $ServerName = "localhost",

              [Parameter(Mandatory=$true, Position=1)]
              [ValidateNotNullOrEmpty()]
              [string] $DbName,
             
              [Parameter(Mandatory=$true, Position=2)]
              [ValidateNotNullOrEmpty()]
              [string] $Username,
             
              [Parameter(Mandatory=$false, Position=3)]
              [string] $Role = "db_owner"
       )

       Initialize-Assembly -Name 'Microsoft.SqlServer.SMO'
       if(-not(Test-AssemblyLoaded -Name 'Microsoft.SqlServer.SMO'))
       {
              throw "Unable to find the SQL server management objects assembly"
       }

       $sqlServer = new-object Microsoft.SqlServer.Management.Smo.Server $ServerName

       $db = $sqlServer.Databases[$DbName]
       if($db -eq $null)
       {
              "Unable to find a database with name $DbName" | Write-Warning
       }
       else
       {
              if($db.Users[$Username] -ne $null)
              {
                     "User $Username already exists as login in database $DbName" | Write-Verbose
              }
              else
              {
                     $dbUser = New-Object Microsoft.SqlServer.Management.Smo.User $db, $Username
                     $dbUser.Login = $Username
                     $dbUser.Create()
                     "$Username added to database $DbName login" | Write-Verbose

                     "Setting $Username as $Role in database $DbName"
                     $dbrole = $db.Roles[$Role]
                     $dbrole.AddMember($Username)
                     $dbrole.Alter()
              }
       }
}

Calling these methods in our installation scripts will ensure that we have the SQL server configured properly for installing SonarQube.

#Create database for SonarQube
New-SqlDatabase -DbName "sonar" -Verbose

#Set SonarUser with db_owner role in database
Set-DbLogin -DbName "sonar" -Username "sonaruser" -Role "db_owner" -Verbose

Next we’ll see how to configure firewall ports, update the SonarQube configurations based on our setup and finally start the SonarQube service on our machine.

No comments: