????? ??? ??????? salt ??????? ??? SQL Server sa ??????? ????? ??????

?????? ????????? ?????? ?????????
???? ???????: 980671 - ??? ???????? ???? ????? ????? ??? ???????.
????? ???? | ?? ????

?? ??? ??????

???????

?? Microsoft SQL Server 2005 ??? ????????? ?????? ?????? ?????? ?? SQL Server ??????? ??? ????? ??????? ?????? ?????? sa ???????. ??? ????? ????? ????? ?????? ???????, ??? ????? ????? ?? ?????? ??????? ???? ?????? ??? ??????? ???? ????? ?????? ??? ???? ?????? ???????. ????? ????? ?????? ??????? ??????? ??? ??????? SQL Server.

?????

?? SQL Server 2005 ??? ????????? ?????? ??? ????? ??????? ????? ?? ????? ???? sa. ??? ?? ????? CHECK_POLICY ????? ??????? ???? ????? ?? ????? ????? ???? ???????? ?????? ???? ?????? ??? ???? ??????? ?? ??????? ???? ??????. ???? ???????? ??? ????? CHECK_POLICY ?? SQL Server 2005. ??? ????? CHECK_POLICY ??????? salt ?? ??? ????? ?????? ?????? sa ? ???? ????? ????? ????? ???? ??? ??????? ?????? ????? ??????.

??? ????? ?? ?? ???? ??? ?????? ????? ???????? ??? ????? ???? ????? ?????? sa ????? ????? ???????. ????? ??? ?????? ????? ????? ????? ??? ????? ?????? ?? ????? ??? ??????? ?? ????? ???? ???? ?? SQL Server ???????.

?????? ???? ??? ??????? ????? ??? ??????? ?????? ?????????? ???? ??? ????????? ?????? ???? ??? ????? ??????? ??? SQL Server 2008 ???? ????? ???????. ?????????? ??? ????? ?????? ????? ?????? ???.

????? ????? ?????

??? ?? ???? ??? ????? ??????? ??? ?????? ????? ?????? ? ?? ??? ????? ???? ????? ???? ????. ???????? ??? ?????? ????? ???????? ????? ?? ?????? ??????? ??? ???? SQL Server ?????? ??? ??????? ???? ??????. ??? ?? ????? ???? ????????? ?? ???? ?????? ??? ??????? ????? ???? ???? ?????????? ???????. ????? ?? ?????? ??? ???? ??? ??????? ??? ??????? salt ???????.

???? ??????

???? ????? ???????? ????? ?????? ?????? ????? ????? ??????? ?? ???? sa ????? ?????? ?? SQL Server 2005 ?????? ????? ?????? SP2 ?? ????????? ??????. ?????? ???????? ????? ??? ????? ???????? ???? ???? ?????? CONTROL ?????? ?? ?? ???? ????? ??? ????? ?????? ???? ??????. You should be aware that, after you reset the cryptographic salt, the password history for the sa login will also be reset.
-- Work around for SQL Server 2005 SP2+
--
-- Sets the password policy check off for [sa]
-- Replaces [sa] password with a random byte array
-- NOTE: This effectively replaces the sa password hash with 
-- a random bag of bytes, including the salt,
-- and finally sets the password policy check on again
--
-- After resetting the salt, 
-- it is necessary to set the sa password,
-- or if preferred, disable sa
--
CREATE PROC #sp_set_new_password_and_set_for_sa(@new_password sysname, @print_only int = null)
AS
	DECLARE @reset_salt_pswdhash nvarchar(max)
	DECLARE @random_data varbinary(24)
	DECLARE @hexstring nvarchar(max)
	DECLARE @i int
	DECLARE @sa_name sysname;
	
	SET @sa_name = suser_sname(0x01);
	SET @random_data = convert(varbinary(16), newid()) + convert(varbinary(8), newid())
	SET @hexstring = N'0123456789abcdef'
	SET @reset_salt_pswdhash = N'0x0100'
	SET @i = 1
	WHILE @i <= 24
	BEGIN
		declare @tempint int
		declare @firstint int
		declare @secondint int

		select @tempint = convert(int, substring(@random_data,@i,1))
		select @firstint = floor(@tempint/16)
		select @secondint = @tempint - (@firstint*16)

		select @reset_salt_pswdhash = @reset_salt_pswdhash +
			substring(@hexstring, @firstint+1, 1) +
			substring(@hexstring, @secondint+1, 1)

		set @i = @i+1
	END

	DECLARE @sql_cmd nvarchar(max)

	SET @sql_cmd = N'ALTER LOGIN ' + quotename(@sa_name) + N' WITH CHECK_POLICY = OFF;
	ALTER LOGIN ' + quotename(@sa_name) + N' WITH PASSWORD = ' + @reset_salt_pswdhash + N' HASHED;
	ALTER LOGIN ' + quotename(@sa_name) + N' WITH CHECK_POLICY = ON;
	ALTER LOGIN ' + quotename(@sa_name) + N' WITH PASSWORD = ' + quotename(@new_password, '''') + ';'

	IF( @print_only is not null AND @print_only = 1 )
		print @sql_cmd
	ELSE
		EXEC( @sql_cmd )
go

---------------------------------------------------------------------------------------
-- Usage example:
--
DECLARE @new_password sysname 

-- Use tracing obfuscation in order to filter the new password from SQL traces
-- http://blogs.msdn.com/sqlsecurity/archive/2009/06/10/filtering-obfuscating-sensitive-text-in-sql-server.aspx
--
SELECT @new_password = CASE WHEN 1=1 THEN 
    -- TODO: replace password placeholder below with a strong password
    --
   ##[MUST_CHANGE: replace this placehoder with a new password]##:
   ELSE EncryptByPassphrase('','') END
EXEC #sp_set_new_password_and_set_for_sa @new_password
go

DROP PROC #sp_set_new_password_and_set_for_sa 
go
?? SQL Server 2008, ????? ????? ???????? ????? ??????. ?????? ???????? ????? ??? ????? ???????? ???? ???? ?????? CONTROL ?????? ?? ?? ???? ????? ??? ????? ?????? ???? ??????. ?????
-- Work around for SQL Server 2008
--

------------------------------------------------------------------------
-- Set the password policy check off for [sa]
-- Reset the password
-- Set the password policy check on for [sa] once again
-- 
-- NOTE: The password history will be deleted
--
CREATE PROC #sp_set_new_password_and_set_for_sa(@new_password sysname, @print_only int = null) 
AS
	DECLARE @sql_cmd nvarchar(max);

	DECLARE @sa_name sysname;

	-- Get the current name for SID 0x01. 
	-- By default the name should be "sa", but the actual name may have been chnaged by the system administrator
	--
	SELECT @sa_name = suser_sname(0x01);


	-- NOTE: This password will not be subject to password policy or complexity checks
	-- if desired, this step can be replaced with a "throw away" password for 
	-- and set the real password after the check policy setting has been set
	--
	SELECT @sql_cmd = 'ALTER LOGIN ' + quotename(@sa_name) + ' WITH CHECK_POLICY = OFF;
	ALTER LOGIN ' + quotename(@sa_name) + ' WITH PASSWORD = ' + quotename(@new_password, '''') + ';
	ALTER LOGIN ' + quotename(@sa_name) + ' WITH CHECK_POLICY = ON;'

	IF( @print_only is not null AND @print_only = 1 )
		print @sql_cmd
	ELSE
		EXEC( @sql_cmd )
go

---------------------------------------------------------------------------------------
-- Usage example:
--
DECLARE @new_password sysname

-- Use tracing obfuscation in order to filter the new password from SQL traces
-- http://blogs.msdn.com/sqlsecurity/archive/2009/06/10/filtering-obfuscating-sensitive-text-in-sql-server.aspx
--
SELECT @new_password = CASE WHEN 1=1 THEN 
    -- TODO: replace password placeholder below with a strong password
    --
   ##[MUST_CHANGE: replace this placehoder with a new password]##:
   ELSE EncryptByPassphrase('','') END
EXEC #sp_set_new_password_and_set_for_sa @new_password
go

DROP PROC #sp_set_new_password_and_set_for_sa 
go
In SQL Server 2008, the cryptographic salt for the Policy Based Management logins can be reset by using the following script. ?????? ???????? ????? ??? ????? ???????? ???? ???? ?????? CONTROL ?????? ?? ?? ???? ????? ??? ????? ?????? ???? ??????.
------------------------------------------------------------------------
-- Set the password policy check off for the Policy principals
-- Reset the password
-- Set the password policy check on for them once again
--
-- NOTE: 
-- These principals are not intended to establish connections to SQL Server
-- So this SP will also make sure they are disabled
--
CREATE PROC #sp_reset_password_and_disable(@principal_name sysname, @print_only int = null) 
AS
	DECLARE @random_password nvarchar(max)
	
	SET @random_password = convert(nvarchar(max), newid()) + convert(nvarchar(max), newid())
	DECLARE @sql_cmd nvarchar(max)

	SET @sql_cmd = N'ALTER LOGIN ' + quotename(@principal_name) + N' WITH CHECK_POLICY = OFF;
	ALTER LOGIN ' + quotename(@principal_name) + N' WITH PASSWORD = ''' + replace(@random_password, '''', '''''') + N''';
	ALTER LOGIN ' + quotename(@principal_name) + N' WITH CHECK_POLICY = ON;
	ALTER LOGIN ' + quotename(@principal_name) + N' DISABLE;'

	IF( @print_only is not null AND @print_only = 1 )
		print @sql_cmd
	ELSE
		EXEC( @sql_cmd )
go


EXEC #sp_reset_password_and_disable '##MS_PolicyEventProcessingLogin##';
EXEC #sp_reset_password_and_disable '##MS_PolicyTsqlExecutionLogin##';
go

SELECT name, password_hash, is_disabled FROM sys.sql_logins
go

?????

??? ????? ??? ??????? ?? ??? ??????? ?????????? ?? SQL Server 2005 ? SQL Server 2008 ? ?? ???????? ??????? SQL Server.

??????? ????

Microsoft thanks ??????? ????? ???? ???????? ??? ????? ???????:

???????

???? ???????: 980671 - ????? ??? ??????: 16/???? ?????/1431 - ??????: 1.0
????? ???
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Enterprise Edition for Itanium Based Systems
  • Microsoft SQL Server 2005 Enterprise X64 Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Standard X64 Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Web
  • Microsoft SQL Server 2008 Workgroup
????? ??????: 
kbmt kbpasswords kbexpertiseadvanced kbsurveynew kbprb KB980671 KbMtar
????? ????
???: ??? ????? ??? ?????? ???????? ?????? ????? ???? ????? ?????????? ????? ?? ????????? ?????? ????. ???? ???? ?????????? ???? ?? ???????? ???????? ?????? ????????? ????? ????????? ???????? ????? ???????? ?????? ?? ?????? ??? ?? ???????? ???????? ?? ????? ??????? ?????? ??? ??????? ?????? ??. ?????? ?? ???? ??? ??????? ???????? ????? ?? ???? ????? ?????? ??? ????? ??? ????? ??????? ?? ????? ?? ?????? ??? ??? ??????? ??????? ?? ????? ????? ????? ????? ?????. ?? ????? ???? ?????????? ??????? ??? ????? ?? ??????? ?? ????? ?????? ?? ??? ????? ?? ????? ??????? ?? ???????? ?? ??? ???????. ???? ???? ?????????? ???????? ??? ????? ?????? ??????? ??????
???? ??? ????? ??????? ?????? ??????????980671

????? ???????

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com