question

PeterNMoore-3735 avatar image
1 Vote"
PeterNMoore-3735 asked Criszhan-msft commented

SQL Server Slow Performance with Windows Authentication and Machine Accounts (Non-Domain) (LSASS Issue?)

I'm experiencing severe performance problems with SQL Server 2019 (any edition) when using Windows/integrated authentication but with machine accounts - NOT domain accounts. The simplest reproducible steps are:

  • Have 1 VM ("web") connecting to another ("sql").

  • Both VMs are on "WORKGROUP", not a Windows domain

  • Create an account "serviceaccount" on both machines, with the same password.

  • On "web", run the IIS application pool as "serviceaccount".

  • Create a login on "sql" for "serviceaccount" using "FROM WINDOWS"

  • When connecting to "sql" from the IIS application, use integrated security

It works, but is on average 3x slower than when using a SQL login or Windows Authentication with a domain service account.

When using Windows authentication with the machine accounts, I see this in the task manager:

117606-task-manager.png

Local Security Authority Process (LSASS.EXE) is taking up almost as much CPU as SQL. However, when I use domain accounts or SQL logins, LSASS is doing nothing, and SQL uses about double the CPU. Operations are on average 3X faster.

This problem only seems to affect SQL Server. I use the same service account to access a file share attached to the SQL VM using SMB, which doesn't cause LSASS to use any CPU and goes at normal speed.

Windows Defender, Firewall, and anything else that I could think of as being responsible for this are all turned off.

Appreciate any ideas!



sql-server-generalazure-sql-virtual-machines
task-manager.png (51.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.

Criszhan-msft avatar image
0 Votes"
Criszhan-msft answered

Hi,

Create an account "serviceaccount" on both machines, with the same password.

In this way, the client actually "impersonates" the Windows account on the SQL Server side. This method is called "pass through".

In my test, I used the SSMS with the specified windows account to connect to SQL Server, and the authentication speed seemed to be normal. Have you tested it with SSMS? It may be related to the IIS application pool.

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.

PeterNMoore-3735 avatar image
0 Votes"
PeterNMoore-3735 answered Criszhan-msft commented

Hi there - Great idea. I just tried it and agree with you both methods of authentication seemed to go at the same speed when run once. However my application is opening and closing a connection once a second during long background operations to update the database with task progress, so it's possible that the continuous connecting and disconnecting is causing LSASS to consume a lot of CPU and bog down the rest of the server. Not sure how to simulate that through SSMS or SQLCMD. Perhaps I should try to leave the connection open all the 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.

Hi,

I also think that the high frequency of physical connection and close operations may cause LSASS to consume a lot of CPU.

I am not familiar with IIS application. Does the application use connection pooling?

0 Votes 0 ·