[Imported] SQL Export Object - Output File Name - UNC Path Fails

[sbeadle]

>>Message imported from previous forum - Category:ClearSCADA Software<<
User: rlao, originally posted: 2019-11-06 06:10:02 Id:615
Hi,

Can one of the forum admins please restore the contents of this thread from the old forums:

http://telemetry.schneider-electric.com/id3/forum/messageview.cfm?catid=67&threadid=3058

Forum title is same as my own forum title: SQL Export Object - Output File Name - UNC Path Fails

It looks like I am encountering the same issue as that post - getting the SQL Export Driver to write to a UNC path fails due to 'access denied' even though the user I'm running it as has all the required permissions to write to that network location. The same SQL export produces a file without issue if the output path is a local C: drive path.

[sbeadle · Accepted solution]

>>Responses imported from previous forum

Reply From User: andrewscott, posted: 2019-11-06 10:22:12
The SQL Export driver runs under the Windows user account specified in the "System Configuration \\ Printing" section of the server configuration tool. This user must have write access to the share and folder where the files are saved.
This user must also have the "Log on as a service" privilege as the ClearSCADA server runs as a Windows service.
This needs to be configured on each main/standby server in the system (but not permanent standbys).

Reply From User: adamwoodland, posted: 2019-11-06 21:42:03
Also, if no use is defined in printing it'll be whatever the ClearSCADA service is run under (so most likely SYSTEM). Windows block services running as SYSTEM from smb calls, so calls to \\\\server will always fail

Reply From User: sbeadle, posted: 2019-11-07 08:46:28
Post 3058 from the original forum:

Question :

We use a SQL Export Object that uploads the file to a UNC share (another server).

When we export to the local machines UNC path or just "C:\\file" everything goes well. When we export to another computers UNC path, it fails.

On this other computer, I turned off firewalls, I have no anti-virus running, I gave all sharing rights to Everyone, Anonymous Logon,... But nothing works. Although I can perfectly access the share (read/write) through windows explorer with the same UNC path.

I also played around with the "File Upload" user settings in the configuration by configuring a user, but it gives the same result.

On the other computer (with the share) we see that a user succesfully logs in and logs out as "ANONYMOUS LOGON, NT AUTHORITY", every time we trigger the export. So something is happening... Nevertheless, no file is written to the share and we always get this error :

"

Low 2/07/2015 10:21:34.949 Export SQL Query result request failed - Failed to open or write to SQL Query result file (Access is denied.). Check privileges and/or free disk space (2/07/2015 10:21:34.928) admin Action

Low 2/07/2015 10:21:34.927 Export SQL Query result requested admin Action

"

Solution :

The following configuration must be defined:

Enable the Guest Account - not necessary but you can try if it still doesn't work.
Open up Local Group Policy Manager. (secpol.msc)
Expand Computer Configuration
Expand Windows Settings
Expand Security Settings
Expand Local Policies
Select Security Options
Change "Network access: Let Everyone permissions to apply to anonymous users" from Disabled to Enabled.
Change "Network access: Restrict anonymous access to Named Pipes and Shares" from Enabled to Disabled.
Enter in the share name for "Network access: Shares that can be accessed anonymously" - \\\\SERVERNAME\\ShareName - Fill in only ShareName

Set up the Share with Everybody permissions and you're set!

User:
bevanweiss
Posted:
7/2/2015 5:55:01 PM

It would have been interesting to try something different...

On the share in question, if you had of edited the Permissions and added the particular ClearSCADA server machine account explicitly (with Full control permissions). I expect this would have worked.

I believe the SQL Export would run as the SYSTEM account (the same as the ClearSCADA DBServer), and I've actually just checked and this is the default.

This means that when trying to access a remote resource, it will only pass through the credentials of the PC on which it is currently running (i.e. it will attempt to authenticate using the computer account).

I would have expected the Windows Everyone group to include the computer accounts, but I'm not completely across the intricacies of the Windows Authentication system.

EDIT: I've just looked, and I'm wrong on the SYSTEM account remote resource aspect. It uses the null session, so this wouldn't work. NETWORK SERVICE is the one I was thinking of that would provide the computer account.
Probably what you really want, is to use a domain level service account to run the DriverSQLExport service, and then give this appropriate local and remote permissions to perform the task required. This is more secure than allowing Anonymous Read/Write access to a particular file share.
User:
AWoodland
Posted:
7/2/2015 6:09:37 PM

Just a word of caution, whilst it is great you have found and posted a solution for others to use be aware that if you're trying to operate in a secure environment and perhaps have security policies to meet, you'll probably fail. The basic securiy checks from 15 years ago for Windows NT checked these policy settings.

If you are looking for a more secure way, perhaps export to your local disk and then using something that does meet your security requirements to get the data off-server, such as dfs or an encrypted copying solution.

Might be nice if the SQL Export has user impersonation available so it doesn't run that driver as SYSTEM (as Bevan points out); maybe in the future an enhancement by the development team.

User:
andrewscott
Posted:
7/3/2015 5:10:44 AM

Actually the SQL Export driver does support user impersonation, however it doesn't have its own user account, instead it shares the printing user account, see the "System Configuration\\Printing" section of the server configuration tool.

NB. This Windows user must have the "Log on as a service" permission.

This printing user must be setup if you want to export to a UNC path.

User:
StijnA
Posted:
7/3/2015 9:29:54 AM

Yes indeed this solution is not very secure, but it was the only thing that worked. I like the idea of storing the file locally and use a secure service to transfer these files to the server.

We also tried setting a printing user in the "System Configuration\\Printing" as recommended, and that seems to be working fine as well! Which is great news 🙂 Thanks for the info!

Great! Thank you Bevan, Adam, Andrew!

Reply From User: rlao, posted: 2019-11-14 00:16:20
Hi all,

Just an update on this.

I've tried both methods: (1) Giving Everyone write permissions to the shared folder, (2) setting up the Printing user.

Method 1 worked fine, but as already mentioned here is not ideal from a security point of view.

However, I still cannot get Method 2 with the Printing user to work. I've added a Printing user (a domain account) to all primary and standby servers - even the Permanent Standby to be extra sure.

I'm certain that domain user account is correctly set up with 'Log on as a Service' privilege. The server configuration tool doesn't complain about the user not being granted the correct logon type when I enter that user under System Configuration/Printing and save changes.

I've also verified the permissions on the share are set up correctly. I can manually log on as that Printing user and create/edit a file in that share folder.

I checked the event logs of the computer that's hosting the share and can find the successful logon attempt from the ClearSCADA server when I trigger the SQL Export. However the user is coming across as NULL SID and I'm getting a blank dash for Account Name/Domain. I've attached the event log entry as a text file.

Does this mean it's not able to impersonate the Printing user?

Reply From User: adamwoodland, posted: 2019-11-14 05:28:54
Any errors in the DB logs?

Reply From User: andrewscott, posted: 2019-11-14 12:20:40
What does the SQL Export driver log file show?
If it successfully impersonates the printing user it will log the following:
`14-NOV-2019 12:07:00.998 [SQL Export1] Impersonated user \\`
If it fails an error message will be logged instead.