Logon as batch job right is still required even if the account has sysadmin role.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".
-
- Veeam Software
- Posts: 21069
- Liked: 2115 times
- Joined: Jul 11, 2011 10:22 am
- Full Name: Alexander Fogelson
- Contact:
Re: SQL Backups and 9.5U3
-
- Influencer
- Posts: 10
- Liked: 1 time
- Joined: Feb 26, 2013 11:24 am
- Full Name: Hans
- Contact:
Re: SQL Backups and 9.5U3
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: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.
Code: Select all
use msdb
go
alter role db_datawriter add member [username];
go
-
- Chief Product Officer
- Posts: 31455
- Liked: 6646 times
- Joined: Jan 01, 2006 1:01 am
- Location: Baar, Switzerland
- Contact:
Re: SQL Backups and 9.5U3
Please use test SQL queries from the last post on the previous page to troubleshoot this (or open a support case for assistance).
-
- Influencer
- Posts: 10
- Liked: 1 time
- Joined: Feb 26, 2013 11:24 am
- Full Name: Hans
- Contact:
Re: SQL Backups and 9.5U3
I indeed was missing somethinghansvh wrote:Job still finishes with same warning. Am I missing something?
![Embarassed :oops:](https://forums.veeam.com/images/smilies/icon_redface.gif)
-
- Novice
- Posts: 3
- Liked: never
- Joined: Jul 09, 2018 12:30 pm
- Full Name: Marcus Elliott
- Contact:
Re: SQL Backups and 9.5U3
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,
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,
-
- Veeam Software
- Posts: 21069
- Liked: 2115 times
- Joined: Jul 11, 2011 10:22 am
- Full Name: Alexander Fogelson
- Contact:
Re: SQL Backups and 9.5U3
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.
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.
-
- Chief Product Officer
- Posts: 31455
- Liked: 6646 times
- Joined: Jan 01, 2006 1:01 am
- Location: Baar, Switzerland
- Contact:
Re: SQL Backups and 9.5U3
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.
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.
-
- Lurker
- Posts: 1
- Liked: never
- Joined: Jul 12, 2018 4:58 am
- Full Name: Marcin Igel
- Contact:
Re: SQL Backups and 9.5U3
Hello,
Where exactly can I download this hotfix?
Where exactly can I download this hotfix?
-
- Veeam Software
- Posts: 21069
- Liked: 2115 times
- Joined: Jul 11, 2011 10:22 am
- Full Name: Alexander Fogelson
- Contact:
Re: SQL Backups and 9.5U3
Hi Marcin, please contact support to obtain it.
-
- Novice
- Posts: 4
- Liked: 2 times
- Joined: Jul 11, 2018 8:02 am
- Full Name: William Johnson
- Contact:
Re: SQL Backups and 9.5U3
We've asked twice but still haven't received the hotfix...
-
- Veeam Software
- Posts: 21069
- Liked: 2115 times
- Joined: Jul 11, 2011 10:22 am
- Full Name: Alexander Fogelson
- Contact:
Re: SQL Backups and 9.5U3
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.
-
- Veeam Software
- Posts: 21069
- Liked: 2115 times
- Joined: Jul 11, 2011 10:22 am
- Full Name: Alexander Fogelson
- Contact:
Re: SQL Backups and 9.5U3
Now all the support tiers are informed, so no more need to escalate the case to get the hotfix. Thanks!
-
- Influencer
- Posts: 10
- Liked: 1 time
- Joined: Nov 02, 2015 4:02 pm
- Full Name: Federico Compaired
- Contact:
Re: SQL Backups and 9.5U3
I'm experiencing same issue after upgrading. I opened a case and was sent to this forum thread. ![Question :?:](https://forums.veeam.com/images/smilies/icon_question.gif)
![Question :?:](https://forums.veeam.com/images/smilies/icon_question.gif)
-
- Influencer
- Posts: 10
- Liked: 1 time
- Joined: Nov 02, 2015 4:02 pm
- Full Name: Federico Compaired
- Contact:
Re: SQL Backups and 9.5U3
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.
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.
-
- Novice
- Posts: 3
- Liked: never
- Joined: Jul 09, 2018 12:30 pm
- Full Name: Marcus Elliott
- Contact:
Re: SQL Backups and 9.5U3
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,
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,
-
- Veeam Software
- Posts: 21069
- Liked: 2115 times
- Joined: Jul 11, 2011 10:22 am
- Full Name: Alexander Fogelson
- Contact:
Re: SQL Backups and 9.5U3
Hi Marcus, please contact technical support for a closer look at what's happening.
-
- Service Provider
- Posts: 60
- Liked: 28 times
- Joined: Jul 13, 2018 3:33 pm
- Full Name: Derek M. Loseke
- Contact:
Re: SQL Backups and 9.5U3
FYI, I was getting the below error after upgrading from 9.0 to 9.5 3a.
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.
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'.
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.
Derek M. Loseke, Senior Systems Engineer | Veeam Legend 2022-2023 | VMSP/VMTSP | VCP6-DCV | VSP/VTSP | CCNA | https://technotesanddadjokes.com | @dloseke
-
- Lurker
- Posts: 1
- Liked: never
- Joined: Jul 17, 2018 1:52 pm
- Full Name: JEFF D DEYO
- Contact:
Re: SQL Backups and 9.5U3
I am having the same error after upgrading to 3a.
Is there an estimated release date for this "hotfix" mentioned above?
Thank you.
Is there an estimated release date for this "hotfix" mentioned above?
Thank you.
-
- Lurker
- Posts: 2
- Liked: never
- Joined: Jan 06, 2011 8:43 am
- Full Name: Stuart Boyne
- Contact:
Re: SQL Backups and 9.5U3
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.
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.
-
- Veeam Software
- Posts: 21069
- Liked: 2115 times
- Joined: Jul 11, 2011 10:22 am
- Full Name: Alexander Fogelson
- Contact:
Re: SQL Backups and 9.5U3
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.
-
- Veeam Software
- Posts: 21069
- Liked: 2115 times
- Joined: Jul 11, 2011 10:22 am
- Full Name: Alexander Fogelson
- Contact:
Re: SQL Backups and 9.5U3
Hi Jeff, it is already available. Thanks.318BigDaddy wrote:I am having the same error after upgrading to 3a.
Is there an estimated release date for this "hotfix" mentioned above?
-
- Service Provider
- Posts: 60
- Liked: 3 times
- Joined: Nov 16, 2015 5:52 pm
- Full Name: Bill Boyer
- Contact:
[MERGED] SQL Update Permission denied after Update3a
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
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
-
- Chief Product Officer
- Posts: 31455
- Liked: 6646 times
- Joined: Jan 01, 2006 1:01 am
- Location: Baar, Switzerland
- Contact:
Re: SQL Backups and 9.5U3
Did you install the hot fix from KB2689?
-
- Service Provider
- Posts: 60
- Liked: 3 times
- Joined: Nov 16, 2015 5:52 pm
- Full Name: Bill Boyer
- Contact:
Re: SQL Backups and 9.5U3
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
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
-
- Chief Product Officer
- Posts: 31455
- Liked: 6646 times
- Joined: Jan 01, 2006 1:01 am
- Location: Baar, Switzerland
- Contact:
Re: SQL Backups and 9.5U3
KB2689 also fixes the warning due to lack of UPDATE permission (changes one into the info event).
-
- Service Provider
- Posts: 60
- Liked: 3 times
- Joined: Nov 16, 2015 5:52 pm
- Full Name: Bill Boyer
- Contact:
Re: SQL Backups and 9.5U3
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'.
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'.
-
- Chief Product Officer
- Posts: 31455
- Liked: 6646 times
- Joined: Jan 01, 2006 1:01 am
- Location: Baar, Switzerland
- Contact:
Re: SQL Backups and 9.5U3
There are no known issues with the KB2689 hotfix, so please work with support to investigate your situation. Thanks!
-
- Lurker
- Posts: 1
- Liked: never
- Joined: Aug 17, 2018 7:39 am
- Full Name: Benjamin Bittner
- Contact:
Re: SQL Backups and 9.5U3
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.
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
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()
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
-
- 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
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
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
-
- Service Provider
- Posts: 43
- 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
See workaround in this KB:
https://www.veeam.com/kb2689
https://www.veeam.com/kb2689
@jam_davidson
Who is online
Users browsing this forum: xzvf and 186 guests