SQL Backups and 9.5U3

[foggy]

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]

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]

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

[hansvh]

Job still finishes with same warning. Am I missing something?

I indeed was missing something . 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]

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]

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]

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]

Hello,
Where exactly can I download this hotfix?

[foggy]

Hi Marcin, please contact support to obtain it.

[willjohnson]

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

[foggy]

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]

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

[fcompaired]

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

[fcompaired]

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]

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]

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

[dloseke]

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]

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

Thank you.

[StuartB]

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]

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]

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]

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]

Did you install the hot fix from KB2689?

[bjdboyer]

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]

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

[bjdboyer]

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]

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

[bbittner]

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]

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]

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