Comprehensive data protection for all workloads
DaveWatkins
Expert
Posts: 348
Liked: 92 times
Joined: Dec 13, 2015 11:33 pm
Contact:

SQL Backups and 9.5U3

Post by DaveWatkins » Jul 05, 2018 8:24 pm

Is anyone else seeing issues backing up SQL Servers?

Since upgrading to 9.5U3 (from U2) we're seeing either
Failed to retrieve SQL server databases information required to perform transaction logs backup
which then results is log backup failures and uncontrolled log growth.

or
Unable to truncate Microsoft SQL Server transaction logs. Details: Failed to call RPC function 'Vss.TruncateSqlLogs': Error code: 0x80004005. Failed to invoke func [TruncateSqlLogs]: Unspecified error. Failed to process 'TruncateSQLLog' command. Failed to logon user [domain\user]. Win32 error:Logon failure: the user has not been granted the requested logon type at this computer. Code: 1385
This isn't affecting all of our SQL servers, just a small subset that all exist on a separate domain

The user being used by B&R for these servers has sysadmin rights on all the SQL Servers and admin rights on the machines themselves. All these VM's were being successfully backed up with U2

Case # 03087011

Thanks
Dave

dynaudio
Lurker
Posts: 1
Liked: never
Joined: Jul 06, 2018 6:33 am
Full Name: Michael Grohnheit
Contact:

Re: SQL Backups and 9.5U3

Post by dynaudio » Jul 06, 2018 6:38 am

Hi.

After updating from 9.5U3 to 9.5U3a we got this error on a lot of servers:
Unable to truncate Microsoft SQL Server transaction logs. Details: Failed to call RPC function 'Vss.TruncateSqlLogs': Error code: 0x80004005. Failed to invoke func [TruncateSqlLogs]: Unspecified error. Failed to process 'TruncateSQLLog' command. Failed to logon user [domain\user]. Win32 error:Logon failure: the user has not been granted the requested logon type at this computer. Code: 1385.
Our backup user is domain admin, and everything was working flawless on 9.5U3.

tschw_ham
Service Provider
Posts: 3
Liked: never
Joined: Nov 08, 2017 2:06 pm
Contact:

Re: SQL Backups and 9.5U3

Post by tschw_ham » Jul 06, 2018 9:06 am

Hello Dave,

I was just going to open a new thread to the same issue. I updated Veeam B&R at one of our customers from version 9.5u2 to 9.5u3a (Build 1922). After the update Veeam could no longer truncate SQL Logs on one SQL Server. It worked fine until the Update.

Error Message:
02.07.2018 16:25:26 4624 ERR BlobCall() error.
02.07.2018 16:25:26 4624 ERR Failed to process 'TruncateSQLLog' command.
02.07.2018 16:25:26 4624 ERR Failed to logon user [###\###]
02.07.2018 16:25:26 4624 ERR Win32 error:Anmeldung fehlgeschlagen: Der Benutzer besitzt nicht den benötigten Anmeldetyp auf diesem Computer.
02.07.2018 16:25:26 4624 ERR Code: 1385
As mentioned in https://www.veeam.com/kb2447 we already added the db_datawriter role to the Backup User in the msdb database.

SQL Cluster or Availability Groups: No
SQL Version: SQL 2014 12.0.5571.0



I already opened a Case at Veeam Support # 03084266

Regards
Tobias

gurneetech
Novice
Posts: 9
Liked: 4 times
Joined: Jun 23, 2014 1:51 pm
Contact:

Re: SQL Backups and 9.5U3

Post by gurneetech » Jul 06, 2018 1:24 pm

I am seeing this as well. I have a several different jobs, some configured with domain admin, some with a domain user user granted SQL sysadmin.
Both ways are failing, both worked without issue before the update.

I am also seeing "Failed to retrieve SQL server databases information required to perform transaction logs backup" for VM's that do not have SQL.

Edit:
Enumerating SQL Server databases
Failed to logon UPN user [user@domain.local] Win32 error:Logon failure: the user has not been granted the requested logon type at this computer. Code: 1385
Transaction logs for the following databases were not backed up: <database list>
Transaction logs from some databases were not backed up for the last 7 intervals
Edit#2: I should clarify the versions. T-log backups were working in 9.5U3, this issue developed after 9.5U3a.

foggy
Veeam Software
Posts: 18157
Liked: 1542 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: SQL Backups and 9.5U3

Post by foggy » Jul 06, 2018 2:35 pm

Could you all please check that Deny log on as a batch job is turned OFF for the corresponding account? This requirement was changed in U3a and might be the reason of the observed behavior.

gurneetech
Novice
Posts: 9
Liked: 4 times
Joined: Jun 23, 2014 1:51 pm
Contact:

Re: SQL Backups and 9.5U3

Post by gurneetech » Jul 06, 2018 8:24 pm

foggy wrote:Could you all please check that Deny log on as a batch job is turned OFF for the corresponding account? This requirement was changed in U3a and might be the reason of the observed behavior.
"Deny log on as a batch job" is not set for any of our users in our domain or local GPOs. "Log on as a batch job" is explicitly set for some required accounts, including the one we were using the the primary SQL backup.

This account was not the Administrator account, in testing I found that UAC and RPC may not play nice unless the account used is 'administrator'

Thinking this may be the issue, I added the Domain admin account to the BuiltIn\Backup Operators group, which is defined in the "Log on as a batch job" GPO. I then changed the SQL jobs from the explicitly defined account for Veeam SQL access to the domain admin account.

Unfortunately, still:
7/6/2018 2:02:49 PM :: Failed to logon UPN user [administrator@domain.local]
Win32 error:Logon failure: the user has not been granted the requested logon type at this computer.
Code: 1385

DaveWatkins
Expert
Posts: 348
Liked: 92 times
Joined: Dec 13, 2015 11:33 pm
Contact:

Re: SQL Backups and 9.5U3

Post by DaveWatkins » Jul 07, 2018 3:53 am 1 person likes this post

In our case the SQL servers not backing up are all on another domain (inherited network) and while they don't have any accounts set to deny batch logins, they do have a GPO set to allow specific accounts to Log in as a batch job. I've added my backup user to that GPO and am trying a test backup now.

Sadly so far in my ticket I've just had to tell them for the third time that the backup user has sysadmin access on the SQL server, they seem fixated on that and don't seem to go any further than that which is disappointing given I've been having to manually control log growth on all of these while this is going on.

Foggy, this sort of thing is why it would be nice if the release notes were significantly more comprehensive. While I've been typing this the backup has completed successfully so in my case this is the fix. It's not enough for them not to be assigned the Deny login as batch job right. If you have anything set in the "Logon as batch job" then your account needs to be added there as anything not in that list is effectively denied.

I'm disappointed the support agent hasn't gotten this far and just kept asking if the user has sysadmin privileges, also disappointed this sort of this isn't included in the release notes for the update. It was obviously known and the main documentation was updated, but even if I had gone there I can't see any way I would know this had changed just for U3a. Is there realistically any way I could have known this and planned for it?

Thanks
Dave

Gostev
SVP, Product Management
Posts: 24641
Liked: 3468 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: SQL Backups and 9.5U3

Post by Gostev » Jul 07, 2018 8:29 pm

DaveWatkins wrote:Is there realistically any way I could have known this and planned for it?
No - it's totally our mistake, and I apologize.

I personally create the release notes to ensure nothing important is omitted, and I do that by reviewing raw data of all changes and enhancements as provided by the team leaders. This particular item simply was not included in the raw data provided to me, otherwise of course it would have been highlighted. I've already expressed my frustration internally - and we will put some process in place to make sure this does not happen again.

DEM
Service Provider
Posts: 7
Liked: 2 times
Joined: Oct 25, 2017 3:04 pm
Full Name: Demando
Contact:

[MERGED] SQL Warning after Update 3a

Post by DEM » Jul 08, 2018 6:44 pm

After Update 3a we are receiving the following warning message for several SQL database backups:
[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.
What does that mean? New permissions needed?

mats.jansson
Service Provider
Posts: 17
Liked: 1 time
Joined: Mar 18, 2014 9:13 am
Full Name: Mats

Re: SQL Warning after Update 3a

Post by mats.jansson » Jul 08, 2018 8:19 pm

Hi
We are also having a lot of new sql backup warnings.
It turned out that the backup account not having sysadmin permission.
I looked at old backup logs before updade3a and the sql warnings was alreade there. I think Veeam have updated the error handling in updare3a.
Mats

foggy
Veeam Software
Posts: 18157
Liked: 1542 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: [MERGED] SQL Warning after Update 3a

Post by foggy » Jul 08, 2018 8:50 pm

Mats, please check if you're seeing messages similar to the ones described in the thread above.
DEM wrote:What does that mean? New permissions needed?
I will check regarding this message though, since it differs (and somehow slipped away during proofreading).

DaveWatkins
Expert
Posts: 348
Liked: 92 times
Joined: Dec 13, 2015 11:33 pm
Contact:

Re: SQL Backups and 9.5U3

Post by DaveWatkins » Jul 08, 2018 11:01 pm

Gostev wrote:No - it's totally our mistake, and I apologize.

I personally create the release notes to ensure nothing important is omitted, and I do that by reviewing raw data of all changes and enhancements as provided by the team leaders. This particular item simply was not included in the raw data provided to me, otherwise of course it would have been highlighted. I've already expressed my frustration internally - and we will put some process in place to make sure this does not happen again.
Thanks for this, I wonder if this is also related to why the support side hasn't found this yet. Worth mentioning perhaps is how the main documentation got updated but you got missed, seems like someone knew about this change to get he main documentation updated.

Gostev
SVP, Product Management
Posts: 24641
Liked: 3468 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: SQL Backups and 9.5U3

Post by Gostev » Jul 08, 2018 11:36 pm

Actually, the main documentation was ninja-updated already as a result of the first posts in this thread :D but indeed, the change was well known internally, particularly to the QC team testing SQL Server processing. They responded to me right away suggesting to check Deny log on as a batch job setting after I sent them your original post.

nathano
Veeam Vanguard
Posts: 55
Liked: 1 time
Joined: Sep 05, 2016 5:08 am
Full Name: Nathan Oldfield
Contact:

Re: SQL Backups and 9.5U3

Post by nathano » Jul 09, 2018 4:01 am

had a similar SQL issue. in my case I uninstalled the SQL instance, was no longer required. Was a trial of Dell Wyse management stuff from a while ago :-)

foggy
Veeam Software
Posts: 18157
Liked: 1542 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: [MERGED] SQL Warning after Update 3a

Post by foggy » Jul 09, 2018 12:20 pm 2 people like this post

DEM 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.
Please check whether the account has db_datawriter permission on msdb database. This permission was required in previous releases as well, but now Veeam B&R displays this warning in case the permission is not granted.

gurneetech
Novice
Posts: 9
Liked: 4 times
Joined: Jun 23, 2014 1:51 pm
Contact:

Re: SQL Backups and 9.5U3

Post by gurneetech » 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!

Gostev
SVP, Product Management
Posts: 24641
Liked: 3468 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: SQL Backups and 9.5U3

Post by Gostev » 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.

ae_group_ag
Lurker
Posts: 1
Liked: never
Joined: Jul 09, 2018 5:28 am
Full Name: ae group
Contact:

Re: SQL Backups and 9.5U3

Post by ae_group_ag » 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".

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

Re: [MERGED] SQL Warning after Update 3a

Post by kvoelpel » 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

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

Re: [MERGED] SQL Warning after Update 3a

Post by LMyers » 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

hansvh
Influencer
Posts: 10
Liked: 1 time
Joined: Feb 26, 2013 11:24 am
Full Name: Hans
Contact:

Re: [MERGED] SQL Warning after Update 3a

Post by hansvh » 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.

GodzillaSnacks
Lurker
Posts: 1
Liked: never
Joined: Jul 10, 2018 6:35 am
Full Name: Simon Hawkes
Contact:

Re: SQL Backups and 9.5U3

Post by GodzillaSnacks » 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?

melliott7
Novice
Posts: 3
Liked: never
Joined: Jul 09, 2018 12:30 pm
Full Name: Marcus Elliott
Contact:

Re: SQL Backups and 9.5U3

Post by melliott7 » 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'.

mmonroe
Enthusiast
Posts: 75
Liked: 3 times
Joined: Jun 16, 2010 8:16 pm
Full Name: Monroe
Contact:

Re: SQL Backups and 9.5U3

Post by mmonroe » Jul 10, 2018 8:04 pm 1 person likes this post

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.

Morgenstern
Influencer
Posts: 22
Liked: 2 times
Joined: Jan 30, 2014 2:29 pm
Full Name: Martin Anděl
Contact:

Re: SQL Backups and 9.5U3

Post by Morgenstern » 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'.

Gostev
SVP, Product Management
Posts: 24641
Liked: 3468 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: SQL Backups and 9.5U3

Post by Gostev » 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.

Morgenstern
Influencer
Posts: 22
Liked: 2 times
Joined: Jan 30, 2014 2:29 pm
Full Name: Martin Anděl
Contact:

Re: SQL Backups and 9.5U3

Post by Morgenstern » 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?

SBarrett847
Service Provider
Posts: 305
Liked: 40 times
Joined: Feb 02, 2016 5:02 pm
Full Name: Stephen Barrett
Contact:

Re: SQL Backups and 9.5U3

Post by SBarrett847 » 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.

Gostev
SVP, Product Management
Posts: 24641
Liked: 3468 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: SQL Backups and 9.5U3

Post by Gostev » 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
SVP, Product Management
Posts: 24641
Liked: 3468 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: SQL Backups and 9.5U3

Post by Gostev » 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.

Post Reply

Who is online

Users browsing this forum: Bing [Bot], tcranger and 60 guests