question

SalamElias-0832 avatar image
0 Votes"
SalamElias-0832 asked $$ANON_USER$$ published

Strange Login and AppDomain xx created

On a sql 2k16, when I go through error logs, I find a lot of messages las follows

Server Date Process Count Text
SQL2K12 2021-03-15 13:30:44.640 spid17s 1 AppDomain 39 (MDS.mds_schema_user[runtime].38) unloaded.
SQL2K12 2021-03-15 13:00:15.550 spid62 1 AppDomain 40 (master.sys[runtime].39) created.
SQL2K12 2021-03-15 13:00:15.550 spid62 1 AppDomain 40 (master.sys[runtime].39) created.
SQL2K12 2021-03-15 10:00:43.090 spid45s 1 AppDomain 39 (MDS.mds_schema_user[runtime].38) created.


Not sure what this appDomain creating and unloading?
Thanks

sql-server-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 completely normal and expected when you are running MDS.

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

In addition to Tom's post: these AppDomain relates the CLR, running .NET inside SQL Server, which apparently Master Data Services uses.

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.

CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered

Hi @SalamElias-0832,

Represents an application domain, which is an isolated environment where applications execute. This class cannot be inherited. Application domains provide a unit of isolation for the common language runtime. They are created and run inside a process. Application domains are usually created by a runtime host, which is an application responsible for loading the runtime into a process and executing user code within an application domain. The runtime host creates a process and a default application domain, and runs managed code inside it. Please refer to AppDomain Class and Using Application Domains to get more information.

Best regards,
Carrin


If the answer 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 |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.

SalamElias-0832 avatar image
0 Votes"
SalamElias-0832 answered SalamElias-0832 commented

So many thanks. I would like to accept all your answers but it seems I can't. In this case I will ask the following question and I think is related to this probelm.
I receive an alert indicating
SQL2K12 2021-03-16 10:01:12.400 SALAM\SQL2K12$ 1 ::1 Reason: Could not find a login matching the name provided

I am not aware of any application I have that uses such login in my domain salam. I tried to do a trace on the sql server, entering "winNtLogin = SALAM\SQL2K12$", nothing is logged in the profiler in spite of the fact that I still get the alert every 60 minutes

· 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.

@SalamElias-0832 , although you can't mark more than one answer, you can still upvote the others that helped.

Did you capture the audit login failed event in your trace?


0 Votes 0 ·

SQL2K12 2021-03-16 10:01:12.400 SALAM\SQL2K12$ 1 ::1 Reason: Could not find a login matching the name provided

I am not aware of any application I have that uses such login in my domain salam.

SQL2K12$ is a machine account. That is, this is something that runs on the machine SQL2K12 on a local account, like Local Service or for that matter NT Service\MSSQLSERVER.

But this really a separate question, and is unrelated your AppDomain.

0 Votes 0 ·

Erland, I use powershell script to alert me, I get this alert when not excluding "MDS.mds_schema_user[runtime", when it is not excluded I get those messages with the login in question

0 Votes 0 ·
SalamElias-0832 avatar image
0 Votes"
SalamElias-0832 answered

No, I rerun right now with Audit fail, I thought Audit login in and out will show something, sorry.
I will let you know, I will wait for my script to run

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.

SalamElias-0832 avatar image
0 Votes"
SalamElias-0832 answered SalamElias-0832 commented

After 2 hours, I see nothing in the trace file

78208-sqltracelogin.jpg



sqltracelogin.jpg (125.7 KiB)
· 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.

Try removing the NTUserName filter.

0 Votes 0 ·

This will log everything and log will be huge

0 Votes 0 ·
SalamElias-0832 avatar image
0 Votes"
SalamElias-0832 answered

After 4 hours of waiting, trace stopped and I have the following

78209-sqltracelogin-002.jpg



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.

SalamElias-0832 avatar image
0 Votes"
SalamElias-0832 answered ErlandSommarskog commented

Hello All, sorry for being late but I needed a lot of time to find the right parameters for the SQL profiler, finally I was able to nail down who was trying to use this login. In fact it is a job that runs against a Biztalk Database. Here are couple of lines of the trace.

EventClass TextData ApplicationName NTUserName LoginName DatabaseID DatabaseName EventSequence EventSubClass HostName LoginSid NTDomainName ServerName SessionLoginName TransactionID XactSequence Severity State GroupID
162 Changed database context to 'BizTalkMsgBoxDb'. SQLAgent - TSQL JobStep (Job 0x0235365006525D458358CC7A59C84CA8 : Step 1) SQLSERVERAGENT NT SERVICE\SQLSERVERAGENT 44 BizTalkMsgBoxDb 111095 NULL SQL2K12 0x010600000000000550000000DCA88F14B79FD47A992A3D8943F829A726066357 NT SERVICE SQL2K12 SALAM\SQL2K12$ NULL 0 10 2 1
162 Changed language setting to us_english. SQLAgent - TSQL JobStep (Job 0x0235365006525D458358CC7A59C84CA8 : Step 1) SQLSERVERAGENT NT SERVICE\SQLSERVERAGENT 44 BizTalkMsgBoxDb 111096 NULL SQL2K12 0x010600000000000550000000DCA88F14B79FD47A992A3D8943F829A726066357 NT SERVICE SQL2K12 SALAM\SQL2K12$ NULL 0 10 1 1

I tried to search msdb for the jbo step but cant find any trace for the job step mentioned "SQLAgent - TSQL JobStep (Job 0x0235365006525D458358CC7A59C84CA8 : Step 1)

When I run this sql against steps table, no step with 0x0235365006525D458358CC7A59C84CA8 id as you can notice

 Select step_uid, database_name, step_id   from dbo.sysjobsteps 
 WHERE database_name = 'BizTalkMsgBoxDb'

3BF7FD9C-684E-40C9-AC2A-DBDDD4D7F4A3 BizTalkMsgBoxDb 1
1C4F99EE-B6B1-4890-B2F5-96209491937E BizTalkMsgBoxDb 1
FAC2B44A-60B7-450B-B985-216FD285C0E6 BizTalkMsgBoxDb 1
9D26AF3A-ABAD-4089-9503-CAF41A2383C5 BizTalkMsgBoxDb 1
36A0EE9A-7C1F-4A44-A06F-324A6F01C4FE BizTalkMsgBoxDb 1
CFACD8F2-14AD-4E2D-94C4-4A89EFBB0799 BizTalkMsgBoxDb 1
0495D9AE-2835-4C99-A6EF-E820CF2AC4DF BizTalkMsgBoxDb 1
496AE9C3-EE78-4D14-AB42-7729BE8303B2 BizTalkMsgBoxDb 1



· 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.

Try this SELECT to find the job:

SELECT * FROM msdb.dbo.sysjobs 
WHERE job_id = Cast(0x0235365006525D458358CC7A59C84CA8 AS uniqueidentifier)
0 Votes 0 ·
SalamElias-0832 avatar image
0 Votes"
SalamElias-0832 answered

Hi, you are correct, I was able to find the record in jobsteps. However, it is strange, why it is using a login "sql2k12$" which is the name of mt sql server and as you can notice in the snapshot it runs under my user and not "sql2k12$" , plus the job is disabled!!

82701-bts-clean.jpg



bts-clean.jpg (85.2 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.

SalamElias-0832 avatar image
0 Votes"
SalamElias-0832 answered SalamElias-0832 edited

and here is the output of the sql staement which helped

 SELECT * FROM msdb.dbo.sysjobs 
 WHERE job_id = Cast(0x0235365006525D458358CC7A59C84CA8 AS uniqueidentifier)

job_id originating_server_id name enabled description start_step_id
50363502-5206-455D-8358-CC7A59C84CA8 0 MessageBox_Message_Cleanup_BizTalkMsgBoxDb 0 This job removes all messages that are no longer being referenced by any subscribers in the BizTalk MessageBox database tables. This is an unscheduled job and is automatically started by the ManageRefCountLog job. 1


 Select job_id, step_uid, database_name, step_id   from dbo.sysjobsteps 
 WHERE job_id = '50363502-5206-455D-8358-CC7A59C84CA8'

job_id step_uid database_name step_id
50363502-5206-455D-8358-CC7A59C84CA8 CFACD8F2-14AD-4E2D-94C4-4A89EFBB0799 BizTalkMsgBoxDb 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.