• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 826
  • Last Modified:

xp_cmdshell : execute an exe

I'm trying to run an exe that also looks at an ini file to read in some values using xp_cmdshell.
There's no user input to this exe.

The exe and ini are somewhere on the network and I logged into QA using the login/pwd the client gave us. I did this : exec master..xp_cmdshell '\\main\folder\..\..\..\mytest.exe". I get the error:

xp_cmdshell failed to execute because current security context is not sysadmin and proxy acount is not setup correctly. For more information, refer to Book Online, search for topic related to xp_sqlagent_proxy_account.

I did this :EXEC master.dbo.xp_sqlagent_proxy_account N'GET'
 and I got no rows back.

*** So I did this: logged to QA as admin. Copied the exe and ini to a c:\test folder on the server that has the sql server. I ran the exec master..xp_cmdshell 'c:\test\mytest.exe'.
     At first, I got the error: system cannot find path and another row that has "NULL". I ran it couple of other times and I only got the "null" row back. Checked the Windows Task and dont see the exe running.

What to do??
0
Camillia
Asked:
Camillia
  • 3
1 Solution
 
ZberteocCommented:
By default, only members of the sysadmin fixed server role can execute this extended stored procedure. You may, however, grant other users permission to execute this stored procedure.

When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running. When the user is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, xp_cmdshell will fail. This is true only for Microsoft® Windows NT® 4.0 and Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell is always executed under the security context of the Windows 9.x user who started SQL Server.

The account SQL server runs under has no permission for network paths. It will work only with local paths to the server that executes the sp. That means if you are executing from Quary Analize on your machine but with a connection to a SQL server on a different machine it will execute on that SQL server machine:

EXEC master..xp_cmdshell 'dir *.*'

will return whatever the C drive on the SQL server machine contains and not on your machine. If the SQL server is on your machine that you'll see your C drive content.

0
 
ZberteocCommented:
Actuelly:

From BOL:
<<
By default, only members of the sysadmin fixed server role can execute this extended stored procedure. You may, however, grant other users permission to execute this stored procedure.

When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running. When the user is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, xp_cmdshell will fail. This is true only for Microsoft® Windows NT® 4.0 and Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell is always executed under the security context of the Windows 9.x user who started SQL Server.
>>

My comment:
The account SQL server runs under has no permission for network paths. It will work only with local paths to the server that executes the sp. That means if you are executing from Quary Analize on your machine but with a connection to a SQL server on a different machine it will execute on that SQL server machine:

EXEC master..xp_cmdshell 'dir *.*'

will return whatever the C drive on the SQL server machine contains and not on your machine. If the SQL server is on your machine that you'll see your C drive content.

back to top
0
 
ZberteocCommented:
Sorry, this is the command for the C drive:

EXEC master..xp_cmdshell 'dir C:\*.*'


0
 
CamilliaAuthor Commented:
The login i was using..i doubled clicked on it and in the second tab..i made it "system administrator".

The path on the network explanation makes sense.

So, for the rest of it...i copied the exe and ini to the c drive of the sql server. First i got 2 rows: one saying it cant find the path and a NULL row. Second and third time I ran it...i only got the NULL row. Not sure if it ran or not...
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now