asked on

PowerShell permissions error when exporting CSV

Hi EE,

I am experiencing an error when running Powershell script via SQL Agent on SQL 2016:

Export-Csv $OutputFile + ~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : OpenError: (:) [Export-Csv], UnauthorizedAccessException + FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.ExportCsvCommand. Process Exit Code 0. The step succeeded.

What is causing this error, any assistance is welcome. Attached is the script and command I am using in SQL Agent to Launch is also included:

When I launch the scripts manually the create the files as required.

Thank you.
SQL_ClientAgedCareReport.ps1
LaunchCommand.PNG

David Todd

Hi,

Describe z: drive.

My bet is that it is NOT a locally attached disk (well, maybe a SAN) but is a mapped drive. You can see the mapped drive but SQL Agent can't. And you need to map this as SQL agent in this session and not just log in with the SQL Agent account and password.

HTH
David

Zack

ASKER

Hi David,

How would I do this map the drive as a SQL agent?

Thank you.

Zack

ASKER

Hi David,

Could I create a Network Share and SQL Agent could read that?

Thank you

ASKER CERTIFIED SOLUTION

David Todd

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

David Todd

Hi Zack

You'll need to know what account SQL Agent is running under. A good practice is that SQL and SQL Agent are running under their own domain account(s) that nothing else uses. Then its really super easy to give rights to SQL Agent to use share xyz on server abc.

If on the other hand, it runs as something like NT Service\SQLAgent$MY_SQL_Server then it will possibly be easiest to change things to the above suggestion first.

HTH
David

PS Your servers are runnign in a domain, right, and not just a workgroup?

Zack

ASKER

Hi David,

Made modification to the script: $OutputFile = "\\DC1PRDSQLFCI02\ProcuraExports" + $timestamp + "_SQL_Financial Transactions.csv"

Still no fix can you give an example, please.

Thank you.

Zack

ASKER

Hi David,

Forgot mention I have gave 'Everyone' Access to the Network Share Read/Write Privileges.

Thank you.

David Todd

Hi Zack

Look at the SQL Configuration manager and see what account SQL Agent uses and report back.

Look at how the step is executing ie open the job then open the job step - and tell us what that says.

Regards
David

Zack

ASKER

Hi David,

Agent Name is SLNS\sql-agt-prod$.

Thank you

Zack

ASKER

Hi David,

Executed as user: SLNS\sql-agt-prod$. INFO: Exporting data from ProcuraLive to Z:\ProcuraExports\2019-08-29_1515_SQL_ClientAgedCare.csv41437Export-Csv : Access to the path 'Z:\ProcuraExports\2019-08-29_1515_SQL_ClientAgedCare.csv' is denied. At Z:\ClayScripts\SQL_ClientAgedCareReport.ps1:85 char:

Got it that's the issue but I changed the script let me double check.

Thank you.

Zack

ASKER

Hi David,

Sorry EE when down:

The change the script is confirmed "\\DC1PRDSQLFCI02\ProcuraExports" + $timestamp + "_SQL_Financial Transactions.csv"

But it still shows as the error above. Checking some permissions on my end.

Zack

ASKER

Hi David,

Got this error now. Executed as user: SLNS\sql-agt-prod$. INFO: Exporting data from ProcuraLive to \\DC1PRDSQLFCI02\ProcuraExports2019-08-29_1544_SQL_Financial Transactions.csv949373Export-Csv : The network name cannot be found.

Thank you.

Zack

ASKER

Wait I think might I have got it there is no \ DC1PRDSQLFCI02\ProcuraExports one sec

Zack

ASKER

Hi David,

Cracked it thank you for patient assistance.

David Todd

Hi Zack

You're welcome.

Regards
David