Extended Stored Procedure to access the command shell

• Shay-256753

  SSC Enthusiast

  Points: 179

  More actions

  January 29, 2007 at 1:17 pm

  #118643

  Hi.

  I'm trying to write an extended stored procedure to access the command shell.

  What I'm basically trying to do is allow non dba's to import data from a text file into a database table, (or export data from a database table to a text file).

  Giving non dba's EXECUTE permission to xp_cmdshell is not an option.

  The extended stored procedure should accept 1 string parameter, (a bcp command string or any dos command).

  I managed to do this part using C++

  I'm now trying to access the command shell using the "string()" function, but I'm having no success. I've also tried the shellexecute(), CreateProcess(), & spawnlp() (c++) functions, again, with no success.

  In all cases I can get the code to work when I compile it as a c++ executable, but not when I compile it as an Extended Stored Procedure.

  Is there something about accessing the command shell thru' SQL Server that I'm missing?

  I know that xp_cmdshell accesses the command shell thru' a proxy account.

  Is there a way of doing this?

  Here's my c++ code, (note this code returns the input parameter as a recordset for verification:

  // xp_example.cpp : Defines the entry point for the DLL application.

  //

  #using

  using namespace System;

  using namespace System:iagnostics;

  using namespace System::ComponentModel;

  #include "stdafx.h"

  #include "xp_example.h"

  #include

  #include

  #include

  #include

  #include

  #include

  #include

  #define MAXCOLNAME50

  #define MAXNAME25

  #define MAXTEXT255

  BOOL APIENTRY DllMain( HANDLE hModule,

  DWORD ul_reason_for_call,

  LPVOID lpReserved

  )

  {

  return TRUE;

  }

  RETCODE __declspec(dllexport) xp_example(SRV_PROC *srvproc)

  {

  DBCHAR spText[MAXTEXT];

  DBCHAR colname[MAXCOLNAME];

  // Check that there are the correct number of parameters.

  if ( srv_rpcparams(srvproc) != 1 )

  {

  // If there is not exactly one parameter, send an error to the client.

  _snprintf(spText, MAXTEXT, "ERROR. You need to pass one parameter.");

  srv_sendmsg( srvproc, SRV_MSG_INFO,0,(DBTINYINT)0,(DBTINYINT)0,NULL,0,0,spText,SRV_NULLTERM);

  // Signal the client that we are finished.

  srv_senddone(srvproc, SRV_DONE_ERROR, (DBUSMALLINT)0, (DBINT)0);

  return XP_ERROR;

  }

  // Define column 1

  _snprintf(colname, MAXCOLNAME, "ID");

  srv_describe(srvproc, 1, colname, SRV_NULLTERM, SRVINT4, sizeof(DBSMALLINT), SRVINT2, sizeof(DBSMALLINT), 0);

  // Define column 2

  _snprintf(colname, MAXCOLNAME, "Hello World");

  srv_describe(srvproc, 2, colname, SRV_NULLTERM, SRVCHAR, MAXTEXT, SRVCHAR, 0, NULL);

  BOOL bNull;

  BYTE bType;

  ULONG uLen;

  ULONG uMaxLen;

  // Get the info about the parameter.

  // Note pass NULL for the pbData parameter to get information rather than the parameter itself.

  //this will effectively, get the correct uLen (length).. NULL is used for 6th argument on purpose

  srv_paraminfo(srvproc, 1 , &bType, &uMaxLen, &uLen, NULL, &bNull);

  uLen +=1; // // Without this fix. it was doing weird character stuff

  // Create some memory to get the parameter in to.

  BYTE* Data = new BYTE[uLen];

  //SrvMessage(srvproc, (char *)uLen);

  //memset(Data , '\0', uLen);

  memset(Data , 0, uLen); //not sure which syntax is better

  // Get the parameter //notice 6th argument is now populated

  srv_paraminfo(srvproc, 1 , &bType, &uMaxLen, &uLen, Data, &bNull);

  // Convert the parameter into a char*

  char *shellCommand = (char *)Data;

  //int executed;

  //executed =

  system(shellCommand);

  //int pid=spawnlp(P_NOWAIT,"notepad","C:\\WINDOWS\\system32",NULL,NULL);

  int i = 1;

  // Generate "numRows" output rows.

  //for ( long i = 1; i <= numRows; i++ )
  //{
  // Set the first column to be the count.
  srv_setcoldata(srvproc, 1, &i);

  // Set the second column to be a text string
  int ColLength = _snprintf(spText, MAXTEXT, shellCommand, i);
  srv_setcoldata(srvproc, 2, spText);
  srv_setcollen(srvproc, 2, ColLength);

  // Send the row back to the client
  srv_sendrow(srvproc);
  //}

  // Tell the client we're done and return the number of rows returned.
  srv_senddone(srvproc, SRV_DONE_MORE | SRV_DONE_COUNT, (DBUSMALLINT)0, (DBINT)i);

  // Tidy up.
  delete []Data;

  return XP_NOERROR ;
  }

  [/code]

• dclark

  SSC Eights!

  Points: 843

  More actions

  January 29, 2007 at 2:25 pm

  #686279

  This doesn't really seem to be a T-SQL question.

   

   

   

   

• fasttrack

  SSCertifiable

  Points: 6071

  More actions

  January 30, 2007 at 2:35 am

  #686377

  To do it,

  You should use a DTS pakage (SQL 2000) or SSIS (SQL 2K5) to perform this operation without writing to much rows of code.

  Then, the package can be called from client application (.net) 

   

• Shay-256753

  SSC Enthusiast

  Points: 179

  More actions

  January 30, 2007 at 5:48 am

  #686410

  Thanks for your response to this.

  I know it's not strictly speaking a T-SQL post, but I'm trying to find a method whereby SQL Server can interact with the command shell and execute commands, namely bcp.

  I'll investigate calling a DTS package from the application although I have been advised that I might have problems releasing it to different client sites.

  Thanks again for taking the trouble to respond.

• fasttrack

  SSCertifiable

  Points: 6071

  More actions

  January 30, 2007 at 6:51 am

  #686431

  If you want to avoid using DTS or SSIS you can run SQL command inside .net application, using for instance:

  osql.exe or isql.exe utilities (SQL 2K, these are utilities to be executed in the windows prompt), then they may call sql command or run bcp utility (bcp is a SQL utility to import or export file in/from table).

  Hava a look in Book On Line (SQL server) or everywhere in internet there are lots of examples.

• Shay-256753

  SSC Enthusiast

  Points: 179

  More actions

  January 30, 2007 at 7:07 am

  #686433

  This sounds promising.

  I'll have a look and post here with my results.

  Thanks.

• Shay-256753

  SSC Enthusiast

  Points: 179

  More actions

  January 30, 2007 at 9:22 am

  #686495

  I think the problem with isql and osql, (and bcp for that matter), is that they would need to be installed on each machine using our application.

  This would not be practical I'm afraid, as our clients would have an average of 400 users.

  Oh well.

  The solution I'm investigating at the moment is to use the "xp_instance_regwrite" extended stored procedure to temporarily give all users EXECUTE permissions for the "xp_cmdshell" stored procedure.

  I could then use this xp_cmdshell procedure to execute my bcp commands.

  The problem with this, (sigh), is that our biggest client, (a bank), is reluctant to give all users access to such a powerful procedure as xp_instance_regwrite.

  The SQL would look something like this:

  EXECUTE master.dbo.xp_instance_regwrite

  N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'SysAdminOnly',

  N'REG_DWORD',

  0

  //some code.....

  EXEC master..xp_cmdshell 'bcp IN -c S U P

  //some more code.....

  EXECUTE master.dbo.xp_instance_regwrite

  N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'SysAdminOnly',

  N'REG_DWORD',

  1

  Again, thanks for taking an interest.

• Rick Townsend

  Ten Centuries

  Points: 1228

  More actions

  January 30, 2007 at 6:12 pm

  #686660

  What about just writing a wrapper stored proc that calls xp_cmdshell, using the EXECUTE AS clause to run that proc under an account that has appropriate permissions?

  Looking through BOL, I see they already have a page for this. Look for:

  Using EXECUTE AS to Create Custom Permission Sets

  -- User stored proc for calling xp_cmdshell

  create procedure usp_cmdshell (

  @params ...

  ) WITH EXECUTE AS sa

  AS

  exec xp_cmdshell @params ...

  GO

  ---

  Rick
  townsends.ca

• Jeff Moden

  SSC Guru

  Points: 1004329

  More actions

  January 30, 2007 at 7:24 pm

  #686672

  You say you specifically want to use BCP... try BULK INSERT instead... lot's simpler than trying to get around the command shell thing.

  Also, you can still use command shell as part of an SQL Server job if you can convince the DBA (through a serious justification and code review) to setup a proxy account for the running of an "immutable" stored procedure. 

  Love the DBA that does that for you... shows an extraordinary knowledge of security procedures and the cooperation and patience of a saint.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• fasttrack

  SSCertifiable

  Points: 6071

  More actions

  January 31, 2007 at 1:56 am

  #686750

  I do not if SQL server is 2K or 2K5; in the case of 2K it is not possible use WITH EXECUTE AS, only in 2k5.

  I do not the level of security for that table, you could give the right permission for a specific user and perform the SQL operation.

  Ask Database and System administrators for security.

   

   

• Shay-256753

  SSC Enthusiast

  Points: 179

  More actions

  January 31, 2007 at 4:07 am

  #686777

  My apologies to everyone.

  I now find that my original code, (see first post), does actually work.

  I had set up my local as a server as was doing all my testing on this.

  I had mistakenly set this up such that files were being written to my share on the company server instead of my local machine, (don't really understand how I managed to do this, it was my boss who actually pointed it out to me).

  Anyway, thanks to everyone for their input.

  Hope I haven't wasted too much of your time.