SQL Agent job step fails to run SQL Data Compare Exe with UnauthorizedAccessException

MMMm

Running SQL Data Compare exe in command prompt and power-shell under service account context works, and able to sync data. This is a scenario of syncing data from database backup file to local database. However, the same fails when done via SQL job step with below exception:

The service account has have necessary permission to run SQL data compare exe, and access the backup file. The service account has been provisioned to use professional license.

********* Error when running via SQL job step *******

 Exception: System.UnauthorizedAccessException:  Access to the path is denied.     at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)     

 at System.IO.Pipes.NamedPipeClientStream.Connect(Int32 timeout)     

 at RedGate.Ipc.NamedPipes.NamedPipeEndpointClient.Connect(Int32 timeoutMs)     

 at RedGate.Ipc.Rpc.RpcConnectionProvider.TryGetConnection(TimeSpan timeout)     

 at RedGate.Ipc.ReconnectingConnectionProvider.ReconnectionWorker()     

 at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)     

 at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)     

 at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)     

 at System.Threading.ThreadHelper.ThreadStart()Error: Database updated failed. See the above errors.  Process Exit Code 0.  The step succeeded.


********* No error when running SQL data compare using powershell or command prompt *******

Redgate SQL Data Compare Command Line Utility V13.4.5.6953

=================================================================

Copyright c 1999 - 2018 Redgate Software Ltd

SQL Data Compare: activated, edition professional, serial number:

Registering databases

Mapping

Comparing databases

Generating SQL scripts

Deploying databases

Finished

Tianjiao_Li

Hi @MMMm

Can you double check the account running the SQL job have permissions and the job "run as user" has the permission?

Can you kill any sqldatacompare.exe and try again?

MMMm

The service account has necessary permission. SQL Data compare works fine when running in cmd/powershell when logged into the server with that account. However, it fails when invoked via Sql job. The SQL job runs under a proxy account mapped to the above service account.

Any idea what is happening here? What could be the "maybeFullPath" it is trying to access?

System.UnauthorizedAccessException: Access to the path is denied.

    at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)

at System.IO.Pipes.NamedPipeClientStream.Connect(Int32 timeout)

at RedGate.Ipc.NamedPipes.NamedPipeEndpointClient.Connect(Int32 timeoutMs)

at RedGate.Ipc.Rpc.RpcConnectionProvider.TryGetConnection(TimeSpan timeout)

at RedGate.Ipc.ReconnectingConnectionProvider.ReconnectionWorker() 

rferraton

Same error for me,
the path seam good become log show "SQL Compare Command Line V13.6.1.7928"
It is after SQL Compare was launch that the error happened.

No error happened when the powerscript is launch manually . Does sqlcompare need elevation rights maybe ? or workdir are different when launching directly or with sql agent ?

rferraton

PS : i use a proxy account that is the same that i use to launch the script manually.

rferraton

SQL Compare Command Line V13.6.1.7928

======================================================================================================================

Copyright c 1999 - 2018 Redgate Software Ltd

The job script encountered the following errors. These errors did not stop the script:

A job step received an error at line 3 in a PowerShell script. The corresponding line is 'C:\"Program Files (x86)"\"Red Gate"\"SQL Compare 13"\sqlcompare.exe /server1:.\DATAD1 /database1:SIGMA_SA /scripts2:D:\EXPLOIT\SCRIPT\SQL_DEV_PROVISIONING_RESULT\Databases\SIGMA_SA /q /synch /options:Default /report:D:\EXPLOIT\SCRIPT\SQL_DEV_PROVISIONING_RESULT\Databases\SIGMA_SA.html /reportType:Simple /rad /force

'. Correct the script and reschedule the job. The error information returned by PowerShell is: '

'

A job step received an error at line 3 in a PowerShell script. The corresponding line is 'C:\"Program Files (x86)"\"Red Gate"\"SQL Compare 13"\sqlcompare.exe /server1:.\DATAD1 /database1:SIGMA_SA /scripts2:D:\EXPLOIT\SCRIPT\SQL_DEV_PROVISIONING_RESULT\Databases\SIGMA_SA /q /synch /options:Default /report:D:\EXPLOIT\SCRIPT\SQL_DEV_PROVISIONING_RESULT\Databases\SIGMA_SA.html /reportType:Simple /rad /force

'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Unhandled Exception: 

'

A job step received an error at line 3 in a PowerShell script. The corresponding line is 'C:\"Program Files (x86)"\"Red Gate"\"SQL Compare 13"\sqlcompare.exe /server1:.\DATAD1 /database1:SIGMA_SA /scripts2:D:\EXPLOIT\SCRIPT\SQL_DEV_PROVISIONING_RESULT\Databases\SIGMA_SA /q /synch /options:Default /report:D:\EXPLOIT\SCRIPT\SQL_DEV_PROVISIONING_RESULT\Databases\SIGMA_SA.html /reportType:Simple /rad /force

'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'System.UnauthorizedAccessException: Access to the path is denied.

   at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)

   at System.IO.Pipes.NamedPipeClientStream.Connect(Int32 timeout)

   at RedGate.Ipc.NamedPipes.NamedPipeEndpointClient.Connect(Int32 timeoutMs)

   at RedGate.Ipc.Rpc.RpcConnectionProvider.TryGetConnection(TimeSpan timeout)

   at RedGate.Ipc.ReconnectingConnectionProvider.ReconnectionWorker()

   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)

   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)

   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)

   at System.Threading.ThreadHelper.ThreadStart()

'

A job step received an error at line 3 in a PowerShell script. The corresponding line is 'C:\"Program Files (x86)"\"Red Gate"\"SQL Compare 13"\sqlcompare.exe /server1:.\DATAD1 /database1:SIGMA_SA /scripts2:D:\EXPLOIT\SCRIPT\SQL_DEV_PROVISIONING_RESULT\Databases\SIGMA_SA /q /synch /options:Default /report:D:\EXPLOIT\SCRIPT\SQL_DEV_PROVISIONING_RESULT\Databases\SIGMA_SA.html /reportType:Simple /rad /force

'. Correct the script and reschedule the job. The error information returned by PowerShell is: '

'

'

Do you think it will need to have elevated privileges to call program in the c:\program files(x86) ? 

rferraton

The issue does not appear when lauching the same script using a scheduled task

MMMm

I was able to get sql job step working by not using proxy. 

rferraton

Good to know ! Thanks

My problem,  in that case, when i don't use a sql agent proxy account, the sql agent account (that is a named account) is used and in this case i have a licence error (which is normal...).
Unfortunatly the account used for launching sql agent or sql server does not have enought privileges to login as a normal user for security reason... so i can't define a serial for this named account.

May be you know a method to give a valid serial number to this kind of users/accounts ?

coavlanche

Did you ever resolve this issue?  I'm having a very similar error as this.  I have an SSIS package which executes SQL Compare from within the package (command-line).  The SSIS package is executed via SQL Agent Job that is using a proxy account.  I have tried everything I can think of.  If I run the SQL Agent Job under the SQL Agent account and don't use a proxy, it works.

ThanhN

I ran into this exact issue, i.e. sql agent job failed when using Proxy account. Do we have hotfix for this?

JessB

I am having this problem as well.  I can run the SQL Data Compare from a command line or a bat file, but if I try to run it inside my SSIS package it fails.  I am also using a proxy user.  Everything else fails when I don't use the proxy. Has anyone figured out why this is happening or how to fix it?