I have a stored proc that's going through the tables, getting data, and write the result into a csv file (mainly to provide up to date data for FTP customers).
When writing it to the local D: drive, everything runs fine. File writes as it should and everyone is happy.
As I'm setting up new FTP server, I decided to try and ditch 12672 batch scripts transferring files from the DB server (Win Server 2012 r2 running SQL Server 2016) to the FPT server (CentOS 7 + Samba) and have the query outputs written straight there.
SQL Server reports that query was executed successfully, but the output file disappears into a network black hole somewhere along the way.
Slowly losing my mind with this one.
Below list of things that I'm 99% sure are not causing the issue:
- Samba - even though security is still mildly paranoid it's temporarily not user restricted.
- Firewall - turning it off / on makes no difference whatsoever.
- SELinux - same as firewalld, enforcing, permissive, disabled makes no difference.
- File system permissions on Linux - 777'ed a test share, and su'ed as MSSQLUSER and successfully written a file in the same path I'm trying to get SQL server to write into, to check that.
Samba logs absolutely nothing, which leads me to believe SQL isn't even trying to touch that share. There's nothing at all on the Linux side that would suggest SQL tried to make a connection.
My current theory is that it's refusing to do it because it's not SQL Server's service account that mapped the share, so here goes:
- Is there a way to map that network drive for all users? I'm reluctant adding extra code to the stored proc to map the drive / delete it afterwards. End game is to have all the internal transfers working this way, meaning quite a number of SP's to alter, and different schedules would at some point inevitably clash. Not to mention the insane number of mappings per day. Countless Google searches point towards a simple answer "No.", but as they say, hope dies last.
- What potential consequences am I faced with if I were to change the service accounts (NT SERVICE\MSSQLSERVER) credentials? Unfortunately I haven't got a server I can sacrifice in the name of science and find out myself.
- Is there a simple solution I'm missing that'd allow me to do this? Am I trying to re-invent the wheel?
Any suggestions appreciated. I can do / change whatever I want on the Linux box, Windows side of things is a bit more complicated as my knowledge / experience with Microsoft systems is "mediocre" at best.