3

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.

Adam
  • 41
  • 3

2 Answers2

1

In order to map a network drive under the SQL Server Service Account, you need to login to the server as the service account. Then use net use /persistent:yes to connect the share. This should make the share available to the SQL Server Service Account automatically each time it logs into that server.

Having said all that, instead of using a drive letter for the share, simply write directly to the Linux SMB share via the UNC name... so \\linux-machine\share\some-file-name.csv

Alternately, as Aaron Bertrand alluded to in a comment, write a utility to query the data from SQL Server and write directly to the samba share on Linux.

Hannah Vernon
  • 70,928
  • 22
  • 177
  • 323
0

Managed to get close to what I was trying to achieve. With the benefit of hindsight - @Aaron Bertrand's suggested approach is the most sane way of doing this - Export data to a local share on SQL Server box, mount it on Linux box, and set a cron job to pull the data.

I'm not sane however and was determined to do things my way (also, I still believe it's a better way to handle it in our case).

First of all, SQL Server (2016) does NOT want to write to a mapped network drive. It doesn't seem to matter which user mapped it, how "accessible" the share is on the Linux box (SELinux off, Firewalld off, directory permissions set so anyone could do whatever they desired). It looks like it doesn't even try to write there - there's absolutely nothing in the logs on CentOS box.

Giving SQL Server UNC path (\\1.2.3.4\share\file or \\server-name\share\file) made things better, at least I could see that it finally tries to do something, but gets shut down by samba. A lot of cigarettes and swearing later - SQL Server does not try to authenticate as the account it's using to get access to the samba share. It sends no credentials whatsoever and therefore gets defaulted to the guest account (Which, of course, I had disabled). Below probably does not belong here, as it's got nothing to do with databases and everything to do with samba/permissions, but someone may find it helpful one day so here goes.

Next step was to create a user with limited access, that samba could use as guest account instead of default "nobody":

adduser user_name -M -r -g samba_group -s /sbin/nologin

Change smb.conf to use it:

[global]
        map to guest = never
-That way guest login attempts outside specified shares still get shut down
        guest account = user_name

Set filesystem permissions:

chmod -R g+w /directory/path and chown -R owner:samba_group /directory/path

Update lookup tables in SQL Server to use network path instead of local directory, see it finally doing what you want, have a beer and celebrate.

Adam
  • 41
  • 3