Check if SQL Authentication is enabled in SQL Server 2008 programmatically
There are different ways to check if sql authentication is enabled. We can use sql queries or else we can use C# code. We are going to looking into all the possibilities. We can choose the best possible scenario which fits our requirement.
1) Using SQL Queries
a. Using loginconfig
EXEC master.sys.xp_loginconfig 'login mode'
|
b. Using SERVERPROPERTY
SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly') WHEN 1 THEN 'Windows Authentication' WHEN 0 THEN 'Mixed Mode' END as [SQL Authentication Mode]
|
c. Using xp_instance_regread registry read
DECLARE @SQLAuthenticationMode INT
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', @AuthenticationMode OUTPUT
SELECT CASE @AuthenticationMode
WHEN 1 THEN 'Windows Authentication'
WHEN 2 THEN 'Windows and SQL Server Authentication'
ELSE 'Unknown' END as [Authentication Mode]
|
2) Using C# code for checking the registry entry.
The code reads a registry entry of SQL Server 2008 and the key “LoginMode” has a value 2 it’s a mixed mode and if it has a value 1 then it has Windows Authentication. In order to read a registry entry we need to reference the Microsoft.Win32.dll;
using System;
using System.Collections.Generic;
using System.Text;
using System.Security;
using System.Security.AccessControl;
using Microsoft.Win32;
namespace ReadWriteRegistryEntry
{
class Program
{
static void Main(string[] args)
{
// Code to Read the registry entry to check the authentication mode of Sql Server
try
{
string keyName = @"SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQLServer";
RegistryKey baseKey = RegistryKey.OpenBaseKey(RegistryHive.LocalMachine, RegistryView.Registry32);
RegistryKey subkey = baseKey.OpenSubKey(keyName);
if (subkey == null)
{
Console.WriteLine("No Key found");
}
else
{
try
{
if (subkey.GetValue("LoginMode").ToString() == "2")
{
Console.WriteLine("Mixed Mode");
}
else if (subkey.GetValue("LoginMode").ToString() == "1")
{
Console.WriteLine("Windows Authentication");
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
Console.Read();
}
}
}
|
