PowerShell Script Permissions

(Shane Walford) #1

I have created a step that runs a PowerShell Script. The script runs on the server fine if run manually, but if i run it as a step in Octopus it errors. Below is the script, and the error. When a step runs a PowerShell script, what user does it run it as?

The script is creating a SQL database from a template one we have stored on the server, and in the script i specify the credentials to use to run the script, which does work when run manually. Is there a way around this?

$password = ConvertTo-SecureString ‘*********’ -AsPlainText -Force

$cred = New-Object System.Management.Automation.PSCredential (‘testadmin’, $password)

#Relocate the Database file

$RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile(“DB_training”, “S:\Data\Testing.mdf”)

#Relocate the Log file

$RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile(“DB_training_Log”, “L:\Log\Testing.ldf”)

#Restore the template database

Restore-SqlDatabase -ServerInstance “localserver” -Database “Testing” -SQLCredential $cred -BackupFile “C:\Template\backup_V7_201810011430.bak” -RelocateFile @($RelocateData,$RelocateLog)

Error -Restore-SqlDatabase : System.Data.SqlClient.SqlError: CREATE DATABASE permission denied in database ‘master’.


(Justin Walsh) #3

Hi @Swalford!

Your tentacle will execute powershell scripts as the account that the service was configured to run as. You can check/verify this by loading the Services Management console snap-in and seeing which user the OctopusDeploy Tentacle service runs under.

I hope this helps, please let me know if you have any further questions.

(Shane Walford) #4

Thank you for the response. The service is running as Local System, is this OK, or should this be changed to a different account? The script works if i grant the Local System account dbcreator role in SQL, but im not sure how secure this is.

Is it recommended to change the account Octopus runs as?