| Author |
Topic |
|
Huligan
Yak Posting Veteran
66 Posts |
 Posted - 2006-05-12 : 17:26:45
|
| Hello. The end result I'm shooting for is a daily job that creates a RSS XML file and writes it to my website. My SQL Server is on a different box from the web server that hosts my website. At this point I'm able to output the file to the SQL Server box, but I'm unable to get it to write to the web server. Here's my job.EXECUTE sp_makewebtask@outputfile = N'M:\rss\output.xml', @query=N'EXECUTE [sp_RSS_Output]', @templatefile=N'M:\rss\output_template.xml'The job is running as "sa". I have created a mapped drive (M:/) and am able to create a file on the webserver through it. I also opened up the folder permissions on the web server I'm trying to write to so that "Everyone" has full access to it. I also tried putting the template file on the local SQL server, but that didn't work either. I would really appreciate any ideas on what is preventing SQL Server from outputing my XML file to the web server. SQL's job history just isn't being helpful ("The job failed. The Job was invoked by .... The last step to run was step 1."). Thanks for your help.Les |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-05-12 : 17:40:24
|
| Don't use a mapped drive. Write the file to a share name:\\MyWebServer\Sharename\rss\output_template.xmlMake sure that the SQL Server service account has access to write to the share on the web server.CODO ERGO SUM |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-12 : 17:53:47
|
| Log into the database server using the MSSQLSERVER service account. Run your command in Query Analyzer. Does it work there?This simulates what the job is doing.If you are using the local system account for the MSSQLSERVER service, then that's your problem. It doesn't have any access to any network resources.Tara Kizeraka tduggan |
|
|
|
Huligan
Yak Posting Veteran
66 Posts |
Posted - 2006-05-15 : 13:32:22
|
| Hello Michael. I tried using a share name already and it didn't work. I'm looking into Tara's suggestion now.Les |
|
|
|
Huligan
Yak Posting Veteran
66 Posts |
Posted - 2006-05-15 : 14:29:02
|
| I decided to do it another way because I got tired of messing with it. Instead, I created a virtual directory on the server that SQL Server lives on. So now the virtual path goes from the website over to the database server which circumvents the permission issue of SQL writing to another server. I still wish I knew why it wasn't working but I'll have to look into it on a day when I have less to do.Les |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-15 : 15:05:19
|
This is most likely the culprit of your problem:quote:
If you are using the local system account for the MSSQLSERVER service, then that's your problem. It doesn't have any access to any network resources.
You can easily check this in a couple of minutes.Tara Kizeraka tduggan |
|
|
|
Huligan
Yak Posting Veteran
66 Posts |
Posted - 2006-05-15 : 15:31:48
|
| I don't know the password and digging it out would take awhile. I'm trying not to just reset it since it may or may not affect other services I may not know about.Les |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-15 : 15:36:43
|
| It isn't a real account, so there is no password. If you are using the local system account, then that's the problem. It has no access to any network resources. So you'll need to change the service to use a different account. The recommendation is to use a domain user that is a member of the local admin group. It will require a restart of the service though.Tara Kizeraka tduggan |
|
|
|
Huligan
Yak Posting Veteran
66 Posts |
Posted - 2006-05-15 : 16:53:38
|
| Here's my problem. The web server I want to write the file on isn't on the domain and can't be added to the domain. If I'm understanding you correctly, this issue cancels out your solution. Turns out the virtual directory idea can't be the solution either (even though it worked) because it messed up FTP for the web server. Any ideas?Les |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-15 : 17:05:39
|
| The only thing I can think of which might work is to create a local user on both boxes using the same password. I've heard that this works when the boxes aren't on the same domain. Tara Kizeraka tduggan |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-05-15 : 17:47:21
|
quote:
Originally posted by tkizer
The only thing I can think of which might work is to create a local user on both boxes using the same password. I've heard that this works when the boxes aren't on the same domain. Tara Kizeraka tduggan
That will work, but the process that copies the file from the database server to the web server will have to run under that account. That would probably mean creating an account with the same user name and password as the SQL Server Service account on the web server.Example:MYSQLSERVER\SQL_SERVICE_ACCTMYWEBSERVER\SQL_SERVICE_ACCTExample if SQL Server runs with domain acct:MYDOMAIN\SQL_SERVICE_ACCTMYWEBSERVER\SQL_SERVICE_ACCTYou will still need to create a share on the Web Server and grant the local account access to that share and the underlying directory.CODO ERGO SUM |
|
|
|
darinh
Yak Posting Veteran
58 Posts |
Posted - 2006-05-15 : 21:27:50
|
| Alternatively you could create a DTS Package that saves the file locally, and then ftps it across to your web server. I find the ftp task in DTS to be a bit limited, so I use this one http://www.sqldts.com/default.aspx?302 which gives you a lot more control. You should set your ftp server properties to only allow connections from the ip address of your sql server to keep the security tight. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-05-15 : 22:55:06
|
| just a bit of verification...from your sql server, if you try to open the shared folder on the web server and create a new file, does that work?--------------------keeping it simple... |
|
|
|
Huligan
Yak Posting Veteran
66 Posts |
Posted - 2006-05-16 : 08:43:30
|
| Hello Jen. Yes, I can create a file in the shared folder on the web server from the server hosting SQL Server.Les |
|
|
|
|
Scheduled job won't output file to another server