Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bama0906
Starting Member
5 Posts |
 Posted - 2008-01-28 : 17:40:56
|
| I've got a permissions error that is driving me nuts, and would appreciate any suggestions. I have a web application (.NET Framework 1.1) running on a server (Server 2003) that has a page that exports data to a text file. It does this by calling a stored procedure on a database server (SQL Sever 2000) which then executes a DTS package located on the same database server. This DTS package creates the file and tries to write it to the web server.The problem is that the DTS package is trying to write the file to a location on the webserver and is not able to. The stored procedure generates this command: 'DTSRun /S servername /U user /P password /N DTS_Package_Name /A fileName:8=\\webserver\share\upload\filename.txt /A exportUser:8=userid'When I run this DTS command manually in SQL Query Analyzer on the database server, I get the following error:DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: Error opening datafile: Access is denied. Just for fun, I commented out the DTS package call in the stored procedure call. Now I get the same error, but on the web page instead. This error is:Access to the path "\\server\share\upload\filename" is denied.I'm fairly certain that the problem is that an account doesn't have permission to write to the folder on the web server. However, which account(s) needs permission? I've tried giving write permission to all of the following with no luck:WEBSERVER\ASPNETWEBSERVER\IIS_WPGWEBSERVER\IUSR_WEBSERVERWEBSERVER\EveryoneThanks in advance for your help. |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-01-28 : 20:56:11
|
| Does sql service account have permission to write to that file? Do you have sysadmin rights? If not, need set proxy account. |
 |
|
|
bama0906
Starting Member
5 Posts |
Posted - 2008-01-28 : 21:15:13
|
| The SQL Server service account is the local admin on the database server. The admins tell me there is no way they are going to change it to run under a domain account. Could you point me to any documentation on how to set a proxy account? Would it be a proxy account for the database server or the web server? Thanks so much |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-01-28 : 21:28:12
|
| Set domain account as proxy in sql, you can find details in books online. |
|
|
|
bama0906
Starting Member
5 Posts |
Posted - 2008-01-28 : 22:05:26
|
| I've done the following and when I try to execute I'm getting the same error. Am I missing a step?The proxy account is being set correctly, so there isn't anything wrong with that step. I'm still getting the same error when the DTS package attempts to run:exec master.dbo.xp_sqlagent_proxy_account N'SET', N'mydomain', -- agent_domain_name N'login',--, -- agent_username N'password' -- agent passwordGO'DTSRun /S servername /U user /P password /N DTS_Package_Name /A fileName:8=\\webserver\share\upload\filename.txt /A exportUser:8=userid'GO |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-01-28 : 22:23:01
|
| Did you enable it? Double check sql agent -> properties -> job system, and try run package as sql job. |
|
|
|
|
|
|
|
|
DTS Package Permissions Error