0

The generic problem is as listed here SQL Maintenance Cleanup Task Working but Not Deleting but no solutions applicable. Environment: Windows Server 2012R2, AD DS (with policies of course), RDSH/TS Licensing, 1C-server. The primary problem is SQL Server generating insane amount of events per backup plan run, recording a pair of 18456+17052 errors per file to delete. Errors are as follows:

  • 17052: [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user 'DOMAIN\mssql_srv'
  • 18456: Reason: Could not find a login matching the name provided. [CLIENT: 192.168.x.x] (matches localhost)

Given that each pair of errors appears once per file to delete (there are about 6000 files already!), the algorithm looks like this:

  • First, backup plan task runs xp_delete_file, it enumerates all the files in target folder;
  • Second, each file is deleted by creating a separate connection to machine with service's credentials;
  • Each connection fails due to whatever restrictions default DC policy applies, generating the pair of events. Of course the file remains in place.

The workaround is of course assign file delete task to a local script run as system, for example, but the very reason of why does SQL server fail to delete a file remains unknown. Permissions have been checked and verified that both SQL Server Agent and SQL Server service accounts have full control to the folder.

Vesper
  • 18,599
  • 6
  • 39
  • 61
  • The link provided has a DIFFERENT problem. You instead has a wrong login name – sepupic Nov 13 '17 at 09:47
  • Go to SQL Server error log, find there 18456 error, you'll see there the wrong login name – sepupic Nov 13 '17 at 09:48
  • @sepupic "Wrong login name" - backup plan and its job starts from "mssql_agent" account, how then "mssql_srv" appears at all? – Vesper Nov 13 '17 at 09:48
  • So you checked error log and found there "login failed for user mssql_agent"? – sepupic Nov 13 '17 at 10:23
  • @sepupic No, I have found there a "mssql_srv". I'd like to know why. I managed to work around this problem by creating a login for mssql_srv, with no rights except access, still I'd like to know why SQL server is connecting to remote server with service account rather than the one that runs the job. – Vesper Nov 13 '17 at 11:15
  • 1
    If it's T-SQL step and job owner is member of sysadmin server role, the step is executed under service account – sepupic Nov 13 '17 at 11:59

1 Answers1

0

It turned out that this "login missing" is not a Windows login, but rather SQL "login" which was not present for the service account. So I needed to create a "DOMAIN\mssql_srv" login in SSMS, give it "public" access rights and voila, files started to get deleted properly. The reason is explained in comment:

If it's T-SQL step and job owner is member of sysadmin server role, the step is executed under service account.

Vesper
  • 18,599
  • 6
  • 39
  • 61