question

CharlieLor-4693 avatar image
0 Votes"
CharlieLor-4693 asked saldana-msft edited

Does anyone know what application name SMS_MP_FILE_DISPATCH_MANAGER is? It's throwing an error.

This is the audit log in SQL Server. It shows the following login failed. From the SQL Server side, this is all I can see. What I wonder what is SMS_MP_FILE_DISPATCH_MANAGER and how do I verify what account it's running at? How do I go about troubleshooting this error further?

Date 2/4/2021 2:34:01 AM
Log Audit Collection (LoginFailed)

Event Time 02:34:01.6182729
Server Instance Name DevDB
Action ID LOGIN FAILED
Class Type LOGIN
Sequence Number 1
Succeeded False
Permission Bit Mask 0x00000000000000000000000000000000
Column Permission False
Session ID 0
Server Principal ID 0
Database Principal ID 0
Target Server Principal ID 0
Target Database Principal ID 0
Object ID 0
Session Server Principal Name
Server Principal Name NT AUTHORITY\SYSTEM
Server Principal SID NULL
Database Principal Name
Target Server Principal Name
Target Server Principal SID NULL
Target Database Principal Name
Database Name
Schema Name
Object Name
Statement Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: Failed to open the explicitly specified database 'dbName'. [CLIENT: <local machine>]
Additional Information <action_info xmlns="http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data"><pooled_connection>0</pooled_connection><error>0x00004818</error><state>38</state><address>local machine</address></action_info>
File Name C:\Temp\SQLAuditFiles\LoginFailed_C9114B7D-590B-48B9-8978-D40508F15326_0_132568475672460000.sqlaudit
File Offset 10240
User Defined Event ID 0
User Defined Information
Sequence Group ID 0x1D52222EBA21AE4E884DD7A55652AC6C
Transaction ID 0
Client IP local machine
Application Name SMS_MP_FILE_DISPATCH_MANAGER
Affected Rows 0
Response Rows 0
Connection Id 1429ba8e-ca3c-4391-b7e8-0792f5a655c7
Duration Milliseconds 0
Data Sensitivity Information

Message

sql-server-generalwindows-servermem-cm-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

That is a "System Center Configuration Manager" component.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CharlieLor-4693 avatar image
0 Votes"
CharlieLor-4693 answered CharlieLor-4693 commented

Is there something on the SCCM side that I can verify? I've looked at the mpfdm.log and there is no error.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SCCM was named SMS many many years ago. Nowadays it is named SCCM. I suggest you ask in sn SCCM forum, since this is where you need to fix this. All we can say from the sQL Server side is that something is trying to login to, and the database it tries to use doesn't exist or it doesn't have permissions on that database.

1 Vote 1 ·

Hi @CharlieLor-4693,

Is there something on the SCCM side that I can verify?

As Tibor mentioned, I add mem-cm-general tag for this thread, people from SCCM team will give you a better help.

0 Votes 0 ·

Thank you for tagging the mem-cm-general. So instead of posting in two groups, I could jut tag another group. Thanks for letting me know...will definitely do that next time.

0 Votes 0 ·
AllenLiu-MSFT avatar image
0 Votes"
AllenLiu-MSFT answered

@CharlieLor-4693
Thank you for posting in Microsoft Q&A forum.
SMS_MP_FILE_DISPATCH_MANAGER is a component of SCCM, you may check the component status to see if there any error or warning like below:
66246-5.png


If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



5.png (197.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CharlieLor-4693 avatar image
0 Votes"
CharlieLor-4693 answered ErlandSommarskog converted comment to answer

Here's something I'm still confused about this error. Based on the audit, it said the client is <local machine> so that means it's coming from the same server that's hosting the DevDB SQL Server instance. However, SMS_MP_FILE_DISPATCH_MANAGER is not installed on the DevDB SQL Server instance. SCCM is installed in a separate VM server. So, how can <local machine> which does not have SMS_MP_FILE_DISPATCH_MANAGER installed tried to access itself with System and failed?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

That's indeed mysterious.

Although, there is a possibility. The application name is taken from the connection string, so there could be a process on the local machine pretends to be SMS_MP_FILE_DISPATCH_MANAGER. I am not going deny that this sounds far-fetched, but sometimes crazy things happen.

Here is an idea to track this down, although it is not entirely recommendable from a security perspective.

Create the database in question and grant NT AUTHORITY\SYSTEM access to it. Then write a logon trigger first checks if the user is NT AUTHORITY\SYSTEM, and if so captures host_process_id and writes it to a table. Furthermore, the logon trigger would run xp_cmdshell and run a command to list all processes, redirecting the output to a file. Once you have a host_process_id, you can look for it in the file.

I would definitely be hesitant to do this in production, but given the DB name, I assume that this is a Dev instance?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CharlieLor-4693 avatar image
0 Votes"
CharlieLor-4693 answered ErlandSommarskog commented

Okay, is there a way to create an audit to track if NT AUTHORITY\SYSTEM ever successfully logged into the SQL Server instance at certain time of day?

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

You can set up an audit that tracks successful logins alright. But you cannot specify a filter in the audit definition, so you are likely to get tons of data. You can filter when you query the audit file, but there may be a lot sift though.

Also, I check the docs for fn_get_audit_file, and I can't see that it returns something that can tie the login to a client process, so I don't think an audit is very helpful here.

0 Votes 0 ·
CharlieLor-4693 avatar image
0 Votes"
CharlieLor-4693 answered DanGuzman commented

Would ClientProcessID or PID be helpful in this case? Profiler does show these two columns.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yes, you could use the PID from task manager to correlate the actual EXE. This assumes, however, the process has not exited/restarted since the activity.

0 Votes 0 ·
CharlieLor-4693 avatar image
0 Votes"
CharlieLor-4693 answered ErlandSommarskog commented

Yes, the issue is that process exited before I can see what it is. Is there any tools that will let me capture all the processes during a period of time?

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

As Tom says, it is the ClientProcessID that maps to the PID in Windows.

Yes, there is a tool you can use: Process Monitor from sysinternals.

Be prepare to be flooded with data :-), but there are some good filter capabilities and once you have a ClientHostprocessID from Profiler, you can filter on PID in Process Monitor.

0 Votes 0 ·
CharlieLor-4693 avatar image
0 Votes"
CharlieLor-4693 answered TomPhillips-1744 commented

I've looked at it again and, sadly, it's not PID. What's showing in the Profiler is SPID. I believed this is different than PID in the task manager. I wish there is a way to detect what process is trying to connect to SQL Server at a particular time.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

"ClientProcessID" should be the process Id you see in task manager.

0 Votes 0 ·

I would expect an error in the Windows event log for this problem with more information from the application having the problem connecting.

0 Votes 0 ·
CharlieLor-4693 avatar image
0 Votes"
CharlieLor-4693 answered

Thank you for all the help. I've finally found a more concrete evidence of what causes this login failed error. Based on the ClientProcessID, it's the smsexec.exe process. I'm not sure exactly what it does but it seems to be related to System Center Configuration Manager. I'm not sure why it's running on a database server...will have to dig more into this process.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.