Comprehensive data protection for all workloads
foggy
Veeam Software
Posts: 18158
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 11, 2018 11:47 am 1 person likes this post

ae_group_ag wrote: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".
Logon as batch job right is still required even if the account has sysadmin role.

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

Re: SQL Backups and 9.5U3

Post by hansvh » Jul 11, 2018 12:45 pm

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.
Hmmm. This does not seem to work. I've got a replication job running of a SQL server with this issue. Gave the Veeam user which runs the job the db_datawriter permission on the SQL server by running this query:

Code: Select all

use msdb
go

alter role db_datawriter add member [username];
go
Job still finishes with same warning. Am I missing something?

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

Re: SQL Backups and 9.5U3

Post by Gostev » Jul 11, 2018 12:52 pm

Please use test SQL queries from the last post on the previous page to troubleshoot this (or open a support case for assistance).

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

Re: SQL Backups and 9.5U3

Post by hansvh » Jul 11, 2018 1:18 pm 1 person likes this post

hansvh wrote:Job still finishes with same warning. Am I missing something?
I indeed was missing something :oops: . The useraccount that runs the backups has local admin rights on the database server as well as SQL sysadmin rights on the database itself. Completely forgot about another SQL server instance running on the same server (sigh). Gave the Veeam user sysadmin rights on that instance as well which solved my problem!

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 11, 2018 1:43 pm

Hi Gostev,

I ran the first 2 test quires and It had the correct output on those. However when I go to run the third query it gave me this message below.

!!Here is the query I copied from your post!!

update [msdb].[dbo].[backupset]
set name = 'test'
where backup_set_id = <any_backup_set_id>


!!Here is the error message I recieved!!!

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '<'.

Let me know what you think when you get a chance.

Thanks,

foggy
Veeam Software
Posts: 18158
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 11, 2018 1:52 pm

You should specify a valid ID from msdb.dbo.backupset table here. You can take any from the select backup_set_id from msdb.dbo.backupset output.
Also, please ensure you're executing it under the user that is specified in the job and on the same instance you're getting the warning for.

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

Re: SQL Backups and 9.5U3

Post by Gostev » Jul 11, 2018 9:25 pm

All, we have created a hotfix that does two things:

1. After failing to logon as a batch job, the job will immediately retry using interactive logon (pre-U3a way) before failing completely.

2. Warning about backupset update failure was changed to info event which additionally explains the issue in better details:
Transaction logs backup will not be possible due to insufficient permissions to update backupset for SQL instance: %SQLInstance%

Adjusting account's security rights and permissions on msdb according to the updated system requirements remains the preferred solution for obvious reasons, however we still decided to provide the hotfix for situation when adjusting account settings is not possible. The hotfix is available through our support.

igelm
Lurker
Posts: 1
Liked: never
Joined: Jul 12, 2018 4:58 am
Full Name: Marcin Igel
Contact:

Re: SQL Backups and 9.5U3

Post by igelm » Jul 12, 2018 11:21 am

Hello,
Where exactly can I download this hotfix?

foggy
Veeam Software
Posts: 18158
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 12, 2018 11:27 am

Hi Marcin, please contact support to obtain it.

willjohnson
Novice
Posts: 4
Liked: 2 times
Joined: Jul 11, 2018 8:02 am
Contact:

Re: SQL Backups and 9.5U3

Post by willjohnson » Jul 12, 2018 11:29 am

We've asked twice but still haven't received the hotfix...

foggy
Veeam Software
Posts: 18158
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 12, 2018 11:33 am

Please ask to escalate the case, chances are not everyone on the first line is yet aware, since the hot fix was just posted. T3 is certainly aware.

foggy
Veeam Software
Posts: 18158
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 12, 2018 4:42 pm 1 person likes this post

Now all the support tiers are informed, so no more need to escalate the case to get the hotfix. Thanks!

fcompaired
Novice
Posts: 7
Liked: 1 time
Joined: Nov 02, 2015 4:02 pm
Full Name: Federico Compaired
Contact:

Re: SQL Backups and 9.5U3

Post by fcompaired » Jul 12, 2018 7:26 pm

I'm experiencing same issue after upgrading. I opened a case and was sent to this forum thread. :?:

fcompaired
Novice
Posts: 7
Liked: 1 time
Joined: Nov 02, 2015 4:02 pm
Full Name: Federico Compaired
Contact:

Re: SQL Backups and 9.5U3

Post by fcompaired » Jul 12, 2018 7:43 pm 1 person likes this post

The error I started seeing in alll SQL transaction log sessions is:
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

Veeam support mentioned the setting 'Log on as batch job'

In our scenario we use a domain service account that has sysadmin role in all SQL instances. What I just did was granting 'Log on as batch job' permission to the service account in AD and tested running a backup job restarting the Log session. It has worked for this 1 job. I will keep checking all the other jobs.

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 13, 2018 2:10 pm

Hi Foggy and Gostev,

Thanks for the help with the 3 SQL Queries. I was able to run the third query and that completed with out a hitch so all three check out okay. I Ran the quires with the same account that is used for the backups and in SQL Server Management Studio I signed in using windows authentication with the same account. I also double checked SQL Server configuration manager to make sure there wasn't any other instances running that I didn't know about and it looks like there is only one (MSSQLSERVER, SQL Server Browser, and SQL Server Agent). Im still getting that warning message after several days.

let me know what you guys think when you get a chance.

Thanks,

foggy
Veeam Software
Posts: 18158
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 13, 2018 3:03 pm

Hi Marcus, please contact technical support for a closer look at what's happening.

dloseke
Lurker
Posts: 2
Liked: never
Joined: Jul 13, 2018 3:33 pm
Full Name: Derek M. Loseke
Contact:

Re: SQL Backups and 9.5U3

Post by dloseke » Jul 13, 2018 3:37 pm

FYI, I was getting the below error after upgrading from 9.0 to 9.5 3a.

Code: Select all

7/12/2018 10:51:36 PM :: [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'.
I just wanted to note that I granted sysadmin access to our Veeam service account on all database instances, reran the jobs and everything was successful for me.

Strangely, I have one instance with databases using the simple recovery model that have always had a warning because Veeam was attempting to truncate logs (I haven't changed this setting because the job is also truncating logs on SQL servers using the Full recovery model). After the sysadmin rights were added, the warning has disappeared as well and the jobs reports that logs were truncated. Seems strange to me, but my goal of getting past the new error and having successful backups was achieved, so I guess I'm not going to question it.

318BigDaddy
Lurker
Posts: 1
Liked: never
Joined: Jul 17, 2018 1:52 pm
Full Name: JEFF D DEYO
Contact:

Re: SQL Backups and 9.5U3

Post by 318BigDaddy » Jul 17, 2018 2:06 pm

I am having the same error after upgrading to 3a.
Is there an estimated release date for this "hotfix" mentioned above?

Thank you.

StuartB
Lurker
Posts: 2
Liked: never
Joined: Jan 06, 2011 8:43 am
Full Name: Stuart Boyne
Contact:

Re: SQL Backups and 9.5U3

Post by StuartB » Jul 27, 2018 9:42 am

Having the same problem after upgrade to 9.5U3a

Only happens on 2 SQL databases and they are all set to "Perform copy only (lets another application use logs)". We want SQL server to truncate the logs and not Veeam. It's strange that not all of the SQL databases are reporting the same error.

foggy
Veeam Software
Posts: 18158
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 27, 2018 10:13 am

Hi Stuart, some of the required permissions are instance/database level, not having them for the particular database might be the reason of the observed warnings. You can either check permissions for the account used to process databases in question or apply the hotfix mentioned above, to switch the warnings to informational messages.

foggy
Veeam Software
Posts: 18158
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 27, 2018 10:14 am

318BigDaddy wrote:I am having the same error after upgrading to 3a.
Is there an estimated release date for this "hotfix" mentioned above?
Hi Jeff, it is already available. Thanks.

bjdboyer
Enthusiast
Posts: 43
Liked: 2 times
Joined: Nov 16, 2015 5:52 pm
Full Name: Bill Boyer
Contact:

[MERGED] SQL Update Permission denied after Update3a

Post by bjdboyer » Aug 03, 2018 2:20 pm

OK since applying Update3a a couple days ago I've been getting a lot of these "warning" messages in the job logs I didn't get before. Alsmost all of them are on SQLEXPRESS instances that were installed as part of an applicaiton on the server. KB2689 has below to say about the errors, but none of the VM's has the "Backup logs periodically" option enabled.

So how can I either resolve this issue or stop these @#$&^% warning messages in my jobs? It generates support tickets in our system for each one of these and they're not really "errors" if I'm reading correctly.

From KB2689:
In addition, backup jobs for SQL Servers VM with Backup logs periodically option enabled for transaction log backup may complete with warning message:

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'.

Bill

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

Re: SQL Backups and 9.5U3

Post by Gostev » Aug 03, 2018 2:52 pm

Did you install the hot fix from KB2689?

bjdboyer
Enthusiast
Posts: 43
Liked: 2 times
Joined: Nov 16, 2015 5:52 pm
Full Name: Bill Boyer
Contact:

Re: SQL Backups and 9.5U3

Post by bjdboyer » Aug 03, 2018 3:17 pm

I already fixed the ones that failed due to the logon as batch policy. And apparently from reading this attached thread I was getting the UPDATE warnings before. Just not seeing them. I'll try to see if I can get the customers (we're are an MSP) to update the instances to make sure the Veeam user is sysadmin. Some of these may not be possible as SQLEXPRESS installed along with another application and they may not have credentials to connect and grant the required permission. Most of the jobs are running wiht credentials that are either local admin, part of the administrator group or a domain admin account. Named instances probably won't be a problem.

I'm afraid to upgrade my other MSP datacenter for fear of what Update3a may uncover. the logon as batch is probably the biggest "hit" we'll take for the SQL servers. I've found that Windows2003 doesn't include the administrator group, but later Windows versions appear to have that included by default.

Thanks,
Bill

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

Re: SQL Backups and 9.5U3

Post by Gostev » Aug 03, 2018 3:26 pm

KB2689 also fixes the warning due to lack of UPDATE permission (changes one into the info event).

bjdboyer
Enthusiast
Posts: 43
Liked: 2 times
Joined: Nov 16, 2015 5:52 pm
Full Name: Bill Boyer
Contact:

Re: SQL Backups and 9.5U3

Post by bjdboyer » Aug 08, 2018 1:24 pm

Now here's a strange one. This VM QMMSQL2 backs up just fine and the userid is Sysadmin to SQL server, but i still get the warning. interesting is there is no instance listed in the message.

QMMSQL2 Warning 7:59:09 AM 8:01:34 AM 420.0 GB 14.7 GB 7.1 GB 0:02:25 [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: 24643
Liked: 3470 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: SQL Backups and 9.5U3

Post by Gostev » Aug 08, 2018 2:13 pm

There are no known issues with the KB2689 hotfix, so please work with support to investigate your situation. Thanks!

bbittner
Lurker
Posts: 1
Liked: never
Joined: Aug 17, 2018 7:39 am
Full Name: Benjamin Bittner
Contact:

Re: SQL Backups and 9.5U3

Post by bbittner » Aug 17, 2018 7:55 am

Hi,
thanks for all the detailed information about this problem, it helped me to find a solution.
Because we have multiple SQLExpress-installation without SMS, I looked up for a better solution than installing this huge SW-package just to change user roles.

I found quite a good solution with PowerShell and I wanted to share it here.
After testing it on 2 different installations, it looks quite promising. I'll roll it out on all of our systems now.

Code: Select all

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null;

$sqlSrv = New-Object 'Microsoft.SqlServer.Management.Smo.Server' "<HOSTNAME>\SQLEXPRESS"

$login = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Login -ArgumentList $sqlSrv, "<DOMAIN>\<USER>"
$login.LoginType = "(WindowsGroup|WindowsUser)"
$login.Create()
$login.AddToRole("sysadmin")
$login.Alter()
Please adjust the hostname (and eventually the instance), the login (user/group) and the LoginType to your needs.
An option would be to use "BUILTIN\Administrators" as login, so normally this means lokal admins and domains-admins.

@Veeam,
Maybe also add this to KB2477

BR
ben

bjesteadt
Influencer
Posts: 13
Liked: 2 times
Joined: Aug 13, 2018 2:53 pm
Full Name: Brian Jesteadt
Contact:

[MERGED] Backup warnings - version 9.5 post update 3a

Post by bjesteadt » Aug 20, 2018 8:35 pm

Once we updated to version 3a of B&R 9.5, we started getting warnings on our SQL backups, saying it couldn't truncate our transaction logs. This isn't a big deal, because we typically use SQL native backups to do transaction log truncation, so we don't need transaction logs truncated.

The solution is to give Veeam more access in SQL, but we have one "black box" SQL instance that is installed on one or our servers, as part of a licensing software package (I think, not entirely sure). I don't have access to change the permissions on this SQL instance, or even see the tables contained in SQL. So Veeam support says I can either just deal with the daily warning in my backup results, or I can stop the SQL instance. Neither solution thrills me. Does anyone else have a suggestion?

For reference, Veeam case # is 03100599.

Brian

jdavidson_waters
Service Provider
Posts: 40
Liked: 15 times
Joined: May 07, 2013 2:50 pm
Full Name: James Davidson
Location: Northeast UK
Contact:

Re: Backup warnings - version 9.5 post update 3a

Post by jdavidson_waters » Aug 20, 2018 8:56 pm

See workaround in this KB:
https://www.veeam.com/kb2689
@jam_davidson

Post Reply

Who is online

Users browsing this forum: Google [Bot] and 23 guests