Unable to set SQL proxy for xp

Unable to set SQL proxy for xp_cmdshell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bry - 17 Mar 2006 07:08 GMT

I have checked the google group search and found a topic regarding
the setting Proxy account for SQL agent. The topic was logged last Dec.

2003. I have the same issue but this time, a changed with Domain might
be the cause. we moved from NT domain to AD. I am encountering 'No
mapping between account names and security IDS was done.' error, when i

tried to reset the proxy using the new domain account. I have tried to
delete the proxy using the DEL of xp_sqlagent_proxy_account but i
encountered the same error. I used GET and it returned the result
having the old
domain name and user name. I am wondering if the SQL is still looking
for the SID of the NT domain account. please someone attended this
topic asap. i need to resolve this ASAP.. =)

Reply to this Message

Dan Guzman - 18 Mar 2006 14:18 GMT

Do you get the account SID back when you SELECT SUSER_SID('<your ad
domain>/<your proxy account>')? Can you login locally on the SQL box using
the proxy account?

--
Hope this helps.

Dan Guzman
SQL Server MVP

>I have checked the google group search and found a topic regarding
> the setting Proxy account for SQL agent. The topic was logged last Dec.
[quoted text clipped - 10 lines]
> for the SID of the NT domain account. please someone attended this
> topic asap. i need to resolve this ASAP.. =)

Reply to this Message

Bry - 20 Mar 2006 03:32 GMT

When I select the SID of the proxy account, it returns NULL values.
yes, i can login to SQL box using the proxy account via terminal
services. i tried to use the same account as of the SQL services and it
returns the same error. but it is just for testing, i am using a domain
account and I add it in User group of the box. What could be the
possible problem of these boxes? our last resolution is to reinstall
the SQL but this is not our priority approach since our team dont want
any downtimes on their development environments. hope you could help
me. thanks.

> Do you get the account SID back when you SELECT SUSER_SID('<your ad
> domain>/<your proxy account>')? Can you login locally on the SQL box using
[quoted text clipped - 20 lines]
> > for the SID of the NT domain account. please someone attended this
> > topic asap. i need to resolve this ASAP.. =)

Reply to this Message

Dan Guzman - 20 Mar 2006 13:55 GMT

You should be able to get the SID using SUSER_SID for any domain account.
One thing you might check is to make sure the SQL Server service account has
the needed permissions. Perhaps the service account rights were lost as a
result of your AD migration.

The easiest way to do this is to use EM to change the SQL Server service
account to local system and then back to a domain account. You'll need to
restart SQL Server for the change to take affect. Then try to set the proxy
account again.

Signature

Hope this helps.

Dan Guzman
SQL Server MVP

> When I select the SID of the proxy account, it returns NULL values.
> yes, i can login to SQL box using the proxy account via terminal
[quoted text clipped - 31 lines]
>> > for the SID of the NT domain account. please someone attended this
>> > topic asap. i need to resolve this ASAP.. =)

Reply to this Message

Bry - 21 Mar 2006 05:08 GMT

I can get now the SID of the domain account. but the suggestion you
give still don't worked.

I changed the start up account of the MSSQLSERVICE and SQLSERVERAGENT
to local system then I reboot the box, after that i set the proxy and
still get the same error. I changed the startup account to our domain
account then reboot again the box. Still i got the same error. I tried
to delete the previous proxy, which is a domain account from our
previous domain, using the xp_sqlagent_proxy_account N'DEL' but i get
the same error.

are there any other approach?

btw, here are the permissions of the account i am using:

for the start up account: part of domain user, part local admin of the
box.
for the proxy account: part of the domain user, part of local users of
the box, also have explicit login access to SQL. I also included in
local policy to log on as batch job.

is that enough permission?

Bry

> You should be able to get the SID using SUSER_SID for any domain account.
> One thing you might check is to make sure the SQL Server service account has
[quoted text clipped - 47 lines]
> >> > for the SID of the NT domain account. please someone attended this
> >> > topic asap. i need to resolve this ASAP.. =)

Reply to this Message

Dan Guzman - 22 Mar 2006 03:47 GMT

As long as you used Enterprise Manager to specify the service account, the
required service accoint permissions will be set. The special service
account permissions need to run xp_cmdshell under a different security
context are listed in the SQL 2000 Books Online
(\instsql.chm::/in_overview_6k1f.htm).

You might try manually deleting the SQLSERVERAGENT proxy account registry
keys so that references to the old domain/account are removed. This should
allow you to reset the account. Note that you''ll need to grant yourself
permissions to see these subkeys keys and the usual 'at your own risk'
disclainer applies.

The keys below are for the default instance. Named instance keys begin with
'SQLAgent$'

HKEY_LOCAL_MACHINE\SECURITY\Policy\Secrets\SQLSERVERAGENT_SQLAgentProxyDomain
HKEY_LOCAL_MACHINE\SECURITY\Policy\Secrets\SQLSERVERAGENT_SQLAgentProxyPassword
HKEY_LOCAL_MACHINE\SECURITY\Policy\Secrets\SQLSERVERAGENT_SQLAgentProxyUsername

Signature

Hope this helps.

Dan Guzman
SQL Server MVP

>I can get now the SID of the domain account. but the suggestion you
> give still don't worked.
[quoted text clipped - 81 lines]
>> >> > for the SID of the NT domain account. please someone attended this
>> >> > topic asap. i need to resolve this ASAP.. =)

Reply to this Message

Bry - 22 Mar 2006 06:14 GMT

What is the permission needed to access those keys. Currently, i am
using a local account that is member of the Administrators group of the
box and I can't view those keys, i can only expand up to
HKEY_LOCAL_MACHINE\SECURITY\ only. Please advice.

> As long as you used Enterprise Manager to specify the service account, the
> required service accoint permissions will be set. The special service
[quoted text clipped - 106 lines]
> >> >> > for the SID of the NT domain account. please someone attended this
> >> >> > topic asap. i need to resolve this ASAP.. =)

Reply to this Message

Bry - 22 Mar 2006 06:29 GMT

I already access the keys. by the way, which of the values in there
should I delete? I see CupdTime, CurrVal, OldVar,OupdTime, SecDesc.

Reply to this Message

Dan Guzman - 22 Mar 2006 13:34 GMT

I mentioned permissions because there is no guarantee that you will have
access to the keys even as an administrator. Full control is needed in
order to delete.

Delete the entire keys that I listed and these subkeys will be deleted too.
The absence of the SQLSERVERAGENT_* and SQLAgent$* keys indicates no proxy
account is configured.

Signature

Hope this helps.

Dan Guzman
SQL Server MVP

>I already access the keys. by the way, which of the values in there
> should I delete? I see CupdTime, CurrVal, OldVar,OupdTime, SecDesc.

Reply to this Message

Bry - 29 Mar 2006 11:11 GMT

hi,

The issue has been resolved. I applied the resolution in one of the
servers. the account has been reset.
Thank you very much for the assistance and informationed shared.

bry

> I mentioned permissions because there is no guarantee that you will have
> access to the keys even as an administrator. Full control is needed in
[quoted text clipped - 12 lines]
> >I already access the keys. by the way, which of the values in there
> > should I delete? I see CupdTime, CurrVal, OldVar,OupdTime, SecDesc.

Reply to this Message