XP_cmdshell error.

Question

XP_cmdshell error.

Viewing 3 posts - 31 through 32 (of 32 total)

You must be logged in to reply to this topic. Login to reply

Sopheap Suy SSCertifiable Points: 7893 More actions · Answer 1

Sunshine,

Have you thought that maybe it is best to leave out the proxy account and start making the job run as sa? The user will be notify if their job is run.

Sopheap

Bulent Gucuk SSCertifiable Points: 5498 More actions · Answer 2

After failing over to passive node I started seeing a nightly process failing with the same error message. It was working fine on the former active node of the sql cluster. Quick search on SSC and found this forum then immediately check both servers local policy for log on as batch. It revealed that former active node had the account in the log on as batch but the current node did not. I added the account and tested the process worked fine. So things are fine now but when setting up a proxy account on the cluster is this something we need to remember to add the account to all the nodes in the cluster? I did every configuration changes thru using the virtual name but this did not seem to be added to passive node back then? Did anyone have the same experience?..

Bulent

Edu Gomes Newbie Points: 7 More actions · Answer 3

This is the solution I found for SQL2005 and SQL2008

-- 1 - Configure server (connect as admin user)

-- a) Check the correct login to SQL Server Service (service.msc)

-- b1) Access GPEDIT.MSC

-- B2) Add the SQL Server Account Service in :

--Act as part of the operating system.

--Increase quotas.

--Replace process level token.

--Log on as a batch job.

-- Path: (COMPUTER CONFIGURATION\Windows Setting\Security Settings\Local Policies\User Rights Assigment)

-- c) Config settings in SQL Server (connect as admin user) :

exec master..sp_configure 'Ad Hoc Distributed Queries', 1

go

exec master..sp_configure 'xp_cmdshell', 1

go

reconfigure with override

go

-- 2 - Create Proxy Account ##xp_cmdshell_proxy_account##

-- The correct login configured in SQL Server Services (eg Net\SQL_Login)

EXECUTE msdb..sp_xp_cmdshell_proxy_account 'Net\SQL_Login, 'Password'

GO

-- 3 - Grant on xp_cmdshell to login "USER"

Exec master..sp_adduser ‘user’, ‘user’, public

go

use master

go

GRANT Exec on xp_cmdshell to ‘user’

go

Edu Gomes

DBA Admin