Sunday, September 28, 2014

0 SQL Server authentication mode

SQL Server support 2 authentication mode.

Windows Authentication mode and SQL server/Windows Authentication mode.

image

In Windows authentication mode, SQL Server only allow domain account connect to the SQL Server.

This serve configuration can not be checked or changed via sp_configure.

Check the authentication mode

--- 1: Windows Authentication mode, 0: Mixed Mode
EXEC master.sys.xp_loginconfig 'login mode'
EXEC  xp_instance_regread 'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode'
SELECT  SERVERPROPERTY('IsIntegratedSecurityOnly')   

image

Change the Authentication mode

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2
GO

After the changed, we need to bounce SQL Server to make it effective.

Below is the result after the SQL Server restart.

image

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates