SQL Server Always On Üzerinde Login oluşturmak
Merhabalar,
Bugün sizlere veri tabanı yönetimi konusunda takip edilmesi ve doğru yapılandırılması gereken, firma ve yazılımlarda güvenlik açısından büyük önem taşıyan,alt kırılımları doğru kullanıldığında ise bazı durumlarda günü kurtabilecek olan login yapısından bahsedeceğim.
Konuya tam anlamıyla giriş yapmadan önce,
Login oluştururken nelere dikkat etmeliyiz?
Konumuz veri tabanı olunca her şey ne eksik ne de fazla olmalı.Bu bakımdan kullanıcılarımızı da yetkilendirmeden önce nelere ihtiyacı olduğu iyi analiz edilmeli,çalışma alanının dışında bir yetkilendirme yapılmamalıdır.Zira sadece select sorgusu atacak bir kullanıcıya,sistem üzerinde tam yetkili olan sysadmin yetkisi vermek pek sağlıklı olmayacaktır.Farklı kişilerin eline geçmesi durumunda da ise sistemi komple teslim etmekten çok da farklı olmayacaktır.Bu bakımdan kullanıcı tanımlamalarında tablo-kolon bazında bile yetkilendirmeye inebiliyorken bu esnekliği mümkün olduğunca doğru kullanmalıyız.
Kullanıcıyı oluştururken vereceğimiz yetkiler belirlendikten sonra Login-User oluşturma aşamasına geçebiliriz.
Örneğin bir kullanıcı oluşturmayı ve bu kullanıcının database üzerinde sadece bir tabloya erişebilmesini istiyoruz.Bu durumda önce login oluşturmak ve daha sonrasında oluşturduğumuz bu login için user tanımlamalarını yapmamız gerekir.
Login-User farkı nedir ?
Bu soruyu örnek üzerinden daha net cevaplayabiliriz.Bir kişinin kendi evindeki odaya girebilmesi için öncelikle o dairenin olduğu binaya giriş yapabilecek anahtara sahip olması gerekir.Bu kişi öncelikle binadan giriş yapacak ve daha sonrasında dairesine ve hatta odalarına kadar erişebilecektir.İşte bu örnek üzerinde incelediğimiz binayı SQL SERVER,daireyi database,odaları ise tablo olarak düşünebiliriz.Server’a erişemeyen bir kullanıcı database ya da tablolara erişemeyecektir.O yüzden SQL sunucuya erişebilmemiz için server seviyesinde bir tanımlama olan login create etmeliyiz.
Databases altında yer alan Security kısmını açtığımızda Logins’e sağ tıklayıp New Login diyoruz.
Login oluştururken iki farklı yol üzerinden ilerleyebiliriz.
- Windows Authentication
- SQL Server Authentication
1.) Windows Authentication
Bu yöntem ile kullanıcılar direkt olarak Windows ya da Active Directory(AD) üzerinden yapılandırılmalı ve o şekilde eklenmelidir.Password belirlemeye gerek duyulmadan oluşturduğumuz kullanıcı, Windows ya da AD üzerinden doğrulanarak erişebilmektedir.
2.) SQL Server Authentication
Mixed mode olarak da adlandırabileceğimiz bu yöntemde ise belirlemiş olduğumuz login name ve password bilgilerini girerek SQL sunucumuza bağlanabiliriz.
Ben örnekler üzerinde ikinci yöntemden ilerleyeceğim.
Kullanıcımız database üzerinde sadece bir tabloya erişebileceği için Server Roles kısmından herhangi bir alanı işaretlemiyoruz.
Daha sonrasında User Mapping kısmına gelip hangi database üzerinde erişim sağlayabileceğini belirtiyoruz ve default schema belirliyoruz.Resimde yer alan 3 numaralı yere tıkladığımızda açılan sekmede dbo yazıp Check Names ile doğruladıktan sonra OK diyoruz.
Securables kısmına geçtikten sonra
şeklinde ilerliyoruz ve server seviyesinde login tanımlamasını tamamlıyoruz.Oluşturduğumuz login için yetkilendirdiğimiz tüm databaseler altında users oluşturuldu.Biz sadece AdventureWorks2019 veri tabanı için yetki verdiğimizden sadece burada tanımlanmış durumda.
Artık kullanıcımız binadan içeri girebildiğine göre yetkilendirme konusunda daire ve oda kırılımlarına kadar inebiliriz.Oluşturduğumuz users için sağ tıklayıp Properties diyoruz.
Açılan menüde resimdeki gibi ilerleyerek obje seviyesinde yetkilendirmeyi seçiyoruz.
Bu sekmede birçok alt kırılımlara sahip objeler mevcut.Ben tablo bazında yetkilendirme ile devam edeceğim.Fakat burada database seviyesinden tablo seviyesine,tablo seviyesinden sadece bir kolon seviyesine kadar birçok yetkilendirme alternatifi bulunmaktadır.
Tables seçerek ilerleyelim ve karşımıza gelen menüden Customer tablosunda select yetkisi vererek devam edelim.
3 numaralı alanda gösterilen kısımda Customer tablosunda yer alan kolonların hangisine select sorgusu atmak istiyorsak onu belirtiyoruz.3 numaralı alanda gösterdiğimiz yere tıklamadan Customer tablosunda sadece select yetkisi vererek yetkilendirmemizi tamamlıyoruz.
Oluşturduğumuz kullanıcı ile veri tabanına bağlanalım ve başka tablolara sorgu atabilecek miyiz ya da Customer tablosunda delete sorgusu kullanabiliyor muyuz görelim.
SSMS üzerinde Object Explorer–>Connect–>Database Engine olarak ilerliyoruz.Gelen menüde SQL Server Authentication seçiyoruz.Oluşturduğumuz login için bilgileri giriyoruz ve connect diyerek bağlanıyoruz.
Görüldüğü üzere,kullanıcımız sadece Customer tablosunu görebilmekte.Şimdi bu kullanıcı ile bir satır silmeye çalışalım.
Bu kullanıcı ile tablodan data silmeye çalıştığımızda iznimiz olmadığına dair hata verdi.İstediğimiz şekilde kullanıcıyı oluşturmuş olduk.
SQL Server arka tarafta bu kullanıcı tanımlamalar ile ilgili olarak nasıl ilerliyor ona bakalım.
SID Kimliği
Sistem üzerinde oluşturulan her kullanıcı için unique bir key oluşturulmaktadır. SID(Security Identifier) olarak adlandırdığımız bu kimliği,her login olduğumuzda sistem kontrol eder ve eğer sunucuda kayıtlı kullanıcı ile eşleşiyor ise veri tabanına erişebiliriz.
Login olduğumuz kullanıcının SID bilgisine erişmek istersek ;
SELECT SUSER_SID()
sorgusu ile ulaşabiliriz.
Sunucuya erişim sağladığımız kullanıcının yetkilerine bağlı olarak , eğer sadece bu kullanıcı değil de oluşturulmuş olan kullanıcıların SID değerlerine görmek istersek kullanacağımız sorguyu
SELECT [NAME],SID FROM syslogins
şeklinde değiştirmemiz yeterlidir.
Bir sunucu üzerinde oluşturduğumuz kullanıcının SID değeri fazla önem taşımasa da birden çok sunucu ile sync çalışan Always On gibi bir mimaride ya da oluşturduğumuz kullanıcıyı başka bir sunucuda oluşturmak durumunda kaldığımızda bu SID değerini korumamız gereklidir.
Çalıştığımız şirkette/organizasyonda veri tabanında Always On yapısını kullanarak sunucuya kesintisiz hizmet verildiğini varsayalım.
Bu durumda bir adet Primary ve birden fazla Secondary node olabilir.Oluşturulmuş olan bu yapı sync çalışarak birbirini takip eder.Dataları önce Secondary (AO-2) üzerinde yürütür daha sonra Primary(AO-1) sunucu üzerinde işleyerek yazma işlemini sonlandırır.Primary sunucuda herhangi bir problem yaşanıp failover olduğunda ise listener secondary node ile iletişime geçer ve secondary node üzerinde hizmet vermeye devam eder.Bu işlem saniye bazında gerçekleştiği için kesintiler DBA dışında genelde farkedilmez.
Always On üzerinde senkron çalışan databaseler datayı her iki sunucuda da tutmaktadır.Fakat burada veriler ve database seviyesindeki objeler eşleniyor olsa da kesinti anında senkron olmayan bazı obje ve servisler vardır.
Örneğin sistemi otomatize etmek için kullandığımız SQL Server Agent servisiyle çalıştırdığımız job ve server seviyesinde bir obje olan Login bilgileri kesinti yaşandığında Secondary üzerinden sorunsuz devam edememektedir.Failover olup AO-2 sunucumuz primary olduğunda bazen kullanıcılar SQL’e bağlanırken hata almaktadır.
Bu hata nerden kaynaklanmaktadır ?
Always On yapısından bahsederken birden fazla sunucu üzerinde kurulu yapı olduğundan bahsetmiştik.Ve bu database seviyesindeki objelerin sync olduğunu,server seviyesindeki objelerden login bilgilerinin sync olmadığı için bilgilerimizin diğer sunucuya aktarılmadığını belirtmiştik.Bu sebeple Primary sunucu üzerinde oluşturduğumuz kullanıcıyı AG üzerinde yer alan diğer sunucularda da create etmeliyiz.
Yazılımlarımızda connection string ile veri tabanına bağlarken kullandığımız login bilgilerinin olası bir kesintiden etkilenmemesi önemlidir.Bu sebeple diğer sunucularımızda da oluşturacağımız aynı login için SID değerlerini korumamız gerekmektedir.
Burada önemli birkaç detay bulunmaktadır.
Active Directory üzerinden Windows Authentication ile oluşturulan kullanıcı için bir SID değeri verilir ve secondary node üzerinde de oluşturduğumuzda bu değerler eşleşmektedir.Fakat SQL Server Authentication ile kullanıcı oluşturduğumuzda burada SID değeri korunmamaktadır.
Secondary üzerinde oluşturacağımız kullanıcı için Primary de oluşturulmuş olan SID değeriyle kullanıcıyı oluşturmalıyız.
Primary üzerinden ;
SELECT [NAME],SID FROM syslogins
sorgusunu çalıştırarak istediğimiz kullanıcı için SID değerini alıyoruz.
Bu SID değeriyle Secondary node üzerinde create login scriptimizi düzenliyoruz.
CREATE LOGIN oluşturulacak_login_name WITH PASSWORD = ‘primary_password’, SID = kopyalanan_SID_degeri
Bu şekilde oluşturacağımız kullanıcı ile primary-secondary node üzerinde yer alan login bilgilerimiz eşleşecek ve bize hata sunmayacaktır.Artık her iki sunucuda da bu login bilgisi ile erişebiliriz.
Bonus :
Yeni bir Always On yapısı aktifleştirdiğimizde ya da SQL Server hizmetini başka bir sunucuya geçirmek istediğimizde birden fazla kullanıcı ile tek tek SID değerini koruyarak login oluşturmak oldukça zahmetli olacaktır. Bunun yerine bir sp yardımıyla login create scriptlerini SID değerleri ile birlikte alabiliriz.Bu create scriptlerini toplu olarak ya da sadece istediğimiz login için secondary sunucularda/SQL Server taşıyacağımız yeni sunucularda çalıştırıp kullanıcılarımızı oluşturabiliriz.
Bazı durumlarda ise daha önceden başkası tarafından oluşturulmuş ve çalışma arkadaşınız/hizmet aldığınız firma ile bağlantınız kesildiği için parolasını bilmediğiniz kullanıcılara sahip olabilirsiniz.Bu kullanıcı bilgilerini de password bilmeseniz dahi hash olarak tutulan password bilgisi ile yeni sunucularda oluşturabilir ve çalışmaya devam edebilirsiniz.
USE master
GO
IF OBJECT_ID (‘sp_hexadecimal’) IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = ‘0x’
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = ‘0123456789ABCDEF’
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint – (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
IF OBJECT_ID (‘sp_help_revlogin’) IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary varbinary (256)
DECLARE @PWD_string varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)
DECLARE @defaultdb sysname
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( ‘S’, ‘G’, ‘U’ ) AND p.name <> ‘sa’
ELSE
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( ‘S’, ‘G’, ‘U’ ) AND p.name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
PRINT ‘No login(s) found.’
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = ‘/* sp_help_revlogin script ‘
PRINT @tmpstr
SET @tmpstr = ‘** Generated ‘ + CONVERT (varchar, GETDATE()) + ‘ on ‘ + @@SERVERNAME + ‘*/’
PRINT @tmpstr
PRINT ”
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ”
SET @tmpstr = ‘– Login: ‘ + @name
PRINT @tmpstr
IF (@type IN ( ‘G’, ‘U’))
BEGIN — NT authenticated account/group
SET @tmpstr = ‘CREATE LOGIN ‘ + QUOTENAME( @name ) + ‘ FROM WINDOWS WITH DEFAULT_DATABASE = [‘ + @defaultdb + ‘]’
END
ELSE BEGIN — SQL Server authentication
— obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, ‘PasswordHash’ ) AS varbinary (256))
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
— obtain password policy state
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
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
SET @tmpstr = ‘CREATE LOGIN ‘ + QUOTENAME( @name ) + ‘ WITH PASSWORD = ‘ + @PWD_string + ‘ HASHED, SID = ‘ + @SID_string + ‘, DEFAULT_DATABASE = [‘ + @defaultdb + ‘]’
IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ‘, CHECK_POLICY = ‘ + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ‘, CHECK_EXPIRATION = ‘ + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN — login is denied access
SET @tmpstr = @tmpstr + ‘; DENY CONNECT SQL TO ‘ + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN — login exists but does not have access
SET @tmpstr = @tmpstr + ‘; REVOKE CONNECT SQL TO ‘ + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN — login is disabled
SET @tmpstr = @tmpstr + ‘; ALTER LOGIN ‘ + QUOTENAME( @name ) + ‘ DISABLE’
END
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
Bu script çalıştığında master database altında iki adet sp oluşmaktadır.
exec sp_help_revlogin
ile yetkiniz dahilinde görüntülemenize izin verilen kullanıcıların create scriptlerini elde edebilir ve başka bir ortamda create edebilirsiniz.
Umarım faydalı olmuştur.
Hoşça kalın.