stored procedure unable to find file on another server

  • helo,

    i have created an application that needs to find a file on the network and ingest data.

    I have been testing by just having test files on the local server, and when my stored procedure is called it processes without any problems.

    however, the live files will live on a different server. I can create a mapped drive to the firectory and I can UNC to it as well. But my stored procedure cannot find the file.

    In order to be able to process a file on another server, is their specific SQL or Network permissions that need to be set?

  • What OS user is running your SQL Server instance?

    What SQL user is running your stored procedure?

    How are you accessing the file, when it is on the same server?

    What file-transfer protocols are required to access the file on the remote server (SMB, FTP, SFTP)?

    Is the other server on the same domain (or a domain with an adequate trust relationship)?

    Assuming SMB is your transfer protocol, essentially, the other server has to accept fileshare connections from the user that is running the SQL Server processes.

    If the two servers are on the same domain and the SQL Server instance is running under an account which supplies a network credential (i.e. NETWORK SERVICE or a domain account; note that machine-local accounts and SYSTEM do not), then you may be able to permit that account to access the remote file share.

    You will also have to specify UNC paths, rather than a mapped drive letter, as the drive letter is transient, while the UNC path should be fixed.

  • Jim has given you good advice.

    Note that when you run something, it isn't your account that runs it. It is the service account for SQL Server, not the user connected. Therefore the path needs to be valid for the service account, along with the proper security.

Viewing 3 posts - 1 through 2 (of 2 total)

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