Which authentication is better for MS-SQL Server 2019 : integrated security=SSPI vs uid=sa;pwd=xx | windows auth vs sa auth

Question

In my different web applications I use both ways, however, I would like to learn which method is better:

1: server=localhost;database=x; integrated security=SSPI;persist security info=False; Trusted_Connection=Yes; 

2: server=localhost;database=x;uid=sa;pwd=y;

In my web application, for each database query, I open a connection and then close the connection.

So I am pretty much using the regular style such as:

    using (SqlConnection connection = new SqlConnection(srConnectionString))
    {
        connection.Open();
        using (SqlDataAdapter DA = new SqlDataAdapter(strQuery, connection))
        {
            DA.Fill(dSet);
        }
    }

You can safely assume that there are hundreds or even thousands of queries at any given second.

My questions:

Are there any performance wise difference between 2 authentication methodology?

Are there any security difference between 2 authentication methodology?

I don't allow remote connections to the SQL Server. So only local connections are allowed.

Operating system Windows Server 2019

Answer 1

Security Difference

You can find that on the Connecting Through Windows Authentication doc:

Windows Authentication is the default authentication mode, and is much more secure than SQL Server Authentication. Windows Authentication uses Kerberos security protocol, provides password policy enforcement with regard to complexity validation for strong passwords, provides support for account lockout, and supports password expiration. A connection made using Windows Authentication is sometimes called a trusted connection, because SQL Server trusts the credentials provided by Windows.

Performance Difference

The existing performance difference between these two methods isn't enough to advise you to choose SQL Server Authentication over Windows Authentication to gain any performance improvement in detriment of security. Therefore, if you have the option to use Windows Authentication, use it.

As J.D.'s comment says, storing credentials is not the safest option and even the performance disadvantage of Windows authentication was mitigated with SQL Server connection pooling as mentioned by Dan Guzman. He also added:

I tested 10K connection open/close requests in my test lab on bare metal. The average milliseconds per connection were: SQL auth with polling: 0.10838721, Windows auth with pooling: 0.12424151, SQL auth with no pooling: 2.66011692, Windows auth without pooling: 3.2432628. Consider that query execution rather than connections will likely be the long pole in the tent.