Backup User Privileges for 3rd party backup
-
SQL 2008 R2 on win 2008 R2 , with mixed mode authentication
Backup exec 2010 R3 (may 2011)
mydomain\s-bexec (service account for BACKUP EXEC)
Basically I am ready to lose the plot with symantec for their dreadful admin guide - section sql logon account prvilieges needed.
http://www.symantec.com/business/support/index?page=content&id=HOWTO21793
Its far too vague, and they need to assume the person reading it is not an SQLDBA.
its states:
"The windows user account must have been granted the sysadmin role on the sql instance.
in the backup selection list apply that logon account to the windows server sql is installed on and not to the acutal sql instance"
i read that the account should have db owner and sysadmin roles but i'm finding this frustrating since i have already given the s-bexec account :
Local Admin group membership
Backup Operators membership
Logon as a service
SQL Security Added user domain\s-bexec in logons
Also gave it sysadmin role and dbowner via the Management Studio console to each DB
and then when i go to test the Backup Selection List Credentials in Backupexec console it fails with
"the logon account provided does not have the appropriate privileges to backup."
under the sql server Roles i open the sysadmin role and i can see the domain\s-bexec account..
surely this should be enough and would apply to all existing DB and any new Db in the future, for this logon.
im pulling my hair out at this point... :crazy: even tried adding that logon to some of the other roles...
Really need help with this ... and after we get to bottom of it im going to post a how to here and on the symantec backupexec forums so no oneelse has to suffer the same frustration... :crazy:
-
Third party backup products do require sysadmin membership. You might want to open up profiler, set it up to pickup failed logins, exceptions and user error messages and then run the test. That'll tell you what account is running and what exactly it is failing on.
K. Brian Kelley
@kbriankelley -
Barry
are you able to check the SQL Server logs on the instance you are trying to backup the databases on
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs"
-
Perry Whittle (5/18/2011)
Barryare you able to check the SQL Server logs on the instance you are trying to backup the databases on
never done it before, will have a look, if you can point me in right direction cheers.
-
In Management Studio, Object Explorer, you have a Management folder. Click that, and in there is the "SQL Server Logs" folder.
There are also logs under SQL Server Agent.
-
K. Brian Kelley (5/18/2011)
Third party backup products do require sysadmin membership. You might want to open up profiler, set it up to pickup failed logins, exceptions and user error messages and then run the test. That'll tell you what account is running and what exactly it is failing on.Hi Brian, thanks for that. Got it running while the selecetion test was being done.
TextDATA for Audit Login, while the account (NTUSername & LoginName are s-bexec , dom\s-bexec)
-- network protocol: TCP/IP
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
this is followed by a Audit Logout.
then i realised i wasnt seeing all the event type so i enabled a few more than mention and filtered by NTLOGONNAME s-bexec.
Now im seeing User Error Message :
Changed database context to 'BEDB'.
followed by: Changed language setting to us_english.
could the issue be related to regional /localisation settings?
-
No, those are normal. If you do the same thing against a development SQL Server and login using SSMS, you'll see the same thing. Any other errors being reported? You're obviously connecting, and connecting with the account you thought it was.
K. Brian Kelley
@kbriankelley -
have you checked the sql server logs
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs"
-
Log File Viewer -> SQL Server - Current
For today shoe these errors:
3041, Sev 16, State 1
18204, Sev 16, State 1
I also see the BAckupDiskFile: CreateMedia for the x:\symantec\data\BEDB.bak' failed to create. OS Err 5 ( access denied),
but i believe that to be a separate issue.
Under SQL SERver Agent - Current :
nothing major there other than from 2 days ago:
[396 ] idle cpu condition undefined
[260] unable to start mail session - no profile defined
[364] messenger service not started.
-
OK, you've got an NTFS permission issue. You need to make sure your SQL Server and SQL Server Agent account have read-write access to that folder on your X drive.
John
-
barry reilly (5/20/2011)
I also see the BAckupDiskFile: CreateMedia for the x:\symantec\data\BEDB.bak' failed to create. OS Err 5 ( access denied),Ensure the SQL Server service account has permissions on x:\symantec\data because that's likely the account being used if it's originating from within SQL Server. Otherwise, it's using the proxy account, but I would doubt that.
K. Brian Kelley
@kbriankelley -
John Mitchell-245523 (5/20/2011)
OK, you've got an NTFS permission issue. You need to make sure your SQL Server and SQL Server Agent account have read-write access to that folder on your X drive.John
yep, secondary to the main issue and not related.
-
So those accounts have the correct permissions and you still get the error?
K. Brian Kelley
@kbriankelley
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply