SQL SERVER – Cannot open backup device. Operating system error 1326 (Logon failure: unknown user name or bad password.)

SQL
12 Comments

There might be various reasons for above error, but this case is very specific to a situation where SQL Server was installed on a machine which was in Workgroup whereas the backup destination was the server located in the domain.  Here is the error message seen in SQL Agent job history. Let us learn about how to fix Operating system error 1326.

Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility Version 11.0.2100.60 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved.
Started: 12:23:24 PM Progress: 2016-12-27 12:23:25.00 Source: {229AB1E3-E7E5-4185-B305-BF0F7657122A}
Executing query “DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp…”.: 100% complete End Progress Error: 2016-12-27 12:23:25.35 Code: 0xC002F210 Source: Back Up Database Task Execute SQL Task
Description: Executing the query “BACKUP DATABASE [master] TO DISK = N’\\192.168.3….” failed with the following error: “Cannot open backup device ‘\\192.168.3.55\backupShare\master_backup_2016_12_27_122325_2407734.bak’. Operating system error 1326(Logon failure: unknown user name or bad password.). BACKUP DATABASE is terminating abnormally.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly. End Error

Generally, when the backup is taken SQL Agent Service account is used to take backup. In this case since SQL was on Workgroup, it was NT Service\SQLSERVERAGENT. This account is not a valid account to validate for a domain.

SQL SERVER - Cannot open backup device. Operating system error 1326 (Logon failure: unknown user name or bad password.) bad-password-800x185

VERIFICAITON

First, let’s verify that we are hitting the same issue which I experienced. From Workgroup machine, we would try to query remote share using xp_cmdshell. Here is the command.

Solarwinds
xp_cmdshell 'dir \\192.168.3.55\backupShare'

This command should fail with below error.

Logon failure: unknown user name or bad password.

SOLUTION

Since we are talking about authentication issue. So, we need to make sure that we map a drive to SQL Server (not windows). As per SQL Server Books-On-Line:

“For a network share to be visible to SQL Server, the share must be mapped as a network drive in the session in which SQL Server is running”

Here is the command to map the drive.

EXEC XP_CMDSHELL 'net use Z: \\192.168.3.55\backupShare password@123 /User:domain\user'

Once the above is completely successful, we should be able to see content on the Z drive by running below command.

EXEC XP_CMDSHELL 'dir Z:\'

After this we should be able to take backup on the Z drive as its mapped within SQL Server as a drive.

Backup database master to disk = 'Z:\master.bak'

The above command should work and create the backup file in \\192.168.3.55\backupShare

Have you encountered this error earlier? Did you find this solution interesting? Please comment and let me know.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , , ,
Previous Post
SQL SERVER – FIX: Msg 15281- SQL Server Blocked Access to Procedure ‘sys.xp_cmdshell’ of Component ‘xp_cmdshell’
Next Post
SQL SERVER – Fix: Error 946, Severity: 14 – Cannot open database ‘DB’ version 782. Upgrade the database to the latest version

Related Posts

12 Comments. Leave new

  • Is this safe for ransomeware attack?

    Reply
  • It’s not working doing this in my system. pls help..

    Reply
  • great post, what do you mean by session? does it mean that if I disconnect from sql server the mapping will be deleted? as far as I know it would only be deleted if the sql server service gets restarted

    Reply
  • Charanjit Singh
    June 7, 2018 12:36 pm

    What to do if user’s password is changed?

    i am getting below error:

    The user name or password is incorrect.

    Reply
  • This is excellent, after two days i have resolved issue with the help of this article.
    Thank you very much

    Reply
  • Exactly resolved my problem.But I’m new on the sql and before search how to work xp_cmdshell on the other web site.Thanks and Regards.I think this is very helpful information.

    Reply
  • Shabbir Hussain
    June 4, 2019 7:31 am

    One of our Cluster Node was able to access network share while the other wasn’t. I tried many steps but it didn’t work. finally this article was life saver. it fixed my issue.
    Thanks heaps.

    Reply
  • awesome! thanks for the tip. One question, if I make this a stored procedure how do I get it to append or write a different file if one already exists? Example, I want 7 days worth of backup files, so when the second one executes, it doesn’t overwrite the first and so on.

    Reply
  • We have tried many steps it didn’t worked. finally we check this article was help us a lot. and we fixed issue with help of above given steps relay it’s work us..
    Thank you so much for giving with details.

    Reply

Leave a Reply

Menu