|
|
You're in the
sql server programming
group:xp_cmdshell permission denied SQL 2005
sql server programming:
using SQL Authentication. One task the app will have to do is utilize th "xp_cmdshell" to populate an Excel spreadsheet and move the file from location A to B which is a linked server. Since the DB user executing all my sql commands is not a default sysadmin. My app encounters the the following error "...EXECUTE permission denied on object 'xp_cmdshell', database 'mssqlsystemresource'..." I've investigated on possible ways to resolve this issue however I've had no luck on resolving this issue; below are the step's I taken so far. 1) I created a windows acct - "ADomain\AUserAcct" 2) Created a Login acct for this user ("ADomain\AUserAcct") in SQL 2005 and granted user access to this DB. User access for this account is "dbowner" 3) Executed the "sp_xp_cmdshell_proxy_account" sproc for this new acct a. EXEC sp_xp_cmdshell_proxy_account "ADomain\AUserAcct", "pwd" 4) Granted execute rights on sys.xp_cmdshell for this user a. GRANT EXECUTE ON sys.xp_cmdshell TO "ADomain\AUserAcct After these steps, I tried to execute my process again but ecountered the same error msg. so 1) I modified the stored sproc to execute with the new user's acct. but had the same error a.Create proc myprocname with Execute As ADomain\AUserAcct Note: when I execute the proc. Before any of these steps thru sql 2005 IDE with a sysadmin account all works great. If anyone has any suggestions on how to resolve this or pointers on where I went wrong, I would greatly appreciate it. Thanks,
Jose
USE master; GRANT EXECUTE on xp_cmdshell to [ADomain\AUserAcct] [quoted text, click to view] <jose.mendez22@gmail.com> wrote in message news:1187850729.193816.181030@x35g2000prf.googlegroups.com... > My issue. I'm running a web app which makes command calls to a DB > using SQL Authentication. One task the app will have to do is utilize > th "xp_cmdshell" to populate an Excel spreadsheet and move the file > from location A to B which is a linked server. Since the DB user > executing all my sql commands is not a default sysadmin. My app > encounters the the following error "...EXECUTE permission denied on > object 'xp_cmdshell', database 'mssqlsystemresource'..." I've > investigated on possible ways to resolve this issue however I've had > no luck on resolving this issue; below are the step's I taken so far. > > 1) I created a windows acct - "ADomain\AUserAcct" > 2) Created a Login acct for this user ("ADomain\AUserAcct") in SQL > 2005 and granted user access to this DB. User access for this account > is "dbowner" > 3) Executed the "sp_xp_cmdshell_proxy_account" sproc for this new acct > a. EXEC sp_xp_cmdshell_proxy_account "ADomain\AUserAcct", > "pwd" > 4) Granted execute rights on sys.xp_cmdshell for this user > a. GRANT EXECUTE ON sys.xp_cmdshell TO "ADomain\AUserAcct > > After these steps, I tried to execute my process again but ecountered > the same error msg. so > > 1) I modified the stored sproc to execute with the new user's acct. > but had the same error > a.Create proc myprocname with Execute As ADomain\AUserAcct > > Note: when I execute the proc. Before any of these steps thru sql 2005 > IDE with a sysadmin account all works great. > > If anyone has any suggestions on how to resolve this or pointers on > where I went wrong, I would greatly appreciate it. > > Thanks, >
[quoted text, click to view]
> 3) Executed the "sp_xp_cmdshell_proxy_account" sproc for this new acct That is not how xp_cmdshell security works. See my other reply. There are basically three things involved here. 1. Execution of xp_cmdshell need to be allowed at the instance level. 2. The user need permissions in SQL Server to execute xp_cmdshell. 3. SQL Server then need to open a process (the process spawned by xp_cmdshell) in a certain Windows context (every process in Windows need to execute in a certain Windows user context). 1: Use "Surface Area Configuration" or sp_configure to allow usage of xp_cmdshell: EXEC sp_configure 'xp_cmdshell', 1 RECONFIGURE 2. You need to have a user in the *master* database which has execute permission on xp_cmdshell. If you are uncertain about the difference between login and user, you should read up in BOL on security: USE master CREATE LOGIN x WITH PASSWORD = 'jlkw#.6(' --Note, we are in the master database!!! CREATE USER x FROM LOGIN x GO --Run as login x EXECUTE AS login = 'x' --Below fails, no execute permission on xp_cmdshell EXEC xp_cmdshell 'DIR C:\*.*' REVERT --Note, we are in the master database!!! GRANT EXECUTE ON xp_cmdshell TO x --Try again EXECUTE AS login = 'x' --Execution of xp_cmdshell is allowed. --But I haven't configured the proxy account... EXEC xp_cmdshell 'DIR C:\*.*' REVERT The execution of xp_cmdshell gave below error message: Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1 The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information. Above is expected. So for the final step: 3. Now we need to say what Windows account should be used when a non-sysadmin user is executing xp_cmdshell: EXEC sp_xp_cmdshell_proxy_account 'domain\WinUser','pwd' EXECUTE AS login = 'x' --Execution of xp_cmdshell is allowed. --And executes successfully!!! EXEC xp_cmdshell 'DIR C:\*.*' REVERT And below is a clean-up of what I've done: EXEC sp_xp_cmdshell_proxy_account null DROP USER JohnDoe DROP LOGIN JohnDoe EXEC sp_configure 'xp_cmdshell', 0 RECONFIGURE I've blogged about this in http://sqlblog.com/blogs/tibor_karaszi/archive/2007/08/23/xp-cmdshell-and-permissions.aspx. The blog basically say what I said above. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi [quoted text, click to view] <jose.mendez22@gmail.com> wrote in message
news:1187850729.193816.181030@x35g2000prf.googlegroups.com... > My issue. I'm running a web app which makes command calls to a DB > using SQL Authentication. One task the app will have to do is utilize > th "xp_cmdshell" to populate an Excel spreadsheet and move the file > from location A to B which is a linked server. Since the DB user > executing all my sql commands is not a default sysadmin. My app > encounters the the following error "...EXECUTE permission denied on > object 'xp_cmdshell', database 'mssqlsystemresource'..." I've > investigated on possible ways to resolve this issue however I've had > no luck on resolving this issue; below are the step's I taken so far. > > 1) I created a windows acct - "ADomain\AUserAcct" > 2) Created a Login acct for this user ("ADomain\AUserAcct") in SQL > 2005 and granted user access to this DB. User access for this account > is "dbowner" > 3) Executed the "sp_xp_cmdshell_proxy_account" sproc for this new acct > a. EXEC sp_xp_cmdshell_proxy_account "ADomain\AUserAcct", > "pwd" > 4) Granted execute rights on sys.xp_cmdshell for this user > a. GRANT EXECUTE ON sys.xp_cmdshell TO "ADomain\AUserAcct > > After these steps, I tried to execute my process again but ecountered > the same error msg. so > > 1) I modified the stored sproc to execute with the new user's acct. > but had the same error > a.Create proc myprocname with Execute As ADomain\AUserAcct > > Note: when I execute the proc. Before any of these steps thru sql 2005 > IDE with a sysadmin account all works great. > > If anyone has any suggestions on how to resolve this or pointers on > where I went wrong, I would greatly appreciate it. > > Thanks, >
Hi Audrey
It looks like you missed something in Tibor's instructions. You should not issue the GRANT command after setting EXECUTE AS. The EXECUTE AS tells SQL Server to run the following commands as if you were logged in as JohnDoe. And then as JohnDoe you try to grant permission to JohnDoe. You cannot grant permission to yourself, as the error message indicates. Tibor's script had a REVERT at some point after the EXECUTE AS, to revert your identity back to the sysadmin, who can grant permission. The EXECUTE AS is only needed to test the permission that has been granted to JohnDoe, but not to actually grant the permission. So try this: USE master CREATE LOGIN JohnDoe WITH PASSWORD = 'somepassword' CREATE USER ohnDoe FROM LOGIN ohnDoe GRANT EXECUTE ON xp_cmdshell TO JohnDoe EXECUTE as login = 'JohnDoe' --Now, as JohnDoe, try executing xp_cmdshell: EXEC sp_cmdshell 'your command here....' -- HTH Kalen Delaney, SQL Server MVP www.InsideSQLServer.com http://sqlblog.com [quoted text, click to view] <audrey.nsh@gmail.com> wrote in message
news:1189541759.875019.208840@x40g2000prg.googlegroups.com... > On Sep 11, 1:13 pm, "Tibor Karaszi" > <tibor_please.no.email_kara...@hotmail.nomail.com> wrote: >> I'm sorry, but I'm out of ideas... :-( >> >> -- >> Tibor Karaszi, SQL Server >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi >> >> >> >> <audrey....@gmail.com> wrote in >> messagenews:1189529952.930980.79700@r29g2000hsg.googlegroups.com... >> > On Sep 11, 12:12 pm, "Tibor Karaszi" >> > <tibor_please.no.email_kara...@hotmail.nomail.com> wrote: >> >> > Ok, I followed all your instructions above and I am still getting >> >> > the >> >> > the following error "...EXECUTE permission denied on >> >> > object 'xp_cmdshell', database 'mssqlsystemresource'..." >> >> >> > This is what I have done: >> >> >> > (1) Created a login (server role=public) >> >> > (2) Created a user from login in the master database >> >> > (3) Gave it Execute permission on xp_cmdshell >> >> > (4) exec sp_xp_cmdshell_proxy_account 'Domain\WinAccount','pwd' >> >> >> > and it doesn't work. >> >> >> I would suspect that you aren't using the login you created in stepo 1 >> >> when you execute >> >> xp_cmdshell. >> >> >> > BTW, I am using xp_cmdshell off a trigger, >> >> > do I always have to specify exec sp_xp_cmdshell_proxy_account >> >> > 'Domain >> >> > \WinAccount','pwd', exec as login = 'Login' whenever the trigger >> >> > gets >> >> > invoked? >> >> >> No, that is a one-time configuration. And, of course, you need to >> >> specify a valif login name and >> >> password for your domain... >> >> >> -- >> >> Tibor Karaszi, SQL Server >> >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/... >> >> >> <audrey....@gmail.com> wrote in >> >> messagenews:1189524563.343178.290270@w3g2000hsg.googlegroups.com... >> >> > On Aug 23, 3:42 am, "Tibor Karaszi" >> >> > <tibor_please.no.email_kara...@hotmail.nomail.com> wrote: >> >> >> > 3) Executed the "sp_xp_cmdshell_proxy_account" sproc for this new >> >> >> > acct >> >> >> >> That is not howxp_cmdshellsecurity works. See my other reply. >> >> >> >> There are basically three things involved here. >> >> >> >> 1. Execution ofxp_cmdshellneed to be allowed at the instance level. >> >> >> >> 2. The user need permissions in SQL Server to executexp_cmdshell. >> >> >> >> 3. SQL Server then need to open a process (the process spawned >> >> >> byxp_cmdshell) in a certain >> >> >> Windows >> >> >> context (every process in Windows need to execute in a certain >> >> >> Windows user context). >> >> >> >> 1: >> >> >> Use "Surface Area Configuration" or sp_configure to allow usage >> >> >> ofxp_cmdshell: >> >> >> >> EXEC sp_configure 'xp_cmdshell', 1 >> >> >> RECONFIGURE >> >> >> >> 2. You need to have a user in the *master* database which has >> >> >> execute permission >> >> >> onxp_cmdshell. >> >> >> If >> >> >> you are uncertain about the difference between login and user, you >> >> >> should read up in BOL on >> >> >> security: >> >> >> >> USE master >> >> >> CREATE LOGIN x WITH PASSWORD = 'jlkw#.6(' >> >> >> >> --Note, we are in the master database!!! >> >> >> CREATE USER x FROM LOGIN x >> >> >> GO >> >> >> >> --Run as login x >> >> >> EXECUTE AS login = 'x' >> >> >> --Below fails, no execute permission onxp_cmdshell >> >> >> EXECxp_cmdshell'DIR C:\*.*' >> >> >> REVERT >> >> >> >> --Note, we are in the master database!!! >> >> >> GRANT EXECUTE ONxp_cmdshellTO x >> >> >> >> --Try again >> >> >> EXECUTE AS login = 'x' >> >> >> --Execution ofxp_cmdshellis allowed. >> >> >> --But I haven't configured the proxyaccount... >> >> >> EXECxp_cmdshell'DIR C:\*.*' >> >> >> REVERT >> >> >> >> The execution ofxp_cmdshellgave below error message: >> >> >> >> Msg 15153, Level 16, State 1, Procedurexp_cmdshell, Line 1 >> >> >> Thexp_cmdshellproxyaccountinformationcannotberetrievedor isinvalid. >> >> >> Verify that the >> >> >> '##xp_cmdshell_proxy_account##' credential exists and contains >> >> >> validinformation. >> >> >> >> Above is expected. So for the final step: >> >> >> 3. >> >> >> Now we need to say what Windowsaccountshould be used when a >> >> >> non-sysadmin user is >> >> >> executingxp_cmdshell: >> >> >> EXEC sp_xp_cmdshell_proxy_account 'domain\WinUser','pwd' >> >> >> EXECUTE AS login = 'x' >> >> >> --Execution ofxp_cmdshellis allowed. >> >> >> --And executes successfully!!! >> >> >> EXECxp_cmdshell'DIR C:\*.*' >> >> >> REVERT >> >> >> >> And below is a clean-up of what I've done: >> >> >> EXEC sp_xp_cmdshell_proxy_account null >> >> >> >> DROP USER JohnDoe >> >> >> DROP LOGIN JohnDoe >> >> >> EXEC sp_configure 'xp_cmdshell', 0 >> >> >> RECONFIGURE >> >> >> >> I've blogged about this >> >> >> inhttp://sqlblog.com/blogs/tibor_karaszi/archive/2007/08/23/xp-cmdshell.... >> >> >> The blog >> >> >> basically say what I said above. >> >> >> >> -- >> >> >> Tibor Karaszi, SQL Server >> >> >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/... >> >> >> >> <jose.mende...@gmail.com> wrote in message >> >> >> >>news:1187850729.193816.181030@x35g2000prf.googlegroups.com... >> >> >> >> > My issue. I'm running a web app which makes command calls to a >> >> >> > DB >> >> >> > using SQL Authentication. One task the app will have to do is >> >> >> > utilize >> >> >> > th "xp_cmdshell" to populate an Excel spreadsheet and move the >> >> >> > file >> >> >> > from location A to B which is a linked server. Since the DB user >> >> >> > executing all my sql commands is not a default sysadmin. My app >> >> >> > encounters the the following error "...EXECUTE permission denied >> >> >> > on >> >> >> > object 'xp_cmdshell', database 'mssqlsystemresource'..." I've
On Aug 23, 3:42 am, "Tibor Karaszi"
[quoted text, click to view] <tibor_please.no.email_kara...@hotmail.nomail.com> wrote: > > 3) Executed the "sp_xp_cmdshell_proxy_account" sproc for this new acct > > That is not howxp_cmdshellsecurity works. See my other reply. > > There are basically three things involved here. > > 1. Execution ofxp_cmdshellneed to be allowed at the instance level. > > 2. The user need permissions in SQL Server to executexp_cmdshell. > > 3. SQL Server then need to open a process (the process spawned byxp_cmdshell) in a certain Windows > context (every process in Windows need to execute in a certain Windows user context). > > 1: > Use "Surface Area Configuration" or sp_configure to allow usage ofxp_cmdshell: > > EXEC sp_configure 'xp_cmdshell', 1 > RECONFIGURE > > 2. You need to have a user in the *master* database which has execute permission onxp_cmdshell. If > you are uncertain about the difference between login and user, you should read up in BOL on > security: > > USE master > CREATE LOGIN x WITH PASSWORD = 'jlkw#.6(' > > --Note, we are in the master database!!! > CREATE USER x FROM LOGIN x > GO > > --Run as login x > EXECUTE AS login = 'x' > --Below fails, no execute permission onxp_cmdshell > EXECxp_cmdshell'DIR C:\*.*' > REVERT > > --Note, we are in the master database!!! > GRANT EXECUTE ONxp_cmdshellTO x > > --Try again > EXECUTE AS login = 'x' > --Execution ofxp_cmdshellis allowed. > --But I haven't configured the proxyaccount... > EXECxp_cmdshell'DIR C:\*.*' > REVERT > > The execution ofxp_cmdshellgave below error message: > > Msg 15153, Level 16, State 1, Procedurexp_cmdshell, Line 1 > Thexp_cmdshellproxyaccountinformationcannotberetrievedor isinvalid. Verify that the > '##xp_cmdshell_proxy_account##' credential exists and contains validinformation. > > Above is expected. So for the final step: > 3. > Now we need to say what Windowsaccountshould be used when a non-sysadmin user is executingxp_cmdshell: > EXEC sp_xp_cmdshell_proxy_account 'domain\WinUser','pwd' > EXECUTE AS login = 'x' > --Execution ofxp_cmdshellis allowed. > --And executes successfully!!! > EXECxp_cmdshell'DIR C:\*.*' > REVERT > > And below is a clean-up of what I've done: > EXEC sp_xp_cmdshell_proxy_account null > > DROP USER JohnDoe > DROP LOGIN JohnDoe > EXEC sp_configure 'xp_cmdshell', 0 > RECONFIGURE > > I've blogged about this inhttp://sqlblog.com/blogs/tibor_karaszi/archive/2007/08/23/xp-cmdshell.... The blog > basically say what I said above. > > -- > Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi > > <jose.mende...@gmail.com> wrote in message > > news:1187850729.193816.181030@x35g2000prf.googlegroups.com... > > > > > My issue. I'm running a web app which makes command calls to a DB > > using SQL Authentication. One task the app will have to do is utilize > > th "xp_cmdshell" to populate an Excel spreadsheet and move the file > > from location A to B which is a linked server. Since the DB user > > executing all my sql commands is not a default sysadmin. My app > > encounters the the following error "...EXECUTE permission denied on > > object 'xp_cmdshell', database 'mssqlsystemresource'..." I've > > investigated on possible ways to resolve this issue however I've had > > no luck on resolving this issue; below are the step's I taken so far. > > > 1) I created a windows acct - "ADomain\AUserAcct" > > 2) Created a Login acct for this user ("ADomain\AUserAcct") in SQL > > 2005 and granted user access to this DB. User access for thisaccount > > is "dbowner" > > 3) Executed the "sp_xp_cmdshell_proxy_account" sproc for this new acct > > a. EXEC sp_xp_cmdshell_proxy_account "ADomain\AUserAcct", > > "pwd" > > 4) Granted execute rights on sys.xp_cmdshellfor this user > > a. GRANT EXECUTE ON sys.xp_cmdshellTO "ADomain\AUserAcct > > > After these steps, I tried to execute my process again but ecountered > > the same error msg. so > > > 1) I modified the stored sproc to execute with the new user's acct. > > but had the same error > > a.Create proc myprocname with Execute As ADomain\AUserAcct > > > Note: when I execute the proc. Before any of these steps thru sql 2005 > > IDE with a sysadminaccountall works great. > > > If anyone has any suggestions on how to resolve this or pointers on > > where I went wrong, I would greatly appreciate it. > > > Thanks,- Hide quoted text - > > - Show quoted text - Ok, I followed all your instructions above and I am still getting the the following error "...EXECUTE permission denied on object 'xp_cmdshell', database 'mssqlsystemresource'..." This is what I have done: (1) Created a login (server role=public) (2) Created a user from login in the master database (3) Gave it Execute permission on xp_cmdshell (4) exec sp_xp_cmdshell_proxy_account 'Domain\WinAccount','pwd' and it doesn't work. what am i doing wrong?? BTW, I am using xp_cmdshell off a trigger, do I always have to specify exec sp_xp_cmdshell_proxy_account 'Domain \WinAccount','pwd', exec as login = 'Login' whenever the trigger gets invoked?
On Sep 11, 12:12 pm, "Tibor Karaszi"
[quoted text, click to view] <tibor_please.no.email_kara...@hotmail.nomail.com> wrote: > > Ok, I followed all your instructions above and I am still getting the > > the following error "...EXECUTE permission denied on > > object 'xp_cmdshell', database 'mssqlsystemresource'..." > > > This is what I have done: > > > (1) Created a login (server role=public) > > (2) Created a user from login in the master database > > (3) Gave it Execute permission on xp_cmdshell > > (4) exec sp_xp_cmdshell_proxy_account 'Domain\WinAccount','pwd' > > > and it doesn't work. > > I would suspect that you aren't using the login you created in stepo 1 when you execute xp_cmdshell. > > > BTW, I am using xp_cmdshell off a trigger, > > do I always have to specify exec sp_xp_cmdshell_proxy_account 'Domain > > \WinAccount','pwd', exec as login = 'Login' whenever the trigger gets > > invoked? > > No, that is a one-time configuration. And, of course, you need to specify a valif login name and > password for your domain... > > -- > Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi > > > > <audrey....@gmail.com> wrote in messagenews:1189524563.343178.290270@w3g2000hsg.googlegroups.com... > > On Aug 23, 3:42 am, "Tibor Karaszi" > > <tibor_please.no.email_kara...@hotmail.nomail.com> wrote: > >> > 3) Executed the "sp_xp_cmdshell_proxy_account" sproc for this new acct > > >> That is not howxp_cmdshellsecurity works. See my other reply. > > >> There are basically three things involved here. > > >> 1. Execution ofxp_cmdshellneed to be allowed at the instance level. > > >> 2. The user need permissions in SQL Server to executexp_cmdshell. > > >> 3. SQL Server then need to open a process (the process spawned byxp_cmdshell) in a certain > >> Windows > >> context (every process in Windows need to execute in a certain Windows user context). > > >> 1: > >> Use "Surface Area Configuration" or sp_configure to allow usage ofxp_cmdshell: > > >> EXEC sp_configure 'xp_cmdshell', 1 > >> RECONFIGURE > > >> 2. You need to have a user in the *master* database which has execute permission onxp_cmdshell. > >> If > >> you are uncertain about the difference between login and user, you should read up in BOL on > >> security: > > >> USE master > >> CREATE LOGIN x WITH PASSWORD = 'jlkw#.6(' > > >> --Note, we are in the master database!!! > >> CREATE USER x FROM LOGIN x > >> GO > > >> --Run as login x > >> EXECUTE AS login = 'x' > >> --Below fails, no execute permission onxp_cmdshell > >> EXECxp_cmdshell'DIR C:\*.*' > >> REVERT > > >> --Note, we are in the master database!!! > >> GRANT EXECUTE ONxp_cmdshellTO x > > >> --Try again > >> EXECUTE AS login = 'x' > >> --Execution ofxp_cmdshellis allowed. > >> --But I haven't configured the proxyaccount... > >> EXECxp_cmdshell'DIR C:\*.*' > >> REVERT > > >> The execution ofxp_cmdshellgave below error message: > > >> Msg 15153, Level 16, State 1, Procedurexp_cmdshell, Line 1 > >> Thexp_cmdshellproxyaccountinformationcannotberetrievedor isinvalid. Verify that the > >> '##xp_cmdshell_proxy_account##' credential exists and contains validinformation. > > >> Above is expected. So for the final step: > >> 3. > >> Now we need to say what Windowsaccountshould be used when a non-sysadmin user is > >> executingxp_cmdshell: > >> EXEC sp_xp_cmdshell_proxy_account 'domain\WinUser','pwd' > >> EXECUTE AS login = 'x' > >> --Execution ofxp_cmdshellis allowed. > >> --And executes successfully!!! > >> EXECxp_cmdshell'DIR C:\*.*' > >> REVERT > > >> And below is a clean-up of what I've done: > >> EXEC sp_xp_cmdshell_proxy_account null > > >> DROP USER JohnDoe > >> DROP LOGIN JohnDoe > >> EXEC sp_configure 'xp_cmdshell', 0 > >> RECONFIGURE > > >> I've blogged about this > >> inhttp://sqlblog.com/blogs/tibor_karaszi/archive/2007/08/23/xp-cmdshell.... The blog > >> basically say what I said above. > > >> -- > >> Tibor Karaszi, SQL Server > >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/... > > >> <jose.mende...@gmail.com> wrote in message > > >>news:1187850729.193816.181030@x35g2000prf.googlegroups.com... > > >> > My issue. I'm running a web app which makes command calls to a DB > >> > using SQL Authentication. One task the app will have to do is utilize > >> > th "xp_cmdshell" to populate an Excel spreadsheet and move the file > >> > from location A to B which is a linked server. Since the DB user > >> > executing all my sql commands is not a default sysadmin. My app > >> > encounters the the following error "...EXECUTE permission denied on > >> > object 'xp_cmdshell', database 'mssqlsystemresource'..." I've > >> > investigated on possible ways to resolve this issue however I've had > >> > no luck on resolving this issue; below are the step's I taken so far. > > >> > 1) I created a windows acct - "ADomain\AUserAcct" > >> > 2) Created a Login acct for this user ("ADomain\AUserAcct") in SQL > >> > 2005 and granted user access to this DB. User access for thisaccount > >> > is "dbowner" > >> > 3) Executed the "sp_xp_cmdshell_proxy_account" sproc for this new acct > >> > a. EXEC sp_xp_cmdshell_proxy_account "ADomain\AUserAcct", > >> > "pwd" > >> > 4) Granted execute rights on sys.xp_cmdshellfor this user > >> > a. GRANT EXECUTE ON sys.xp_cmdshellTO "ADomain\AUserAcct > > >> > After these steps, I tried to execute my process again but ecountered > >> > the same error msg. so > > >> > 1) I modified the stored sproc to execute with the new user's acct. > >> > but had the same error > >> > a.Create proc myprocname with Execute As ADomain\AUserAcct > > >> > Note: when I execute the proc. Before any of these steps thru sql 2005 > >> > IDE with a sysadminaccountall works great. > > >> > If anyone has any suggestions on how to resolve this or pointers on > >> > where I went wrong, I would greatly appreciate it. > > >> > Thanks,- Hide quoted text - > > >> - Show quoted text - > > > Ok, I followed all your instructions above and I am still getting the > > the following error "...EXECUTE permission denied on > > object 'xp_cmdshell', database 'mssqlsystemresource'..." > > > This is what I have done: > > > (1) Created a login (server role=public) > > (2) Created a user from login in the master database > > (3) Gave it Execute permission on xp_cmdshell > > (4) exec sp_xp_cmdshell_proxy_account 'Domain\WinAccount','pwd' > > > and it doesn't work. > > > what am i doing wrong?? > > > BTW, I am using xp_cmdshell off a trigger, > > do I always have to specify exec sp_xp_cmdshell_proxy_account 'Domain > > \WinAccount','pwd', exec as login = 'Login' whenever the trigger gets > > invoked?- Hide quoted text - > > - Show quoted text - in the trigger, i run exec xp_cmdshell
[quoted text, click to view]
> Ok, I followed all your instructions above and I am still getting the > the following error "...EXECUTE permission denied on > object 'xp_cmdshell', database 'mssqlsystemresource'..." > > This is what I have done: > > (1) Created a login (server role=public) > (2) Created a user from login in the master database > (3) Gave it Execute permission on xp_cmdshell > (4) exec sp_xp_cmdshell_proxy_account 'Domain\WinAccount','pwd' > > and it doesn't work. I would suspect that you aren't using the login you created in stepo 1 when you execute xp_cmdshell. [quoted text, click to view] > BTW, I am using xp_cmdshell off a trigger, > do I always have to specify exec sp_xp_cmdshell_proxy_account 'Domain > \WinAccount','pwd', exec as login = 'Login' whenever the trigger gets > invoked? No, that is a one-time configuration. And, of course, you need to specify a valif login name and password for your domain... -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi [quoted text, click to view] <audrey.nsh@gmail.com> wrote in message news:1189524563.343178.290270@w3g2000hsg.googlegroups.com...
> On Aug 23, 3:42 am, "Tibor Karaszi" > <tibor_please.no.email_kara...@hotmail.nomail.com> wrote: >> > 3) Executed the "sp_xp_cmdshell_proxy_account" sproc for this new acct >> >> That is not howxp_cmdshellsecurity works. See my other reply. >> >> There are basically three things involved here. >> >> 1. Execution ofxp_cmdshellneed to be allowed at the instance level. >> >> 2. The user need permissions in SQL Server to executexp_cmdshell. >> >> 3. SQL Server then need to open a process (the process spawned byxp_cmdshell) in a certain >> Windows >> context (every process in Windows need to execute in a certain Windows user context). >> >> 1: >> Use "Surface Area Configuration" or sp_configure to allow usage ofxp_cmdshell: >> >> EXEC sp_configure 'xp_cmdshell', 1 >> RECONFIGURE >> >> 2. You need to have a user in the *master* database which has execute permission onxp_cmdshell. >> If >> you are uncertain about the difference between login and user, you should read up in BOL on >> security: >> >> USE master >> CREATE LOGIN x WITH PASSWORD = 'jlkw#.6(' >> >> --Note, we are in the master database!!! >> CREATE USER x FROM LOGIN x >> GO >> >> --Run as login x >> EXECUTE AS login = 'x' >> --Below fails, no execute permission onxp_cmdshell >> EXECxp_cmdshell'DIR C:\*.*' >> REVERT >> >> --Note, we are in the master database!!! >> GRANT EXECUTE ONxp_cmdshellTO x >> >> --Try again >> EXECUTE AS login = 'x' >> --Execution ofxp_cmdshellis allowed. >> --But I haven't configured the proxyaccount... >> EXECxp_cmdshell'DIR C:\*.*' >> REVERT >> >> The execution ofxp_cmdshellgave below error message: >> >> Msg 15153, Level 16, State 1, Procedurexp_cmdshell, Line 1 >> Thexp_cmdshellproxyaccountinformationcannotberetrievedor isinvalid. Verify that the >> '##xp_cmdshell_proxy_account##' credential exists and contains validinformation. >> >> Above is expected. So for the final step: >> 3. >> Now we need to say what Windowsaccountshould be used when a non-sysadmin user is >> executingxp_cmdshell: >> EXEC sp_xp_cmdshell_proxy_account 'domain\WinUser','pwd' >> EXECUTE AS login = 'x' >> --Execution ofxp_cmdshellis allowed. >> --And executes successfully!!! >> EXECxp_cmdshell'DIR C:\*.*' >> REVERT >> >> And below is a clean-up of what I've done: >> EXEC sp_xp_cmdshell_proxy_account null >> >> DROP USER JohnDoe >> DROP LOGIN JohnDoe >> EXEC sp_configure 'xp_cmdshell', 0 >> RECONFIGURE >> >> I've blogged about this >> inhttp://sqlblog.com/blogs/tibor_karaszi/archive/2007/08/23/xp-cmdshell.... The blog >> basically say what I said above. >> >> -- >> Tibor Karaszi, SQL Server >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi >> >> <jose.mende...@gmail.com> wrote in message >> >> news:1187850729.193816.181030@x35g2000prf.googlegroups.com... >> >> >> >> > My issue. I'm running a web app which makes command calls to a DB >> > using SQL Authentication. One task the app will have to do is utilize >> > th "xp_cmdshell" to populate an Excel spreadsheet and move the file >> > from location A to B which is a linked server. Since the DB user >> > executing all my sql commands is not a default sysadmin. My app >> > encounters the the following error "...EXECUTE permission denied on >> > object 'xp_cmdshell', database 'mssqlsystemresource'..." I've >> > investigated on possible ways to resolve this issue however I've had >> > no luck on resolving this issue; below are the step's I taken so far. >> >> > 1) I created a windows acct - "ADomain\AUserAcct" >> > 2) Created a Login acct for this user ("ADomain\AUserAcct") in SQL >> > 2005 and granted user access to this DB. User access for thisaccount >> > is "dbowner" >> > 3) Executed the "sp_xp_cmdshell_proxy_account" sproc for this new acct >> > a. EXEC sp_xp_cmdshell_proxy_account "ADomain\AUserAcct", >> > "pwd" >> > 4) Granted execute rights on sys.xp_cmdshellfor this user >> > a. GRANT EXECUTE ON sys.xp_cmdshellTO "ADomain\AUserAcct >> >> > After these steps, I tried to execute my process again but ecountered >> > the same error msg. so >> >> > 1) I modified the stored sproc to execute with the new user's acct. >> > but had the same error >> > a.Create proc myprocname with Execute As ADomain\AUserAcct >> >> > Note: when I execute the proc. Before any of these steps thru sql 2005 >> > IDE with a sysadminaccountall works great. >> >> > If anyone has any suggestions on how to resolve this or pointers on >> > where I went wrong, I would greatly appreciate it. >> >> > Thanks,- Hide quoted text - >> >> - Show quoted text - > > Ok, I followed all your instructions above and I am still getting the > the following error "...EXECUTE permission denied on > object 'xp_cmdshell', database 'mssqlsystemresource'..." > > This is what I have done: > > (1) Created a login (server role=public) > (2) Created a user from login in the master database > (3) Gave it Execute permission on xp_cmdshell > (4) exec sp_xp_cmdshell_proxy_account 'Domain\WinAccount','pwd' > > and it doesn't work. > > what am i doing wrong?? > > BTW, I am using xp_cmdshell off a trigger, > do I always have to specify exec sp_xp_cmdshell_proxy_account 'Domain > \WinAccount','pwd', exec as login = 'Login' whenever the trigger gets > invoked? >
I'm sorry, but I'm out of ideas... :-(
-- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi [quoted text, click to view] <audrey.nsh@gmail.com> wrote in message news:1189529952.930980.79700@r29g2000hsg.googlegroups.com...
> On Sep 11, 12:12 pm, "Tibor Karaszi" > <tibor_please.no.email_kara...@hotmail.nomail.com> wrote: >> > Ok, I followed all your instructions above and I am still getting the >> > the following error "...EXECUTE permission denied on >> > object 'xp_cmdshell', database 'mssqlsystemresource'..." >> >> > This is what I have done: >> >> > (1) Created a login (server role=public) >> > (2) Created a user from login in the master database >> > (3) Gave it Execute permission on xp_cmdshell >> > (4) exec sp_xp_cmdshell_proxy_account 'Domain\WinAccount','pwd' >> >> > and it doesn't work. >> >> I would suspect that you aren't using the login you created in stepo 1 when you execute >> xp_cmdshell. >> >> > BTW, I am using xp_cmdshell off a trigger, >> > do I always have to specify exec sp_xp_cmdshell_proxy_account 'Domain >> > \WinAccount','pwd', exec as login = 'Login' whenever the trigger gets >> > invoked? >> >> No, that is a one-time configuration. And, of course, you need to specify a valif login name and >> password for your domain... >> >> -- >> Tibor Karaszi, SQL Server >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi >> >> >> >> <audrey....@gmail.com> wrote in >> messagenews:1189524563.343178.290270@w3g2000hsg.googlegroups.com... >> > On Aug 23, 3:42 am, "Tibor Karaszi" >> > <tibor_please.no.email_kara...@hotmail.nomail.com> wrote: >> >> > 3) Executed the "sp_xp_cmdshell_proxy_account" sproc for this new acct >> >> >> That is not howxp_cmdshellsecurity works. See my other reply. >> >> >> There are basically three things involved here. >> >> >> 1. Execution ofxp_cmdshellneed to be allowed at the instance level. >> >> >> 2. The user need permissions in SQL Server to executexp_cmdshell. >> >> >> 3. SQL Server then need to open a process (the process spawned byxp_cmdshell) in a certain >> >> Windows >> >> context (every process in Windows need to execute in a certain Windows user context). >> >> >> 1: >> >> Use "Surface Area Configuration" or sp_configure to allow usage ofxp_cmdshell: >> >> >> EXEC sp_configure 'xp_cmdshell', 1 >> >> RECONFIGURE >> >> >> 2. You need to have a user in the *master* database which has execute permission >> >> onxp_cmdshell. >> >> If >> >> you are uncertain about the difference between login and user, you should read up in BOL on >> >> security: >> >> >> USE master >> >> CREATE LOGIN x WITH PASSWORD = 'jlkw#.6(' >> >> >> --Note, we are in the master database!!! >> >> CREATE USER x FROM LOGIN x >> >> GO >> >> >> --Run as login x >> >> EXECUTE AS login = 'x' >> >> --Below fails, no execute permission onxp_cmdshell >> >> EXECxp_cmdshell'DIR C:\*.*' >> >> REVERT >> >> >> --Note, we are in the master database!!! >> >> GRANT EXECUTE ONxp_cmdshellTO x >> >> >> --Try again >> >> EXECUTE AS login = 'x' >> >> --Execution ofxp_cmdshellis allowed. >> >> --But I haven't configured the proxyaccount... >> >> EXECxp_cmdshell'DIR C:\*.*' >> >> REVERT >> >> >> The execution ofxp_cmdshellgave below error message: >> >> >> Msg 15153, Level 16, State 1, Procedurexp_cmdshell, Line 1 >> >> Thexp_cmdshellproxyaccountinformationcannotberetrievedor isinvalid. Verify that the >> >> '##xp_cmdshell_proxy_account##' credential exists and contains validinformation. >> >> >> Above is expected. So for the final step: >> >> 3. >> >> Now we need to say what Windowsaccountshould be used when a non-sysadmin user is >> >> executingxp_cmdshell: >> >> EXEC sp_xp_cmdshell_proxy_account 'domain\WinUser','pwd' >> >> EXECUTE AS login = 'x' >> >> --Execution ofxp_cmdshellis allowed. >> >> --And executes successfully!!! >> >> EXECxp_cmdshell'DIR C:\*.*' >> >> REVERT >> >> >> And below is a clean-up of what I've done: >> >> EXEC sp_xp_cmdshell_proxy_account null >> >> >> DROP USER JohnDoe >> >> DROP LOGIN JohnDoe >> >> EXEC sp_configure 'xp_cmdshell', 0 >> >> RECONFIGURE >> >> >> I've blogged about this >> >> inhttp://sqlblog.com/blogs/tibor_karaszi/archive/2007/08/23/xp-cmdshell.... The blog >> >> basically say what I said above. >> >> >> -- >> >> Tibor Karaszi, SQL Server >> >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/... >> >> >> <jose.mende...@gmail.com> wrote in message >> >> >>news:1187850729.193816.181030@x35g2000prf.googlegroups.com... >> >> >> > My issue. I'm running a web app which makes command calls to a DB >> >> > using SQL Authentication. One task the app will have to do is utilize >> >> > th "xp_cmdshell" to populate an Excel spreadsheet and move the file >> >> > from location A to B which is a linked server. Since the DB user >> >> > executing all my sql commands is not a default sysadmin. My app >> >> > encounters the the following error "...EXECUTE permission denied on >> >> > object 'xp_cmdshell', database 'mssqlsystemresource'..." I've >> >> > investigated on possible ways to resolve this issue however I've had >> >> > no luck on resolving this issue; below are the step's I taken so far. >> >> >> > 1) I created a windows acct - "ADomain\AUserAcct" >> >> > 2) Created a Login acct for this user ("ADomain\AUserAcct") in SQL >> >> > 2005 and granted user access to this DB. User access for thisaccount >> >> > is "dbowner" >> >> > 3) Executed the "sp_xp_cmdshell_proxy_account" sproc for this new acct >> >> > a. EXEC sp_xp_cmdshell_proxy_account "ADomain\AUserAcct", >> >> > "pwd" >> >> > 4) Granted execute rights on sys.xp_cmdshellfor this user >> >> > a. GRANT EXECUTE ON sys.xp_cmdshellTO "ADomain\AUserAcct >> >> >> > After these steps, I tried to execute my process again but ecountered >> >> > the same error msg. so >> >> >> > 1) I modified the stored sproc to execute with the new user's acct. >> >> > but had the same error >> >> > a.Create proc myprocname with Execute As ADomain\AUserAcct >> >> >> > Note: when I execute the proc. Before any of these steps thru sql 2005 >> >> > IDE with a sysadminaccountall works great. >> >> >> > If anyone has any suggestions on how to resolve this or pointers on >> >> > where I went wrong, I would greatly appreciate it. >> >> >> > Thanks,- Hide quoted text - >> >> >> - Show quoted text - >> >> > Ok, I followed all your instructions above and I am still getting the >> > the following error "...EXECUTE permission denied on >> > object 'xp_cmdshell', database 'mssqlsystemresource'..." >> >> > This is what I have done: >> >> > (1) Created a login (server role=public) >> > (2) Created a user from login in the master database >> > (3) Gave it Execute permission on xp_cmdshell
On Sep 11, 1:13 pm, "Tibor Karaszi"
[quoted text, click to view] <tibor_please.no.email_kara...@hotmail.nomail.com> wrote:
> I'm sorry, but I'm out of ideas... :-( > > -- > Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi > > > > <audrey....@gmail.com> wrote in messagenews:1189529952.930980.79700@r29g2000hsg.googlegroups.com... > > On Sep 11, 12:12 pm, "Tibor Karaszi" > > <tibor_please.no.email_kara...@hotmail.nomail.com> wrote: > >> > Ok, I followed all your instructions above and I am still getting the > >> > the following error "...EXECUTE permission denied on > >> > object 'xp_cmdshell', database 'mssqlsystemresource'..." > > >> > This is what I have done: > > >> > (1) Created a login (server role=public) > >> > (2) Created a user from login in the master database > >> > (3) Gave it Execute permission on xp_cmdshell > >> > (4) exec sp_xp_cmdshell_proxy_account 'Domain\WinAccount','pwd' > > >> > and it doesn't work. > > >> I would suspect that you aren't using the login you created in stepo 1 when you execute > >> xp_cmdshell. > > >> > BTW, I am using xp_cmdshell off a trigger, > >> > do I always have to specify exec sp_xp_cmdshell_proxy_account 'Domain > >> > \WinAccount','pwd', exec as login = 'Login' whenever the trigger gets > >> > invoked? > > >> No, that is a one-time configuration. And, of course, you need to specify a valif login name and > >> password for your domain... > > >> -- > >> Tibor Karaszi, SQL Server > >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/... > > >> <audrey....@gmail.com> wrote in > >> messagenews:1189524563.343178.290270@w3g2000hsg.googlegroups.com... > >> > On Aug 23, 3:42 am, "Tibor Karaszi" > >> > <tibor_please.no.email_kara...@hotmail.nomail.com> wrote: > >> >> > 3) Executed the "sp_xp_cmdshell_proxy_account" sproc for this new acct > > >> >> That is not howxp_cmdshellsecurity works. See my other reply. > > >> >> There are basically three things involved here. > > >> >> 1. Execution ofxp_cmdshellneed to be allowed at the instance level. > > >> >> 2. The user need permissions in SQL Server to executexp_cmdshell. > > >> >> 3. SQL Server then need to open a process (the process spawned byxp_cmdshell) in a certain > >> >> Windows > >> >> context (every process in Windows need to execute in a certain Windows user context). > > >> >> 1: > >> >> Use "Surface Area Configuration" or sp_configure to allow usage ofxp_cmdshell: > > >> >> EXEC sp_configure 'xp_cmdshell', 1 > >> >> RECONFIGURE > > >> >> 2. You need to have a user in the *master* database which has execute permission > >> >> onxp_cmdshell. > >> >> If > >> >> you are uncertain about the difference between login and user, you should read up in BOL on > >> >> security: > > >> >> USE master > >> >> CREATE LOGIN x WITH PASSWORD = 'jlkw#.6(' > > >> >> --Note, we are in the master database!!! > >> >> CREATE USER x FROM LOGIN x > >> >> GO > > >> >> --Run as login x > >> >> EXECUTE AS login = 'x' > >> >> --Below fails, no execute permission onxp_cmdshell > >> >> EXECxp_cmdshell'DIR C:\*.*' > >> >> REVERT > > >> >> --Note, we are in the master database!!! > >> >> GRANT EXECUTE ONxp_cmdshellTO x > > >> >> --Try again > >> >> EXECUTE AS login = 'x' > >> >> --Execution ofxp_cmdshellis allowed. > >> >> --But I haven't configured the proxyaccount... > >> >> EXECxp_cmdshell'DIR C:\*.*' > >> >> REVERT > > >> >> The execution ofxp_cmdshellgave below error message: > > >> >> Msg 15153, Level 16, State 1, Procedurexp_cmdshell, Line 1 > >> >> Thexp_cmdshellproxyaccountinformationcannotberetrievedor isinvalid. Verify that the > >> >> '##xp_cmdshell_proxy_account##' credential exists and contains validinformation. > > >> >> Above is expected. So for the final step: > >> >> 3. > >> >> Now we need to say what Windowsaccountshould be used when a non-sysadmin user is > >> >> executingxp_cmdshell: > >> >> EXEC sp_xp_cmdshell_proxy_account 'domain\WinUser','pwd' > >> >> EXECUTE AS login = 'x' > >> >> --Execution ofxp_cmdshellis allowed. > >> >> --And executes successfully!!! > >> >> EXECxp_cmdshell'DIR C:\*.*' > >> >> REVERT > > >> >> And below is a clean-up of what I've done: > >> >> EXEC sp_xp_cmdshell_proxy_account null > > >> >> DROP USER JohnDoe > >> >> DROP LOGIN JohnDoe > >> >> EXEC sp_configure 'xp_cmdshell', 0 > >> >> RECONFIGURE > > >> >> I've blogged about this > >> >> inhttp://sqlblog.com/blogs/tibor_karaszi/archive/2007/08/23/xp-cmdshell.... The blog > >> >> basically say what I said above. > > >> >> -- > >> >> Tibor Karaszi, SQL Server > >> >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/... > > >> >> <jose.mende...@gmail.com> wrote in message > > >> >>news:1187850729.193816.181030@x35g2000prf.googlegroups.com... > > >> >> > My issue. I'm running a web app which makes command calls to a DB > >> >> > using SQL Authentication. One task the app will have to do is utilize > >> >> > th "xp_cmdshell" to populate an Excel spreadsheet and move the file > >> >> > from location A to B which is a linked server. Since the DB user > >> >> > executing all my sql commands is not a default sysadmin. My app > >> >> > encounters the the following error "...EXECUTE permission denied on > >> >> > object 'xp_cmdshell', database 'mssqlsystemresource'..." I've > >> >> > investigated on possible ways to resolve this issue however I've had > >> >> > no luck on resolving this issue; below are the step's I taken so far. > > >> >> > 1) I created a windows acct - "ADomain\AUserAcct" > >> >> > 2) Created a Login acct for this user ("ADomain\AUserAcct") in SQL > >> >> > 2005 and granted user access to this DB. User access for thisaccount > >> >> > is "dbowner" > >> >> > 3) Executed the "sp_xp_cmdshell_proxy_account" sproc for this new acct > >> >> > a. EXEC sp_xp_cmdshell_proxy_account "ADomain\AUserAcct", > >> >> > "pwd" > >> >> > 4) Granted execute rights on sys.xp_cmdshellfor this user > >> >> > a. GRANT EXECUTE ON sys.xp_cmdshellTO "ADomain\AUserAcct > > >> >> > After these steps, I tried to execute my process again but ecountered > >> >> > the same error msg. so > > >> >> > 1) I modified the stored sproc to execute with the new user's acct. > >> >> > but had the same error > >> >> > a.Create proc myprocname with Execute As ADomain\AUserAcct > > >> >> > Note: when I execute the proc. Before any of these steps thru sql 2005 > >> >> > IDE with a sysadminaccountall works great. > > >> >> > If anyone has any suggestions on how to resolve this or pointers on > >> >> > where I went wrong, I would greatly appreciate it. > > >> >> > Thanks,- Hide quoted text - > > >> >> - Show quoted text - > > >> > Ok, I followed all your instructions above and I am still getting the > >> > the following error "...EXECUTE permission denied on
On Sep 11, 1:13 pm, "Tibor Karaszi"
[quoted text, click to view] <tibor_please.no.email_kara...@hotmail.nomail.com> wrote:
> I'm sorry, but I'm out of ideas... :-( > > -- > Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi > > > > <audrey....@gmail.com> wrote in messagenews:1189529952.930980.79700@r29g2000hsg.googlegroups.com... > > On Sep 11, 12:12 pm, "Tibor Karaszi" > > <tibor_please.no.email_kara...@hotmail.nomail.com> wrote: > >> > Ok, I followed all your instructions above and I am still getting the > >> > the following error "...EXECUTE permission denied on > >> > object 'xp_cmdshell', database 'mssqlsystemresource'..." > > >> > This is what I have done: > > >> > (1) Created a login (server role=public) > >> > (2) Created a user from login in the master database > >> > (3) Gave it Execute permission on xp_cmdshell > >> > (4) exec sp_xp_cmdshell_proxy_account 'Domain\WinAccount','pwd' > > >> > and it doesn't work. > > >> I would suspect that you aren't using the login you created in stepo 1 when you execute > >> xp_cmdshell. > > >> > BTW, I am using xp_cmdshell off a trigger, > >> > do I always have to specify exec sp_xp_cmdshell_proxy_account 'Domain > >> > \WinAccount','pwd', exec as login = 'Login' whenever the trigger gets > >> > invoked? > > >> No, that is a one-time configuration. And, of course, you need to specify a valif login name and > >> password for your domain... > > >> -- > >> Tibor Karaszi, SQL Server > >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/... > > >> <audrey....@gmail.com> wrote in > >> messagenews:1189524563.343178.290270@w3g2000hsg.googlegroups.com... > >> > On Aug 23, 3:42 am, "Tibor Karaszi" > >> > <tibor_please.no.email_kara...@hotmail.nomail.com> wrote: > >> >> > 3) Executed the "sp_xp_cmdshell_proxy_account" sproc for this new acct > > >> >> That is not howxp_cmdshellsecurity works. See my other reply. > > >> >> There are basically three things involved here. > > >> >> 1. Execution ofxp_cmdshellneed to be allowed at the instance level. > > >> >> 2. The user need permissions in SQL Server to executexp_cmdshell. > > >> >> 3. SQL Server then need to open a process (the process spawned byxp_cmdshell) in a certain > >> >> Windows > >> >> context (every process in Windows need to execute in a certain Windows user context). > > >> >> 1: > >> >> Use "Surface Area Configuration" or sp_configure to allow usage ofxp_cmdshell: > > >> >> EXEC sp_configure 'xp_cmdshell', 1 > >> >> RECONFIGURE > > >> >> 2. You need to have a user in the *master* database which has execute permission > >> >> onxp_cmdshell. > >> >> If > >> >> you are uncertain about the difference between login and user, you should read up in BOL on > >> >> security: > > >> >> USE master > >> >> CREATE LOGIN x WITH PASSWORD = 'jlkw#.6(' > > >> >> --Note, we are in the master database!!! > >> >> CREATE USER x FROM LOGIN x > >> >> GO > > >> >> --Run as login x > >> >> EXECUTE AS login = 'x' > >> >> --Below fails, no execute permission onxp_cmdshell > >> >> EXECxp_cmdshell'DIR C:\*.*' > >> >> REVERT > > >> >> --Note, we are in the master database!!! > >> >> GRANT EXECUTE ONxp_cmdshellTO x > > >> >> --Try again > >> >> EXECUTE AS login = 'x' > >> >> --Execution ofxp_cmdshellis allowed. > >> >> --But I haven't configured the proxyaccount... > >> >> EXECxp_cmdshell'DIR C:\*.*' > >> >> REVERT > > >> >> The execution ofxp_cmdshellgave below error message: > > >> >> Msg 15153, Level 16, State 1, Procedurexp_cmdshell, Line 1 > >> >> Thexp_cmdshellproxyaccountinformationcannotberetrievedor isinvalid. Verify that the > >> >> '##xp_cmdshell_proxy_account##' credential exists and contains validinformation. > > >> >> Above is expected. So for the final step: > >> >> 3. > >> >> Now we need to say what Windowsaccountshould be used when a non-sysadmin user is > >> >> executingxp_cmdshell: > >> >> EXEC sp_xp_cmdshell_proxy_account 'domain\WinUser','pwd' > >> >> EXECUTE AS login = 'x' > >> >> --Execution ofxp_cmdshellis allowed. > >> >> --And executes successfully!!! > >> >> EXECxp_cmdshell'DIR C:\*.*' > >> >> REVERT > > >> >> And below is a clean-up of what I've done: > >> >> EXEC sp_xp_cmdshell_proxy_account null > > >> >> DROP USER JohnDoe > >> >> DROP LOGIN JohnDoe > >> >> EXEC sp_configure 'xp_cmdshell', 0 > >> >> RECONFIGURE > > >> >> I've blogged about this > >> >> inhttp://sqlblog.com/blogs/tibor_karaszi/archive/2007/08/23/xp-cmdshell.... The blog > >> >> basically say what I said above. > > >> >> -- > >> >> Tibor Karaszi, SQL Server > >> >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/... > > >> >> <jose.mende...@gmail.com> wrote in message > > >> >>news:1187850729.193816.181030@x35g2000prf.googlegroups.com... > > >> >> > My issue. I'm running a web app which makes command calls to a DB > >> >> > using SQL Authentication. One task the app will have to do is utilize > >> >> > th "xp_cmdshell" to populate an Excel spreadsheet and move the file > >> >> > from location A to B which is a linked server. Since the DB user > >> >> > executing all my sql commands is not a default sysadmin. My app > >> >> > encounters the the following error "...EXECUTE permission denied on > >> >> > object 'xp_cmdshell', database 'mssqlsystemresource'..." I've > >> >> > investigated on possible ways to resolve this issue however I've had > >> >> > no luck on resolving this issue; below are the step's I taken so far. > > >> >> > 1) I created a windows acct - "ADomain\AUserAcct" > >> >> > 2) Created a Login acct for this user ("ADomain\AUserAcct") in SQL > >> >> > 2005 and granted user access to this DB. User access for thisaccount > >> >> > is "dbowner" > >> >> > 3) Executed the "sp_xp_cmdshell_proxy_account" sproc for this new acct > >> >> > a. EXEC sp_xp_cmdshell_proxy_account "ADomain\AUserAcct", > >> >> > "pwd" > >> >> > 4) Granted execute rights on sys.xp_cmdshellfor this user > >> >> > a. GRANT EXECUTE ON sys.xp_cmdshellTO "ADomain\AUserAcct > > >> >> > After these steps, I tried to execute my process again but ecountered > >> >> > the same error msg. so > > >> >> > 1) I modified the stored sproc to execute with the new user's acct. > >> >> > but had the same error > >> >> > a.Create proc myprocname with Execute As ADomain\AUserAcct > > >> >> > Note: when I execute the proc. Before any of these steps thru sql 2005 > >> >> > IDE with a sysadminaccountall works great. > > >> >> > If anyone has any suggestions on how to resolve this or pointers on > >> >> > where I went wrong, I would greatly appreciate it. > > >> >> > Thanks,- Hide quoted text - > > >> >> - Show quoted text - > > >> > Ok, I followed all your instructions above and I am still getting the > >> > the following error "...EXECUTE permission denied on
[quoted text, click to view]
On Sep 11, 4:30 pm, audrey....@gmail.com wrote:
> On Sep 11, 1:13 pm, "Tibor Karaszi" > > > > > > <tibor_please.no.email_kara...@hotmail.nomail.com> wrote: > > I'm sorry, but I'm out of ideas... :-( > > > -- > > Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/... > > > <audrey....@gmail.com> wrote in messagenews:1189529952.930980.79700@r29g2000hsg.googlegroups.com... > > > On Sep 11, 12:12 pm, "Tibor Karaszi" > > > <tibor_please.no.email_kara...@hotmail.nomail.com> wrote: > > >> > Ok, I followed all your instructions above and I am still getting the > > >> > the following error "...EXECUTE permission denied on > > >> > object 'xp_cmdshell', database 'mssqlsystemresource'..." > > > >> > This is what I have done: > > > >> > (1) Created a login (server role=public) > > >> > (2) Created a user from login in the master database > > >> > (3) Gave it Execute permission on xp_cmdshell > > >> > (4) exec sp_xp_cmdshell_proxy_account 'Domain\WinAccount','pwd' > > > >> > and it doesn't work. > > > >> I would suspect that you aren't using the login you created in stepo 1 when you execute > > >> xp_cmdshell. > > > >> > BTW, I am using xp_cmdshell off a trigger, > > >> > do I always have to specify exec sp_xp_cmdshell_proxy_account 'Domain > > >> > \WinAccount','pwd', exec as login = 'Login' whenever the trigger gets > > >> > invoked? > > > >> No, that is a one-time configuration. And, of course, you need to specify a valif login name and > > >> password for your domain... > > > >> -- > > >> Tibor Karaszi, SQL Server > > >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/... > > > >> <audrey....@gmail.com> wrote in > > >> messagenews:1189524563.343178.290270@w3g2000hsg.googlegroups.com... > > >> > On Aug 23, 3:42 am, "Tibor Karaszi" > > >> > <tibor_please.no.email_kara...@hotmail.nomail.com> wrote: > > >> >> > 3) Executed the "sp_xp_cmdshell_proxy_account" sproc for this new acct > > > >> >> That is not howxp_cmdshellsecurity works. See my other reply. > > > >> >> There are basically three things involved here. > > > >> >> 1. Execution ofxp_cmdshellneed to be allowed at the instance level. > > > >> >> 2. The user need permissions in SQL Server to executexp_cmdshell. > > > >> >> 3. SQL Server then need to open a process (the process spawned byxp_cmdshell) in a certain > > >> >> Windows > > >> >> context (every process in Windows need to execute in a certain Windows user context). > > > >> >> 1: > > >> >> Use "Surface Area Configuration" or sp_configure to allow usage ofxp_cmdshell: > > > >> >> EXEC sp_configure 'xp_cmdshell', 1 > > >> >> RECONFIGURE > > > >> >> 2. You need to have a user in the *master* database which has execute permission > > >> >> onxp_cmdshell. > > >> >> If > > >> >> you are uncertain about the difference between login and user, you should read up in BOL on > > >> >> security: > > > >> >> USE master > > >> >> CREATE LOGIN x WITH PASSWORD = 'jlkw#.6(' > > > >> >> --Note, we are in the master database!!! > > >> >> CREATE USER x FROM LOGIN x > > >> >> GO > > > >> >> --Run as login x > > >> >> EXECUTE AS login = 'x' > > >> >> --Below fails, no execute permission onxp_cmdshell > > >> >> EXECxp_cmdshell'DIR C:\*.*' > > >> >> REVERT > > > >> >> --Note, we are in the master database!!! > > >> >> GRANT EXECUTE ONxp_cmdshellTO x > > > >> >> --Try again > > >> >> EXECUTE AS login = 'x' > > >> >> --Execution ofxp_cmdshellis allowed. > > >> >> --But I haven't configured the proxyaccount... > > >> >> EXECxp_cmdshell'DIR C:\*.*' > > >> >> REVERT > > > >> >> The execution ofxp_cmdshellgave below error message: > > > >> >> Msg 15153, Level 16, State 1, Procedurexp_cmdshell, Line 1 > > >> >> Thexp_cmdshellproxyaccountinformationcannotberetrievedor isinvalid. Verify that the > > >> >> '##xp_cmdshell_proxy_account##' credential exists and contains validinformation. > > > >> >> Above is expected. So for the final step: > > >> >> 3. > > >> >> Now we need to say what Windowsaccountshould be used when a non-sysadmin user is > > >> >> executingxp_cmdshell: > > >> >> EXEC sp_xp_cmdshell_proxy_account 'domain\WinUser','pwd' > > >> >> EXECUTE AS login = 'x' > > >> >> --Execution ofxp_cmdshellis allowed. > > >> >> --And executes successfully!!! > > >> >> EXECxp_cmdshell'DIR C:\*.*' > > >> >> REVERT > > > >> >> And below is a clean-up of what I've done: > > >> >> EXEC sp_xp_cmdshell_proxy_account null > > > >> >> DROP USER JohnDoe > > >> >> DROP LOGIN JohnDoe > > >> >> EXEC sp_configure 'xp_cmdshell', 0 > > >> >> RECONFIGURE > > > >> >> I've blogged about this > > >> >> inhttp://sqlblog.com/blogs/tibor_karaszi/archive/2007/08/23/xp-cmdshell.... The blog > > >> >> basically say what I said above. > > > >> >> -- > > >> >> Tibor Karaszi, SQL Server > > >> >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/... > > > >> >> <jose.mende...@gmail.com> wrote in message > > > >> >>news:1187850729.193816.181030@x35g2000prf.googlegroups.com... > > > >> >> > My issue. I'm running a web app which makes command calls to a DB > > >> >> > using SQL Authentication. One task the app will have to do is utilize > > >> >> > th "xp_cmdshell" to populate an Excel spreadsheet and move the file > > >> >> > from location A to B which is a linked server. Since the DB user > > >> >> > executing all my sql commands is not a default sysadmin. My app > > >> >> > encounters the the following error "...EXECUTE permission denied on > > >> >> > object 'xp_cmdshell', database 'mssqlsystemresource'..." I've > > >> >> > investigated on possible ways to resolve this issue however I've had > > >> >> > no luck on resolving this issue; below are the step's I taken so far. > > > >> >> > 1) I created a windows acct - "ADomain\AUserAcct" > > >> >> > 2) Created a Login acct for this user ("ADomain\AUserAcct") in SQL > > >> >> > 2005 and granted user access to this DB. User access for thisaccount > > >> >> > is "dbowner" > > >> >> > 3) Executed the "sp_xp_cmdshell_proxy_account" sproc for this new acct > > >> >> > a. EXEC sp_xp_cmdshell_proxy_account "ADomain\AUserAcct", > > >> >> > "pwd" > > >> >> > 4) Granted execute rights on sys.xp_cmdshellfor this user > > >> >> > a. GRANT EXECUTE ON sys.xp_cmdshellTO "ADomain\AUserAcct > > > >> >> > After these steps, I tried to execute my process again but ecountered > > >> >> > the same error msg. so > > > >> >> > 1) I modified the stored sproc to execute with the new user's acct. > > >> >> > but had the same error > > >> >> > a.Create proc myprocname with Execute As ADomain\AUserAcct > > > >> >> > Note: when I execute the proc. Before any of these steps thru sql 2005 > > >> >> > IDE with a sysadminaccountall works great. > > > >> >> > If anyone has any suggestions on how to resolve this or pointers on
[quoted text, click to view]
On Sep 11, 4:30 pm, audrey....@gmail.com wrote:
> On Sep 11, 1:13 pm, "Tibor Karaszi" > > > > > > <tibor_please.no.email_kara...@hotmail.nomail.com> wrote: > > I'm sorry, but I'm out of ideas... :-( > > > -- > > Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/... > > > <audrey....@gmail.com> wrote in messagenews:1189529952.930980.79700@r29g2000hsg.googlegroups.com... > > > On Sep 11, 12:12 pm, "Tibor Karaszi" > > > <tibor_please.no.email_kara...@hotmail.nomail.com> wrote: > > >> > Ok, I followed all your instructions above and I am still getting the > > >> > the following error "...EXECUTE permission denied on > > >> > object 'xp_cmdshell', database 'mssqlsystemresource'..." > > > >> > This is what I have done: > > > >> > (1) Created a login (server role=public) > > >> > (2) Created a user from login in the master database > > >> > (3) Gave it Execute permission on xp_cmdshell > > >> > (4) exec sp_xp_cmdshell_proxy_account 'Domain\WinAccount','pwd' > > > >> > and it doesn't work. > > > >> I would suspect that you aren't using the login you created in stepo 1 when you execute > > >> xp_cmdshell. > > > >> > BTW, I am using xp_cmdshell off a trigger, > > >> > do I always have to specify exec sp_xp_cmdshell_proxy_account 'Domain > > >> > \WinAccount','pwd', exec as login = 'Login' whenever the trigger gets > > >> > invoked? > > > >> No, that is a one-time configuration. And, of course, you need to specify a valif login name and > > >> password for your domain... > > > >> -- > > >> Tibor Karaszi, SQL Server > > >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/... > > > >> <audrey....@gmail.com> wrote in > > >> messagenews:1189524563.343178.290270@w3g2000hsg.googlegroups.com... > > >> > On Aug 23, 3:42 am, "Tibor Karaszi" > > >> > <tibor_please.no.email_kara...@hotmail.nomail.com> wrote: > > >> >> > 3) Executed the "sp_xp_cmdshell_proxy_account" sproc for this new acct > > > >> >> That is not howxp_cmdshellsecurity works. See my other reply. > > > >> >> There are basically three things involved here. > > > >> >> 1. Execution ofxp_cmdshellneed to be allowed at the instance level. > > > >> >> 2. The user need permissions in SQL Server to executexp_cmdshell. > > > >> >> 3. SQL Server then need to open a process (the process spawned byxp_cmdshell) in a certain > > >> >> Windows > > >> >> context (every process in Windows need to execute in a certain Windows user context). > > > >> >> 1: > > >> >> Use "Surface Area Configuration" or sp_configure to allow usage ofxp_cmdshell: > > > >> >> EXEC sp_configure 'xp_cmdshell', 1 > > >> >> RECONFIGURE > > > >> >> 2. You need to have a user in the *master* database which has execute permission > > >> >> onxp_cmdshell. > > >> >> If > > >> >> you are uncertain about the difference between login and user, you should read up in BOL on > > >> >> security: > > > >> >> USE master > > >> >> CREATE LOGIN x WITH PASSWORD = 'jlkw#.6(' > > > >> >> --Note, we are in the master database!!! > > >> >> CREATE USER x FROM LOGIN x > > >> >> GO > > > >> >> --Run as login x > > >> >> EXECUTE AS login = 'x' > > >> >> --Below fails, no execute permission onxp_cmdshell > > >> >> EXECxp_cmdshell'DIR C:\*.*' > > >> >> REVERT > > > >> >> --Note, we are in the master database!!! > > >> >> GRANT EXECUTE ONxp_cmdshellTO x > > > >> >> --Try again > > >> >> EXECUTE AS login = 'x' > > >> >> --Execution ofxp_cmdshellis allowed. > > >> >> --But I haven't configured the proxyaccount... > > >> >> EXECxp_cmdshell'DIR C:\*.*' > > >> >> REVERT > > > >> >> The execution ofxp_cmdshellgave below error message: > > > >> >> Msg 15153, Level 16, State 1, Procedurexp_cmdshell, Line 1 > > >> >> Thexp_cmdshellproxyaccountinformationcannotberetrievedor isinvalid. Verify that the > > >> >> '##xp_cmdshell_proxy_account##' credential exists and contains validinformation. > > > >> >> Above is expected. So for the final step: > > >> >> 3. > > >> >> Now we need to say what Windowsaccountshould be used when a non-sysadmin user is > > >> >> executingxp_cmdshell: > > >> >> EXEC sp_xp_cmdshell_proxy_account 'domain\WinUser','pwd' > > >> >> EXECUTE AS login = 'x' > > >> >> --Execution ofxp_cmdshellis allowed. > > >> >> --And executes successfully!!! > > >> >> EXECxp_cmdshell'DIR C:\*.*' > > >> >> REVERT > > > >> >> And below is a clean-up of what I've done: > > >> >> EXEC sp_xp_cmdshell_proxy_account null > > > >> >> DROP USER JohnDoe > > >> >> DROP LOGIN JohnDoe > > >> >> EXEC sp_configure 'xp_cmdshell', 0 > > >> >> RECONFIGURE > > > >> >> I've blogged about this > > >> >> inhttp://sqlblog.com/blogs/tibor_karaszi/archive/2007/08/23/xp-cmdshell.... The blog > > >> >> basically say what I said above. > > > >> >> -- > > >> >> Tibor Karaszi, SQL Server > > >> >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/... > > > >> >> <jose.mende...@gmail.com> wrote in message > > > >> >>news:1187850729.193816.181030@x35g2000prf.googlegroups.com... > > > >> >> > My issue. I'm running a web app which makes command calls to a DB > > >> >> > using SQL Authentication. One task the app will have to do is utilize > > >> >> > th "xp_cmdshell" to populate an Excel spreadsheet and move the file > > >> >> > from location A to B which is a linked server. Since the DB user > > >> >> > executing all my sql commands is not a default sysadmin. My app > > >> >> > encounters the the following error "...EXECUTE permission denied on > > >> >> > object 'xp_cmdshell', database 'mssqlsystemresource'..." I've > > >> >> > investigated on possible ways to resolve this issue however I've had > > >> >> > no luck on resolving this issue; below are the step's I taken so far. > > > >> >> > 1) I created a windows acct - "ADomain\AUserAcct" > > >> >> > 2) Created a Login acct for this user ("ADomain\AUserAcct") in SQL > > >> >> > 2005 and granted user access to this DB. User access for thisaccount > > >> >> > is "dbowner" > > >> >> > 3) Executed the "sp_xp_cmdshell_proxy_account" sproc for this new acct > > >> >> > a. EXEC sp_xp_cmdshell_proxy_account "ADomain\AUserAcct", > > >> >> > "pwd" > > >> >> > 4) Granted execute rights on sys.xp_cmdshellfor this user > > >> >> > a. GRANT EXECUTE ON sys.xp_cmdshellTO "ADomain\AUserAcct > > > >> >> > After these steps, I tried to execute my process again but ecountered > > >> >> > the same error msg. so > > > >> >> > 1) I modified the stored sproc to execute with the new user's acct. > > >> >> > but had the same error > > >> >> > a.Create proc myprocname with Execute As ADomain\AUserAcct > > > >> >> > Note: when I execute the proc. Before any of these steps thru sql 2005 > > >> >> > IDE with a sysadminaccountall works great. > > > >> >> > If anyone has any suggestions on how to resolve this or pointers on
[quoted text, click to view]
> Msg 15121, Level 16, State 10, Procedure xp_cmdshell, Line 1 > An error occurred during the execution of xp_cmdshell. A call to > 'CreateProcessAsUser' failed with error code: '1314'. My guess is that the service account for the SQL Server servce is lacking some windows priviliges. Search Books Online for "level token" and you will find topics that lists the windows priviliges that the service account needs. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi [quoted text, click to view] <audrey.nsh@gmail.com> wrote in message
news:1189543785.993908.123950@v29g2000prd.googlegroups.com... > On Sep 11, 4:30 pm, audrey....@gmail.com wrote: >> On Sep 11, 1:13 pm, "Tibor Karaszi" >> >> >> >> >> >> <tibor_please.no.email_kara...@hotmail.nomail.com> wrote: >> > I'm sorry, but I'm out of ideas... :-( >> >> > -- >> > Tibor Karaszi, SQL Server >> > MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/... >> >> > <audrey....@gmail.com> wrote in >> > messagenews:1189529952.930980.79700@r29g2000hsg.googlegroups.com... >> > > On Sep 11, 12:12 pm, "Tibor Karaszi" >> > > <tibor_please.no.email_kara...@hotmail.nomail.com> wrote: >> > >> > Ok, I followed all your instructions above and I am still getting the >> > >> > the following error "...EXECUTE permission denied on >> > >> > object 'xp_cmdshell', database 'mssqlsystemresource'..." >> >> > >> > This is what I have done: >> >> > >> > (1) Created a login (server role=public) >> > >> > (2) Created a user from login in the master database >> > >> > (3) Gave it Execute permission on xp_cmdshell >> > >> > (4) exec sp_xp_cmdshell_proxy_account 'Domain\WinAccount','pwd' >> >> > >> > and it doesn't work. >> >> > >> I would suspect that you aren't using the login you created in stepo 1 when you execute >> > >> xp_cmdshell. >> >> > >> > BTW, I am using xp_cmdshell off a trigger, >> > >> > do I always have to specify exec sp_xp_cmdshell_proxy_account 'Domain >> > >> > \WinAccount','pwd', exec as login = 'Login' whenever the trigger gets >> > >> > invoked? >> >> > >> No, that is a one-time configuration. And, of course, you need to specify a valif login name >> > >> and >> > >> password for your domain... >> >> > >> -- >> > >> Tibor Karaszi, SQL Server >> > >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/... >> >> > >> <audrey....@gmail.com> wrote in >> > >> messagenews:1189524563.343178.290270@w3g2000hsg.googlegroups.com... >> > >> > On Aug 23, 3:42 am, "Tibor Karaszi" >> > >> > <tibor_please.no.email_kara...@hotmail.nomail.com> wrote: >> > >> >> > 3) Executed the "sp_xp_cmdshell_proxy_account" sproc for this new acct >> >> > >> >> That is not howxp_cmdshellsecurity works. See my other reply. >> >> > >> >> There are basically three things involved here. >> >> > >> >> 1. Execution ofxp_cmdshellneed to be allowed at the instance level. >> >> > >> >> 2. The user need permissions in SQL Server to executexp_cmdshell. >> >> > >> >> 3. SQL Server then need to open a process (the process spawned byxp_cmdshell) in a >> > >> >> certain >> > >> >> Windows >> > >> >> context (every process in Windows need to execute in a certain Windows user context). >> >> > >> >> 1: >> > >> >> Use "Surface Area Configuration" or sp_configure to allow usage ofxp_cmdshell: >> >> > >> >> EXEC sp_configure 'xp_cmdshell', 1 >> > >> >> RECONFIGURE >> >> > >> >> 2. You need to have a user in the *master* database which has execute permission >> > >> >> onxp_cmdshell. >> > >> >> If >> > >> >> you are uncertain about the difference between login and user, you should read up in BOL >> > >> >> on >> > >> >> security: >> >> > >> >> USE master >> > >> >> CREATE LOGIN x WITH PASSWORD = 'jlkw#.6(' >> >> > >> >> --Note, we are in the master database!!! >> > >> >> CREATE USER x FROM LOGIN x >> > >> >> GO >> >> > >> >> --Run as login x >> > >> >> EXECUTE AS login = 'x' >> > >> >> --Below fails, no execute permission onxp_cmdshell >> > >> >> EXECxp_cmdshell'DIR C:\*.*' >> > >> >> REVERT >> >> > >> >> --Note, we are in the master database!!! >> > >> >> GRANT EXECUTE ONxp_cmdshellTO x >> >> > >> >> --Try again >> > >> >> EXECUTE AS login = 'x' >> > >> >> --Execution ofxp_cmdshellis allowed. >> > >> >> --But I haven't configured the proxyaccount... >> > >> >> EXECxp_cmdshell'DIR C:\*.*' >> > >> >> REVERT >> >> > >> >> The execution ofxp_cmdshellgave below error message: >> >> > >> >> Msg 15153, Level 16, State 1, Procedurexp_cmdshell, Line 1 >> > >> >> Thexp_cmdshellproxyaccountinformationcannotberetrievedor isinvalid. Verify that the >> > >> >> '##xp_cmdshell_proxy_account##' credential exists and contains validinformation. >> >> > >> >> Above is expected. So for the final step: >> > >> >> 3. >> > >> >> Now we need to say what Windowsaccountshould be used when a non-sysadmin user is >> > >> >> executingxp_cmdshell: >> > >> >> EXEC sp_xp_cmdshell_proxy_account 'domain\WinUser','pwd' >> > >> >> EXECUTE AS login = 'x' >> > >> >> --Execution ofxp_cmdshellis allowed. >> > >> >> --And executes successfully!!! >> > >> >> EXECxp_cmdshell'DIR C:\*.*' >> > >> >> REVERT >> >> > >> >> And below is a clean-up of what I've done: >> > >> >> EXEC sp_xp_cmdshell_proxy_account null >> >> > >> >> DROP USER JohnDoe >> > >> >> DROP LOGIN JohnDoe >> > >> >> EXEC sp_configure 'xp_cmdshell', 0 >> > >> >> RECONFIGURE >> >> > >> >> I've blogged about this >> > >> >> inhttp://sqlblog.com/blogs/tibor_karaszi/archive/2007/08/23/xp-cmdshell.... The blog >> > >> >> basically say what I said above. >> >> > >> >> -- >> > >> >> Tibor Karaszi, SQL Server >> > >> >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/... >> >> > >> >> <jose.mende...@gmail.com> wrote in message >> >> > >> >>news:1187850729.193816.181030@x35g2000prf.googlegroups.com... >> >> > >> >> > My issue. I'm running a web app which makes command calls to a DB >> > >> >> > using SQL Authentication. One task the app will have to do is utilize >> > >> >> > th "xp_cmdshell" to populate an Excel spreadsheet and move the file >> > >> >> > from location A to B which is a linked server. Since the DB user >> > >> >> > executing all my sql commands is not a default sysadmin. My app >> > >> >> > encounters the the following error "...EXECUTE permission denied on >> > >> >> > object 'xp_cmdshell', database 'mssqlsystemresource'..." I've >> > >> >> > investigated on possible ways to resolve this issue however I've had >> > >> >> > no luck on resolving this issue; below are the step's I taken so far. >> >> > >> >> > 1) I created a windows acct - "ADomain\AUserAcct" >> > >> >> > 2) Created a Login acct for this user ("ADomain\AUserAcct") in SQL >> > >> >> > 2005 and granted user access to this DB. User access for thisaccount >> > >> >> > is "dbowner"
On Sep 12, 2:00 am, "Tibor Karaszi"
[quoted text, click to view] <tibor_please.no.email_kara...@hotmail.nomail.com> wrote:
> > Msg 15121, Level 16, State 10, Procedure xp_cmdshell, Line 1 > > An error occurred during the execution of xp_cmdshell. A call to > > 'CreateProcessAsUser' failed with error code: '1314'. > > My guess is that the service account for the SQL Server servce is lacking some windows priviliges. > Search Books Online for "level token" and you will find topics that lists the windows priviliges > that the service account needs. > > -- > Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi > > <audrey....@gmail.com> wrote in message > > news:1189543785.993908.123950@v29g2000prd.googlegroups.com... > > > On Sep 11, 4:30 pm, audrey....@gmail.com wrote: > >> On Sep 11, 1:13 pm, "Tibor Karaszi" > > >> <tibor_please.no.email_kara...@hotmail.nomail.com> wrote: > >> > I'm sorry, but I'm out of ideas... :-( > > >> > -- > >> > Tibor Karaszi, SQL Server > >> > MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/... > > >> > <audrey....@gmail.com> wrote in > >> > messagenews:1189529952.930980.79700@r29g2000hsg.googlegroups.com... > >> > > On Sep 11, 12:12 pm, "Tibor Karaszi" > >> > > <tibor_please.no.email_kara...@hotmail.nomail.com> wrote: > >> > >> > Ok, I followed all your instructions above and I am still getting the > >> > >> > the following error "...EXECUTE permission denied on > >> > >> > object 'xp_cmdshell', database 'mssqlsystemresource'..." > > >> > >> > This is what I have done: > > >> > >> > (1) Created a login (server role=public) > >> > >> > (2) Created a user from login in the master database > >> > >> > (3) Gave it Execute permission on xp_cmdshell > >> > >> > (4) exec sp_xp_cmdshell_proxy_account 'Domain\WinAccount','pwd' > > >> > >> > and it doesn't work. > > >> > >> I would suspect that you aren't using the login you created in stepo 1 when you execute > >> > >> xp_cmdshell. > > >> > >> > BTW, I am using xp_cmdshell off a trigger, > >> > >> > do I always have to specify exec sp_xp_cmdshell_proxy_account 'Domain > >> > >> > \WinAccount','pwd', exec as login = 'Login' whenever the trigger gets > >> > >> > invoked? > > >> > >> No, that is a one-time configuration. And, of course, you need to specify a valif login name > >> > >> and > >> > >> password for your domain... > > >> > >> -- > >> > >> Tibor Karaszi, SQL Server > >> > >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/... > > >> > >> <audrey....@gmail.com> wrote in > >> > >> messagenews:1189524563.343178.290270@w3g2000hsg.googlegroups.com... > >> > >> > On Aug 23, 3:42 am, "Tibor Karaszi" > >> > >> > <tibor_please.no.email_kara...@hotmail.nomail.com> wrote: > >> > >> >> > 3) Executed the "sp_xp_cmdshell_proxy_account" sproc for this new acct > > >> > >> >> That is not howxp_cmdshellsecurity works. See my other reply. > > >> > >> >> There are basically three things involved here. > > >> > >> >> 1. Execution ofxp_cmdshellneed to be allowed at the instance level. > > >> > >> >> 2. The user need permissions in SQL Server to executexp_cmdshell. > > >> > >> >> 3. SQL Server then need to open a process (the process spawned byxp_cmdshell) in a > >> > >> >> certain > >> > >> >> Windows > >> > >> >> context (every process in Windows need to execute in a certain Windows user context). > > >> > >> >> 1: > >> > >> >> Use "Surface Area Configuration" or sp_configure to allow usage ofxp_cmdshell: > > >> > >> >> EXEC sp_configure 'xp_cmdshell', 1 > >> > >> >> RECONFIGURE > > >> > >> >> 2. You need to have a user in the *master* database which has execute permission > >> > >> >> onxp_cmdshell. > >> > >> >> If > >> > >> >> you are uncertain about the difference between login and user, you should read up in BOL > >> > >> >> on > >> > >> >> security: > > >> > >> >> USE master > >> > >> >> CREATE LOGIN x WITH PASSWORD = 'jlkw#.6(' > > >> > >> >> --Note, we are in the master database!!! > >> > >> >> CREATE USER x FROM LOGIN x > >> > >> >> GO > > >> > >> >> --Run as login x > >> > >> >> EXECUTE AS login = 'x' > >> > >> >> --Below fails, no execute permission onxp_cmdshell > >> > >> >> EXECxp_cmdshell'DIR C:\*.*' > >> > >> >> REVERT > > >> > >> >> --Note, we are in the master database!!! > >> > >> >> GRANT EXECUTE ONxp_cmdshellTO x > > >> > >> >> --Try again > >> > >> >> EXECUTE AS login = 'x' > >> > >> >> --Execution ofxp_cmdshellis allowed. > >> > >> >> --But I haven't configured the proxyaccount... > >> > >> >> EXECxp_cmdshell'DIR C:\*.*' > >> > >> >> REVERT > > >> > >> >> The execution ofxp_cmdshellgave below error message: > > >> > >> >> Msg 15153, Level 16, State 1, Procedurexp_cmdshell, Line 1 > >> > >> >> Thexp_cmdshellproxyaccountinformationcannotberetrievedor isinvalid. Verify that the > >> > >> >> '##xp_cmdshell_proxy_account##' credential exists and contains validinformation. > > >> > >> >> Above is expected. So for the final step: > >> > >> >> 3. > >> > >> >> Now we need to say what Windowsaccountshould be used when a non-sysadmin user is > >> > >> >> executingxp_cmdshell: > >> > >> >> EXEC sp_xp_cmdshell_proxy_account 'domain\WinUser','pwd' > >> > >> >> EXECUTE AS login = 'x' > >> > >> >> --Execution ofxp_cmdshellis allowed. > >> > >> >> --And executes successfully!!! > >> > >> >> EXECxp_cmdshell'DIR C:\*.*' > >> > >> >> REVERT > > >> > >> >> And below is a clean-up of what I've done: > >> > >> >> EXEC sp_xp_cmdshell_proxy_account null > > >> > >> >> DROP USER JohnDoe > >> > >> >> DROP LOGIN JohnDoe > >> > >> >> EXEC sp_configure 'xp_cmdshell', 0 > >> > >> >> RECONFIGURE > > >> > >> >> I've blogged about this > >> > >> >> inhttp://sqlblog.com/blogs/tibor_karaszi/archive/2007/08/23/xp-cmdshell.... The blog > >> > >> >> basically say what I said above. > > >> > >> >> -- > >> > >> >> Tibor Karaszi, SQL Server > >> > >> >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/... > > >> > >> >> <jose.mende...@gmail.com> wrote in message > > >> > >> >>news:1187850729.193816.181030@x35g2000prf.googlegroups.com... > > >> > >> >> > My issue. I'm running a web app which makes command calls to a DB > >> > >> >> > using SQL Authentication. One task the app will have to do is utilize > >> > >> >> > th "xp_cmdshell" to populate an Excel spreadsheet and move the file > >> > >> >> > from location A to B which is a linked server. Since the DB user > >> > >> >> > executing all my sql commands is not a default sysadmin. My app > >> > >> >> > encounters the the following error "...EXECUTE permission denied on > >> > >> >> > object 'xp_cmdshell', database 'mssqlsystemresource'..." I've > >> > >> >> > investigated on possible ways to resolve this issue however I've had > >> > >> >> > no luck on resolving this issue; below are the step's I taken so far. >
[quoted text, click to view] On Sep 12, 7:35 am, vijay <vjsek...@gmail.com> wrote: > On Sep 12, 2:00 am, "Tibor Karaszi" > > > > > > <tibor_please.no.email_kara...@hotmail.nomail.com> wrote: > > > Msg 15121, Level 16, State 10, Procedure xp_cmdshell, Line 1 > > > An error occurred during the execution of xp_cmdshell. A call to > > > 'CreateProcessAsUser' failed with error code: '1314'. > > > My guess is that the service account for the SQL Server servce is lacki= ng some windows priviliges. > > Search Books Online for "level token" and you will find topics that lis= ts the windows priviliges > > that the service account needs. > > > -- > > Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.a= sphttp://sqlblog.com/blogs/... > > > <audrey....@gmail.com> wrote in message > > >news:1189543785.993908.123950@v29g2000prd.googlegroups.com... > > > > On Sep 11, 4:30 pm, audrey....@gmail.com wrote: > > >> On Sep |