SQL Server - Check The Integrated Security Mode

In this blog, we will see how to check the integrated security mode using "SERVERPROPERTY".

What's the "SERVERPROPERTY"?

"SERVERPROPERTY" is a System-defined function used to return the SQL Server instance information.

"SERVERPROPERTY" Syntax

  1. SERVERPROPERTY ('propertyname')

ISINTEGRATEDSECURITYONLY

Use ISINTEGRATEDSECURITYONLY property to check the integrated security mode.
 
0 Windows and SQL Server Authentication
1 Integrated security (Windows Authentication)

Example

  1. declare @IsIntegratedSecurityOnly as sql_variant  
  2. set @IsIntegratedSecurityOnly = (select SERVERPROPERTY('IsIntegratedSecurityOnly'))  
  3. select @IsIntegratedSecurityOnly as IsIntegratedSecurityOnly,  
  4. case @IsIntegratedSecurityOnly  
  5. when 0 then 'Windows and SQL Server Authentication'  
  6. when 1 then ' Integrated security (Windows Authentication)'  
  7. else 'Invalid Input'  
  8. end as 'Integrated Security Type'  

 

Output

Check the integrated security mode using PowerShell?

You can use Windows PowerShell to invoke SQL command on a reachable server within the network using Invoke-Sqlcmd cmdlet as the following:
  • Open Windows PowerShell as Administrator
  • Type the Invoke-Sqlcmd with the below parameters.

    • -query: the SQL query that you need to run on the remote server.
    • -ServerInstance: the SQL server instance name.
    • -Username: the username that has sufficient permission to access and execute SQL query on the remote server.
    • -Password: the password of the elevated user.
  1. PS SQLSERVER:\> Invoke-Sqlcmd -query "select SERVERPROPERTY('IsIntegratedSecurityOnly') as 'IsIntegratedSecurityOnly'" -ServerInstance "epm\epmdb" -Username sa -Password *****

Applies To

  • SQL Server 2008.
  • SQL Server 2012.
  • SQL Server 2014.
  • SQL Server 2016.
  • SQL Server 2017.

Reference

See Also