rajini88
asked on
SQL 2000 Reset Proxy Server - Specified user can not login
SQL 2000 with SP3 joined to local DOMAIN and reset the SQL Agent proxy account from local administrator to domain adminstrator password and domain, but SQL Server Manager prompt error message "Unable to set the SQL Agent proxy account because of the reason listed below : 'Error executing extended store procedure: Specified user can not login'.
I have change the MSSQLServer & SQLAgent user to domain admin login and have restart the server itself (not just the services). Now even using the local administrator or sqlagentcmdexec account, I received the same message as above.
Confirm all the password OK.
Please help, user can run xp_cmdshell command. Many thanks.
I have change the MSSQLServer & SQLAgent user to domain admin login and have restart the server itself (not just the services). Now even using the local administrator or sqlagentcmdexec account, I received the same message as above.
Confirm all the password OK.
Please help, user can run xp_cmdshell command. Many thanks.
ASKER
Dear EugeneZ,
Yes the domain admin has SA right in SQL server and event I t ried to add manually using SP
xp_sqlagent_proxy_account N'SET'
, N'<mydomain>'
, N'<ntuser>'
, N'<ntuser password>'
got message below
Error executing extended stored procedure: Specified user can not login.
Thank you.
Yes the domain admin has SA right in SQL server and event I t ried to add manually using SP
xp_sqlagent_proxy_account N'SET'
, N'<mydomain>'
, N'<ntuser>'
, N'<ntuser password>'
got message below
Error executing extended stored procedure: Specified user can not login.
Thank you.
did you add the <ntlogin> to sql server logins?
do not forget to run:
EXECUTE msdb.dbo.sp_set_sqlagent_p roperties @sysadmin_only = 0
EXECUTE msdb.dbo.sp_set_sqlagent_p
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Dear EugeneZ,
Done as above, still can't. I already changed the MSSQLServer & SQLAgent account to domain admin login and restart the server itself. Later login using domain admin and SQL Server & Agent running fine under the new ntlogin id (domain admin).
Previously the SQL Server are member of workgroup and moved to Domain last month and I facing this problem until now. Consult with MS Expert and they told me to give some access in Local Securiy Policy, done for all admin users but no luck.
EXEC master..xp_sqlagent_proxy_ account N'GET' but no results, blank.
Let checklist, maybe I redo the steps below and update you. Please advise for missing steps.
1) Change SQL Services to domain admin id
2) Restart the server and login to domain id
3) Verify SQL Services
4) Verify Local Security Policies - MS Expert
5) RUN xp_sqlagent_proxy_account N'SET'
, N'<mydomain>'
, N'<ntuser>'
, N'<ntuser password
Thanks for fast reply. Cheers.
Done as above, still can't. I already changed the MSSQLServer & SQLAgent account to domain admin login and restart the server itself. Later login using domain admin and SQL Server & Agent running fine under the new ntlogin id (domain admin).
Previously the SQL Server are member of workgroup and moved to Domain last month and I facing this problem until now. Consult with MS Expert and they told me to give some access in Local Securiy Policy, done for all admin users but no luck.
EXEC master..xp_sqlagent_proxy_
Let checklist, maybe I redo the steps below and update you. Please advise for missing steps.
1) Change SQL Services to domain admin id
2) Restart the server and login to domain id
3) Verify SQL Services
4) Verify Local Security Policies - MS Expert
5) RUN xp_sqlagent_proxy_account N'SET'
, N'<mydomain>'
, N'<ntuser>'
, N'<ntuser password
Thanks for fast reply. Cheers.
ASKER
Hi EugeneZ,
Now I managed to add the domain admin password in proxy server. Maybe required to restart the server after changes in Security Policies and Services.
Thanks. Cheers.
Now I managed to add the domain admin password in proxy server. Maybe required to restart the server after changes in Security Policies and Services.
Thanks. Cheers.
use xp_sqlagent_proxy_account (give the user execute rights for the SP)
it can be any NT account (do not forget to add it to sql logins no Sa rights)
create 2 procs with
xp_sqlagent_proxy_account inside -
1st add
2nd delete
in midle xp_cmdshell ''
in this case user just can run if 1st sp ran...
more:
https://www.experts-exchange.com/questions/20971258/master-xp-cmdshell-not-working.html?query=xp_sqlagent_proxy_account.&topics=42