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

All we need is an easy explanation of the problem, so here it is.

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))
        using (SqlDataAdapter DA = new SqlDataAdapter(strQuery, connection))

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply