permission to write to output file by non-sa job owner

Question

permission to write to output file by non-sa job owner

Hollyz

SSCommitted

Points: 1763
More actions
December 29, 2009 at 11:11 am

#215000

Error message when executing the job:
Executed as user: int_prol. Warning: cannot write logfile D:\test_area\test_rpt.txt. Writing to log files is only allowed to jobs that are owned by sysadmin. Please consider writing log to table.
This is in sql 2005 and the job is owned by non-sa user- int_prol.
The D:\test_area folder is in the local drive within the sql server where the job is in.
I gave everyone full rights to this folder.
The job fails with the above error message.
Then I setup a proxy account that has sa privilege to the sql server. The proxy account's principal are int_prol.
Under the Active to the following system, It has operating system, active-x, integration services checked mark.
The credential is: #xp_cmdshell_proxy_account##
sql 2005 standard edition with sp2 installed.
Would sp3 solve this problem?
The job is still owned by int_prol (non-sa user)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 1

If that account is not a domain account, then you will never be allowed to write to a drive, any where. The service needs to be running on a domain account, and then you give that user permissions to write to the directory. This has nothing to do with SQL Accounts.

Change the account that all the services use and then give user write permissions to the directory. The SQL job will then be able to write to that directory

Andrew SQLDBA

Hollyz SSCommitted Points: 1763 More actions · Answer 2

that account is a domain account. The sql service and sql agent are both running the same domain account that has SA privilege.

The proxy account is set up using a domain account that has SA privilege also. It something to do with the proxy_account.

The sql job that runs the dtsx package step, uses the proxy_account to run as. Still getting the same error no matter what.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

start from scratch !

create a new windows account with no privileges at all ( none on windows, none on sqlserver)

Then grant that account only the needed auth on your sqlserver instance and db (or table if you can).

create it for proxy usage and grant it ssis execution auth.

Then grant that account create/modify/read/write auth on the windows folder you chose.

Make sure you know its password, so you can test the package.

Logon to windows using that proxy account.

use dtexec to execute the meant ssis package.

You can add logging parameters at test runtime.

You accounts should not need sysadmin privileges at all !!

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 4

I would suggest getting rid of the proxy account, and setting the permission of your domain account to a simple user. That account does not need, nor should it have domain administrator permissions. I have all my SSIS Packages set up this way and they all can write to drives all over the domain.

Andrew SQLDBA

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

Hollyz (12/30/2009)
that account is a domain account

And, you're absolutely sure that "domain" account can "see" the path you want to write to?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

arr.nagaraj SSCertifiable Points: 6528 More actions · Answer 6

Not too sure whether this one helps. But anyways thought of posting.

The SQL Service account shd be a part of Windows Adminstrative group for proxies to work. Is your Domain account running the SQL Service part of windows admin group?

http://support.microsoft.com/kb/890775

Agreed that this requirement is in SQL 2k, I am not too sure abt 2k5.

Regards,
Raj

http://Strictlysql.blogspot.com