Question about stored procs, xp_cmdshell, and .NET applications

  • Hi all,

    Not sure if this is the correct forum to be asking this, but I've got a question about the way that xp_cmdshell works.

    I've read up on this, and based on the info I've seen, it appears as though a stored proc which is using xp_cmdshell to execute a statement, requires a higher level of permission than a normal one; it is not sufficient to simply grant execute access to the stored procedure, as the user/role executing the stored procedure also requires sysadmin access.

    Now, I'd like to avoid having to give sysadmin access to the role that my .NET application is using for access to that database server.

    Reading further, I've seen that the way to accomplish this is to create a proxy account using windows authentication, for the .NET application to access through, using the statement

    EXEC sp_xp_cmdshell_proxy_account , [PWD]

    The question which I have though - is the account that I need to create the proxy for, the account of the user who is running the application? If so, will I need to create a separate proxy account for each user that is running the application? If not, which account do I need to make the proxy for?

  • The proxy account is for xp_cmdshell to use. It should have the rights needed to do what the stored procedure needs done, but no more than that.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Actually, a followup question for this -

    Upon thinking about this a bit more, I realised that in order to get what I'm trying to do to work, I'll need to give xp_cmdshell access to the login that is being used by the application. This obviously poses a security risk, as anyone who has access to the code can thus be given access to the system that the database is residing on.

    My ultimate objective is to allow the .NET application to execute an SSIS package. The way which I'm doing this right now, is using xp_cmdshell to run the DTEXEC command, thus invoking the SSIS package.

    The followup question would be, is there any alternative to using xp_cmdshell for running the SSIS package? I see one possible alternative by having the stored procedure execute a job, which in turn executes the DTEXEC command - but this has the drawback that I still need to give access to running the sp_start_job command to the login, which could be just as dangerous.

  • kramaswamy (11/2/2011)


    Actually, a followup question for this -

    Upon thinking about this a bit more, I realised that in order to get what I'm trying to do to work, I'll need to give xp_cmdshell access to the login that is being used by the application. This obviously poses a security risk, as anyone who has access to the code can thus be given access to the system that the database is residing on.

    My ultimate objective is to allow the .NET application to execute an SSIS package. The way which I'm doing this right now, is using xp_cmdshell to run the DTEXEC command, thus invoking the SSIS package.

    The followup question would be, is there any alternative to using xp_cmdshell for running the SSIS package? I see one possible alternative by having the stored procedure execute a job, which in turn executes the DTEXEC command - but this has the drawback that I still need to give access to running the sp_start_job command to the login, which could be just as dangerous.

    Actually, you might not. The procedure that runs the job is the only one that needs the additinal access. You may be able to do this using the EXECUTE AS clause in the stored procedure declaration. You may need to play with it a bit, but that should do what you need.

  • I would probably handle the starting of the job as a Service Broker task. It separates the initiator from the executor very cleanly. It also means that the caller CANNOT directly perform the action.

    CEWII

  • Elliott Whitlow (11/2/2011)


    I would probably handle the starting of the job as a Service Broker task. It separates the initiator from the executor very cleanly. It also means that the caller CANNOT directly perform the action.

    CEWII

    +1

    Design away from xp_cmdshell. This is just the first of many issues you'll encounter. Decouple, as Elliott suggested. Use Service Broker within SQL Server or use MSMQ within Windows and go from there.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • And on top of that SB is really fairly easy to setup, the first time is a learning curve but after that it is pretty straightforward..

    CEWII

  • Well unfortunately Lynn, that solution doesn't work - the user still needs access to xp_cmdshell, even if I use EXECUTE AS.

    I've looked for other solutions, but it appears as though there isn't any alternative, except for perhaps the Service Broker approach.

    Guess I'll look in to that and see what I can do. Thanks all!

  • kramaswamy (11/4/2011)


    Well unfortunately Lynn, that solution doesn't work - the user still needs access to xp_cmdshell, even if I use EXECUTE AS.

    I've looked for other solutions, but it appears as though there isn't any alternative, except for perhaps the Service Broker approach.

    Guess I'll look in to that and see what I can do. Thanks all!

    The way I do this is to ensure that the database is owned by "SA" and that the proc is owned by "DBO". In the definition of the proc, use the WITH EXECUTE AS OWNER just before the AS. Then grant privs to the user/login to execute the proc.

    At this point, the user only needs PUBLIC privs to execute the proc which contains a call to xp_CmdShell and will NOT have the privs to execute xp_CmdShell directly.

    It's that simple. 😉

    --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)

  • I'm guessing that, short of logging in as SA, there's no way of being able to get a database to be owned by SA?

  • Any sysadmin (or probably db_owner) can change the ownership of a database

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hmm Jeff, now I'm getting the error:

    The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.

    Here's what I tried to do:

    set ANSI_NULLS ON

    set QUOTED_IDENTIFIER ON

    GO

    CREATE PROCEDURE [dbo].[usp_UpdateIEXValuesTest]

    (

    @StartDate DATETIME,

    @EndDate DATETIME

    )

    WITH EXECUTE AS OWNER

    AS

    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    DECLARE @StartDateString VARCHAR(10)

    DECLARE @EndDateString VARCHAR(10)

    SET @StartDateString = REPLACE(CONVERT(VARCHAR, @StartDate, 102), '.', '-')

    SET @EndDateString = REPLACE(CONVERT(VARCHAR, @EndDate, 102), '.', '-')

    DECLARE @DTExec VARCHAR(500)

    SET @DTExec = 'dtexec /FILE "\c0275con01\MSSQL\SSIS Files\Solutions\IEX\IEX\IEX.dtsx" /DECRYPT #### /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E'

    SET @DTExec = @DTExec + ' /SET "\Package.Variables[StartDate].Value;' + @StartDateString + '" /SET "\Package.Variables[EndDate].Value;' + @EndDateString + '"'

    EXEC xp_cmdshell @DTExec

    END

    The owner of the database is sa, the schema of the procedure is dbo, and I executed "ALTER AUTHORIZATION ON dbo.usp_UpdateIEXValuesTest TO dbo" in order to set the owner of the procedure to dbo.

  • I didn't look back to see but do you have the xp_CmdShell proxy set up?

    Also, is this a direct connection or are you "talking" through more than one connection?

    --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)

  • I don't think there is a proxy account set up. I was looking into that, but I'm not sure which login I need to use in the proxy for this to work.

    Not sure what you mean by a direct connection. What I'll have is a .NET application which, when a button is clicked, should execute a stored procedure that in turn executes an SSIS task using xp_cmdshell.

  • kramaswamy (11/7/2011)


    I don't think there is a proxy account set up. I was looking into that, but I'm not sure which login I need to use in the proxy for this to work.

    Not sure what you mean by a direct connection. What I'll have is a .NET application which, when a button is clicked, should execute a stored procedure that in turn executes an SSIS task using xp_cmdshell.

    Normally, you'd set up a separate Windows Login for the proxy account. I believe I have an example bit of code and I'll look for it.

    What I meant by "direct connection" is are you logging directly into the machine or are you doing something like the following steps?

    1. Log in to a Desktop

    2. Fire up Citrix (or something similar)

    3. Log into another box.

    4. Fire up SSMS on that other box so that it points to a server not on the current box. This creates what is known as a "double hop". xp_CmdShell won't work for a double hop because the credentials of your login are obfuscated by the second hop. This is sometimes referred to as the "Kerberos Double-Hop Problem".

    I haven't had to solve that myself because I've always had some good systems folks that know how to solve it. Normally, though, its not something that should necessarily be solved because it IS an addition form of security. It's incovenient for development but stored procedures executed by the server should work just fine.

    A "direct connection" (but remote) would use the following steps...

    1. Log in to a Desktop

    2. Fire up Citrix (or something similar)

    3. Log into another box. This is the SQL Server itself.

    4. Fire up SSMS on that server.

    --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)

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply