SSMS : 18.12.1 and 18.6 : clicking New Query button crashes SSMS; 'SQL Server Agent' service doesn't start at all.

Question

Hello All !

SSMS : 18.12.1 and 18.6 : clicking New Query button crashes SSMS; 'SQL Server Agent' service doesn't start at all.

=============================================

OS: Windows 10 pro : version: 10.0.19044

steps:
Installed SQL Server 2022
Logged on to SSMS 18.6 (windows authentication)
Upon clicking New query button, after a few seconds of hour glass wait, SSMS crashes and SSMS logon window reappears.

At the same time, 'SQL Server Agent' will be in 'Stopped' status. It never starts (after restart or start)

--------------------------------

Under Management>SQL Server Agent>Following message is seen:
[000] This installation of SQL Server Agent is disabled. The edition of SQL Server that installed this service does not support SQL Server Agent.

--------------------------------

===================================================================================

At the same time, the error on eventvwr is as below:
Event viewer Log:

Faulting application name: Ssms.exe, version: 2019.150.18424.0, time stamp: 0x62b10e6f
Faulting module name: clr.dll, version: 4.8.4515.0, time stamp: 0x624cf48c
Exception code: 0xc00000fd
Fault offset: 0x004c301a
Faulting process id: 0x2d84
Faulting application start time: 0x01d8fdb382eb693b
Faulting application path: D:\Microsoft SQL Server Management Studio 18\Common7\IDE\Ssms.exe
Faulting module path: C:\Windows\Microsoft.NET\Framework\v4.0.30319\clr.dll
Report Id: a0bd4d10-ce5c-4be6-b3b6-cbf543a65b30
Faulting package full name:
Faulting package-relative application ID:

========================================================================================================

Trials (same error seen in all A, B C and D):
(A):

1. Right click on 'SQL Server Agent' > Right click properties > Log on tab : change account to 'Local Systems' > Restart SQL Service.
2. Open SSMS, for the logged in SSMS user, give sysadmin role (server roles)
3. Restart SQL server.
4. reopen the SSMS. Logon, click New Query

Shared memory, Named pipes, TCP/IP: > Enabled

Following combinations gave the same error:

(B):SQL Server 2022
SSMS 18.12.1

(C):SQL Server 2019
SSMS 18.12.1

(D):SQL Server 2019
SSMS 18.6

---------
Note in : https://learn.microsoft.com/en-us/dotnet/framework/migration-guide/how-to-determine-which-versions-are-installed :
registry at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\NET Framework Setup\NDP\v4\Full. If the Full subkey is missing, then .NET Framework 4.5 or above isn't installed.

On this machine, Full key is "Not" available under v4 and under Wow6432Node too.

-------------

(recent activity: I uninstalled VS code)

I consider that it is .NET Framework version issue.
(Not sure whether installing .NET framework 4.8 will create some other issues?)

I would appreciate if some knowledgeable person throws some light to this problem over all.

Thanks !!!!
Srini

Answer 1

Check permissions on the C:Windows\temp file - apparently the 22H2 release of Windows 10 changed those permissions, and SSMS tries to open and do other operations on that folder. We just pinned this down with MS after running Procmon and seeing access denied errors from SSMS in the event logs. Certainly explains why running as Admin fixes it.

Answer 2

Hi, @srini
May I ask did you try using the repair function in the SQL installation window, like the following picture shows:

Besides I met one case before, it was something wrong with vs and he couldn't start SQL. So I assume that maybe...the VS is the reason ,you can try reinstalling the vs see if the error disappear.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment"

Answer 3

Hi PandaPan,

Thanks for response.

I did that repair it didn't seem to work. Also, I worked with SQL server 2022 (complete fresh installation)

• SSMS 18.6 (please refer my comment, it has more detail).......When New query was clicked, hour glass came up and crashes with the event viewer message "Faulting module path: C:\Windows\Microsoft.NET\Framework\v4.0.30319\clr.dll"

(Here are the sequence of events that took place after posting this: Day before yesterday, there was a 'windows SQL server security update' - not sure whether this is related to the stated issue at a high level.)

Current update:

1. If the SSMS short cut is opened as 'Run as administrator' this error is not appearing. Yes. !!!!
2. If the SSMS short cut is opened by just double clicking (i.e., as plain, non administrator), - with windows authentication or SQL server authentication. The error occurs. (This never happened earlier)

(In both above cases, the logged in windows user has full admin rights given and as seen in user accounts section. )

Please let me know your thoughts.

(For the moment, I am working by opening as administrator)

Thanks,

Answer 4

Hi PandaPan,

Thanks for response.

Here are the findings:

(1): SQL Server and Windows authentication was selected (I could login with both to server instance)

(2) During installation, it didn't explicitly select 1433. However, on the installed 2022, the TCP port value is seen as blank (No value).
Trial: Enabled TCP/IP and I changed this port to 1433 (as per your picture) for all IPs (and for all other existing IPs), restarted the server and logged in again, the same error seen.

(3) There are more than one databases in that instance. By default sa is selected as owner for the DB (for me). For the DB, 'New query' gave same error. (crash)

---When opened as administrator, no error is seen.

Answer 5

Hi @srini ，
Try this solution:

Copy the File : Microsoft.VisualStudio.Shell.Interop.8.0.dll

Located in

C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\PrivateAssemblies\Interop

Paste in the Folder:

C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\

I'm not one hundred percent sure this can solve the error.
Like I had commented at first, the VS is an important part of SQL Server.
Really wish you good luck !

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment"