Cannot Open Backup Device

Your success as a DBA is directly relational to your ability to ensure proper Database backups are occurring. There is a whole slew of considerations that go into effect when discussing what a proper Database backup actually is. To drill it down to its most basic definition, a proper database backup is one that fulfills business requirements for recovery, retention, and has been routinely tested to ensure the backup can be restored.

Remember, a backup that cannot be restored is factually not a backup. Why? Well, going back to the definition of a backup, it is a fail-safe mechanism to be implemented in the event of a failure. If it cannot be implemented (in this case restored), then it provides no value because it cannot be used.

What about when you run into a problem even getting to the point of getting a successful backup operation? For instance, this error can be a bit troubling and may just cause you to lose plenty of time troubleshooting it.

Msg 3201, Level 16, State 1, Line 3
Cannot open backup device ‘\SomeServerC$MSSQL14MSSQLDATAFull_Backup2.bak’. Operating system error 1326(The user name or password is incorrect.).
Msg 3013, Level 16, State 1, Line 3
BACKUP DATABASE is terminating abnormally.

Sure, the error seems to report plenty of adequate information, but you may be surprised at the pain this particular error can cause.

Backup Device Permissions

If you search long enough you will come across numerous articles or forum posts that all pretty much have the same direction. They all point to the problem being a permissions issue. There are a few concerns with the permissions stigma however. In a recent issue for a client, we could verify that permissions were indeed not at fault. To ensure the permissions were not at fault, we granted local admin in the OS as well as sysadmin within SQL Server for the service account. In addition, we also tried a proxy account with full blown access in Windows and in SQL.

To take it a step further, we also ensured that the special permissions for service accounts were also in effect. Those permissions are:

1. Permission to bypass traverse checking (SeChangeNotifyPrivilege)
2. Permission to replace a process-level token (SeAssignPrimaryTokenPrivilege)
3. Permission to adjust memory quotas for a process (SeIncreaseQuotaPrivilege)
4. Permission to access this computer from the network (SeNetworkLogonRight)

None of this seemed to have any effect when trying to perform a database backup in SQLServer. In our case, we also ensured that the service account had full blown access to the folders as well.

Following the traditional troubleshooting measures, I created a virtual backup device as well only to get the same error. I tried to use the UNC from a remote server and it worked perfectly fine for all accounts in question. What could be going on? Then, I figured why not try the UNC from the server itself. Finally, a clue! Suddenly I was getting prompted for my credentials but my credentials would not work no matter what. Just for giggles, I verified my account was not locked out, and indeed my account was working just fine.

As it turns out, sometimes the problem is not really a permissions issue but it is a veiled security issue. You see, if I use the actual server name instead of the CNAME in all cases above, suddenly everything worked. The issue turns out to be more along the lines of a Windows issue and is documented here (still valid on Windows 2016).

Here is the basic SQL backup test script that led us down the path to the CNAME being the issue.

USE master;
GO
BACKUP DATABASE MAS_SYSTEM
TO
DISK = '\ServerName.yourdomain.comc$MSSQL14MSSQLDATAFull_Backup2.bak';
--Preceding Succeeds
BACKUP DATABASE MAS_SYSTEM
TO
DISK = '\CName.yourdomain.comc$MSSQL14MSSQLDATAFull_Backup2.bak';
--Preceding Fails with Error 1326
USE [master];
GO
EXEC master.dbo.sp_addumpdevice
@devtype = N'disk'
  , @logicalname = N'DB_BAK'
  , @physicalname = N'\CName.yourdomain.comc$MSSQL14MSSQLDATAFull_Backup.bak';
GO
BACKUP DATABASE YourDB TO DB_BAK;
GO
--Preceding fails with error 1326
EXEC master.dbo.sp_dropdevice @logicalname = N'DB_BAK';
GO
EXEC master.dbo.sp_addumpdevice
@devtype = N'disk'
  , @logicalname = N'DB_BAK'
  , @physicalname = N'\ServerName.yourdomain.comc$MSSQL14MSSQLDATAFull_Backup.bak';
GO
BACKUP DATABASE YourDB TO DB_BAK;
GO
--Preceding Succeeds

Put a bow on it

When running into error 1326, it makes plenty of sense to try to create a backup dump device (only as a testing exercise) as well as test the connectivity to the UNC path from the local server instead of a remote server. In my case, the problem was the local server was blocking access to a CName version of the server.

Interested in learning about some deep technical information instead? Check these out!

Want to learn more about your indexes? Try this index maintenance article or this index size article.

This is the eighth article in the 2019 “12 Days of Christmas” series. For the full list of articles, please visit this page.