"Login Failed" on Azure SQL Snapshot


#1

We are evaluating SQLBackupAndFTP for backing up Azure SQL, restoring and notifications for success and failures. Unfortunately I am unable to make the backup job work reliably.

I have created a backup job in SQLBackupAndFTP. The first few backups went fine but now it fails almost always with the error below.

5/27/2019 8:45:43 AM	[Error]	Failed to backup "dm365" database with "Full" backup type: Could not extract package from specified database. > Unable to reconnect to database: Cannot open database "dm365_SQLBackupAndFTP_Snapshot" requested by the login. The login failed.
Login failed for user 'MetaShareBackup'. > Cannot open database "dm365_SQLBackupAndFTP_Snapshot" requested by the login. The login failed.
Login failed for user 'MetaShareBackup'.

I have tried to create a snapshot manually using T-SQL.

CREATE DATABASE dm365_SQLBackupAndFTP_Snapshot AS COPY OF dm365

In that case the snapshot database is created successfully and I can query it. I use same same account in both cases. This is a custom SQL account with the dbmanager and db_owner roles.

Making a DACPAC-export from the Azure Portal also finishes without errors. Again, using the same account as I use for the backup job.

If I stop the SQLBackupAndFTP process from the task manager before it gets a chance to drop the snapshot database I find that I cannot login to that database from SSMS.

To me it looks like something is wrong with SQLBackupAndFTP since I can do both snapshot and export using the same credentials and the snapshot database created by SQLBackupAndFTP is not working as expected.

Full log (edited a bit since I can only have 2 links in a post):

5/27/2019 8:44:47 AM	[Info]	Starting job "Backup Job - 1 " - "Full". Computer "CLIENT-01". App v."12.2.9"
5/27/2019 8:44:47 AM	[Info]	Connecting to SQL Azure: 2014 RTM SQL Azure. foobarDOTdatabaseDOTwindowsDOTnet
5/27/2019 8:44:48 AM	[Info]	The backup folder "\SQLBackupAndFTP\backup" has "15,338GB" free space. The temporary folder "\SQLBackupAndFTP\backup" has "15,338GB" free space
5/27/2019 8:44:48 AM	[Info]	Backing up "foobarDOTdatabaseDOTwindowsDOTnet" SQL Azure databases
5/27/2019 8:44:48 AM	[Debug]	Getting maximum file size...
5/27/2019 8:44:48 AM	[Debug]	The maximum file size: ""
5/27/2019 8:44:48 AM	[Info]	Creating snapshot of "dm365" database to "dm365_SQLBackupAndFTP_Snapshot"
5/27/2019 8:45:18 AM	[Info]	Backing up "dm365_SQLBackupAndFTP_Snapshot" database to .bacpac file
5/27/2019 8:45:33 AM	[Info]	Drop "dm365_SQLBackupAndFTP_Snapshot"
5/27/2019 8:45:43 AM	[Error]	Failed to backup "dm365" database with "Full" backup type: Could not extract package from specified database. > Unable to reconnect to database: Cannot open database "dm365_SQLBackupAndFTP_Snapshot" requested by the login. The login failed.
Login failed for user 'MetaShareBackup'. > Cannot open database "dm365_SQLBackupAndFTP_Snapshot" requested by the login. The login failed.
Login failed for user 'MetaShareBackup'.
5/27/2019 8:45:44 AM	[Info]	Cleaning up backups of "Backup Job - 1 " job
5/27/2019 8:45:45 AM	[Error]	Job "Backup Job - 1 " finished with "1" errors

I have sent Advanced logs to the developers.

Application ID: 4a4d3a82-c76b-4e52-af46-3201e8b57e94
Version: 12.2.9


#2

Hello Gabriel_Smoljar,

Thank you for the details.

Please give us some time to investigate this issue.

Sorry for the inconvenience.


#3

Thanks for looking into this. I should also add that during my investigation I found that after I created a snapshot with PowerShell I could see that there was failed jobs on that newly created database in the Azure Portal. I created this snapshot with the same name as the one generated by SQLBackupAndFTP. Perhaps it would be more reliable if each run had a different name on the snapshot with a timestamp or something. This is just a guess from my side.


#4

Dear Gabriel,

Besides the snapshot creation command, we execute another one, that either puts the snapshot in the same pool as the original database or changes the edition to the same as the original database.

We have additionally logged this issue, could you please download and install Alpha version http://sqlbackupandftp.com/download/alpha enable the advanced log (“Tools” > “Advanced Log” > “Log enabled”) reproduce the issue and find the script that does this in your advanced logs (“Tools” > “Advanced Log” > “Open folder”) by look for the substring:

Pranas.NET.SqlBak.Job.Sql.Impl.Operations.AzureDatabaseOperations | SetAzureConfiguration. Execute script

Could you please try to execute this script after manually creating a snapshot. And check if there is a given error.

Also, we noticed that immediately after creation - a database is not available for some time. In Alpha version, we have improved the processing of this moment, perhaps this will resolve that problem (the application will try to connect to the database for some time).

Sorry for the inconvenience.


#5

I just tried 3 consecutive backups after installing the Alpha. Did not manage to reproduce the issue.

I found this in the log:

2019-05-30 15:57:34.2597|DEBUG|24|Pranas.NET.SqlBak.Job.Sql.Impl.Operations.AzureDatabaseOperations|SetAzureConfiguration. Execute script: ALTER DATABASE [dm365_SQLBackupAndFTP_Snapshot] MODIFY (EDITION='Basic', SERVICE_OBJECTIVE='Basic').

Running the script on a manually created snapshot did not produce any errors.

I reverted to the stable version of SQLBackupAndFTP and could confirm that the issue is still reproducible with it.

If a backup fails in the new version because it could not connect to the snapshot, will there be a specific error message instead of the generic “Login Failed” message?


#6

Hi Gabriel,

Good news! We recommend you use SQLBackupAndFTP Alpha version, it will be updated automatically to stable once the stable version will be released

The “Login Failed” error message is sent by Azure, and except this error message, we cannot write anything. It seems that Azure SQL right after a database’s copy is made, tunes access rights to that copy during some time, and at this time interval, the issue happens. In the Alpha version, we have added the processing in case of receiving an error after creating a snapshot, SQLBackupAndFTP for some time tries to reconnect to the database. If during 3 minutes the connection isn’t set then SQLBackupAndFTP displays the last error message that is sent by Azure.

Sorry for the inconvenience.


#8

Hi Gabriel,

You can export to .bacpac only those objects that are available for a user. To check what objects are available please use SSMS. It seems backupadmin user doesn’t see other users. Perhaps, in your case, you should use Server admin user for backups.

Sorry for the inconvenience.


Tables with Row Level Security not restored