January 22, 2014 at 4:19 am
Hi there,
I have an issue where for some reason my maintenance plans that backup the databases have started to fail. I get the following error messages -
Message
Executed as user: MHS2\sysadmin3. Microsoft (R) SQL Server Execute Package Utility Version 11.0.2100.60 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 11:10:50 Progress: 2014-01-22 11:10:59.82 Source: {330ED30F-742A-4649-9F55-3FD230A4A175} Executing query "DECLARE @GUID UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress Error: 2014-01-22 11:11:02.05 Code: 0xC002F210 Source: Back Up Database Task Execute SQL Task Description: Executing the query "EXECUTE master.dbo.xp_create_subdir N'\\mhsmgmt01\..." failed with the following error: "xp_create_subdir() returned error 161, 'The specified path is invalid.'". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error Error: 2014-01-22 11:11:02.30 Code: 0xC002F210 Source: Back Up Database Task Execute SQL Task Description: Executing the query "BACKUP DATABASE [Datacube_Prog10] TO DISK = N'\\m..." failed with the following error: "Cannot open backup device '\\mhsmgmt01\SQL BACKUPS\MHSVI-DATAWAREH\DATAWAREHOUSE\Datacube_Prog10\Datacube_Prog10_backup_2014_01_22_111101_7383973.bak'. Operating system error 5(Access is denied.). BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error Warning: 2014-01-22 11:11:02.30 Code: 0x80019002 Source: Datacube Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:10:50 Finished: 11:11:02 Elapsed: 11.934 seconds. The package execution failed. The step failed.
Now the SQL Server Agent is set to run with the following account (MHS2\SYSADMIN3) This is an account that has Domain Admin rights on the network and certainly has permissions to where I'm trying to back up to.
Any ideas please?
January 22, 2014 at 4:33 am
I would strongly advise against running any service under a domain admin account. If the service gets compromised (whether accidentally or maliciously), an unlimited amount of damage could be done across your whole network.
Now to your question. Is the login that owns the job that runs the backup a sysadmin?
John
January 22, 2014 at 4:35 am
Hi there,
I can confirm that the user is a sysadmin.
What is the recommended account to use to run these jobs?
January 22, 2014 at 4:45 am
If the login that owns the job is sysadmin, then the job should run in the context of the SQL Server Agent account. Does SQL Server run under the same account? If it does, you can open a new query window and use xp_cmdshell (if it's enabled) to check whether the account does indeed have access to the specified location.
I run all my jobs as either sa or the SQL Server Agent account. I know some people frown upon that practice, but it works for me.
John
January 22, 2014 at 4:45 am
Is the both SQL service & SQL Server Agent runs under same account.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
January 22, 2014 at 4:51 am
I can confirm that SQL Server Agent is running as mhs2\sysadmin3. This is a domain account. It is a sysadmin account.
SQL Server is running as NT Service\MSSQL$DATAWAREHOUSE
January 22, 2014 at 4:54 am
I've now changed SQL Server service to run under the same account of mhs2\sysadmin3 and they are now working.
January 22, 2014 at 4:54 am
OK, so you've opened the job, looked at the Owner field, and check that the login specified there is sysadmin? You'll need to find a different way of checking whether the SQL Server Agent account has the right permissions on the folder given that it isn't also the SQL Server account. But double-check the job owner first. If it isn't sysadmin, the job will run in the context of a proxy account.
John
January 22, 2014 at 4:58 am
Ryan Keast (1/22/2014)
I've now changed SQL Server service to run under the same account of mhs2\sysadmin3 and they are now working.
Interesting. Looks as if the maintenance plans are running in the context of the SQL Server account instead of the SQL Server Agent account. Maybe dtexec.exe (I think that's what it's called) shells it out to the SQL Server account.
John
January 22, 2014 at 5:06 am
John Mitchell-245523 (1/22/2014)
Ryan Keast (1/22/2014)
I've now changed SQL Server service to run under the same account of mhs2\sysadmin3 and they are now working.Interesting. Looks as if the maintenance plans are running in the context of the SQL Server account instead of the SQL Server Agent account. Maybe dtexec.exe (I think that's what it's called) shells it out to the SQL Server account.
John
Exactly John.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
January 22, 2014 at 5:07 am
Ryan Keast (1/22/2014)
This is an account that has Domain Admin rights on the network and certainly has permissions to where I'm trying to back up to.Any ideas please?
Makes absolutely no difference at all!
You're accessing the location via a UNC share, the default when creating a share is for Everyone Read Only. Even an admin with NTFS write permissions will be restricted to read only. Check the share permissions and the NTFS permissions for the account the agent runs under and ensure that it is not a domain administrator!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 22, 2014 at 8:45 am
Others have addressed the permissions problem. I will address the fact that you are using Maintenance Plans in the first place. Please go to Ola.Hallengren.com and start using his FREE, fully documented, incredibly flexible and powerful maintenance suite. Stop using Maintenance Plans!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 22, 2014 at 10:33 am
I recently had a problem with some maintenance plans on a machine I sort of look after but do not control the access rights on.
The problem was fixed by:
SP_CONFIGURE 'ALLOW UPDATE',0
RECONFIGURE
May be worth a try here.
Ola Hallengren's routines are good if you are allowed to implement them.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply