Create a new database user using vRealize Automation XaaS

Recently I described how to deliver Database-as-a-Service using XaaS (Anything-as-a-Service) in vRealize Automation. In the meantime I added some add-ons to this.

It’s nice to have the ability to create a new database using self-service from vRealize Automation but when the database is running, users may want to edit it’s properties. User may want to add a user to a database to log in with or give users additional rights on the database. So I created a few XaaS services in vRealize Automation to do just that.

Running the script inside the guest OS with vRealize Orchestrator.

Next we need a script which creates a database on an existing SQL Server instance. I used the PowerShell script below:

#----------------------------------------------------------------------
# Database as a Service add-on - MS SQL Server
# Create a new database user with db role
# Erik Scholten (VMGuru)
# January 2017 - Version 1.0
#
# Variables:
# [COMPUTERNAME] is SQL Server name
# [DBUSER] is new dbuser account name
# [DBPASSWORD] is password for new dbuser account
# [DATABASE] is the database on which the account receives the rights
# [DBROLE] is the role the account gets on the database
#-----------------------------------------------------------------------

# Import SQL Server module
Import-Module SQLPS -DisableNameChecking
 
# Replace this with your instance name
$instanceName = $env:[COMPUTERNAME]
$dbUserName = '[DBUSER]'
$dbPassword = '[DBPASSWORD]'
$Databases = '[DATABASE]'
$roleName = '[DBROLE]'
 
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName
 
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
 
# Login already exists?
if ($server.Logins.Contains($dbUserName))  
{   
    Write-Host("Create $dbUserName failed because username already exists.")
}
else
{ 
    # Define user type and create the user
    $Login = New-Object Microsoft.SqlServer.Management.Smo.Login($server, $dbUserName)
    $Login.LoginType = 'SqlLogin'
    $Login.Create($dbPassword)
    $login.PasswordExpirationEnabled = $false
    Write-Host("Login $dbUserName created successfully.")

    foreach($databaseToMap in $Databases)  
    {
        $database = $server.Databases[$databaseToMap]
        if ($database.Users[$dbUserName])
        {
            Write-Host("Dropping user $dbUserName on $database.")
            $database.Users[$dbUserName].Drop()
        }

        $dbUser = New-Object `
        -TypeName Microsoft.SqlServer.Management.Smo.User `
        -ArgumentList $database, $dbUserName
        $dbUser.Login = $loginName
        $dbUser.Create()
        Write-Host("User $dbUser created successfully.")

        #assign database role for a new user
        $dbrole = $Database.Roles[$roleName]
        $dbrole.AddMember($dbUserName)
        $dbrole.Alter()
        Write-Host("User $dbUser successfully added to $roleName role.")
    }
}

#----------------------------------------------------------------------

# Database as a Service add-on - MS SQL Server

# Create a new database user with db role

# Erik Scholten (VMGuru)

# January 2017 - Version 1.0

# Variables:

# [COMPUTERNAME] is SQL Server name

# [DBUSER] is new dbuser account name

# [DBPASSWORD] is password for new dbuser account

# [DATABASE] is the database on which the account receives the rights

# [DBROLE] is the role the account gets on the database

#-----------------------------------------------------------------------

# Import SQL Server module

Import-Module SQLPS -DisableNameChecking

# Replace this with your instance name

$instanceName = $env:[COMPUTERNAME]

$dbUserName = '[DBUSER]'

$dbPassword = '[DBPASSWORD]'

$Databases = '[DATABASE]'

$roleName = '[DBROLE]'

$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

# Login already exists?

if ($server.Logins.Contains($dbUserName))

{

Write-Host("Create $dbUserName failed because username already exists.")

}

else

{

# Define user type and create the user

$Login = New-Object Microsoft.SqlServer.Management.Smo.Login($server, $dbUserName)

$Login.LoginType = 'SqlLogin'

$Login.Create($dbPassword)

$login.PasswordExpirationEnabled = $false

Write-Host("Login $dbUserName created successfully.")

foreach($databaseToMap in $Databases)

{

$database = $server.Databases[$databaseToMap]

if ($database.Users[$dbUserName])

{

Write-Host("Dropping user $dbUserName on $database.")

$database.Users[$dbUserName].Drop()

}

$dbUser = New-Object `

-TypeName Microsoft.SqlServer.Management.Smo.User `

-ArgumentList $database, $dbUserName

$dbUser.Login = $loginName

$dbUser.Create()

Write-Host("User $dbUser created successfully.")

#assign database role for a new user

$dbrole = $Database.Roles[$roleName]

$dbrole.AddMember($dbUserName)

$dbrole.Alter()

Write-Host("User $dbUser successfully added to $roleName role.")

}

Just like the ‘Database-as-a-Service’ we’re going to use the VM Guest Script Manager to run this script inside the database server. You can download the Guest Script Manager package from the VMware Development Center. Once downloaded, import the package into your Orchestrator instance.

Now we need to add the script above to the vRealize Orchestrator by running the ‘Guest Script Manager/Script Management/Add script configuration‘ workflow. Name your imported script ‘Create a database user (code)‘ (I added ‘code’ to the name to distinguish between the code and the actual workflow). Select ‘PowerShell’ as the script type and copy+paste this code in the script content window.

How to call the script?

So we’ve imported the script, now we need to create a workflow to run this script and input the variables which are mentioned in the script comments. This workflow is the actual workflow we’re going to call as XaaS service from vRealize Automation.

So create a new workflow called ‘Create a database user‘.

Drag and drop the workflow element ‘Run script in VM guest’ on your workflow canvas.
Click on Setup button at the top right to set the workflow parameters.
Set ‘scriptConfiguration’ as mapping type ‘Value’, click on input value and type in the filter the name of the script (‘Create a database user (code)’). Select it.
Set ‘scriptVariables‘ mapping type to ‘Value‘ but do not set any input value.
Click ‘Promote‘.

The result should look like this.

Create the XaaS workflow and blueprint

Now we have to retrieve input to fill the script variables mentioned in the script comments. The following input variables need to be provided:

[COMPUTERNAME] which is the SQL Server name.
[DBUSER] is the new dbUser account name.
[DBPASSWORD] is password for new dbuser account.
[DATABASE] is the database on which the account action is performed.
[DBROLE] is the role the account gets on the database.

The For each of these variables we need to add an Action element to the workflow which maps a workflow input. This results in an input prompt when running this workflow.

To do this:

Map DatabaseName:
- Drag and drop an Action Element on the canvas and place it before the ‘Run script in VM guest’.
- In the Search Choose the ‘addScriptVariableToArrayOfScriptVariables‘ action.
- Click on Setup button at the top right to set the workflow parameters.
  - Rename “stringToReplace” with ‘ComputerNameVar‘.
  - Set the mapping type to ‘Value‘.
  - Select ‘Input value’ and set it to ‘[COMPUTERNAME]’.
  - Rename “replacingString” with ‘ComputerName‘.
  - Leave the mapping type on ‘Input‘.
  - Set the Output Parameter Name to ‘scriptVariables‘.
  - Click ‘Promote’.

Do the same for [DBUSER], [DBPASSWORD], [DATABASE] and [DBROLE].

Each mapping should look like this (Example: [DATABASE]):

Your completed workflow should look like this:

Now we need to create an XaaS service in vRealize Automation which calls the workflow that we’ve just created.

Go to ‘vRealize Automation/Design/XaaS Blueprints‘ and select ‘New Blueprint‘. Select the Orchestrator workflow you’ve just created.

Name your XaaS blueprint e.g. ‘Create a database user‘ and version it (1.0.0). Give the inputs nice user friendly input values so users who request this will know what they need to enter in which field. I also created some nice dropdown lists so users do not have to guess the values but select them from a predefined list. For now these are all static values but you can populate the field values dynamically for a more agile approach.

The last step is publishing the XaaS Blueprint, set the correct entitlements and enjoy a new Anything-as-a-Service. You can now request a new database administrator account without bothering the database administrators or ever opening SQL Server Management Studio.