August 13, 2004 at 10:05 am
Hi to all:
I'm having a problem setting the proxy account with xp_sqlagent_proxy_account procedure.
I need to allow non sysadmin users the ability to run xp_cmdshell.
But when I try to set the proxy account it shows the following error
Error executing extended stored procedure: Specified user can not login
The user I specify is a domain user, member of domain admins, and also member of administrator groups in the local machine where SQL runs.
Anyone know what can be the problem? I need to get this working because if I don't I will have to give sysadmin rights to a user, something that I don't like.
August 13, 2004 at 12:10 pm
That user have to be able to access SQL Server too.
August 13, 2004 at 12:15 pm
right, but still the same error.
August 13, 2004 at 12:18 pm
Interesting. Do you see the login in the result of sp_helplogins?
August 13, 2004 at 12:22 pm
yes of course.
Even if I set up the same Windows account that I use to start SQL Server, it will still give me the same error.
I'm not sure that the windows account that I want to use must be granted SQL access.
I execute
EXEC master.dbo.xp_sqlagent_proxy_account N'SET',
N'MyDomain',
N'My Win Account',
N'password'
And i get the error listed before.
August 13, 2004 at 12:25 pm
"I'm not sure that the windows account that I want to use must be granted SQL access."
Yes. You need to grant it to access SQL Server.
August 13, 2004 at 12:32 pm
Mmm, I disagree.
The SQL Agent proxy account is a windows account in wich non sysadmin SQL users will use to access OS and network resources, by running xp_cmdshell or jobs.
All sysadmin users, use the account that start the SQL agent service, the non sysadmin will use that account. But I don't think that it need acces to SQL.
Anyway, the account has access to SQL but still isn't working.
Can't figure out why.
August 16, 2004 at 7:04 am
It the proxy account in the same domain as the server itself?
August 16, 2004 at 1:37 pm
Make sure that you gave the non-sysadmin account access to execute xp_cmdshell. Here are the steps that I use:
use master
go
xp_sqlagent_proxy_account N'SET', N'Yourdomain', N'nonsysadmin user', N'nonsysadmin user's password'
go
-- retrieve the proxy account to check that it's correct.
xp_sqlagent_proxy_account N'GET'
go
-- grant database access in master
sp_grantdbaccess 'LimitedUser'
go
grant exec on xp_cmdshell to LimitedUser
go
Oh yeah, make sure that you set the SQLAgent properties to the correct mode (that is to allow non-sysadmins to execute xp_cmdshell. this option was changed after SP3 was released).
EXECUTE msdb..sp_set_sqlagent_properties @sysadmin_only = 0
Hope that this helps ...
September 13, 2004 at 8:14 am
Here is a link to a related article that helps.
http://www.mcse.ms/message227280.html
In essence, if SQL server was not installed with the SERVICE accounts set to NT account initially, it seems the needed local rights are not set for the SERVICE account. You must manually set the rights.
Articles referenced in this message above:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_overview_6k1f.asp
Ensure rights:
Act as part of the Operating System
Increase Quotas
Log on as a batch Job
Log on as a Service
Replace a process level token
September 13, 2004 at 8:45 am
Still having the same problem. I have tried the last link you sent, but i have everything right.
The windows account I use to start sql services is member of the local admin group. Also, is member of the Domain Admins group, so I assume all the needed permission all of you have mention are ok, right?
The account I try to used as the proxy account is the same account I used to start SQL, so all the permissions needed for this account are setted, right?
The error I still getting is
Error executing extended stored procedure: Specified user can not login.
The server in wich is installed is a domain controller. Can this give me problems?
If someone has any idea, please help!.
August 3, 2005 at 6:43 pm
I am having a similar issue on one of my servers. Although I've been able to set up a working proxy account on two servers in my primary domain, I also need to set one up on a matching server in my Development (child) domain and that is where I am having a problem. I have not yet been able to find any account in either domain that SQL will accept as a valid proxy, including the service account that I use to start SQL on that server. I am wondering if this is a cross-domain issue, as the service account used to start SQL on this server is a service account from the parent domain.
Anyway, has anyone run into something like this in their Enterprise?
Also, what would any of you recommend as best practices in the choice of account used for the proxy? I would prefer that it not be a highly privileged account for obvious reasons, but most of those domain accounts that are used to start services such as SQL are at least local admins on the servers they are used with.
Thanks in advance for any advice!
Marck
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply