Hi Team
Im trying to execute a sql script using a installed step template.
I’ve set the parameters required.
set Variables are as follows:
Yet when the step runs its failing for the following:
I’m not really sure what I’m doing wrong? any advice?
Regards
T
Hi @tabrahams1
Thanks for getting in touch with Octopus, and sorry that you’re having issues with the community step template.
The error you are seeing is actually hiding the real error. The technical details are that the catch block doesn’t have an inner exception, and cant display it. I can get the error handling fixed up, which should be out in the library later today.
You have two choices in the meantime:
In my experience, an error being caught like this would indicate an issue with the credentials being used. Since you’re using Window Integrated, the script will take the credentials as the user running the tentacle for the deployment target with role UAT-Tomcat-server. You’d likely need to check that service account has access to connect to the database CreditIT_Sandbox on server CRITSQL_DEV.
I hope that helps!
Below is the user I’m executing octopus steps. Im therefore assuming its using my AD credentials to connect to MS SQL.
As you can see from the below, my AD credentials has access to the server and DB.
How do I check what credentials is being used for authentication from octopus?
Regards
T
Hi @tabrahams1
Version 169 of the step template is now live in the Library. It should now provide the details of the exception.
You’ll need to synchronize your octopus community library by going to Configuration -> Features -> SYNC NOW
Once that task has been completed, then you’ll need to update the version in the library on your instance:
Hope that helps!
Hi @tabrahams1
The user you log into Octopus with isnt the same user context with which the deployment steps run under.
They will run under the service account for the deployment target you’re deploying with/to.
So for your example, your deployment process had a target with role of UAT-Tomcat-server. If you go to the deployment targets page and find the target that matches that role and that environment, you then need to check the log on identity (user account) for the windows service of the deployment target (the tentacle).
It will be this user that is trying to execute the SQL step, not your own user account logged into Octopus.
Hope that helps!
Thanks the update it helped isolate the issue. looks like we cant find MS SQL Server on the VM.
Microsoft SQL Server is installed though
Do you know how I can address this? I’ve been researching but cant find a definitive answer to address it.
regards
T
Hi @tabrahams1
Typically the issue with that is that the step uses SQL Management Objects (SMO) which PowerShell must be able to see in its PATH variable.
Remember that the script is executing on a deployment target, not the VM that SQL Server is running on itself, so SMO must be installed there.
I must admit, I’m not a big fan of SMO, and prefer to use the built-in PowerShell SQL functionality.
Have you tried the SQL - Execute Script file step?
Best regards
Thank you for your recommendation this looks like a easier path 
However I now have new issue, the windows authentication. Its using the server name where I have installed the tentacle on as USERID instead of credentials.
How do I set the username and password?
Hi @tabrahams1
The user that’s set for your connection will be dictated by the contents of your connection string used in the SQL - Execute Script file step.
It has a parameter called ConnectionString. In the help text, there is an example which uses Integrated Security e.g.:
Server=.\SQLExpress;Database=OctoFX;Integrated Security=True;
This instructs the connection to take the user context from the process running the script, which in the case of an Octopus tentacle, would be the service account running the Windows Service.
If you don’t wish to use Integrated Security, you can provide a SQL username and password in the connection string. For example;
Server=.\SQLExpress;Database=OctoFX;User Id=myUsername;Password=myPassword;
Where myUsername and myPassword would be replaced with the credentials you want to provide. If you’re using SQL authentication like this, then I’d recommend considering placing the password as a sensitive variable in Octopus so that it’s not shown in logs or in plain text.
Alternatively, if you want to use Windows Authentication, then you’d need to change the service account that the tentacle Windows Service is running as to one which has access to the SQL Server in question.
I hope that helps!
Best regards,
