none
Windows Service/SQL Stored Procedure Permission to access remote server file

    Question

  • Hi there, been working on this for a while and need some guidance

    I have written a windows service that runs a SQL stored procedure. This all runs fine until I need to access data from a remote server and then, it won't work. I can however run the stored procedure manually from my local machine and get access to the remote machine, when I use "exec <stored procedure>".

     think the problem is with permissions as it works when I do the manual thing because it inherits my local machine settings which are apparently ok, but for some reason when the "exec <SP> call comes from the windows service the permissions and connection are rejected.

    Does anyone know if this is correct? and if so how do I give my service or stored procedure permissions that will get access.

    Thanks in advance for your help...I seem to spend my life on permissions issues!

    Cam

    Wednesday, January 04, 2012 5:18 PM

All replies

  • Are you running windows service or SQL SP from remote server ? Does remote server have SQL Server installed?
    http://uk.linkedin.com/in/ramjaddu
    Wednesday, January 04, 2012 8:12 PM
  • It seems that you have a problem with Authentication tokens.  When you run a command from a remote machine against the SQL Server and the SQL Server needs to access a file that is remote to it, there is a permission hop that needs to take place.  This is where Kerberos comes in.  SQL Server does not have authorization by default to use your credentials to go access the file.

    So what you are seeing when you run it from your machine because as you said, it inherits local machine settings and is not hopping across another machine.

    So you can either hook up Kerberos (which is not an easy task and the gain may be small), or you are going to have to figure out another way to get to the file.


    Ben Miller - SQL Server MVP - @DBADuck
    Wednesday, January 04, 2012 11:45 PM
  • Thanks for your answers. Perhaps I can explain further and see if you still have the same conclusion.

    • There are 2 servers both on the same domain/LAN. ServerA is running MS Small Business Server 2003 and has a csv file that I want to access. ServerB is running MS Server 2008r2 and is running as a workstation. ServerB is running SQL Server 2008 (Express) and wants to upload the csv data (from ServerA).
    • I have written a stored procedure which uploads the csv from ServerA to ServerB without problems
    • I have written a Windows service, which runs on ServerB that executes the stored procedure, on ServerB (against the csv data on sServerA) 
      Scenario 1 

    Using SSMS on ServerB, I can execute the stored procedure and upload the csv data from ServerA, across the network without any problem.

    Scenario 2

    I start the windows service on ServerB, but get no update when the stored procedure is triggered on ServerB against the file on ServerA.

    Other points:

    • The service works fine on my "test environment" and accesses a remote drive for the csv data.
    • In the test environment the file to be accessed is shared (I need to check and confirm this in the production environment, however the upload works in Scenario 1 so I don't think this can be an issue!)

    I hope this makes sense, so you can confirm your solution or offer and alternative.

    Thanks in advance

    Cam

     

    Thursday, January 05, 2012 9:33 AM