SQL server agent unable to open step output file the step failed - mapped drives

Question

I have used Ola Hallengren's maintenance script to create backup scripts and schedules, originally both to a local folder and this worked fine. Then I set it up to send the backup files to a mapped Azure file share as a mapped network drive with no log file output, this worked.

I have now re ran the script so that it backups and outputs to the same drive (one folder called Backup and one called LogBackup), SQL Server agent will now not run if the output file location is declared, I have tried using a local drive but it still does not work. Any suggestions?

This is the error displayed in the job history:

Message Executed as user: NT Service\SQLSERVERAGENT. Unable to open Step output file. The step failed.

Answer 1

Once you get the code straightened out for the local drive, you'll need to set permissions on the network share for the server's computer account since you are using the default NT Service account for the services. The computer account is the server's name and then a dollar sign.

Example: Domain1\MyServerName$

MyServerName must be the name of the server, not the name of the SQL instance. Get the server name via hostname in a cmd window.

You would set the permissions on the remote server. RDP to it, navigate to the share and grant the access there. I am not sure for Azure how it works. But if this were a hosted server instead of a cloud server, it would be on the actual folder and not on the mapped network drive.

• P Corn: I'm RDP'd into it right now - should I be granting the permissions through right clicking the mapped drive? As I only have this: imgur.com/a/yZUka

Answer 2

I hope this helps.

You need to map the SQL server with the Shared drive.

Used this:

Use Master GO
EXEC master.dbo.sp_configure 'show advanced options', 1 RECONFIGURE WITH OVERRIDE GO
EXEC master.dbo.sp_configure 'xp_cmdshell', 1 RECONFIGURE WITH OVERRIDE GO
EXEC master..XP_CMDSHELL 'net use Z: <\backup\drive\location> <password> /user:username'
EXEC master..XP_CMDSHELL 'dir Z: \'
Use Master GO
EXEC master.dbo.sp_configure 'show advanced options', 1 RECONFIGURE WITH OVERRIDE GO
EXEC master.dbo.sp_configure 'xp_cmdshell', 0 RECONFIGURE WITH OVERRIDE

This worked for me.