Timeline for Which authentication is better for MS-SQL Server 2019 : integrated security=SSPI vs uid=sa;pwd=xx | windows auth vs sa auth
Current License: CC BY-SA 4.0
11 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| May 13, 2021 at 10:39 | history | edited | Ronaldo | CC BY-SA 4.0 |
info added from comments
|
| May 9, 2021 at 10:20 | comment | added | Dan Guzman | I'll add that I have seen significant differences in production when Windows authentication requests were inadvertently routed to a remote DR site domain controller across the country. The problem was only observed at application startup, when a problem app opened hundreds of connections at once. It was a non-issue after that due to pooling. The Windows admins ultimately fixed the problem. | |
| May 9, 2021 at 10:19 | comment | added | Dan Guzman | @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. | |
| May 8, 2021 at 23:21 | comment | added | J.D. | @DanGuzman To initially authenticate the user to establish the connection, there could be performance differences, sure. And theoretically this could go either way, but it is negligible especially in the grand scheme of things. But I took OP's question regarding performance moreso about executing queries under one connection context vs the other, which there shouldn't be any actual performance differences (as far as I'm aware). | |
| May 8, 2021 at 22:36 | comment | added | Ronaldo | @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. | |
| May 8, 2021 at 22:17 | comment | added | Furkan Gözükara | @DanGuzman so in practice, there should not be noticeable difference. Anyone experimented on this? Because I can use either way. The server is only managed by me so there is not really any security issue. | |
| May 8, 2021 at 22:06 | comment | added | Dan Guzman | 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. | |
| May 8, 2021 at 22:04 | comment | added | Furkan Gözükara | @J.D. and ronaldo ty for answers | |
| May 8, 2021 at 22:04 | vote | accept | Furkan Gözükara | ||
| May 8, 2021 at 22:00 | comment | added | J.D. | @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. | |
| May 8, 2021 at 21:50 | history | answered | Ronaldo | CC BY-SA 4.0 |