|
|
You're in the
sql server msde
group:xpCmdShell Error 87 from GetProxyAccount on line 604
sql server msde:
Here's the screnario...using an MSDE install of SQL Server to run a stored
procedure the first inserts a record to a table and then executes the master.dbo.xpcmdshell stored procedure (call from within the insert procedure). This is executed from an ASP.Net web site and it assume a SQL Server login user to run the procedure. That login has Execute rights on the procedure as well as the xp_cmdshell procedure. Any ideas on how to get rid of this error? FYI - I run this procedure in Query analyzer as this user with the same results. However, if i run it logged in as a System Administrator user, it
hi,
[quoted text, click to view] bteclt wrote: > Here's the screnario...using an MSDE install of SQL Server to run a > stored procedure the first inserts a record to a table and then > executes the master.dbo.xpcmdshell stored procedure (call from within > the insert procedure). This is executed from an ASP.Net web site and > it assume a SQL Server login user to run the procedure. That login > has Execute rights on the procedure as well as the xp_cmdshell > procedure. > > Any ideas on how to get rid of this error? > > FYI - I run this procedure in Query analyzer as this user with the > same results. However, if i run it logged in as a System > Administrator user, it execute fine. you have to check your account running SQL Server Agent permissions... http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp -- Andrea Montanari (Microsoft MVP - SQL Server) http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0 (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual interface) --------- remove DMO to reply
hi,
[quoted text, click to view] bteclt wrote: > Thanks Andrea, I added a Domain account that I know has full SA > privleges on the SQL Server and it's still not working. Does this > have to be a domain account or can it be a SQL Server login? you have to understand how xp_cmdshell works, actually meaning in what security context... if the SQL Server login associated to the db user is member of the sysadmins role, the procedure is executed in his/her security context (sysadmins... not truly secure)... else, the procedure is executed in the security context of the SQL Server Agent proxy account... so you have to verify and set the relative permission of that account... and plese do not exsagerate with permissions :D [quoted text, click to view] > Can I > just use the "sa" account for this? Or is there something else to > check after adding this user > please dont... sa login is an administrative login and should not be used for normal activity.. more. it is member of sysadmins server role, and using it's high level permission set can be dangerous in case of successfull external attacks.. loosely speaking is a security hole... -- Andrea Montanari (Microsoft MVP - SQL Server) http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0 (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual interface) --------- remove DMO to reply
hi,
[quoted text, click to view] bteclt wrote: as you are talking about a Web app, you have to investigate the account > we'll keep trying but right now our SQL Server is logging in as a > Windows Account with sysadmin privleges and the sql agent proxy is > set to that same account. It's literally a full local admin on the > box itself as well as in the SQL Server. Our Network Admin suspects > we may have a network issue that might cause it but the sql box is > set up just as you have mentioned > running IIS.... if that account is member of local admins and SQL Server sysadmins role (hope not the case) then the procedure will be run in sysadmins security context... else check the proxy account, not the account SQL Server and SQL Server Agent are running on... -- Andrea Montanari (Microsoft MVP - SQL Server) http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0 (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual interface) --------- remove DMO to reply
BTW... is using xp_cmdshell really necessary?
-- Andrea Montanari (Microsoft MVP - SQL Server) http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0 (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual interface) --------- remove DMO to reply
Thanks Andrea, I added a Domain account that I know has full SA privleges on
the SQL Server and it's still not working. Does this have to be a domain account or can it be a SQL Server login? Can I just use the "sa" account for this? Or is there something else to check after adding this user Thanks in advance! [quoted text, click to view] "Andrea Montanari" wrote:
> hi, > bteclt wrote: > > Here's the screnario...using an MSDE install of SQL Server to run a > > stored procedure the first inserts a record to a table and then > > executes the master.dbo.xpcmdshell stored procedure (call from within > > the insert procedure). This is executed from an ASP.Net web site and > > it assume a SQL Server login user to run the procedure. That login > > has Execute rights on the procedure as well as the xp_cmdshell > > procedure. > > > > Any ideas on how to get rid of this error? > > > > FYI - I run this procedure in Query analyzer as this user with the > > same results. However, if i run it logged in as a System > > Administrator user, it execute fine. > > you have to check your account running SQL Server Agent permissions... > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp > -- > Andrea Montanari (Microsoft MVP - SQL Server) > http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org > DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0 > (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual > interface) > --------- remove DMO to reply > >
one more thing...we've added a local windows account on that box which has
full Local Domain Admin rights for that box. That is currently the account that both the SQL Server and SQL Agent are using. Still not working. [quoted text, click to view] "bteclt" wrote:
> Thanks Andrea, I added a Domain account that I know has full SA privleges on > the SQL Server and it's still not working. Does this have to be a domain > account or can it be a SQL Server login? Can I just use the "sa" account for > this? Or is there something else to check after adding this user > > Thanks in advance! > > "Andrea Montanari" wrote: > > > hi, > > bteclt wrote: > > > Here's the screnario...using an MSDE install of SQL Server to run a > > > stored procedure the first inserts a record to a table and then > > > executes the master.dbo.xpcmdshell stored procedure (call from within > > > the insert procedure). This is executed from an ASP.Net web site and > > > it assume a SQL Server login user to run the procedure. That login > > > has Execute rights on the procedure as well as the xp_cmdshell > > > procedure. > > > > > > Any ideas on how to get rid of this error? > > > > > > FYI - I run this procedure in Query analyzer as this user with the > > > same results. However, if i run it logged in as a System > > > Administrator user, it execute fine. > > > > you have to check your account running SQL Server Agent permissions... > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp > > -- > > Andrea Montanari (Microsoft MVP - SQL Server) > > http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org > > DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0 > > (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual > > interface) > > --------- remove DMO to reply > > > >
we'll keep trying but right now our SQL Server is logging in as a Windows
Account with sysadmin privleges and the sql agent proxy is set to that same account. It's literally a full local admin on the box itself as well as in the SQL Server. Our Network Admin suspects we may have a network issue that might cause it but the sql box is set up just as you have mentioned thanks for the insight [quoted text, click to view] "Andrea Montanari" wrote:
> hi, > bteclt wrote: > > Thanks Andrea, I added a Domain account that I know has full SA > > privleges on the SQL Server and it's still not working. Does this > > have to be a domain account or can it be a SQL Server login? > > you have to understand how xp_cmdshell works, actually meaning in what > security context... > if the SQL Server login associated to the db user is member of the sysadmins > role, the procedure is executed in his/her security context (sysadmins... > not truly secure)... else, the procedure is executed in the security context > of the SQL Server Agent proxy account... so you have to verify and set the > relative permission of that account... and plese do not exsagerate with > permissions :D > > > Can I > > just use the "sa" account for this? Or is there something else to > > check after adding this user > > > > please dont... sa login is an administrative login and should not be used > for normal activity.. more. it is member of sysadmins server role, and using > it's high level permission set can be dangerous in case of successfull > external attacks.. loosely speaking is a security hole... > -- > Andrea Montanari (Microsoft MVP - SQL Server) > http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org > DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0 > (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual > interface) > --------- remove DMO to reply > >
not anymore its not...I have gotten around that by now using a scheduled DTS
pacakge that runs every minute. checks some stuff to see if it needs to process and then uses an ActiveXscript task to do what the xp_cmdshell was doing. The advantage of the xp_cmdshell would have been more realtime processing...users are uploading files from our website which have to get moved from the webserver to db server and renamed, then loaded up to the database. the xp_cmdshell call was going to be doing the file move and rename. only problem I have now is that the Agent job takes about 5 minutes to run and keeps hanging up with a status of "Performing completion actions"...it finishes...just taking a while. The DTS itself runs in about 3 seconds. Other than that, we're all set I think. thanks for the valuable insight [quoted text, click to view] "Andrea Montanari" wrote:
> BTW... is using xp_cmdshell really necessary? > -- > Andrea Montanari (Microsoft MVP - SQL Server) > http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org > DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0 > (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual > interface) > --------- remove DMO to reply > >
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |