Post reply

sp_OAMethod to retrieve the file attributes from a folder

  • November 5, 2013 at 7:43 pm

    #1664305

    jsteinbeck-618119 (11/5/2013)

    I like your idea of building the stored procedure to have the privs...

    Can the trigger have the privs?

    I have some notes at work from a really good presentation I got to attend at a SQL Saturday this past weekend. Unfortunately, I don't have them with me here at home and haven't learned the technique well enough yet to rely on memory. I'll look tomorrow morning and see if I have the steps necessary to do this. The approach made complete sense and would allow you to create a procedure that can run xp_cmdshell but without the applicatioin user being able to call it directly.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • November 6, 2013 at 8:10 am

    #1664477

    Would love it if you shared them for sure... Thanks

  • November 6, 2013 at 8:24 am

    #1664484

    jsteinbeck-618119 (11/6/2013)

    Would love it if you shared them for sure... Thanks

    Oops. Sorry about that. Okay, It turns out I do not have complete notes on this, so I will try to go from memory.

    You create a procedure to perform the xp_cmdshell. When you create it, you need to do so with an account that has SA privs. Do this by using the syntax:

    CREATE PROCEDURE proc_name WITH EXECUTE AS OWNER

    AS

    ...

    You then grant permission to execute the procedure to the user being used to connect from the application. The procedure will execute in the security context of the procedure owner. When xp_cmdshell executes, it will do so with the security context of the proxy account if you have one defined or the SQL Server account if you don't.

    I feel like there's another step I'm missing, but I'm just not remembering it. Jeff, can you please fill in what I'm missing? I wouldn't want this implemented incorrectly because my memory sucks. Wish I would have taken better notes...

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • November 6, 2013 at 1:00 pm

    #1664552

    Ed Wagner (11/6/2013)

    jsteinbeck-618119 (11/6/2013)

    Would love it if you shared them for sure... Thanks

    Oops. Sorry about that. Okay, It turns out I do not have complete notes on this, so I will try to go from memory.

    You create a procedure to perform the xp_cmdshell. When you create it, you need to do so with an account that has SA privs. Do this by using the syntax:

    CREATE PROCEDURE proc_name WITH EXECUTE AS OWNER

    AS

    ...

    You then grant permission to execute the procedure to the user being used to connect from the application. The procedure will execute in the security context of the procedure owner. When xp_cmdshell executes, it will do so with the security context of the proxy account if you have one defined or the SQL Server account if you don't.

    I feel like there's another step I'm missing, but I'm just not remembering it. Jeff, can you please fill in what I'm missing? I wouldn't want this implemented incorrectly because my memory sucks. Wish I would have taken better notes...

    To summarize...

    1. You need to have a Windows account setup for the xp_CmdShell proxy. It can and should be just a "standard" user.

    2. You need to create the xp_CmdShell proxy.

    3. The database needs to be owned by "SA" even if the "SA" user is disabled (AND IT SHOULD BE!!)

    4. Create the proc with the WITH EXECUTE AS OWNER phrase.

    5. Make sure the proc isn't subject to DOS INJECTION.

    6. Grant users the ability to execute the proc. They'll be able to run it but they won't be able to run xp_CmdShell directly. DO NOT EVER LET NON-SA USERS EXECUTE XP_CMDSHELL DIRECTLY!!!

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

  • November 7, 2013 at 5:34 am

    #1664717

    Jeff Moden (11/6/2013)

    To summarize...

    1. You need to have a Windows account setup for the xp_CmdShell proxy. It can and should be just a "standard" user.

    2. You need to create the xp_CmdShell proxy.

    3. The database needs to be owned by "SA" even if the "SA" user is disabled (AND IT SHOULD BE!!)

    4. Create the proc with the WITH EXECUTE AS OWNER phrase.

    5. Make sure the proc isn't subject to DOS INJECTION.

    6. Grant users the ability to execute the proc. They'll be able to run it but they won't be able to run xp_CmdShell directly. DO NOT EVER LET NON-SA USERS EXECUTE XP_CMDSHELL DIRECTLY!!!

    Thanks for the summary, Jeff, for both the OP and for me. 😀

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • November 7, 2013 at 8:26 am

    #1664805

    Hi,

    I've given your recommendations to my DBA... Thanks so much...

    How do I query with the cmdShell to get the file Name, Modified Date, and Created Date...

    Thanks,

    John

  • November 7, 2013 at 11:05 am

    #1664872

    jsteinbeck-618119 (11/5/2013)

    Your code would need to co-exist in my trigger l posted earlier...

    I could use your to create and inline table function and pass in the location and file name... I assume I'd have to parse out the file name?

    Is that possible? Does your code retrieve the create date and modified date of the file...

    This is a production db, what security holes would we face?

    Thanks

    Hi there. Just to throw an idea out there that Jeff mentioned early on (and which has not been excluded as an option, as far as I can tell): why not use SQLCLR? You could get around all of this SP_OA*, xp_cmdshell, Stored Proc (can't JOIN to) vs Table-Valued Function (can JOIN to) mess. I am not aware of a free option for this particular function (as in blog post / code project / etc), but I am the author of a rather inexpensive SQLCLR library called SQL#[/url] that has a directory listing Table-Valued Function called FILE_GetDirectoryListing. (There is a Free version but the File System functions are only in the Full version). It returns CreateTime, LastAccessTime, LastWriteTime, and the other attributes (i.e. hidden / system / ReadOnly / etc.).

    The pros and cons would seem to be:

    Pros:

  • Can SELECT from the File System as if it were a table: you can JOIN to it, use a WHERE clause, etc.
  • Fast: the output streams out so it can handle 100's of thousands of entries if you were to do recursive over a large set of subfolders. (I just retrieved 277,607 entries, starting at C:\ on my laptop--not a fast disk, in 2 mins and 46 sec).
  • Can do full Regular Expression filtering on the names of the files and/or folders
  • NO string parsing / FileName and FolderName are separate fields (meaning: less error-prone)

    • Cons:

  • No proxy account (yet) so the log on account of the SQL Server service needs read-only access to the folder(s) in question (and I seem to recall that NTFS permissions allow for granting DIRECTORY LISTING by itself as opposed to also allowing for the contents of the files to be read)
  • not free

    • Take care,

    Solomon...

    SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
    Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
    Info sites — Collations     •     Module Signing     •     SQLCLR

  • November 7, 2013 at 11:53 am

    #1664884

    Hi,

    how much is it???

  • November 7, 2013 at 12:02 pm

    #1664888

    jsteinbeck-618119 (11/7/2013)

    Hi,

    how much is it???

    Hello. In the interest of not stating anything here that gets outdated, that information, and more, can be found at:

    http://www.sqlsharp.com/full/[/url]

    Take care,

    Solomon...

    SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
    Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
    Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
    Info sites — Collations     •     Module Signing     •     SQLCLR

    • Viewing 9 posts - 16 through 23 (of 23 total)

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