Printed 2017/08/19 03:10PM

SQL Server Authentication Modes and Surface Area Test


One of the videos I did for JumpStart TV is up on the front page:

SQL Server Authentication Modes

It is an introductory video to help understand the two types of authentication SQL Server can perform: Windows authentication only and Mixed Mode. It's primary purpose was to cover the two modes and how to switch SQL Server back and forth between the two. However, I did cover a bit about the SQL Server 2005 attack surface.

It turns out that in SQL Server 2005, you can use an SQL Server login connection attempt to determine whether or not the SQL Server is configured for Windows authentication only or for Mixed Mode. Pick a SQL Server login you don't believe will be a defined login for that SQL Server. Then attempt to login with it. I used TestAuthenticationMode. If the SQL Server is configured for Mixed Mode, you'll get the following error message back:

Mixed Mode error 

But if it's configured for Windows authentication only, you'll get a different error message:

Windows authentication only error

Fortunately, this does not work for SQL Server 2008. In both cases, you get the error message indicating the login failed.


Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.