Microsoft Sql Server can operate in two Authentication Modes, the first is Windows Authentication, and the second is Mixed Mode. In Windows Authentication Mode only the users, which have Windows Accounts (either Active Directory or Computer Accounts) can login to Sql Server. In Mixed Mode we can create in Sql Server logins, which do not have corresponding Windows Accounts. Windows Authentication Mode is suggested by Microsoft as the best practice; however Sql Server DBAs often have to configure Mixed Mode, because there are numerous applications on the market, which requires it. Ability to quickly check authentication mode of Sql Server is crucial if we are performing an audit or we have to work on an instance, on which we do not know anything.

The easiest way to check Authentication Mode is to use Sql Server Management Studio, we right click desired server and choose Properties.

Another way to check Sql Server Authentication Mode is to execute xp_logininfo stored procedure, please be aware that this extended stored procedure can be removed in the future versions of Sql Server.

exec master.sys.xp_loginconfig 'login mode'


name
config_value


login mode
Mixed