Powershell, UNC, SQL

Legacy Uncategorised
1

Hi, i’m having a problem ,
I am automating a database restore system and i wrote a powershell script to execute SQL command (stored proc) who’s only parameter is path to the folder where backups reside. I am running into an issue because the path is a network path (\server\folder\folder).
While the script works great in powerhsell ISE it consistently fails to execute using octopus. The only way it works is if i pass a path to a folder on a local drive. Even simple command like “ls \server\folder\folder” fails when running on tentacle.
The tentacle is running on service account with local admin rights and network share is read only open to Everybody.
I also ran the script with service account credentials on the sql server machine itself and it worked.

Suggestions?

NOTES:
Script:
EXEC dbo.spDBRestore @BKFolder = N’\server\folder\folder’

Error:
Exception calling “ExecuteScalar” with “0” argument(s): "Invalid zero-length device name. Reissue the BACKUP statement with a valid device name.

2

Hi,

Thanks you for getting in touch! Could you provide me with a bit more information so I can get a better understating of the problem?

  • Can you include the PowerShell script that you use to execute the SQL? Please remove any sensitive information or mark the conversation as private.
  • Can you attach the raw deployment log of a sample failed deployment?
  • Is the tentacle that you used for testing running on the SQL Server machine?

Regards

Pawel

3

I’ve had a similar issue, and it was the security context that the script was executing in. Under PowerShell ISE, it was running as my account - in Octopus, it was executing under the server’s machine account security context.

I got around it by setting MachineName$ with access to the share & NTFS permissions

4

Hi Nick,

Thanks for sharing your solution.

Regards,

Pawel