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

ISINTEGRATEDSECURITYONLY

Use ISINTEGRATEDSECURITYONLY property to check the integrated security mode.

0	Windows and SQL Server Authentication
1	Integrated security (Windows Authentication)

Example

declare @IsIntegratedSecurityOnly as sql_variant
set @IsIntegratedSecurityOnly = (select SERVERPROPERTY('IsIntegratedSecurityOnly'))
select @IsIntegratedSecurityOnly as IsIntegratedSecurityOnly,
case @IsIntegratedSecurityOnly
when 0 then 'Windows and SQL Server Authentication'
when 1 then ' Integrated security (Windows Authentication)'
else 'Invalid Input'
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.

PS SQLSERVER:\> Invoke-Sqlcmd -query "select SERVERPROPERTY('IsIntegratedSecurityOnly') as 'IsIntegratedSecurityOnly'" -ServerInstance "epm\epmdb" -Username sa -Password *****

Applies To