en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP

 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize

Recent Entries

Minimize
מאי26

Written by: ronen ariely
26/05/2013 08:18 RssIcon

משתמש במסד הנתונים אינו מוגדר רק על ידי ססמה ושם משתמש. כל משתמש הוא ייחודי ויש לו מספר משתמש ייחודי לו. גם אם נפתח בשתי שרתים משתמש עם אותו שם ואותה ססמה אלו למעשה לא יהיו אותם משתמשים. יותר מכך גם יצירה של משתמש חדש עם הגדרה של SID מסויים לא יבטיח לנו שאכן אנחנו מייצרים את אותם נתונים למשתמש.

נראה דוגמאות לבעיות הנוצרות כאשר אנחנו מנסים להעתיק משתמש בצורה לא נכונה תוך שמוש בפרטים הנ"ל

בעיה 1

* נסה ליצור בשרתים שונים משתמש עם אותו שם ואותה ססמה.

* עתה צור מסד נתונים חדש באחד השרתים ותן למשתמש הרשאות מלאות למסד הנתונים. 

* התנתק מהמשתמש הנוכחי שלך ובדוק שאתה מתחבר למסד הנתונים בלי בעיה דרך המשתמש החדש.
הכל עובד? יפה.. הגיע הזמן לבדוק את המשתמש בשרת האחר

* בצע גיבוי מלא למסד הנתונים.

* בשרת השני בצע שחזור למסד הנתונים
בדוק שאכן ברשימת המשתמשים במסד הנתונים מופיע השם של המשתמש שלנו (המשתמש החדש שייצרנו בכל שרת בנפרד)

* נסה להתחבר למסד הנתונים עם המשתמש החדש שלנו

WTF?!?

מדוע אנחנו לא מצליחים?

הסבר 1

פשוט מאוד מפני שזהו לא אותו משתמש. מצב זה נקרא Orphaned Users. אלו משתמשים שאינם ממופים ל SID מתאים הקיים ב INSTANCE הנוכחי שלנו.

פתרון 1.1

נוכל לתקן את המצב בצורה ידנית: מחיקת המשתמש במסד הנתונים... הרי הוא אינו קיים באמת בשרת והוספה של המתמש המתאים הקיים בשרת.

פתרון 1.2

אפשרות אחרת היא לתת לשרת לבצע תיקון אוטומטי עבורינו של כל ה Orphaned Users בעזרת השאילתה הבאה:

-- If you already have a login id and password for this user, fix it by doing:
EXEC sp_change_users_login 'Auto_Fix', 'user'

* ניתן למצוא חומר נוסף בנושא זה בקישור הבא

בעיה 2

הבנו אם כן שלכל משתמש יש SID מסויים. לא מספיק היה להגדיר משתמש על ידי סיסמה והשם, אלא שיש לעשות שימוש כלשהו ב SID. האם העתקה של נתוני ססמה, שם משתמש ו SID של משתמש משרת אחד לשרת אחר כן יעשו את העבודה?

* ניצור משתמש חדש תוך שמוש בסיסמה מסויימת + שם משתמש מסויים + SID מסויים

CREATE LOGIN
    LoginA
WITH
    PASSWORD = N'12#3qwe' ,
    SID = 0x00C9D664AF756647B9A84C430CBFEFBB;
GO

* נבדוק את הנתונים של המשתמש החדש שלנו:

SELECT
    LOGINPROPERTY (N'LoginA' , N'PasswordHash')
    ,CONVERT(varchar(max), LOGINPROPERTY(N'LoginA' , N'PasswordHash'),1 );
GO

התוצאות צריכות להיות משהו כמו:

/*
0x0200E413EB774F022CA3A5C21C302D714E5FCA1538409DF1704BFB020A1594F0B5019529EE89FE9FD91B266008FBA7A8BC92E03CFE302DFC5E207A61353F930FF3DEDB8B1C9B  0x0200E413EB774F022CA3A5C21C302D714E5FCA1538409DF1704BFB020A1594F0B5019529EE89FE9FD91B266008FBA7A8BC92E03CFE302DFC5E207A61353F930FF3DEDB8B1C9B
*/

* נמחוק את המשתמש החדש

DROP LOGIN
    LoginA;
GO

* נחזור על הפעולה שוב: ניצור את אותו משתמש -> נבדוק מה הנתונים שלו שמקבלים. מכיוון שהפעם עשינו שימוש בפרמטרים של שם המשתמש, ססמה, ואפילו SID האם אתם מצפים לקבל נתון זהה?

CREATE LOGIN
    LoginA
WITH
    PASSWORD = N'12#3qwe' ,
    SID = 0x00C9D664AF756647B9A84C430CBFEFBB;
GO
SELECT
    LOGINPROPERTY (N'LoginA' , N'PasswordHash')
    ,CONVERT(varchar(max), LOGINPROPERTY(N'LoginA' , N'PasswordHash'),1 );
GO
/*
before: 0x0200E413EB774F022CA3A5C21C302D714E5FCA1538409DF1704BFB020A1594F0B5019529EE89FE9FD91B266008FBA7A8BC92E03CFE302DFC5E207A61353F930FF3DEDB8B1C9B  0x0200E413EB774F022CA3A5C21C302D714E5FCA1538409DF1704BFB020A1594F0B5019529EE89FE9FD91B266008FBA7A8BC92E03CFE302DFC5E207A61353F930FF3DEDB8B1C9B
new:    0x020081D3566744C7C80A06FE40D3A06EA8CD78E949D1051368D35394E63DBE693B0C601C9552E64E85F05C88E4383471BF1B7C4ACBE7F32D81CD5093BE7CCB52F400AC31D399  0x020081D3566744C7C80A06FE40D3A06EA8CD78E949D1051368D35394E63DBE693B0C601C9552E64E85F05C88E4383471BF1B7C4ACBE7F32D81CD5093BE7CCB52F400AC31D399
WTF ?!?
well the password do not HASHED the same :-)
*/

אם כן הבעיה היא שאפילו במצב זה לא מקבלים את אותו משתמש בדיוק, לכן העתקה כזו לשרת חדש לא תעזור לנו ועדיין יהיה עלינו לבצע תיקון Orphaned Users כמוסבר למעלה.

הסבר

שרת SQL עושה שימוש בפונקציית HASH על פי מייקרוסופט, על מנת לקודד את הססמה שלנו. נראה שהאלגוריתמים של הקידוד עושה שימוש בנתונים נוספים. לכן למרות שפעמיים ייצרנו משתמש עם אותה ססמה הרי שתוצאת הקידוד היתה שונה בכל נסיון.

פתרון

על מנת לבצע העתקה של משתמש בצורה נכונה ומלאה ללא שימוש בתיקון Orphaned Users עלינו ליצור את המשמש תוך שימוש בססמה המקודדת של המשתמש (אחרי ה פעולת ה hashed של השרת) וכמובן עלינו להודיע לשרת שאנחנו עושים שימוש בגרסת ה hashed של הססמה. לכן בשלב ראשון עלינו לבדוק מה הנתונים של המשתמש הנוכחי שלנו (בשרת הישן או ב INSTANCE שרוצים להעתיק). את זה נבצע עם פונקציית  LOGINPROPERTY כך (העמודה המעניינת אותנו היא העמודה השנייה):

SELECT
    LOGINPROPERTY (N'LoginA' , N'PasswordHash')
    ,CONVERT(varchar(max), LOGINPROPERTY(N'LoginA' , N'PasswordHash'),1 );
GO

עתה נוכל ליצור משתמש בצורה הבאה כאשר במיקום של הססמה מכניסים את הנתון השני שקיבלנו בשאילתה למעלה:

CREATE LOGIN LoginA
    WITH PASSWORD = 0x0200E10B6D0A557CBD2F720D984D0F8D2DCDC9BE5E20837D05DFC0781931DC18236CA2F6B89CA1F53C995694DF2F65E9D00805BDEC0145F21E9CBA6C99C8996F1131FF345E8C HASHED
    , SID = 0x00C9D664AF756647B9A84C430CBFEFBB
GO
SELECT
    LOGINPROPERTY (N'LoginA' , N'PasswordHash')
    ,CONVERT(varchar(max), LOGINPROPERTY(N'LoginA' , N'PasswordHash'),1 );
GO
DROP LOGIN
    LoginA;
GO

תוכלו למחוק וליצור את המשתמש כמה פעמים שתרצו בצורה זו והוא תמיד יהיה אותו משתמש בדיוק :-) תוכלו להעתיק אותו לשרת אחר והוא יהיה אותו משתמש בדיוק.

הבעיה הייחידה היא שתיאורטית יכול להיות מצב בו אחד הפרמטרים כמו למשל ה SID משמש אותנו בשרת החדש למשתמש אחר שכבר קיים ולכן נקבל שגיאות. לכן אני מוצא שהדרך היותר קלה וידידותית היא לבצע יצירה של משתמש בצורה רגילה על ידי ססמה ושם משתמש בלבד ואז לבצע תיקון אוטומטי של Orphaned Users. אבל עתה כשיש לכם את האפשרויות השונות הבחירה שלכם

שאלה מעניינת שעלתה

פונקציית HASH אמורה להחזיר את אותו ערך בכל הרצה שנעביר אליה את אותם פרמטרים. אבל פונקציית ה HASH של ה LOGIN מייצרת לנו עבור אותה ססמה, SID ושם משתמש בכל הרצה תוצאה שונה. אז כיצד אנחנו מוציאים מהפונקציה את הססמה? כיצד השרת יודע אם הססמה שהכסנו בהתחברות זהה לזו ששמורה אצלו ?

תשובה

לצערי כאן כבר מגיע חלק שקשור למה שקורה מאחורי הקלעים והוא כמובן נסתר בצורה מכוונת מהמשתמש. ננסה לתת הסבר תיאורטי לפעולה דומה (בלי קשר לזו של שרת ה SQL שכאמור האלגוריתמים בו לא ידוע לנו...). למעשה אני אמציא רגע אלגוריתמים שלי לשם ההסבר.

הגענו כבר למסקנה שברור שחסר לנו נתון בפונקציית ה HASH. הנתון החסר לנו הוא נתון שחייב להיות זמין תמיד בשרת וצריך לשמש אותו בכל  LOGIN. לחילופין זה נתון שאין לו שום חשיבות ואין בו שימוש. מכיוון שאין לנו את הקוד אנחנו יכולים רק להעריך, לבדוק בהינדוס אחורה ואף לנחש וכמובן לבדוק דברים.

פונקציית ה HASH של ה LOGIN היא PWDENCRYPT ואכן ניתן לראות שבכל הרצה שלה נקבל תוצאה שונה. במבט ראשון נראה כאילו זו לא פונקציית HASH על פי ההגדרה הפשוטה שמקיימת: פונקציה שמחזירה תמיד אותה תוצאה עבור אותו פרמטר. נראה בעוד רגע שזה לא כך בהכרח.

נסו להריץ את השאילתה ותראו שהתוצאה שונה בכל הרצה:

select PWDENCRYPT('passwd')

עתה נשכח רגע את נושא הקידוד לשם ההסבר (נחזור אליו בעוד רגע), ונדון בשרשרת רגילה. נניח שאנחנו רוצים להחביא את שרשרת passwd בלי לקודד כך שבכל פעם נקבל תוצאה שונה אבל נוכל תמיד להחזיר את הערך המקורי.

נמציא אלגוריתמים פשוט שעושה שימוש בזמן של ההרצה ומכניס את התווים של הזמן בין התווים של השרשרת המקורית. לכן בהרצה אחת נקבל p2013a06s26s12w09d ובהרצה הבאה נקבל p2013a06s26s12w10d. שימו לב שהסיסמה המקורית מעורבלת בתוך התוצאות (p2013a06s26s12w10d).

אם כן בכל הרצה נקבל תוצאה שונה אבל עדיין אם משתמש נכנס עם הססמה הנכונה הוא יקבל ערך כלשהו מהצורה p~~~~a~~s~~s~~w~~d ולכן נוכל להשוות אם הססמה שהוא הכניס נכונה או לא.

עתה תוסיפו על העירבול הפשוט כאן אלגוריתמים מורכב של עירבול + תוסיפו קידוד של הנתון אחרי העירבול והתוצאה היא שקיבלנו בלאגן שלם שנראה בכל הרצה שונה לחלוטין והוא מהווה הגנה נהדרת על הססמאות שלנו כאשר הוא מסתיר בתוכו את הנתון הרלוונטי.

כמובן שעל מנת לבצע את הפעולה עלינו לבנות פונקציה שבודקת את הססמה שהמשתמש מכניס. בשרתי SQL יש לנו את הפונקציה PWDCOMPARE לשם כך. פונקציה זו מחזירה לנו רק 1 או 0 לפי הבדיקה של הססמה ב STRING לזו של הסמה שמקודדת והמעורבלת עם נתונים נוספים.

כאמור נתון נוסף כמו זמן יצירת המשתמש הוא כניראה הנתון החסר, אבל מייקרוסופט לא מוכנים לפרסם את זה רישמית אז אנחנו חיים בעולם של קראקים, הינדוס אחורה וכו' כדי להגיע לנתון.

הנה דוגמה של שימוש ב PWDENCRYPT שראינו שמחזיר נתון שונה בכל פעם ובכל זאת שימוש בפונקציה ההפוכה PWDCOMPARE מאתר אם הנתון שלנו נכון או לא (פותח את הערבול ומפריד את הנתון החשוב מתוך השרשרת שכוללת נתון לא חשוב)

--DDL+DML
create table #PWDTEst (login varchar(20),UserPassword  nvarchar(256))
insert #PWDTEst values ( 'lapidi', PWDENCRYPT('SQLserverPassword1'))
 
--Testing
select login, password = 'SQLserverPassword1', PWDCOMPARE('SQLserverPassword1', UserPassword, 0) from #PWDTEst
select login, password = 'BadPassword', PWDCOMPARE('BadPassword', UserPassword, 0) from #PWDTEst

אני מקווה שהבלוג תרם מעט והיה מעניין

גלישה מהנה,

.

.

* אפשר גם לערבל את הקוד עם נתון רנדומלי כמובן!

במקרה זה פונקציית הקידוד אינה פונקציית HASH כאמור והערך החוזר אינו קבוע בהתאם לנתונים מתקבלים (כמו במקרה של שימוש בזמן כנתון נוסף). לכן במקרה כזה הפונקציה PWDCOMPARE לא תפעיל את הפונקציה PWDENCRYPT על מנת לבדוק את הנתון אלא פשוט תבצע פעולה ישירה הפוכה על פי האלגוריתים על מנת לבודד את הססמה מהנתון השמור.

השימוש במושג פונקציית HASH עבור פונקציית הקידוד מגיע מהספרות הרשמית BOL וכמובן מהתוצאה שנקראת במפורשת HASHED כפי שראינו בשאילתות. אך זה אינו בהכרח המצב המדוייק כאמור (עד שנראה את הקוד המדוייק).

** במציאות עירבול הקוד עם נתון נוסף לא מופיע בצורה כל כך פשוטה כמו שהצגתי אלא מהווה אלגורתמים שלם שאינו מפורסם רישמית מטעמים ברורים. בכל מקרה לי בשלב זה הוא לא ידוע וכאמור אלו רק הסברים על הערכות ואפשרויות.

*** הקוד של פונקציית ה HASH לא שוחרר לידעתי ואם רוצים אתו צריך לשכנע את מייקרוסופט לשחרר אותו (אין סיבה ש הם יבצעו את זה). בינתיים אפשר לבצע בדיקות על ניחושים, הינדוס לאחור לקוד ועוד כדי להגיע לאלגורתמים אבל החלק החשוב הוא כיצד להישתמש ולא מה יש מאחורי הקלעים (וזה כתוב בבלוג) אלא אם אנחנו מנסים לפתח שרת מתחרה ב SQL או סתם רוצים להבין את מאחורי הקלעים (אפשר לשלוח שאלה לצוות הפיתוח של SQL).

.

קישורים:

העתקה של INSTANCE שלם (כולל משתמשים) לשרת חדש
http://ariely.info/Blog/tabid/83/EntryId/93/COPY-SQL-instance-to-a-new-Instance.aspx

.

Abstract [English]

SQL Server uses hash encryption for its user's passwords. The password STRING is taken and scrambled up using a hash algorithm. the algorithm do not use only SID,user name and password's string so using those will not give us a clean user copy.

In order to get the hashed version of the password we can use the function:
LOGINPROPERTY(loginname, ‘PasswordHash’).

We will use CONVERT in order to copy our information to the new user
CONVERT(varchar(max), LOGINPROPERTY(MyLogin, 'PasswordHash'),1 )

in order to copy the password intact from one server to another we have to get the “hashed” version of the password and create the login with it, As shown above

.