January 4, 2017 at 5:09 pm
Previously, I changed the service acct to domain on a SQl 2008 R2 machine and received an SPN error.
The machine could not be accessed remotely, so I changed the service acct to NT AUTHORITYetwork service. This restored remote connectivity but I have another issue.
This machine has a TSQL Job that uses ROBOCOPY to copy backup files to a second server. NT AUTHORITY does not have adequate permissions so I created a proxy acct for XP_CMDSHELL. I gave this account read/write permissions for the folders I am using on both servers. I ran the TSQL Job and received an error saying Access Denied.
Is the problem the NT AUTHORITY service acct or do I need to get Domain Admin permissions to fix the SPN problem? Thank you.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
January 5, 2017 at 7:46 am
fizzleme (1/4/2017)
Previously, I changed the service acct to domain on a SQl 2008 R2 machine and received an SPN error.The machine could not be accessed remotely, so I changed the service acct to NT AUTHORITYetwork service. This restored remote connectivity but I have another issue.
This machine has a TSQL Job that uses ROBOCOPY to copy backup files to a second server. NT AUTHORITY does not have adequate permissions so I created a proxy acct for XP_CMDSHELL. I gave this account read/write permissions for the folders I am using on both servers. I ran the TSQL Job and received an error saying Access Denied.
Is the problem the NT AUTHORITY service acct or do I need to get Domain Admin permissions to fix the SPN problem? Thank you.
Are you able to see the history of the job in SQL Server Agent? The step history should say something to the effect of:
"Executed as user: blahblahblah"
A problem with using XP_CMDSHELL and proxy accounts is that by default it only uses the proxy if the calling user is not a member of the SYSADMIN security group of the SQL Server instance.
https://msdn.microsoft.com/en-us/library/ms175046.aspx#Anchor_5
Since you have this running as a job, is it possible to script your ROBOCOPY as a step that is type: Operating System (CmdExec)? That way you can setup a proxy and add that proxy to the Operating System (CmdExec) and explicitly specify in the SQL Agent job step to run as that proxy:
https://technet.microsoft.com/en-us/library/ms189064(v=sql.105).aspx
January 5, 2017 at 9:57 am
Many years ago at a previous employer I was finally given an off-site file server to transfer and store backup files. What I did there was configure RoboCopy to run as a service and move files to the off-site server automatically when the file was available (after the backup completed). This allowed my to setup the process without having to worry about adding another step to the backup job in SQL Server.
January 5, 2017 at 1:35 pm
Nice idea. But I prefer the CMD Exec option.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
January 5, 2017 at 1:53 pm
Setting up a SQl Agent Proxy and using CMD exec did the trick. In this process I have learned that working with xp_cmdshell is not amusing. Best avoided if possible. Thanks for the alternative.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
January 5, 2017 at 2:49 pm
fizzleme (1/5/2017)
Nice idea. But I prefer the CMD Exec option.
My desire was to not have to worry about managing another step in the job or working with xp_cmdshell. Setting RoboCopy as a service made more sense and I had the support of our Network Services personnel with direction I was going.
January 5, 2017 at 4:14 pm
It's always great to have support from a solid team. Our network group is excellent.
I am copying 1 set of backups per day to a virtual server and this is temporary. I don't know how to setup
robocopy as a service so I went with what I know. All the best to you.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
January 5, 2017 at 4:42 pm
fizzleme (1/5/2017)
It's always great to have support from a solid team. Our network group is excellent.I am copying 1 set of backups per day to a virtual server and this is temporary. I don't know how to setup
robocopy as a service so I went with what I know. All the best to you.
I didn't either, I had to figure it out myself by reading the documentation for RoboCopy. What I put in place is still being used today and I left there more than 6 years.
January 5, 2017 at 4:49 pm
I didn't even know how to set up Sql Agent Proxies. Not rocket science, but I always enjoy learning new features.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
January 6, 2017 at 6:49 am
I figured staying within SQL Server Agent has the benefit of a very easy to use built in Notification feature to alert someone by e-mail if the copy fails. It is possible to do it other ways yes, but I always want to know when something didn't work as expected.
January 6, 2017 at 4:41 pm
fizzleme (1/5/2017)
I didn't even know how to set up Sql Agent Proxies. Not rocket science, but I always enjoy learning new features.
It's usually fun (for me, anyway) learning new things. Now that you have it set up, you can use it with confidence. There are plenty of things that can be done pretty easily with xp_cmdshell. Nice job.
If you want to read a presentation Jeff Moden gave on xp_cmdshell and how to secure it, check out http://qa.sqlservercentral.com/Forums/FindPost1707671.aspx. It was very good presentation.
January 6, 2017 at 5:03 pm
yes. BTW This blog is really good.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply