SQL Job permission problems writing to UNC share

[ESquared]

I have a DTS package that writes to a text file on a UNC share (\\servername\folder\folder sort of thing). It works fine when I run it.

When I schedule it as a job, it fails. I can only assume this is a permissions issue. What are my options? So far I can only see:

1 • Run the sql agent with a domain account that has permissions to that specific folder. The problem is that either the agent then has to have broad administrative rights or has to be maintained by someone to add and remove access to various folders around the company which may move at times, adding an additional step to anything. Plus, if the password changes all jobs depending on that domain account blow up. In order for someone to enter the password, they have to know it, and if they leave the company policy says to change passwords, so this option is not looking super attractive.

2 • Write the file to a local folder and use some other process to copy it. I don't like this for many reasons, including adding one more point of failure to an already undesirable text export process.

3 • Some way to make the darn thing work easily. I thought about scheduling an SP which calls the DTS package. But I think someone here has tried that unsuccessfully. For that matter, does anyone have a good article on permission inheritance and such in SQL Server? If I knew more it might help.

4 • Your method that has none of the drawbacks and allows me to write directly to the share (can I give permission on that folder to the source machine's local system account, for example).

Just wondering if anyone has any ideas or the elusive method number 4...

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]

 

[mrdenny]

The correct solution to use is #1.

SQL Agent doesn't have to have admin rights to run. Most people typcially give it admin rights, but it doesn't actually need admin rights to any servers to function correctly.

Plus, if the password changes all jobs depending on that domain account blow up. In order for someone to enter the password, they have to know it, and if they leave the company policy says to change passwords, so this option is not looking super attractive.

Yeah, pretty much. That's the way global accounts work in any enviroment. The way to do this is to make sure that only a very few people actually know the password. Then if someone does leave and you have to change the password, then you change it, and schedule time to reset the passwords on the agents.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ESquared]

Thanks for the input, Denny.

I think someone told me the other day that in Unix any scheduled job you set up keeps going even if you change the password for the user that the job is run by. I wish Windows was like that.

Still hoping for another idea...



[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]

 

[mrdenny]

The SQL Agent jobs will continue to run. It's the running program which has the wrong password.

The way *nix does it's scheduling is via the cron tab. Each user has there own cron tab file. The system tracks who enters the entry and ties it to there account. In windows all the entries are stored in a single location, so the passwords must be stored in that location.

Try setting up the SQL Agent job like this.

Setup the agent to run under the system account. Setup the job to run as a domain login which is NOT a sysadmin. Setup the account as a credential, and map it as a proxy which has access to the command line method. Setup the job step to use the proxy account. This "should" give the job domain rights to access the folders. However you will still need to setup the password as part of the credential.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com