SQL Server Agent - 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."
Category: sql server notification
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.