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

MMMmMMMm Posts: 3 New member
in SQL Data Compare
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

Tagged:
· Share on Twitter

Answers

  • Tianjiao_LiTianjiao_Li Posts: 684 Rose Gold 5
    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?
    Kind regards

    Tianjiao Li | Redgate Software
    Have you visited our Help Center?
    · Share on Twitter
  • MMMmMMMm Posts: 3 New member
    edited November 13, 2018 8:53PM
    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() 


    · Share on Twitter
  • rferratonrferraton Posts: 6 Bronze 1
    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 ?
    · Share on Twitter
  • rferratonrferraton Posts: 6 Bronze 1
    PS : i use a proxy account that is the same that i use to launch the script manually.
    · Share on Twitter
  • rferratonrferraton Posts: 6 Bronze 1
    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) ? 
    · Share on Twitter
  • rferratonrferraton Posts: 6 Bronze 1
    The issue does not appear when lauching the same script using a scheduled task
    · Share on Twitter
  • MMMmMMMm Posts: 3 New member
    I was able to get sql job step working by not using proxy. 
    · Share on Twitter
  • rferratonrferraton Posts: 6 Bronze 1
    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 ?
    · Share on Twitter
  • coavlanchecoavlanche Posts: 12 New member
    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.
    · Share on Twitter
  • ThanhNThanhN Posts: 1 New member
    I ran into this exact issue, i.e. sql agent job failed when using Proxy account. Do we have hotfix for this?
    · Share on Twitter
  • JessBJessB Posts: 2 New member
    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?
    · Share on Twitter
Sign In or Register to comment.