SQL Backups and 9.5U3

Availability for the Always-On Enterprise

Re: SQL Backups and 9.5U3

Veeam Logoby gurneetech » Mon Jul 09, 2018 1:33 pm 1 person likes this post

Update on my issue: I was mistaken, the permission change did fix it after the next full backup came around. Makes sense, as the errors I was watching were on scheduled T-Log backups and those must chain off of the full. The full ran later Friday night, and its been good since.

Thanks folks!
gurneetech
Novice
 
Posts: 9
Liked: 2 times
Joined: Mon Jun 23, 2014 1:51 pm

Re: SQL Backups and 9.5U3

Veeam Logoby Gostev » Mon Jul 09, 2018 1:57 pm

That, or perhaps it worked after a periodic Group Policy update. By the way, it can be forced with gpupdate /force command.
Gostev
Veeam Software
 
Posts: 22401
Liked: 2676 times
Joined: Sun Jan 01, 2006 1:01 am
Location: Baar, Switzerland

Re: SQL Backups and 9.5U3

Veeam Logoby ae_group_ag » Mon Jul 09, 2018 1:58 pm

We have the same issue but with Windows Server 2003. I added our veeam service user to the sysadmin role but this doesn't solved it.
The error message is "Logon failure: the user has not been granted the requested logon type at this computer".
ae_group_ag
Lurker
 
Posts: 1
Liked: never
Joined: Mon Jul 09, 2018 5:28 am
Full Name: ae group

Re: [MERGED] SQL Warning after Update 3a

Veeam Logoby kvoelpel » Mon Jul 09, 2018 2:34 pm 2 people like this post

foggy wrote:[TDB]Unable to update SQL backupset for instance X Code = 0x80040e09 Code meaning = IDispatch error #3081 Source = Microsoft OLE DB Provider for SQL Server Description = Die UPDATE-Berechtigung wurde für das backupset-Objekt, msdb-Datenbank, dbo-Schema, verweigert.

foggy wrote:Please check whether the account has db_datawriter permission on msdb. This permission was required in previous releases as well, but now Veeam B&R displays this warning in case the permission is not granted.


I have the same problem. The account which Veeam B&R is using for guest-processing has already "Sysadmin"-right on the SQL Express Server (2014).
There should be no Windows-related problems because it is the built-in local "Administrator" and no GPOs enabled to deny any permissions (i.e. for logon as batch)
It worked until the day before the installation of update 3a

Regards
Kristian

Case # 03092462
kvoelpel
Lurker
 
Posts: 1
Liked: 2 times
Joined: Mon Jul 09, 2018 7:52 am
Full Name: Kristian Völpel

Re: [MERGED] SQL Warning after Update 3a

Veeam Logoby LMyers » Mon Jul 09, 2018 6:23 pm 3 people like this post

[TDB]Unable to update SQL backupset for instance SQLEXPRESS : Code = 0x80040e09 Code meaning = IDispatch error #3081 Source = Microsoft OLE DB Provider for SQL Server Description = The UPDATE permission was denied on the object 'backupset', database 'msdb', schema 'dbo'.

This is happening to me now, too. Unfortunately, I'm the Backup Admin - not the SQL Admin. I don't know how to check the permissions of the backup account at the msdb level.

Leslie
LMyers
Lurker
 
Posts: 2
Liked: 3 times
Joined: Tue May 23, 2017 4:45 pm
Full Name: Leslie Myers

Re: [MERGED] SQL Warning after Update 3a

Veeam Logoby hansvh » Tue Jul 10, 2018 6:11 am

LMyers wrote:[TDB]Unable to update SQL backupset for instance SQLEXPRESS : Code = 0x80040e09 Code meaning = IDispatch error #3081 Source = Microsoft OLE DB Provider for SQL Server Description = The UPDATE permission was denied on the object 'backupset', database 'msdb', schema 'dbo'.
Leslie


Exactly the same error here on some (but not all) SQL instances. Got the error on a SQL Express as well as a full-blown SQL server version, backup of other SQL servers do not report any errors. In both cases where I get the error it only reports an error on one of the databases on that particular server. Account used for Veeam backups is local administrator on the server and has sysadmin rights on the SQL instance.
hansvh
Influencer
 
Posts: 10
Liked: 1 time
Joined: Tue Feb 26, 2013 11:24 am
Full Name: Hans

Re: SQL Backups and 9.5U3

Veeam Logoby GodzillaSnacks » Tue Jul 10, 2018 6:41 am

Hi all,

We've got the same issue;

Failed to truncate Microsoft SQL Server transaction logs. Details: Failed to process 'TruncateSQLLog' command. Failed to logon user [******\********] Win32 error:Logon failure: the user has not been granted the requested logon type at this computer. Code: 1385


It's definitely only been since I upgraded to 9.5 Update 3A, it ran prior to that.

Oddly, all 2008 R2 SQL onwards backup jobs have been fine. The two that are issuing the warning are Windows 2003 Server / SQL 2005 servers. We've got SQL 2008 R2 running on Server 08 R2, SQL 12 on Server 12 R2 so it might just be an older windows issue?
GodzillaSnacks
Lurker
 
Posts: 1
Liked: never
Joined: Tue Jul 10, 2018 6:35 am
Full Name: Simon Hawkes

Re: SQL Backups and 9.5U3

Veeam Logoby melliott7 » Tue Jul 10, 2018 2:04 pm

Hi everybody,

We are running into the same issue as well since the 3a update. Its really weird because the first night we ran on the 3a upgrade I got three warnings from 3 different servers whereas before the update everything completed without any warnings or errors. The first was a older server that had sql server 2005 and wasn't being used so I just uninstalled SQL and that corrected the issue in Veeam. With the second server all I did was give our service account "sysadmin" rights in SQL server and that corrected the issue in Veeam as well. However on this third server that has the same version of Windows(2008 R2) and SQL 2012 as the 2nd server Veeam still gives us the following warning and that's with the sysadmin role along with the following permissions https://www.veeam.com/kb2447

TDB]Unable to update SQL backupset for instance : Code = 0x80040e09 Code meaning = IDispatch error #3081 Source = Microsoft OLE DB Provider for SQL Server Description = The UPDATE permission was denied on the object 'backupset', database 'msdb', schema 'dbo'.
melliott7
Novice
 
Posts: 3
Liked: never
Joined: Mon Jul 09, 2018 12:30 pm
Full Name: Marcus Elliott

Re: SQL Backups and 9.5U3

Veeam Logoby mmonroe » Tue Jul 10, 2018 8:04 pm

Why didnt Veeam simply add a new option for logging in as a batch job and allow those with no issues to continue using the previous method?

I have been fighting SQL issues now for two days and still don't have things working and I am working with support on the "issues". This U3a update has made a mess of my critical backups.

This whole update has been a nightmare and I wish now that I hadn't loaded it. I had a full schedule on the calendar and now I am playing Veeam repair guy trying to get my SQL backups to work.
mmonroe
Enthusiast
 
Posts: 71
Liked: 2 times
Joined: Wed Jun 16, 2010 8:16 pm
Full Name: Monroe

Re: SQL Backups and 9.5U3

Veeam Logoby Morgenstern » Tue Jul 10, 2018 11:50 pm

Yeah, having the same issue on both Server 2008R2 with SQL 2008R2 Standard and 2012 R2 with SQL Studio 2016. Really frustrating! Is this worth opening a support ticket? Do Veeam have a clear set of instructions somewhere on how to adress this?

Veeam B&R 9.5U3a

[TDB]Unable to update SQL backupset for instance : Code = 0x80040e09 Code meaning = IDispatch error #3081 Source = Microsoft OLE DB Provider for SQL Server Description = The UPDATE permission was denied on the object 'backupset', database 'msdb', schema 'dbo'.
Morgenstern
Influencer
 
Posts: 22
Liked: 2 times
Joined: Thu Jan 30, 2014 2:29 pm
Full Name: Martin Anděl

Re: SQL Backups and 9.5U3

Veeam Logoby Gostev » Wed Jul 11, 2018 8:08 am

mmonroe wrote:Why didnt Veeam simply add a new option for logging in as a batch job and allow those with no issues to continue using the previous method?

I agree with you that this change was not implemented correctly, and I am very sorry about that. When changing existing product behavior, normally we always implement transparent failover to "old" way of doing things if "new" way fails, to ensure smooth transition. So I am also puzzled why this usual practice was not used by developers this particular time, and already expressed my frustration to R&D leadership. They will perform the investigation and implement the process to prevent this from happening in future.
Gostev
Veeam Software
 
Posts: 22401
Liked: 2676 times
Joined: Sun Jan 01, 2006 1:01 am
Location: Baar, Switzerland

Re: SQL Backups and 9.5U3

Veeam Logoby Morgenstern » Wed Jul 11, 2018 8:41 am

I agree with you that this change was not implemented correctly, and I am very sorry about that. When changing existing product behavior, normally we always implement transparent failover to "old" way of doing things if "new" way fails, to ensure smooth transition. So I am also puzzled why this usual practice was not used by developers this particular time, and already expressed my frustration to R&D leadership. They will perform the investigation and implement the process to prevent this from happening in future.

Okay, that's a good plan, but is there a solution to this issue available in the meantime? Something I could tell my SQL admin to do to fix it?
Morgenstern
Influencer
 
Posts: 22
Liked: 2 times
Joined: Thu Jan 30, 2014 2:29 pm
Full Name: Martin Anděl

Re: SQL Backups and 9.5U3

Veeam Logoby SBarrett847 » Wed Jul 11, 2018 10:22 am 1 person likes this post

Update 3a has been a nightmare for me (for other issues) but this seems to be my remaining issue.
SBarrett847
Service Provider
 
Posts: 244
Liked: 39 times
Joined: Tue Feb 02, 2016 5:02 pm
Full Name: Stephen Barrett

Re: SQL Backups and 9.5U3

Veeam Logoby Gostev » Wed Jul 11, 2018 10:54 am

Morgenstern wrote:Okay, that's a good plan, but is there a solution to this issue available in the meantime? Something I could tell my SQL admin to do to fix it?

As far as I understand, for the issue with "Unable to update SQL backupset" error, the immediate fix is db_datawriter permission on msdb database for the account you use for SQL processing. And for the original issue in this topic with "the user has not been granted the requested logon type at this computer" error, the account must be granted Logon as a batch job right.

In addition to that, most likely, we will release a hot fix that makes this an informational event (this changing the behavior back to what it was pre-U3a). This is being discussed right now.

@Stephen, can you please send me the other issues via a private message (not to derail this thread)? Just for my information, because I have not seen any other common issues with U3a either here on forums or in support, so a bit puzzled.
Gostev
Veeam Software
 
Posts: 22401
Liked: 2676 times
Joined: Sun Jan 01, 2006 1:01 am
Location: Baar, Switzerland

Re: SQL Backups and 9.5U3

Veeam Logoby Gostev » Wed Jul 11, 2018 11:43 am

For the issue with "Unable to update SQL backupset" error, the following are SQL queries that needs to be executed under account using for SQL processing to test if the appropriate SQL permissions are granted:

Code: Select all
use msdb 
select CAST(1 AS BIT)
FROM fn_my_permissions (Null, 'database')
where permission_name = 'Update'
This SQL query should return 1.

Code: Select all
use msdb
SELECT * FROM fn_my_permissions (Null, 'database')
order by permission_name
GO

use msdb
SELECT * FROM fn_my_permissions ('dbo.backupset', 'OBJECT')
order by permission_name
GO
These queries must return, in particular, INSERT, DELETE and UPDATE.

Code: Select all
update [msdb].[dbo].[backupset]
set name = 'test'
where backup_set_id = <any_backup_set_id>
This is the final test that tries to do the same our product does. If permissions are lacking, you will see the same error about lack of UPDATE permission that you see in our UI.
Gostev
Veeam Software
 
Posts: 22401
Liked: 2676 times
Joined: Sun Jan 01, 2006 1:01 am
Location: Baar, Switzerland

PreviousNext

Return to Veeam Backup & Replication



Who is online

Users browsing this forum: superbeta and 62 guests