Veritabanı yöneticileri (DBA) için birden fazla sunucunun senkronize tutulması en kritik görevlerden biridir. Özellikle iki farklı SQL Server Instance’ı arasında login isimleri aynı olsa bile, arka plandaki SID (Security Identifier) değerlerinin farklı olması, veritabanı taşımalarında “Orphaned User” (Sahipsiz Kullanıcı) hatalarına yol açar.
Bu makalede, Linked Server kullanarak iki sunucu arasındaki login farklarını ve SID tutarsızlıklarını nasıl tespit edeceğimizi inceleyeceğiz.
Karşılaştırmayı tek bir merkezden yapabilmek için birinci sunucunuz üzerinden ikinci sunucuya bir bağlantı (Linked Server) tanımlanmış olmalıdır. Aşağıdaki komut ile linked server bağlantısı sağlanır.
EXEC sp_addlinkedserver
@server = N'S2\INST01',
@srvproduct = N'SQL Server';
GO
Aşağıdaki komut ile her iki sunucudaki sys.server_principals tablosunu tarayarak login isimlerini eşleştirir ve durumlarını raporlar.
SELECT
COALESCE(S1.name, S2.name) AS LoginName,
CASE
WHEN S1.name IS NULL THEN 'Sadece 2. Sunucuda Var'
WHEN S2.name IS NULL THEN 'Sadece 1. Sunucuda Var'
WHEN S1.sid <> S2.sid THEN 'SID Uyuşmazlığı Var!'
ELSE 'Eşleşiyor'
END AS Durum,
S1.sid AS Sunucu1_SID,
S2.name as Sunucu2_Login,
S2.sid AS Sunucu2_SID,
S1.type_desc AS LoginType
FROM sys.server_principals S1
FULL OUTER JOIN [S2\INST01].master.sys.server_principals S2
ON S1.name = S2.name
WHERE COALESCE(S1.type, S2.type) IN ('S', 'U', 'G') -- SQL Login, Windows User, Windows Group
AND COALESCE(S1.name, S2.name) NOT LIKE '##%' -- Sistem loginlerini filtrele
AND COALESCE(S1.name, S2.name) NOT LIKE 'NT %'
ORDER BY Durum, LoginName;Sorgu sonucundan bazı loginlerin eşleştiği bazı loginlerin ise hangi sunucuda olduğu bilgisi verilmektedir.
Secondary sunucusunda farklı bir sid ile login3 kullanıcısı oluşturulduğunda sid uyuşmazlığı olduğunu söylemektedir.
USE [master]
GO
CREATE LOGIN [login3] WITH PASSWORD=N'1', sid=0x8EC7C26C1714574DB1ADD79D6833554b, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
Sunucu1 üzerinde bulunan sid ile login3 kullanıcısı secondary sunucusunda oluşturulduğunda eşleştiğini söylemektedir.
Tespit edilen farkları gidermek için şu yöntemleri izleyebilirsiniz:
- Microsoft tarafından sağlanan sp_help_revlogin prosedürü ile loginleri şifreleri ve SID değerleri ile beraber script haline getirip diğer sunucuda çalıştırabilirsiniz.
- dbatools (PowerShell) Copy-DbaLogin komutu ile tüm loginleri ve yetkilerini saniyeler içinde senkronize edebilirsiniz.
- SID uyuşmazlığı olan veritabanlarında ALTER USER komutu ile eşleşmeyi manuel düzeltebilirsiniz.
ALTER USER [login3] WITH LOGIN = [Login3]Veritabanı tutarlılığı sadece verinin kopyalanması değil, erişim katmanının da birebir aynı olmasıdır.
Loginleri karşılaştırmak için bir başka script:
SELECT
ISNULL(L.name, R.name) AS [Kullanici Adi],
CASE
WHEN L.name IS NOT NULL AND R.name IS NOT NULL THEN 'Her Iki Sunucuda Var'
WHEN L.name IS NOT NULL THEN 'Sadece Local Sunucuda'
ELSE 'Sadece Uzak Sunucuda'
END AS [Durum],
L.type_desc AS [Local Tip],
R.type_desc AS [Uzak Tip],
L.is_disabled AS [Local Engelli mi?],
R.is_disabled AS [Uzak Engelli mi?]
FROM
sys.server_principals L
FULL OUTER JOIN
[172.22.6.13].master.sys.server_principals R ON L.name = R.name
WHERE
ISNULL(L.type, R.type) IN ('S', 'U', 'G') -- S: SQL Login, U: Windows Login, G: Windows Group
AND ISNULL(L.name, R.name) NOT LIKE '##%' -- Sistem kullanicilarini gizle
ORDER BY 1;
--------------------------------------------------------------------------
--------------------------------------------------------------------------
SELECT
ISNULL(L.name, R.name) AS [Kullanici Adi],
CASE
WHEN L.name IS NOT NULL AND R.name IS NOT NULL THEN 'Her Iki Sunucuda Var'
WHEN L.name IS NOT NULL THEN 'Sadece Local Sunucuda'
ELSE 'Sadece Uzak Sunucuda'
END AS [Varlik Durumu],
-- SID Kontrolü (Migration için en kritik olan)
CASE
WHEN L.sid IS NULL OR R.sid IS NULL THEN 'Eksik'
WHEN L.sid = R.sid THEN 'ESLESIYOR'
ELSE 'FARKLI (Riskli)'
END AS [SID Uyumu],
-- Sifre Hash Kontrolü
CASE
WHEN L.type <> 'S' OR R.type <> 'S' THEN 'Windows Auth'
WHEN L.password_hash IS NULL OR R.password_hash IS NULL THEN 'Eksik'
WHEN L.password_hash = R.password_hash THEN 'Ayni'
ELSE 'FARKLI'
END AS [Sifre Uyumu],
L.type_desc AS [Tip],
L.sid AS [Local SID],
R.sid AS [Uzak SID],
L.password_hash AS [Local Hash]
FROM
sys.sql_logins L
FULL OUTER JOIN
[172.22.6.13].master.sys.sql_logins R ON L.name = R.name
WHERE
ISNULL(L.name, R.name) NOT LIKE '##%'
AND ISNULL(L.name, R.name) NOT LIKE 'NT %'
ORDER BY [SID Uyumu] ASC, [Sifre Uyumu] ASC;Gelen sorgu sonucunda password hashed bölümlerinin eş olmadığını belirtirse ne yapılması lazım. Bu durum tamamen SQL Server’ın parola karma (hashing) mekanizmasının çalışma prensibinden kaynaklanır. Şifreler tamamen aynı olsa bile iki farklı sunucu veya kullanıcı arasında hash değerleri farklı çıkar.
Temel nedenler şunlardır:
- Rastgele Tuzlama (Salt Value): SQL Server, şifreyi doğrudan hash’lemez. Şifreleme algoritması (SHA-512), şifrenin başına veya sonuna 4 baytlık rastgele üretilen bir “salt” (tuz) değeri ekler. Şifre tam olarak aynı olsa bile her hash oluşturma işleminde rastgele yeni bir tuz üretildiği için çıkan binary hash tamamen farklı olur.
SQL Server Hash Yapısı: Görseldeki 0x0200… ile başlayan yapıyı incelersek:
- Header (4 Bayt): Algoritma sürümüdür (0x0200 SQL Server 2012 ve sonrası SHA-512’yi temsil eder).
- Salt (4 Bayt): Her şifre oluşturma/değiştirmede rastgele atanan tuz değeridir.
- Password Hash (64 Bayt): SHA-512(Şifre + Salt) sonucudur. Bu Duruma Yol Açan Senaryolar; Farklı Zamanlarda Şifre Belirleme: Aynı parolaya sahip olsalar bile iki kullanıcı farklı anlarda oluştu
Bu makalede çok node’lu sunucularda sid uyuşmazlığı ve kullanıcı sorunlarında neleryapılması gerektiğini görmüş olduk. Başka makalede görüşmek dileğiyle..
“Şüphesiz güçlükle beraber bir kolaylık vardır. Gerçekten, güçlükle beraber bir kolaylık vardır.” İnşirah Suresi; 5-6. Ayet
