Ένα από τα πολλά θέματα της καθημερινότητας ενός μάχιμου DBA είναι η διαχείριση των mismatched security id.

SQL Server Logins

Είναι γνωστό και το έχω αναλύσει στο sql night αυτό  ότι για να αποκτήσει κάποιος πρόσβαση σε ένα SQL instance θα πρέπει να έχει ένα login account σε αυτό.

Ένα τέτοιο account μπορεί να είναι είτε ένα domain/windows account (domain login )είτε ένα sql server account (sql login).

Σε κάθε περίπτωση αυτά αποθηκεύονται στην master database  και μπορούμε να τα δούμε ρωτώντας το sys.server_principals system view ή μέσα από τον SSMS Object Explorer > Security >Logins.

Από τις πληροφορίες που υπάρχουν στο system view μας ενδιαφέρουν για το post αυτό δύο, και αυτές βρίσκονται στα πεδία name και sid.

SELECT name, principal_id, sid 
FROM sys.server_principals;
GO

To SID είναι το σημαντικότερο και έχει ακριβώς την ίδια έννοια που έχουμε στο μυαλό μας με τα SIDs των domain/windows accounts.

Μάλιστα όταν στο SQL Server instance φτιάχνουμε ένα domain login τότε στον SQL Server αποθηκεύεται το SID του domain/window account.

Όταν φτιάχνουμε ένα sql login τότε σε αυτό δίνεται ένα νέο SID. Το ίδιο θα συμβεί και για τα domain logins αν τα σβήσουμε και τα ξαναφτιάξουμε στο AD.

Αυτό σημαίνει ότι αν έχουμε φτιάξει ένα domain account και σε αυτό δώσουμε πρόσβαση στο SQL server φτιάχνοντας ένα login και μετά το σβήσουμε και το ξαναφτιάξουμε στο AD αυτό το νέο δεν θα έχει πρόσβαση στο SQL server καθώς θα έχει ένα νέο SID.

SQL Database Users

Αν τώρα σε κάποιο login θέλουμε να του δώσουμε πρόσβαση σε μια database θα πρέπει σε αυτή να φτιάξουμε ένα  database user που θα κάνει map σε ένα sql login.
Αυτά μπορούμε να τα δούμε ρωτώντας το sys.database_principals system view ή από τον SSMS Object explorer > Databases > Database name > Security > Users.

SELECT name, principal_id, sid 
FROM sys.database_principals;
GO

Mismatched Security IDs

Όλο αυτό το "δέσιμο" γίνεται με την χρήση του SID και όχι με το όνομα όπως αρκετοί πιστεύουν.

Έτσι όταν φτιάχνουμε ένα login, όπως αναφέρθηκε και παραπάνω, αν είναι domain login παίρνει το SID που αυτό έχει στο domain αν είναι sql login τότε ο SQL Server δίνει ένα SID.  Αυτά τα logins όταν θα γίνουν database users θα μεταφέρουν τα SID τους στα database user accounts.

Αν μετά από την δημιουργία ενός sql login και δίνοντας σε αυτό πρόσβαση σε μια database αν εκτελέσουμε τα παραπάνω queries το πρώτο στην master και στο δεύτερο στην database θα δούμε ότι θα έχουν το ίδιο SID.

Έστω ότι έχουμε δύο accounts στα οποία θέλουμε να δώσουμε πρόσβαση στον SQL Server το ένα είναι domain account (SQLDOMAIN\dbuser) και το άλλο είναι sql account (sqluser).

Αυτό μπορεί να γίνει είτε μέσα από τον SSMS είτε με το παρακάτω script

USE [master]
GO
CREATE LOGIN [SQLDOMAIN\dbuser] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
CREATE LOGIN [sqluser] WITH PASSWORD=N'Pa$$w0rd', DEFAULT_DATABASE=[master]
GO

Αν ρωτήσουμε  το sys.server_principals θα έχουμε το παρακάτω αποτέλεσμα (Query 1 results).

Αν τώρα φτιάξουμε μια βάση την SIDTest και τα παραπάνω logins τα δημιουργήσουμε σαν database users

USE [master];
GO
CREATE DATABASE SIDTest;
GO

USE [SIDTest]
GO
CREATE USER [dbuser] FOR LOGIN [SQLDOMAIN\dbuser]
GO
CREATE USER [sqluser] FOR LOGIN [sqluser]
GO

Και σε αυτή ρωτήσουμε το sys.database_principals system view θα έχουμε το παρακάτω αποτέλεσμα (Query 2 Results).

Παρατηρήστε τα SIDs είναι τα ίδια.

Αν τώρα σβήσω αυτά από τα logins και τα ξαναδημιουργήσω

USE [master]
GO
DROP LOGIN [SQLDOMAIN\dbuser] 
GO
DROP LOGIN [sqluser]
GO
CREATE LOGIN [SQLDOMAIN\dbuser] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
CREATE LOGIN [sqluser] WITH PASSWORD=N'Pa$$w0rd', DEFAULT_DATABASE=[master]
GO

και ρωτήσω το sys.server_principals παρατηρώ ότι το domain account συνεχίζει να έχει το ίδιο SID ενώ το sql account έχει άλλο νέο SID (Query 3 )

Αν ρωτήσω και τον sys.database_principals  (Query 4) θα δω ότι πλέον έχω διαφορές στα SID και μάλιστα σε αυτό του sqluser καθώς αυτός είναι sql account και σβήστηκε πραγματικά. Ο domain user δεν σβήστηκε από το domain άρα παραμένει με το ίδιο SID.

Αυτό σημαίνει ότι ο dbuser θα συνεχίζει να έχει πρόσβαση στην SIDTest ενώ ο sqluser όχι.

Για να ξαναποκτήσει πρόσβαση σε αυτή ο sqluser θα πρέπει να συγχρονιστούν τα SIDs, αυτό που υπάρχει στην master με αυτό που υπάρχει στην SIDTest database.
Αυτό μπορεί να γίνει εκτελώντας την παρακάτω εντολή

USE [SIDTest]
GO
ALTER USER [sqluser] WITH LOGIN = [sqluser];
GO

Εάν ρωτήσω πάλι τον sys.database_principals  (Query 5) θα παρατηρήσω ότι το SID είναι πλέον το ίδιο με αυτό που έχω στην master και έτσι πλέον ο sqluser θα έχει πρόσβαση στην SIDTest.

Αν τώρα σβήσω από το domain τον SQLDOMAIN\dbuser και από τον SQL Server και τον ξαναφτιάξω και τον βάλω στον SQL server θα δω (query 6) ότι έχει διαφορετικό SID και δεν θα μπορεί να έχει πρόσβαση  στην SIDTest

Προσέξτε στο τέλος είναι η αλλαγή D25C040000 -> D25D040000

Αυτός ο χρήστης πλέον δεν έχει προσβαση στην SIDTest. Για να αποκτήσει πρέπει να εκτελέσω το παρακάτω

USE [SIDTest]
GO
ALTER USER [dbuser] WITH LOGIN = [SQLDOMAIN\dbuser]
GO

Αν τώρα ρωτήσω το sys.database_principals (Query 7) θα δω ότι και σε αυτόν έχει γίνει sync το SID με αυτό της master.

Αυτό λοιπόν είναι το mismatched security id και έτσι μπορεί να λυθεί.

Άλλοι τρόποι εμφάνισης και αντιμετώπισης

Αυτό μπορεί να συμβεί είτε με τον παραπάνω περιγραφόμενο τρόπο είτε όταν παίρνουμε backup μια βάση από ένα instance και την πάμε σε άλλο instance που είτε δεν υπάρχουν τα logins αυτά σε αυτό, είτε υπάρχουν αλλά έχουν άλλο SID, είτε γιατί είμαστε σε άλλο domain είτε γιατί έχουμε ήδη το sql login είτε τέλος δεν έχει τίποτα από τα δύο.

Αν δεν υπάρχουν τα δημιουργούμε σαν logins και κάνουμε την διαδικασία συγχρονισμού όπως παραπάνω, αλλιώς απλά συγχρονίζουμε.

Στην περίπτωση που δεν υπάρχουν τα logins  στο μεταφερόμενο instance και ειδικά για τα sql logins αυτά μπορούν να δημιουργηθούν μεταφέροντας και SID οπότε δεν χρειάζεται και η διαδικασία συγχρονισμού. Αυτό μπορεί να γίνει ακολουθώντας την διαδικασία που έχω περιγράψει στο άρθρο αυτό

Εκτελώντας την stored procedure που αναφέρω στο άρθρο μπορούμε να πάρουμε το sqluser χρήστη και να τον μεταφέρουμε στο νέο server καθώς θα δείτε ότι μεταφέρει και τo SID όπως στο παρακάτω script

CREATE LOGIN [sqluser] WITH PASSWORD = 0x02000902795319C4B1CF08EFDAE18E2909B2C830B8FE8485B66C57E2FDF638BB7F59FADB4F18C11F812C7898DDD1F736298BD32A84B6DB013F00EAA5A5E925CD4A31ED4B95A7 HASHED,
 
SID = 0xF1E9CEF3C6A58F4F81F70CCC38FAB098, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF 

Για τις παλαιότερες εκδόσεις του SQL Server (πριν το SQL Server 2005 SP2 ) υπάρχει η  sp_change_users_login που είναι πλέον deprecate

/*antonch*/