How to Identify Currently Used SQL Server Authentication Mode

By: Editor
May 02, 2010

Page: 1/2

Introduction

Microsoft SQL Server Support basically supports two types of Authentication Modes to using which one can connect to an instance of SQL Server 2000 and higher versions. Two different authentication modes are Windows Authentication Mode & SQL Server and Windows Authentication Mode (Mixed Mode). In this article we will take a look at how you can verify the currently used authentication mode and how to change the authentication mode using TSQL and SQL Server Management Studio.

Different ways by which SQL Server Authenticate Users

Windows Authentication Mode: - When Windows Authentication Mode is used only windows logins can connect to SQL Server. Windows authentication is much more secure when compared to mixed authentication as in an enterprise environment Windows Login credentials are generally Active Directory domain credentials.

Mixed Mode / SQL Server and Windows Authentication Mode Authentication: - When Mixed Mode aka SQL Server and Windows authentication mode is used either Windows Logins or SQL Server Logins can be used to connect to SQL Server.

Identify SQL Server Authentication Mode Using TSQL

Database administrator can use the below mentioned TSQL code to identify the currently used SQL Server Authentication Mode.

Use Master
GO

SELECT

            CASE
SERVERPROPERTY('IsIntegratedSecurityOnly')
           
WHEN 0 THEN 'Mixed Mode - Allows Both SQL Server and Windows Authentication Mode'
           
WHEN 1 THEN 'Allows Only Windows Authentication Mode'

END
AS [Currently Used SQL Server Authentication Mode]
GO

SQL Server Authentication Mode


You can execute the below mentioned TSQL code to identify the login security configuration on an instance of SQL Server. As per Microsoft one can use this only for backward compatibility purpose.

Use Master
GO

EXEC
xp_loginconfig
GO

xp_loginconfig

Identify SQL Server Authentication Mode Using SQL Server Management Studio

Database administrators can also identify the SQL Server Authentication Mode using SQL Server Management Studio.

1. Connect to an Instance of SQL Server using SQL Server Management Studio

2. In Object Explorer, right click the SQL Server Instance and select Properties from the drop down list.

Continue Free Learning...

  • Please leave below your valuable feedback for this article.
  • Feel Free to refer this article to your friends and colleagues using the below “Share this Article” option.
  • Do subscriber to our News Letter to continue your free learning.
  • Don’t forget to Like Us on Facebook and do follow us on Twitter for latest updates.

Share this Article

Click on Next Page to continue reading rest of the article…


Next Page..



Follow @MyTechMantra on Twitter
We're on Facebook
Bookmark and Share

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."