How to check user accounts so that they do not expire in order to run xp_cmdshell

Question

Hi

I've been asked to make sure that the user account for running xp_cmdshell does not expire. Otherwise I find that I have to reboot the server(s) every 2 months or so.

Can anyone tell me where to check which user account is relevent to run this or where to check if the account expires.

The version of SQL is version 2000 and the servers are running Ms Windows Server 2003.

Much appreciated to anyone who can help.

Thanks
Matthew

Answer 1

Hi

I've been asked to make sure that the user account for running xp_cmdshell does not expire. Otherwise I find that I have to reboot the server(s) every 2 months or so.

I'm not sure how to check which user account to check or where to enable it - if anyone can tell me where to go in Window Server/SMS to find it.

The version of SQL is version 2000 and the servers are running Ms Windows Server 2003.

Much appreciated to anyone who can help.

Thanks
Matthew

Answer 2

Hi

I've been asked to make sure that the user account for running xp_cmdshell does not expire. Otherwise I find that I have to reboot the server(s) every 2 months or so.

Can anyone tell me where to check which user account is relevent to run this or where to check if the account expires.

The version of SQL is version 2000 and the servers are running Ms Windows Server 2003.

Much appreciated to anyone who can help.

Thanks
Matthew

Answer 3

Matt,

If you are running in an active directory environment and your service account/proxy account is a domain account then you'll need to speak with your windows/ad admin and ask him to set the accounts to not expire.

If you are running in a stand alone/workgroup environment where your accounts are local, you can change the account settings through the computer management snapin (admin tools -> computer management or start->run->compmgmt.msc).

-Sean

Answer 4

If you could upgrade your servers to Windows 2008 R2 and SQL Server 2008 R2, You can write a Powershell script to get the User account associated with the xp_cmdShell proxy account,  then use Active directory powershell cmdlets to check if the password for given Windows local account / domain account account was expired or not

Active Directory Cmdlets: http://technet.microsoft.com/en-us/library/dd378937(WS.10).aspx

SQL Powershell:http://msdn.microsoft.com/en-us/library/cc281954.aspx

Thanks

Sethu Srinivasan [MSFT]
SQL Server

http://blogs.msdn.com/sqlagent

Answer 5

Thanks but which user account should be checked?

Answer 6

The one that is running the SQL Server serivce. If you aren't sure which one that is you can check it by either running sql server configuration manager or opening up services and checking the one for the sql server service.

-Sean

Answer 7

The MSSQLSERVER service says Local System under the Log On As in the Services Window. Which user would that be?

Answer 8

where you are using xp_cmdshell. is this in a sql server job or stored procedure or ?

---

Please mark the post as answered to help others to choose the best. chandra sekhar pathivada | www.calsql.com (SQL Server Community Website)

Answer 9

Hi Chandra

It runs in a stored procedure. But even if I run this, it outputs NULL :-

EXEC xp_cmdshell 'Dir C:\*.*'

when it should output the files on the C:\ drive as you know.

This NULL output is telling me that the issue is with xp_cmdshell. Since my original post I think I've almost ruled out that it will be the account that has expired. The puzzle is why do I keep getting a NULL output and why do it happen about once every 2 or 3 month?

Any help is really appreciated.

Thanks

Matthew

Answer 10

Hi Mattew,

What account you used to run xp_cmdshell? Expiration is only for SQL Server login in SQL Server, we can verify that in SQL Server 2000. In enterpris manager, check the properties of the instance. If you are using other accounts, there won't be a expire problem.

As I mentioned in other thread, three workarounds:

1.  Stopped SQL Server Agent see if it has been resolved, if you could.

2. Changed SQL Server Agent service account to different account from SQL Server Service account.

3. If you have antivirus software installed in your machine, disabled the antivirus, the xp_cmdshell will start working fine. The xp_cmdshell remains working fine, if you restart the antivirus.

If it is not helpful, please let me know.

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 11

Hi Iric

I might have trouble changing the SQL Server Agent account as I'm afraid that I won't be able to put it back the way it was as I don't know the SQL Server Agent password :( - or would it not matter if I need to put it back the way it was. I have to be careful in case changing it causes any further issues - I would need to put it back the way it was very quickly if it did cause any more issues.

Cheers

Matthew