Finding information about which account xp_cmdshell is running as


If you ever needed to debug a permission related issue when using xp_cmdshell, you have probably realized that a crucial piece of information is about what particular account xp_cmdshell is executing under. If you are the administrator of the database, you already know the context used by xp_cmdshell, but otherwise you may not have that information. Here are some tips on how to find more.


First, if you have a command line tool that displays the current context, like whoami or a utility for dumping the security contex, you can just execute that under xp_cmdshell. That’s pretty easy. But what if there is no such tool available? One thing you can try in this case is to loop back into SQL (assuming the xp_cmdshell account has access to the database – it usually does) and just ask SQL for more information with queries like the following:


EXEC xp_cmdshell ‘osql -E -Q”select suser_sname()”‘


EXEC xp_cmdshell ‘osql -E -Q”select * from sys.login_token”‘


Don’t forget to pass in the server/instance name using the -S option, if you are not dealing with a default instance. These should give you plenty of information about the xp_cmdshell context and should help you figure out any access permission issue.


Given that I am on the topic of xp_cmdshell, here’s how the command can be enabled using T-SQL:


sp_configure ‘show advanced options’, 1
reconfigure


sp_configure ‘xp_cmdshell’, 1
reconfigure

Comments (5)

  1. yanaty says:

    Hi Laurentiu,

    I have that "access denied" error while trying to execute a simple file task from under SQL server (and, of course, same task works OK from cmd.exe). I’m logged in to SQL server as a member of sysadmin role, and connection to a local server is made under Windows authentication. However, EXEC xp_cmdshell ‘osql -E -Q"select suser_sname()"’ statement (recommended by you) returns NT AUTHORITYSYSTEM name instead of my username… I guess my question is: what does this mean and, still, is there any way to have my xp_cmdshell running?

    Thank you, –

    Tatyana

  2. This is because your SQL service runs as local system (you can verify that using services.msc). Administrators executing xp_cmdshell commands will always use the SQL service credentials.

    See the remarks section of the xp_cmdshell article on BOL:

    http://msdn.microsoft.com/en-us/library/ms175046(SQL.90).aspx

    If you get an access denied, it means the operation you are attempting requires more permissions than the local system account has – are you accessing a network resource?

  3. yanaty says:

    Thank you so much! My SQL server, indeed, ran as a local service, and, following your advice to examine its properties through services.msc, I was able to set it up for logging in as a domain account, and this was enough to start xp_cmdshell executing file tasks across the network. Thanl you, again, for such a practical and to the point advice!

    Tatyana

  4. mark.gao says:

    This is easy :

    exec xp_cmdshell 'whoami'

  5. Indeed: "First, if you have a command line tool that displays the current context, like whoami or a utility for dumping the security contex, you can just execute that under xp_cmdshell. That's pretty easy. But what if there is no such tool available?"

Skip to main content