Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

stored procedure unable to find file on another server Expand / Collapse
Author
Message
Posted Monday, June 10, 2013 2:44 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:22 PM
Points: 55, Visits: 145
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?
Post #1461777
Posted Monday, July 8, 2013 8:59 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 10:34 AM
Points: 64, Visits: 785
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.
Post #1471241
Posted Monday, July 8, 2013 10:21 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 12:34 PM
Points: 31,181, Visits: 15,626
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1471268
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse