SQL backup permissions

[ikbarbero]

Hi,

We are testing Veeam B&R to check how it works and if could be a solution for our systems before launch a procurement process. For VM's works really well, but we have an issue with SQL t-log processing.

First, out environment, Veeam B&R v9.5u3a (the latest available in the download website), SQL 2008, Windows Server 2008R2 bare metal. We've created a service user with interactive logon denied as per our security standards. Given the admin permission in backup proxy and SQL server it can install proxy service and veeam agent without problem. It also backup well the failover cluster drives but the error comes when it's time to truncate transaction log, The error says "Win32 error:Logon failure: the user has not been granted the requested logon type at this computer."

We have checked the community post veeam-backup-replication-f2/sql-backups ... 51793.html regarding the new permissions required in 9.5u3a and also kb2447, at SQL level it has sysadmin permission.

Just to summarize, our permission model is the following.

At OS level.
- local Administrator
- logon as a batch explicit defined for Veeam service user
- Interactive login denied
- logon through terminal services denied

At SQL level.
- sysadmin

If We remove the service user from interactive login denied it truncate the log correctly, so it look like that interactive login should also be granted and our understanding of the user guide is wrong https://helpcenter.veeam.com/docs/backu ... tml?ver=95 (we understand than in 9.5u3 only logon as a batch should be granted)

Anyone could confirm if interative logon is still required for SQL T-log backups?

Thanks in advance.

[Mike Resseler]

Hi ikbarbero,

SQL 2008, I need to dig deep into my memory
First, by default, the local SYSTEM account will be used for database log truncation so it might be somewhere that this is different and might cause the issue. You say that when you give the Veeam service account the interactive login rights, it works?

One of the items I see is that you have denied logon through terminal services, but the documentation clearly states that the account needs Allow log on locally and Allow log on through Terminal Services. Could you verify that also?

Thanks
Mike

[ikbarbero]

Hi Mike,

Thank you for your help. Yep, SQL2008

With interactive login rights the job works well. Reading documentation and the forum post that I linked before we understood that with 9.5u3a only login as a batch is required, and online 9.5u3 without "a" and later needs interactive and rdp login. Our mistake.

Just in case, we agreed with our IT sec team to create a specific account with the required permissions instead of using our main service account (that we use to install agent, proxy...) So we are comfortable with that. Our current software requires way more privileges so it's a good improvement in security.

[Mike Resseler]

Hey Iker,
Glad the IT security team agreed. One last question, when you say your current software requires more privileges, I'm happy to hear what that means. (And no, you don't have to say the name of the other software ).
- Does it need to be a full admin on the SQL box?
- Full SQL admin permissions?

[ikbarbero]

The other software required full local admin and full privileges at SQL Level.

I know that Veeam recommendation is sysadmin, but it works with backup permissions only and it's able to ask the user with adequate permissions to restore (usually the restore will be done by sql admins). You explain it very well in the documentation, if you need to restrict permissions as much as possible the minimum is that, but some funcionality like restores could be affected.