What am i missing!!! RRS feed

  • Question


    I'm trying to access some files on a file server in CLR code and I have to impersonate the windows login to get permission.  I am using the classic example of SqlContext and i can see that after calling the impersonate method it changes.  I always get false from the System.IO Direcotry and File functions to see if there are any files or even if the directory exists.  The permissions are correct on the file share and the batch submission is using the correct login.


    Directory.Exists ALWAYS returns false.


    Slimmed down code



    WindowsImpersonationContext OriginalContext = null;

    WindowsIdentity CallerIdentiy = SqlContext.WindowsIdentity;



    OriginalContext = CallerIdentiy.Impersonate();

    bDirectoryExists = Directory.Exists(@sSourceDir);


    { OriginalContext.Undo(); }



    Any ideas????
    Monday, June 25, 2007 5:01 PM

All replies

  • Make sure that the directory is existing relativly to the server (not the client which is calling the function / procedure). The destination should not be a mapped drive. DO you have more information about the type of directory / permissions / if its an UNC path etc. ?

    Jens K. Suessmeyer.

    Tuesday, June 26, 2007 11:32 AM

    Its a shared folder so the path is  \\Server\FileShare 


    The share and folder have the same permissions.  The NT user Corp\CMSBAT01 has full control on both.


    The batch schedular launches the procedure under [Corp\CMSBAT01].


    I can see it flop from the SQL Server service account [Corp\DBAdminRegulated] to [Corp\CMSBAT01]



    OriginalContext = CallerIdentiy.Impersonate();



    When i add the service account [Corp\DBAdminRegulated] to the file share it works.  When I remove it, it failes.


    This is a database server accessing a file server.



    Tuesday, June 26, 2007 5:54 PM

    When I get somebody to look into the event log under security it shows it is attempting to login anoymously.  =(


    Is there some type of security settings at a server level?



    Event Type:       Success Audit

    Event Source:    Security

    Event Category: Logon/Logoff

    Event ID:           540

    Date:                6/26/2007

    Time:                3:08:39 PM

    User:                NT AUTHORITY\ANONYMOUS LOGON

    Computer:         CORHOUCIS14G


    Successful Network Logon:

                User Name:      


                Logon ID:                      (0x0,0x7F5FAC06)

                Logon Type:      3

                Logon Process: NtLmSsp

                Authentication Package: NTLM

                Workstation Name:        CORHOUDBS14H

                Logon GUID:      -

                Caller User Name:          -

                Caller Domain:   -

                Caller Logon ID: -

                Caller Process ID: -

                Transited Services: -

                Source Network Address: 

                Source Port:      0

    Tuesday, June 26, 2007 10:05 PM

    It seems that when the code is executed from the database the impersonation works.  When there is a third server involved it does not.


    Batch Scheduler > Database > File Server ~ Failes


    Database > File Server ~ Works



    Friday, June 29, 2007 7:41 PM

    If somebody logs into the sql 2000 tools and runs the procedure with it set to Named-Pipes it works. 


    I belive this is a 2003 server but a older Active directory setup.  I'll look into it.


    also, because i've gotten a dozen emails, the paths are correct.

    Friday, July 6, 2007 2:33 PM
  • Which account does the Batch scheduler use for the connection ? Is it a Windows account or a SQL account ? Id the Batch Scheduler account allowed access to the path ?

    Jens K. Suessmeyer.

    Saturday, July 7, 2007 4:13 PM

    Yes its all NT security.  Everything works if i log into the database directly and point it to a directory.  If i log into my persona machine and execute the procedure on the database that then points to the file server it fails.


    I've opened a case with Microsoft so hopefully they can help me find the issue.

    Tuesday, July 10, 2007 8:17 PM
  • This is outside of my scope, but I believe you may be hitting what is known as the double-hop limitation of NT integrated authentication.  This would explain why it works when you log on to the database directly, but not when you try to run the command logged in from your personal machine.



    Thursday, July 19, 2007 4:33 PM