August 18, 2006 at 7:32 am
Also, you said you're calling this from a CF script and that's why you need to use a Sproc...
Why not use CFExecute instead of xp_Cmdshell? http://livedocs.macromedia.com/coldfusion/6/CFML_Reference/Tags-pt126.htm
It should get you the same results I would imagine...
August 18, 2006 at 8:02 am
all the other regulat queries like that work fine - no errors.
August 18, 2006 at 8:16 am
Ok so i tried using CFEXECUTE like this:
<cfexecute name="dtsrun"
arguments ="/s SQL01 /U ColdFusionUser /P ColdFusionUser's Password /N mypackagename">
</cfexecute>
and it gives me the following error:
table.cfdump_wddx,table.cfdump_xml,table.cfdump_struct,table.cfdump_array,table.cfdump_query,table.cfdump_cfc,table.cfdump_object,table.cfdump_binary,table.cfdump_udf,table.cfdump_udfbody,table.cfdump_udfarguments { font-size: xx-small; font-family: verdana, arial, helvetica, sans-serif; cell-spacing: 2px;}table.cfdump_wddx th,table.cfdump_xml th,table.cfdump_struct th,table.cfdump_array th,table.cfdump_query th,table.cfdump_cfc th,table.cfdump_object th,table.cfdump_binary th,table.cfdump_udf th,table.cfdump_udfbody th,table.cfdump_udfarguments th { text-align: left; color: white; padding: 5px;}table.cfdump_wddx td,table.cfdump_xml td,table.cfdump_struct td,table.cfdump_array td,table.cfdump_query td,table.cfdump_cfc td,table.cfdump_object td,table.cfdump_binary td,table.cfdump_udf td,table.cfdump_udfbody td,table.cfdump_udfarguments td { padding: 3px; background-color: #ffffff; vertical-align : top;}table.cfdump_wddx { background-color: #000000;}table.cfdump_wddx th.wddx { background-color: #444444;}table.cfdump_xml { background-color: #888888;}table.cfdump_xml th.xml { background-color: #aaaaaa;}table.cfdump_xml td.xml { background-color: #dddddd;}table.cfdump_struct { background-color: #0000cc ;}table.cfdump_struct th.struct { background-color: #4444cc ;}table.cfdump_struct td.struct { background-color: #ccddff;}table.cfdump_array { background-color: #006600 ;}table.cfdump_array th.array { background-color: #009900 ;}table.cfdump_array td.array { background-color: #ccffcc ;}table.cfdump_query { background-color: #884488 ;}table.cfdump_query th.query { background-color: #aa66aa ;}table.cfdump_query td.query { background-color: #ffddff ;}table.cfdump_cfc { background-color: #ff0000;}table.cfdump_cfc th.cfc{ background-color: #ff4444;}table.cfdump_cfc td.cfc { background-color: #ffcccc;}table.cfdump_object { background-color : #ff0000;}table.cfdump_object th.object{ background-color: #ff4444;}table.cfdump_binary { background-color : #eebb00;}table.cfdump_binary th.binary { background-color: #ffcc44;}table.cfdump_binary td { font-size: x-small;}table.cfdump_udf { background-color: #aa4400;}table.cfdump_udf th.udf { background-color: #cc6600;}table.cfdump_udfarguments { background-color: #dddddd; cell-spacing: 3;}table.cfdump_udfarguments th { background-color: #eeeeee; color: #000000;}
An exception occurred when invoking an external process. The cause of this exception was that: java.io.IOException: CreateProcess: dtsrun /s SQL01 /U ColdFusionUser /P ColdFusionUsersPassword /N MyPackageName error=2.
did i not do it correctly?
August 18, 2006 at 8:17 am
Earlier in this thread people were talkign about account permissions to the file/network location you are trying to use in your DTS task...
Excerpted from BOL...
When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running. When the user is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account.
So since you're using xp_cmdshell, if Coldfusionuser is a member of Sysadmins then it will look for that file with the User that runs your SQL Server Service. If it's not a sysadmin, then it will use the proxy account.
If that's the case, that may have been set at soem point in the past with your old domain\admin account and that's what is still trying to be used to connect.
EXEC master.dbo.xp_sqlagent_proxy_account N'GET' will get you the name of your current proxy. See this reference on how to set a new one... http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp
August 18, 2006 at 8:24 am
Also with CFEXECUTE I beleive your error is being caused by not specifying the entire path to your executable...
instead of
<cfexecute name="dtsrun"
arguments ="/s SQL01 /U ColdFusionUser /P ColdFusionUser's Password /N mypackagename">
</cfexecute>
TRY
<cfexecute name="C:\Program Files\Microsoft SQL Server\80\Tools\Binn\dtsrun.exe"
arguments ="/s SQL01 /U ColdFusionUser /P ColdFusionUser's Password /N mypackagename">
</cfexecute>
August 18, 2006 at 8:37 am
yeah! now were getting somewhere - i looked at the proxy account and it was in fact the admin account from the old domain. i updated it so it is now the admin account on the new AD domain.
and i switched my cf template back to use cfstoredproc and ran it again and now i am getting a different error:
[Macromedia][SQLServer JDBC Driver][SQLServer]xpsql.cpp: Error 1314 from CreateProcessAsUser on line 636
August 18, 2006 at 8:45 am
when you changed domains, did you change the Use that the SQL Service runs as? If so it may not have the correct rights... There are soem specific ones needed that dont' get applied when you change the service account user from the services screen and not from EM. here's some documentation on it... http://www.mcse.ms/archive97-2004-5-641767.html
See the post by Rand Boyd [MSFT]
August 18, 2006 at 8:52 am
ok - according to that post, the new admin account would need to have the following rights:
- Act as part of the operating system.
- Increase quotas.
- replace process level token.
- Log on as a batch job.
none of which i really know how to set.
someone also mentions that if you change the user that the service is running as via enterprise manager, it will make these changes for you. but i tried that and it didnt seem to do anything.
August 18, 2006 at 9:39 am
August 18, 2006 at 9:51 am
yea - went into enterprise manager and changed the service user from newdomain\administrator to newdomain\someotheruseruser then i restarted the service. then i changed it back to newdomain\administrator and restarted the service again.
and still get the 1314 error.
August 18, 2006 at 10:27 am
i just tried again - maybe i am going to the wrong place in enterprise manager, because after i changed the service account in EM and stopped and started the service in EM - it defaulted back to the admin account - the change didnt hold.
in EM i expanded, "Microsoft SQL Servers", then "SQL Server Group" and then i right clicked on "(local) (Windows NT)" and clicked Properties.
then i went to the "Security" tab and at the bottom changed the username and password. i clicked ok and it gave me a warning sayign that i have to restart the service.
so then i go back and right click on "(local) (Windows NT)" and went to stop. i waited a few seconds till the icon updated with the red square.
then i right clicked again on "(local) (Windows NT)" and went to start. waited a few seconds and the server started again.
then when i went to properties and chose the security tab, the username at the bottom was still the old one.
what the heck am i doign wrong?
August 18, 2006 at 11:12 am
I suppose you could make the change in the services applet and follwo these instructions how to fix the security issues that crop up when you do that...
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q283811
August 18, 2006 at 1:05 pm
thanks for the info but unfortunately i gave that a try and still no luck.
August 22, 2006 at 7:32 am
does anyone else have any ideas here - i am really stuck. i have to get this back up and running in the next couple days somehow.
August 22, 2006 at 8:08 am
ok so it seems that changing the service account via enterprise manager did not actually update the service at all. so i went into the services page in computer management and changed the service(s) to run as a system account rather than ournewADdomain/administrator. and now it seems to work.
but will i see any disadvantage by having the SQL Server service run as a system account?
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply