January 13, 2012 at 5:30 am
Hi, im new here but not totally new to SQL server, however ive come across a problem when reading files stored on a different machine to the sql server. I hope that you can help with.
I have a 2 server environment, a DB server that hosts SQL server 2008 r2 and an application/ FTP server. both servers are located in a datacentre, use windows 2008 x64 in a workgroup configuration.
Files are delivered automatically to the FTP server, theres a windows service on that machine that parses these files using sql sprocs and should insert the data into a table. All of this works when the FTP, windows service and sql server are on the same machine, but now that I have seperated SQL server from the windows server, im getting file not found issues as sql server sees the file path as its local machine. Ive tried mapping a network drive, sharing the file location, but no luck.
From searching the web, it seems like delegation is an option to allow me to access these files, but i dont fully understand it and it seems like its meant to be used in a domain environment rather than a work group. I also think its only 1 part of a 2 part problem
Can anybody suggest what are my best options to get around these permissions to allow SQL server access to these text files?
Thanks in advance
January 13, 2012 at 5:42 am
Use a UNC path to access files across the network. Make sure the account that runs SQL Server has permissions on the files.
John
January 13, 2012 at 5:52 am
james.berryman (1/13/2012)
Hi, im new here but not totally new to SQL server, however ive come across a problem when reading files stored on a different machine to the sql server. I hope that you can help with.I have a 2 server environment, a DB server that hosts SQL server 2008 r2 and an application/ FTP server. both servers are located in a datacentre, use windows 2008 x64 in a workgroup configuration.
Files are delivered automatically to the FTP server, theres a windows service on that machine that parses these files using sql sprocs and should insert the data into a table. All of this works when the FTP, windows service and sql server are on the same machine, but now that I have seperated SQL server from the windows server, im getting file not found issues as sql server sees the file path as its local machine. Ive tried mapping a network drive, sharing the file location, but no luck.
From searching the web, it seems like delegation is an option to allow me to access these files, but i dont fully understand it and it seems like its meant to be used in a domain environment rather than a work group. I also think its only 1 part of a 2 part problem
Can anybody suggest what are my best options to get around these permissions to allow SQL server access to these text files?
Thanks in advance
Which account you are using to run that windows service ? The windows service is conneting to SQL Server using Windows Authentication or SQL Server authetication ?
1. If it is connecting with windows authentication then you need to provide the shared access right on that particular directory where the files are stored to this windows login.
2. If it is connecting with SQL Server authentication then you need might need to use to DOS command "net use " to share the directory.
I have never worked in workgroup environment, always worked in doamin environment so may be my solutions are not appropriate for workgroup, but you may give it a try.
& Yes as John said use the UNC path only after you have shared the directory.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply