1

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

2
  • You mention the sa login in your connection. You shouldn't have the sa as your app user unless absolutely necessary. If your application would happen to suffer a SQL Injection attack, with sa credential the attacker would be able to do anything with your database and wouldn't even have the trouble of searching for the names of objects as sa has access to a bunch of default objects that exists in a SQL Server instance. See this.
    – Ronaldo
    May 9, 2021 at 1:24
  • If performance is your concern, it's very likely that you could find queries on your application that would benefit of improvement before increasing the security risk by using a SQL Server login in order to make authentication faster.
    – Ronaldo
    May 9, 2021 at 1:33

1 Answer 1

3

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.

8
  • 1
    @MonsterMMORPG Even authentication protocols aside, storing credentials in the application layer is an added security risk that just doesn't exist when you use a Trusted Connection that passes through the authentication from Windows. This is good info from Ronaldo. Regarding performance differences, there shouldn't be any.
    – J.D.
    May 8, 2021 at 22:00
  • 1
    @J.D. and ronaldo ty for answers May 8, 2021 at 22:04
  • 2
    There are performance differences. Windows authentication requires comminication to a domain controller, which is not needed with SQL auth. That said, the performance penalty is usually insignificant and mitigated by connection pooling.
    – Dan Guzman
    May 8, 2021 at 22:06
  • 1
    @MonsterMMORPG you shouldn't be concerned only with other admins of the server, but any malicious attacks that could come to your server or applications. As J.D. mentioned, storing credentials is not the best option and even the performance disadvantage of Windows authentication was mitigated with SQL Server connection pooling (ADO.NET) as mentioned by Dan Guzman. Thank you guys for making it a much better answer. I'm gonna update it soon.
    – Ronaldo
    May 8, 2021 at 22:36
  • 2
    @MonsterMMORPG, the last paragraph of Ronaldo's answer sums it up well; use Windows auth when possible. As to performance, I suggest you I suggest race your horses. FWIW, 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.
    – Dan Guzman
    May 9, 2021 at 10:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.