Question

lcerni on Thu, 06 Feb 2020 17:22:58



I have set up a step in SQL Server Agent with the following code

sqlcmd -S ServerName\InstanceName -E -Q "backup database LJC to disk ='\\xxx\abc\ManualBackups\ServerName_InstanceName\LJC.bak' with copy_only, init,compression"

However, I am getting the error

Executed as user: DomainName\UserName. Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user 'DomainName\UserName'..  Process Exit Code 1.  The step failed.

The instance is set up with SQL Server and Windows Authentication mode.

The Service that is running the job has been added the the Security folder with sysadmin.

Can anyone else suggest possible solutions?  


lcerni

Replies

lcerni on Thu, 06 Feb 2020 18:01:53


Could it be failing because it is looking for ODBC driver 13 for SQL Server and a higher version already exists on the machine?

lcerni on Fri, 07 Feb 2020 12:37:01


I have found two sqlcmd executables on the machine:

C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\ 09/14/2019
C:\Program Files (x86)\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn 05/10/2018

Could this be my issue?

lcerni on Fri, 07 Feb 2020 13:23:09


In Command Prompt when I set the directory as C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools> and run the command

sqlcmd -S ServerName\InstanceName -E -Q "backup database LJC to disk ='\\xxx\abc\ManualBackups\ServerName_InstanceName\LJC.bak' with copy_only, init,compression"

I can get it to work.  So how do I get it to point to the correct path of sqlcmd in SQL Server Agent?


lcerni


lcerni on Fri, 07 Feb 2020 18:08:50


I now think the issue was permissions.  At this company the Service Account did not have Full Admin permissions.  However, they are not going to tell me what they did to fix the issue.