cYbEr tIMoR

Dala ruma ita hakarak usa base de daus SQL server husi production ba iha test server maibe dalabarak mos ita infrenta problema ho security login, ho rasaun ida ne’e ita tenke import user credencial husi ne’ebe ita exporta base de dadus.

Pasu por pasu tuir mai ne’e lori ita ba hetan resultadu saida mak ita hakarak.

Atu kria login script ne’ebe nia password enkript hanesan tuir mai:

1. Iha server A, loke SQL Server Management Studio, no halo koneksaun ba iha instancia SQL server ne’ebe ita hakarak duplika nia login kredencial.
2. Loke editorial Query foun ida no halao script tuir mai ne’e.

1.  USE master

2.  GO

3.  IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL

4.    DROP PROCEDURE sp_hexadecimal

5.  GO

6.  CREATE PROCEDURE sp_hexadecimal

7.      @binvalue varbinary(256),

8.      @hexvalue varchar (514) OUTPUT

9.  AS

10. DECLARE @charvalue varchar (514)

11. DECLARE @i int

12. DECLARE @length int

13. DECLARE @hexstring char(16)

14. SELECT @charvalue = '0x'

15. SELECT @i = 1

16. SELECT @length = DATALENGTH (@binvalue)

17. SELECT @hexstring = '0123456789ABCDEF'

18. WHILE (@i <= @length)

19. BEGIN

20.   DECLARE @tempint int

21.   DECLARE @firstint int

22.   DECLARE @secondint int

23.   SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))

24.   SELECT @firstint = FLOOR(@tempint/16)

25.   SELECT @secondint = @tempint - (@firstint*16)

26.   SELECT @charvalue = @charvalue +

27.     SUBSTRING(@hexstring, @firstint+1, 1) +

28.     SUBSTRING(@hexstring, @secondint+1, 1)

29.   SELECT @i = @i + 1

30. END

31.  

32. SELECT @hexvalue = @charvalue

33. GO

34.  

35. IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL

36.   DROP PROCEDURE sp_help_revlogin

37. GO

38. CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS

39. DECLARE @name sysname

40. DECLARE @type varchar (1)

41. DECLARE @hasaccess int

42. DECLARE @denylogin int

43. DECLARE @is_disabled int

44. DECLARE @PWD_varbinary  varbinary (256)

45. DECLARE @PWD_string  varchar (514)

46. DECLARE @SID_varbinary varbinary (85)

47. DECLARE @SID_string varchar (514)

48. DECLARE @tmpstr  varchar (1024)

49. DECLARE @is_policy_checked varchar (3)

50. DECLARE @is_expiration_checked varchar (3)

51.  

52. DECLARE @defaultdb sysname

53.  

54. IF (@login_name IS NULL)

55.   DECLARE login_curs CURSOR FOR

56.  

57.       SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM

58. sys.server_principals p LEFT JOIN sys.syslogins l

59.       ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'

60. ELSE

61.   DECLARE login_curs CURSOR FOR

62.  

63.  

64.       SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM

65. sys.server_principals p LEFT JOIN sys.syslogins l

66.       ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name

67. OPEN login_curs

68.  

69. FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin

70. IF (@@fetch_status = -1)

71. BEGIN

72.   PRINT 'No login(s) found.'

73.   CLOSE login_curs

74.   DEALLOCATE login_curs

75.   RETURN -1

76. END

77. SET @tmpstr = '/* sp_help_revlogin script '

78. PRINT @tmpstr

79. SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'

80. PRINT @tmpstr

81. PRINT ''

82. WHILE (@@fetch_status <> -1)

83. BEGIN

84.   IF (@@fetch_status <> -2)

85.   BEGIN

86.     PRINT ''

87.     SET @tmpstr = '-- Login: ' + @name

88.     PRINT @tmpstr

89.     IF (@type IN ( 'G', 'U'))

90.     BEGIN -- NT authenticated account/group

91.  

92.       SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'

93.     END

94.     ELSE BEGIN -- SQL Server authentication

95.         -- obtain password and sid

96.             SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )

97.         EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT

98.         EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

99.  

100.                 -- obtain password policy state

101.                 SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

102.                 SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

103.          

104.                     SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

105.          

106.                 IF ( @is_policy_checked IS NOT NULL )

107.                 BEGIN

108.                   SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked

109.                 END

110.                 IF ( @is_expiration_checked IS NOT NULL )

111.                 BEGIN

112.                   SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked

113.                 END

114.             END

115.             IF (@denylogin = 1)

116.             BEGIN -- login is denied access

117.               SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )

118.             END

119.             ELSE IF (@hasaccess = 0)

120.             BEGIN -- login exists but does not have access

121.               SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )

122.             END

123.             IF (@is_disabled = 1)

124.             BEGIN -- login is disabled

125.               SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'

126.             END

127.             PRINT @tmpstr

128.           END

129.          

130.           FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin

131.            END

132.         CLOSE login_curs

133.         DEALLOCATE login_curs

134.         RETURN 0

135.         GO

Nota katak: Script ne’e kria kria store procedure rua iha master base de dadus. Procedure ne’e nia naran mak sp_hexadecimal ho sp_help_revlogin

3.      Agora halao statement tuir mai ne’e:

EXEC sp_help_revlogin

Output script  husi sp_help_revlogin sei kria procedur script ba login nian. Script login ne’e bainhira halao iha SQL server seluk nia kredencial hanesan lolos ho kredencial iha fatin ne’ebe ita exporta database, hanesan SID (security Identifier) no original password

4.      Iha server B, loke SQL server Management Studio, no liga ba iha instancia base de dadus iha SQL Server

Note: Molok atu ba iha pasu 5. Hare’e didika lai nia prosedural

5.      Loke Window Editor Query nian ida, no halao output ida ne’ebe ita hetan iha pasu 3.

Tested with successful result.