What account is xp_cmdshell using?3
May 25, 2021 by Kenneth Fisher
It works when I run it this way™ but not when I run it through xp_cmdshell!
It’s a permissions issue. When you run xp_cmdshell you are running under the ??? account.
Little bit more detail. The extended stored procedure xp_cmdshell creates a windows command shell. This shell has to be run under a windows/active directory account. Obviously you can’t get access to a windows resource (a directory for example) using a SQL Server login. The trick is to be able to tell them what account xp_cmdshell is using within that shell. There are two possibilities here.
I’m running xp_cmdshell as a sysadmin.
If you are running xp_cmdshell as a sysadmin then it’s running as the service account that is running SQL Server. The easiest way to tell what that is, is to look at the output for sys.dm_server_services.
SELECT * FROM sys.dm_server_services;
Look at the service_account column on the row for the SQL Server entry.
I’m running xp_cmdshell as anything other than sysadmin.
I’m making the assumption that xp_cmdshell is set up correctly and is working otherwise. In this case you are looking at the proxy account set up for xp_cmdshell. The easiest way to find it is to look at sys.credentials.
SELECT * FROM sys.credentials WHERE name = '##xp_cmdshell_proxy_account##';
This time you want to look in the credential_identity column. If there isn’t an entry at all then the proxy for xp_cmdshell isn’t set up and it shouldn’t be working for a non-sysadmin.
All of that said, if you are the one running xp_cmdshell the easiest way to see what account your shell is running under is to use whoami.
EXEC xp_cmdshell 'whoami';
And just look at the output column.
Category: Microsoft SQL Server, Security, Settings, SQLServerPedia Syndication | Tags: security, SQL Server, xp_cmdshell
3 thoughts on “What account is xp_cmdshell using?”
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
[…] Kenneth Fisher asks the important Stockdale questions (Who am I? What am I doing here?): […]
Heh… I was reading through this and kept saying to myself “Show them the proof, Ken”. Nothing like waiting until the last possible minute there! 😀
Also, it’s worth mentioning that if you’ve set xp_CmdShell up correctly, then ONLY people with sysadmin privs should be able to run it directly. You do need to setup the proxy account (with low standard user window privs) in order to be able to allow non-sysadmins to execute well written stored procedures that call xp_CmdShell without those folks being able to call it directly.
Anyway… nice short article on one aspect of xp_CmdShell. Thanks for taking the time to write it up and post it!
[…] instance where the backups were taken. Now, since I’m a sysadmin on that instance when I use xp_cmdshell it uses that service account. I don’t have to know the password or log in as the service account, xp_cmdshell will do it […]