search

trimble-oss / sql-log-shipping-service

This project provides a solution for automatically restoring SQL Server transaction log backups. Designed for scale. Works directly with backups in Azure blob or UNC path
MIT License
46 stars 7 forks source link

Unable to work with SQL 2012-SP4 using SAS token #114

Open SQLISAwsome opened 1 month ago

SQLISAwsome commented 1 month ago

Please see error below. Any way to modify the program to login using storage account access key instead of SAS token. Also what is the usage of LogFilePath? I'm using blob storage for backup/restore. "LOG/SERVERNAME/{DatabaseName}/ Thanks!

2024-09-19 11:32:19.324 -04:00 [INF] Starting log restore iteration 1 <1> 2024-09-19 11:32:19.686 -04:00 [INF] GetDatabases completed in 350.9 ms <1> 2024-09-19 11:32:19.698 -04:00 [INF] Get logs for MYDB after 09/19/2024 02:58:45 (Offset:0) from LOG/SERVERNAME/MYDB/ completed in 0.7 ms <7> 2024-09-19 11:32:19.730 -04:00 [DBG] MYDB Redo Start LSN: 6308030000000657600001 <7> 2024-09-19 11:32:19.916 -04:00 [ERR] Error restoring logs for MYDB <7> System.AggregateException: One or more errors occurred. (This request is not authorized to perform this operation. RequestId:cfdc444b-c01e-0024-21a9-0a3e3f000000 Time:2024-09-19T15:32:19.8974252Z Status: 403 (This request is not authorized to perform this operation.) ErrorCode: AuthorizationFailure

DavidWiseman commented 1 month ago

Hi,

On your log shipping secondary, ensure you have created the credential using the storage access key instead of using a SASToken. 

CREATE CREDENTIAL [<mycredentialname>] WITH IDENTITY = '<mystorageaccountname>'  
,SECRET = '<mystorageaccountaccesskey>';

The SASToken is only supported from SQL 2016 onwards so you will need the storage account key here.

The log shipping app needs to be able to enumerate the files in the blob container. I haven't tested this with a storage account key but there should be no issues using a SAS Token here (even if it's not supported on SQL 2012). The app is just using this to enumerate the files and generate the restore statements.

LogShippingService.exe --SASToken "?sp=..."

I might look at supporting other authentication options at some point, but hopefully this works for you. Let me know if you still have issues.

Note: Using a SASToken is preferred on newer SQL Server versions - it supports larger backups, performs better and it's cheaper. One of our teams saved money a while back switching from page blocks to block blobs (SASToken). It could help justify an upgrade to a newer version of SQL Server.

Thanks,

David