SOLVED! Untrusted SAS certs after SQL startup

If you've tried doing Backup to URL with SQL Server using a Shared Access Signature (SAS) certificate and received this error:

Error: 18204, Severity: 16, State: 1.BackupDiskFile::CreateMedia: Backup device 'https://account.blob.core.windows.net/container/folder/msdb_log_202005170801.trn' failed to create. Operating system error 50(The request is not supported.).Cannot open backup device 'https://account.blob.core.windows.net/container/folder/msdb_log_202005170801.trn'. Operating system error 50(The request is not supported.). [SQLSTATE 42000] (Error 3201) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013). NOTE: The step was retried the requested number of times (1) without succeeding. The step failed. 

You may have received the same error I encountered.

This error popped up only after startup of SQL Server. To resolve the problem, we'd recreate the SAS key, using the same cert in the same script, and the backups would start working again. This affected all types of SQL database backups.

The issue was not our syntax (note the options, the syntax here is standard, but you do need to provide MAXTRANSFERSIZE = 4194304 and BLOCKSIZE = 65536 to backup to Azure blob storage.)

BACKUP DATABASE DB_name
TO URL = 'https://account.blob.core.windows.net/container/server-name/msdb_log_202005170801.trn'
WITH COMPRESSION, CHECKSUM, FORMAT, MAXTRANSFERSIZE = 4194304, BLOCKSIZE = 65536;

The issue was not the SAS key itself, which we tried creating via Storage Explorer, with PowerShell, with various expiration dates, with shared policies, etc.

Rather, the issue was with our SAS-based cert. Here was our syntax:

CREATE CREDENTIAL [https://account.blob.core.windows.net/container/server-name]
WITH IDENTITY='Shared Access Signature'
, SECRET='sv=2019-02-02&xxxxx';

Again, the syntax was correct in the sense that backups would start working again right away. But after a reboot, the cert would not longer be available to SQL. 

On the Azure Storage side, the logged failure was:

AuthenticationErrorDetail:Signed expiry time [Fri, 01 May 2020 13:30:52 GMT] has to be after signed start time [Tue, 12 May 2020 22:30:57 GMT]

Why would the exact same SAS key expire after reboot, but then start working again when it was dropped/created? And why wouldn't ALTER'ing the key work?

Our ticket with Microsoft moved through the storage team, the SQL team, and eventually the SQL engine engineering team, who did a complete stack dump to catch the error.

The issue turned out to be related to the hyphen in our credential creation above, note I included "server-name" because our server names have hyphens in them. Turns out, this is unnecessary and caused SQL to choke on trusting the certificate after startup. Here's the explanation from Microsoft support after analysis of the debugger dump:

When you create the credential, the data as we expected is stored in cache and is also persisted to disk but since the data is available in cache, we directly access with full URL name and it works. But after the SQL Server service is restarted and since the credential wont be available in cache, we try to query it from metadata and the way we do it there seems to be an issue on how we fragment the URL and we incorrectly look for container with foldername causing the issue. This is also why Alter wont work and only DROP\CREATE works since it’s in cache again.

We changed the credential to omit the server-specific folder name in the credential. Note that we had originally included the full path to the backup folder because I had read a little too much into this portion of the documentation: THE CREDENTIAL NAME argument requires that the name match the container path." 

This credential creation worked, and kept working after SQL startup.

CREATE CREDENTIAL [https://account.blob.core.windows.net/container] WITH IDENTITY='Shared Access Signature'
, SECRET='sv=2019-02-02&xxxxx';

Note the credential name ends with the container path but doesn't include the server name-specific subfolder, and still does not end with a '/'. 

The support escalation engineer marked the ticket as a bug and filed a defect with the engineering team, so we may have a solution in place for this soon. The end.

Oh, also, when we were first starting to use Backup to URL, we got this on some of our older instances of SQL Server:

"Backup/Restore to URL device error: Error while decoding the storage key."

Problem was trying to use an SAS key on SQL Server prior to 2016. Not supported! Gotta use the old syntax and a certificate for the  storage account identity and access key. 

Easy Backup History Review Script

I often use these scripts to check on the backups of all databases in an unfamiliar SQL Server instance, regardless of the platform that is taking the backups (SQL maintenance plans, T-SQL scripts in an agent job, and third-party software).

The query sorts databases by the backup type and then the recovery model configured. Because the backups are grouped by database name, it shows the most recent full backup for each database, most recent differential if it exists, and then the most recent transaction log backup (but only for databases in FULL or BULK_LOGGED recovery models).

Most importantly, it will show the most recent transaction log backup for all databases in FULL or BULK_LOGGED recovery models, even if a transaction log backup has never been taken.

If any of the dates look out of place, too old or NULL, they will stand out right away.

• The first two scripts are functionally equivalent. The first uses SQL 2005 system views (sys.databases) that are only appropriate if all databases are running SQL 2005 compatibility mode or higher. 
• The second script is using SQL 2000 system views (sysdatabases), which are now deprecated.
• The third script provides a granular list of all backup events by database, and is valid for SQL 2000 and up.

--sql2005 and above
select 
 database_Name
 , backuptype 
 , d.recovery_model_desc
 , BackupDate = MAX(BackupDate)
 from sys.databases d
 inner join 
 (
select distinct 
 database_name
 , backuptype = case type WHEN 'D' then 'Database'
       WHEN 'I' then 'Differential database'
       WHEN 'L' then 'Transaction Log'
       WHEN 'F' then 'File or filegroup'
       WHEN 'G' then 'Differential file'
       WHEN 'P' then 'Partial'
       WHEN 'Q' then 'Differential partial' END
 , BackupDate = MAX(backup_start_date)  
 from msdb.dbo.backupset bs       
 group by Database_name, type
 UNION 
 select distinct
 db_name(d.database_id)
 , backuptype = 'Database'
 , null
 FROM master.sys.databases d
 UNION
 select distinct
 db_name(d.database_id)
 , backuptype = 'Transaction Log'
 , null
  FROM master.sys.databases d
  where d.recovery_model_desc in ('FULL', 'BULK_LOGGED')
  
 ) a
 on db_name(d.database_id) = a.database_name
 group by database_name, backuptype, d.recovery_model_desc
order by backuptype, recovery_model_desc, BackupDate asc
 
 

 --sql 2000 and above
select distinct 
 database_name = d.name 
 , a.backuptype 
 , RecoveryModel = databasepropertyex(d.name, 'Recovery')  
 , BackupDate = Max(a.backup_start_date)  
 from master.dbo.sysdatabases d
 left outer join 
 (  select distinct 
   database_name
   , backuptype = case type WHEN 'D' then 'Database'
         WHEN 'I' then 'Differential database'
         WHEN 'L' then 'Transaction Log'
         WHEN 'F' then 'File or filegroup'
         WHEN 'G' then 'Differential file'
         WHEN 'P' then 'Partial'
         WHEN 'Q' then 'Differential partial' END
   , backup_start_date = MAX(backup_start_date)   
   from msdb.dbo.backupset bs       
   group by Database_name, type
   UNION 
   select distinct
     d.name
   , backuptype = 'Database'
   , null
   FROM master.dbo.sysdatabases d
   UNION
   select distinct
     d.name
   , backuptype = 'Transaction Log'
   , null
    FROM master.dbo.sysdatabases d
    where databasepropertyex(d.name, 'Recovery') in ('FULL', 'BULK_LOGGED')
  
 ) a
 on d.name = a.database_name
 group by d.name , backuptype , databasepropertyex(d.name, 'Recovery')
order by backuptype, RecoveryModel, BackupDate asc
 

--granular backup history
select distinct 
 database_name
 , type
 , backuptype = case type WHEN 'D' then 'Database'
       WHEN 'I' then 'Differential database'
       WHEN 'L' then 'Transaction Log'
       WHEN 'F' then 'File or filegroup'
       WHEN 'G' then 'Differential file'
       WHEN 'P' then 'Partial'
       WHEN 'Q' then 'Differential partial' END
 , BackupDate = backup_start_date
 , database_backup_lsn
 , bf.physical_device_name
 from msdb.dbo.backupset bs 
 left outer join msdb.dbo.[backupmediafamily] bf
 on bs.[media_set_id] = bf.[media_set_id]
 --where database_name = 'databasenamehere'
 --and type in ('d', 'i')
 
  order by database_name asc, backupdate desc
  

Potential Backup Software Issues with Read-Only Databases in FULL Recovery

If you decide to park a database in read-only mode for a long period of time, go ahead and alter it into SIMPLE recovery mode too.

The message returned by SQL when you try to take a transaction log backup is:
 "BACKUP LOG cannot modify the database because the database is read-only. The backup will continue, although subsequent backups will duplicate the work of this backup."
 which is fairly self-explanatory to a human.

But to an automated system, such as a third-party enterprise backup platform, this message is not always interpreted correctly.

 eVault, for example, does not detect the read-only condition or the message from SQL and instead returns this generic error message:

 "A gap was detected in log files for database foo. You will need to re-run a Full backup."

Which is misleading and incorrect and pointed troubleshooting efforts of backup administrators in the wrong direction.

Actual Emails: The Customer Doesn't Want Full Recovery Mode

Got this question in an email.  I'll paraphrase.

William 

Hoping you can advise me on best way to handle this. 

My client doesn’t want to use SQL backups. Instead, they are using VM and a third party backup software by choice.

Without a DBA on staff, they would prefer to deal with SQL the less the better.

They do not plan on doing any transaction logs and are not concerned with restore from point of failure.

... 

Goal would be to not have transaction logs or somehow keep those logs small.   

Colleague

Here's how I replied.

So, the first question to ask a client who doesn't want transaction log backups is, "why?" 

The transaction log is not something to be afraid of. Backing up the transaction log is a simple process and clears out the log. Backup the log at regular intervals (somewhere between 15 and 60min is typical). Then, you get all the advantages of point-in-time recovery, so if you have a 5:01pm disaster, you could restore all the way to 5:00pm, for example. Plus, full recovery mode is required for most SQL-based HA/DR scenarios.

Using a third party backup software is fine, but they must have purchased the SQL plugin.  This is usually an extra cost add-on for whatever backup license they bought.  You CANNOT backup an .mdf file with a file system backup and expect that to work. SQL MUST BACKUP SQL. 

That SQL plugin does exactly that for the third party software - sends commands to SQL to perform a backup. It's fine if they don't want to use sql maintenance plans or jobs to perform the backups, but they must use the sql plugin for any 3rd party enterprise backup software.

With a database in full recovery mode, you must perform full and transaction log backups, or the log will grow.

Typically, you do a nightly full and hourly tran log backups. The reason people get scared/confused/frustrated with transaction logs is that they don't realize you are supposed to back up the log (which empties it) regularly. As in, hourly at most. If they want the log to stay small, back it up every 15 minutes. BackupExec can do that. 

If they are really stubborn about not wanting to use all the DR capability that they paid for, putting the database in simple mode is fine. Just full sql backups are needed then. In simple mode, transactions aren't stored in the log after they commit. A transaction commits, there's a checkpoint, and then it is wiped from the log. A simple recovery mode database will have its transaction log grow to the size of the biggest single concurrent set of transactions at any given time. 

Never truncate a log file. Perform a log file backup, which will empty the file. The file may still be large though it is empty, in this case if you absolutely need the space back, you can shrink the log file. Never shrink a data or log file on a schedule. It creates fragmentation which hurts performance. 

Let me know if you have any questions! 

-William

Striped backups in SQL Server

Some DBA's don't realize that SQL server can perform a database backup that breaks up the files - stripes them - in a single command.  SQL can stripe the backup into an unlimited number of files, creating a multi-file backup set.