March 2012 | SansSQL

Friday, March 23, 2012

Different ways to check your SQL Server(s) Authentication mode

Checking the Authentication mode using T-SQL:
  1. Using "xp_LoginConfig" extended Stored Procedure
    EXEC Master.dbo.xp_LoginConfig 'login mode'
    

  2. Using "SERVERPROPERTY" Function
    SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly')   
    WHEN 1 THEN 'Windows Authentication mode'   
    WHEN 0 THEN 'SQL Server and Windows Authentication mode'   
    END as [Authentication Mode]  
    
    
  3. Using Registry
    DECLARE @Mode INT  
    EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', 
    N'Software\Microsoft\MSSQLServer\MSSQLServer',   
    N'LoginMode', @Mode OUTPUT  
    
    SELECT CASE @Mode    
    WHEN 1 THEN 'Windows Authentication mode'
    WHEN 2 THEN 'SQL Server and Windows Authentication mode'
    ELSE 'Not known'
    END as [Authentication Mode]  
    
Checking the Authentication mode using SSMS:
To check the Authentication mode using SSMS,
  1. Right-Click on the Server
  2. Choose "Properties"
  3. Navigate to "Security" Page
  4. Check "Server Authentication" Section

Ads