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:
Post a Comment