Oturum açma bilgilerini ve parolaları SQL Server örnekleri arasında aktarma
Bu makalede, Windows üzerinde çalışan farklı SQL Server örnekleri arasında oturum açma bilgilerinin ve parolaların nasıl aktarıldığı açıklanır.
Özgün ürün sürümü: SQL Server
Orijinal BB numarası: 918992, 246133
Giriş
Bu makalede, oturum açma bilgilerinin ve parolaların farklı Microsoft SQL Server örnekleri arasında nasıl aktarıldığı açıklanır.
Not
Örnekler aynı sunucuda veya farklı sunucularda olabilir ve sürümleri farklı olabilir.
Daha fazla bilgi
Bu makalede, A sunucusu ve B sunucusu farklı sunuculardır.
Bir veritabanını A sunucusundaki SQL Server örneğinden B sunucusundaki SQL Server örneğine taşıdığınızda, kullanıcılar B sunucusundaki veritabanında oturum açamayabilir. Ayrıca, kullanıcılar aşağıdaki hata iletisini alabilir:
'MyUser' kullanıcısı oturum açamadı. (Microsoft SQL Server, Hata: 18456)
Bu sorun, oturum açma bilgilerini ve parolaları A sunucusundaki SQL Server örneğinden B sunucusundaki SQL Server örneğine aktarmadığınız için oluşur.
Not
18456 hata iletisi diğer nedenlerden dolayı da oluşur. Bu nedenler ve olası çözümler hakkında ek bilgi için bkz. MSSQLSERVER_18456.
Oturum açma bilgilerini aktarmak için aşağıdaki yöntemlerden durumunuza uygun olanını kullanın.
Yöntem 1: Hedef SQL Server bilgisayarda (Sunucu B) parolayı sıfırlayın.
Bu sorunu çözmek için SQL Server bilgisayarında parolayı sıfırlayın ve oturum açma işleminin betiğini oluşturun.
Not
Parola karması algoritması, parolayı sıfırladığınızda kullanılır.
Yöntem 2: Kaynak sunucuda (Sunucu A) oluşturulan betikleri kullanarak oturum açma bilgilerini ve parolaları hedef sunucuya (Sunucu B) aktarma.
Oturum açma bilgilerini ve parolalarını aktarmak için gerekli betiklerin oluşturulmasına yardımcı olacak saklı yordamlar oluşturun. Bunu yapmak için SQL Server Management Studio (SSMS) veya başka bir istemci aracı kullanarak Sunucu A'ya bağlanın ve aşağıdaki betiği çalıştırın:
USE [master] GO IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL DROP PROCEDURE sp_hexadecimal GO CREATE PROCEDURE [dbo].[sp_hexadecimal] ( @binvalue varbinary(256), @hexvalue varchar (514) OUTPUT ) AS BEGIN 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 END go IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL DROP PROCEDURE sp_help_revlogin GO CREATE PROCEDURE [dbo].[sp_help_revlogin] ( @login_name sysname = NULL ) AS BEGIN 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 @Prefix VARCHAR(255) DECLARE @defaultdb SYSNAME DECLARE @defaultlanguage SYSNAME DECLARE @tmpstrRole VARCHAR (1024) IF (@login_name IS NULL) BEGIN DECLARE login_curs CURSOR FOR SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin, p.default_language_name 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' AND p.name not like '##%' ORDER BY p.name END ELSE DECLARE login_curs CURSOR FOR SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin, p.default_language_name 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 ORDER BY p.name OPEN login_curs FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin, @defaultlanguage 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 SET @tmpstr='IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'''+@name+''') BEGIN' Print @tmpstr IF (@type IN ( 'G', 'U')) BEGIN -- NT authenticated account/group SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']' + ', DEFAULT_LANGUAGE = [' + @defaultlanguage + ']' 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 + ']' + ', DEFAULT_LANGUAGE = [' + @defaultlanguage + ']' 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 SET @Prefix = ' EXEC master.dbo.sp_addsrvrolemember @loginame=''' SET @tmpstrRole='' SELECT @tmpstrRole = @tmpstrRole + CASE WHEN sysadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''sysadmin''' ELSE '' END + CASE WHEN securityadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''securityadmin''' ELSE '' END + CASE WHEN serveradmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''serveradmin''' ELSE '' END + CASE WHEN setupadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''setupadmin''' ELSE '' END + CASE WHEN processadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''processadmin''' ELSE '' END + CASE WHEN diskadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''diskadmin''' ELSE '' END + CASE WHEN dbcreator = 1 THEN @Prefix + [LoginName] + ''', @rolename=''dbcreator''' ELSE '' END + CASE WHEN bulkadmin = 1 THEN @Prefix + [LoginName] + ''', @rolename=''bulkadmin''' ELSE '' END FROM ( SELECT CONVERT(VARCHAR(100),SUSER_SNAME(sid)) AS [LoginName], sysadmin, securityadmin, serveradmin, setupadmin, processadmin, diskadmin, dbcreator, bulkadmin FROM sys.syslogins WHERE ( sysadmin<>0 OR securityadmin<>0 OR serveradmin<>0 OR setupadmin <>0 OR processadmin <>0 OR diskadmin<>0 OR dbcreator<>0 OR bulkadmin<>0 ) AND name=@name ) L PRINT @tmpstr PRINT @tmpstrRole PRINT 'END' END FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin, @defaultlanguage END CLOSE login_curs DEALLOCATE login_curs RETURN 0 END
Not
Bu betik, ana veri tabanında iki saklı yordam oluşturur. Yordamlar sp_hexadecimal ve sp_help_revlogin olarak adlandırılır.
SSMS sorgu düzenleyicisinde Sonuçlardan Metne seçeneğini belirleyin.
Aşağıdaki deyimi aynı veya yeni bir sorgu penceresinde çalıştırın:
EXEC sp_help_revlogin
sp_help_revlogin
saklı yordamının oluşturduğu çıkış betiği, oturum açma betiğidir. Bu oturum açma betiği, özgün Güvenlik Tanımlayıcısı (SID) ve özgün parolaya sahip oturum açma bilgilerini oluşturur.
Önemli
Hedef sunucuda adımları uygulamaya devam etmeden önce aşağıdaki Açıklamalar bölümündeki bilgileri gözden geçirin.
Hedef sunucudaki (B Sunucusu) adımlar
Herhangi bir istemci aracı (SSMS gibi) kullanarak Sunucu B'ye bağlanın ve ardından 4. adımda oluşturulan betiği (çıktısı sp_helprevlogin
) Sunucu A'dan çalıştırın.
Açıklamalar
B sunucusundaki örnekte çıkış betiğini çalıştırmadan önce aşağıdaki bilgileri gözden geçirin:
Parola aşağıdaki yollarla karma olarak oluşturulabilir:
VERSION_SHA1
: Bu karma SHA1 algoritması kullanılarak oluşturulur ve SQL Server 2000 ile SQL Server 2008 R2 arasında kullanılır.VERSION_SHA2
: Bu karma SHA2 512 algoritması kullanılarak oluşturulur ve SQL Server 2012 ve sonraki sürümlerde kullanılır.
Çıkış betiğini dikkatle gözden geçirin. A sunucusu ve B sunucusu farklı etki alanlarındaysa çıkış betiğini değiştirmeniz gerekir. Ardından, deyimlerinde yeni etki alanı adını kullanarak özgün etki alanı adını
CREATE LOGIN
değiştirmeniz gerekir. Yeni etki alanında erişim verilen tümleşik oturum açma işlemleri, özgün etki alanındaki oturum açma bilgileriyle aynı SID'ye sahip değildir. Bu nedenle kullanıcılar bu oturum açmalardan yalnız bırakılır. Bu yalnız bırakılmış kullanıcıları çözme hakkında daha fazla bilgi için bkz. Yalnız bırakılmış kullanıcıların sorunlarını giderme (SQL Server) ve ALTER USER.
A sunucusu ve B sunucusu aynı etki alanındaysa aynı SID kullanılır. Bu nedenle kullanıcıların yalnız bırakılma olasılığı düşüktür.Çıkış betiğinde oturum açma işlemleri şifrelenmiş parola kullanılarak oluşturulur. Bunun nedeni
CREATE LOGIN
deyimindeki HASHED bağımsız değişkenidir. Bu bağımsız değişken, PASSWORD bağımsız değişkeninden sonra girilen parolanın zaten karma olduğunu belirtir.Varsayılan olarak, sysadmin sabit sunucu rolünün bir üyesi yalnızca
sys.server_principals
görünümünden birSELECT
deyimi çalıştırabilir. Sysadmin sabit sunucu rolünün bir üyesi kullanıcılara gerekli izinleri vermediği sürece, kullanıcılar çıkış betiğini oluşturamaz veya çalıştıramaz.Bu makaledeki adımlar, belirli bir oturum açma için varsayılan veritabanı bilgilerini aktarmaz. Bunun nedeni, varsayılan veritabanının B sunucusunda her zaman mevcut olmamasıdır. Oturum açma için varsayılan veritabanını tanımlamak için, oturum açma adını ve varsayılan veritabanını bağımsız değişken olarak geçirerek deyimini kullanın
ALTER LOGIN
.Kaynak ve hedef sunuculardaki sıralama düzenleri:
Büyük/küçük harfe duyarlı olmayan sunucu A ve büyük/küçük harfe duyarlı sunucu B: A sunucusunun sıralama düzeni büyük/küçük harfe duyarlı olmayabilir ve B sunucusunun sıralama düzeni büyük/küçük harfe duyarlı olabilir. Bu durumda, oturum açma bilgilerini ve parolaları B sunucusundaki örneğe aktardıktan sonra kullanıcıların parolaları tümüyle büyük harflerle yazması gerekir.
Büyük/küçük harfe duyarlı sunucu A ve büyük/küçük harfe duyarlı olmayan sunucu B: A sunucusunun sıralama düzeni büyük/küçük harfe duyarlı olabilir ve B sunucusunun sıralama düzeni büyük/küçük harfe duyarlı olmayabilir. Bu durumda, aşağıdaki koşullardan biri doğru olmadığı sürece kullanıcılar B sunucusundaki örneğe aktardığınız oturum açma bilgilerini ve parolaları kullanarak oturum açamaz:
- Özgün parolalarda harf yoktur.
- Özgün parolalardaki tüm harfler büyük harftir.
Her iki sunucuda da büyük/küçük harfe duyarlı veya büyük/küçük harfe duyarlı olmayabilir: Hem A sunucusunun hem de B sunucusunun sıralama düzeni büyük/küçük harfe duyarlı olabilir veya hem A sunucusunun hem de B sunucusunun sıralama düzeni büyük/küçük harfe duyarlı olmayabilir. Bu gibi durumlarda kullanıcılar sorun yaşamaz.
B sunucusundaki örnekte bulunan bir oturum açma işleminin adı çıktı betiğindeki adla aynı olabilir. Bu durumda, B sunucusundaki örnekte çıktı betiğini çalıştırdığınızda aşağıdaki hata iletisini alırsınız:
Msg 15025, Seviye 16, Durum 1, Satır 1 Tablo hatası:
'MyLogin' sunucu sorumlusu zaten var.Benzer şekilde, B sunucusundaki örnekte zaten bulunan bir oturum açma işlemi, çıkış betiğindeki SID ile aynı sid'ye sahip olabilir. Bu durumda, B sunucusundaki örnekte çıktı betiğini çalıştırdığınızda aşağıdaki hata iletisini alırsınız:
Msg 15433, Seviye 16, Durum 1, Satır 1 Sağlanan parametre sid'i kullanılıyor.
Bu nedenle, aşağıdakileri yapmanız gerekir:
Çıkış betiğini dikkatle gözden geçirin.
B sunucusundaki
sys.server_principals
örnekteki görünümün içeriğini inceleyin.Bu hata iletilerini uygun şekilde ele alın.
SQL Server 2005'te oturum açma için SID, veritabanı düzeyinde erişim uygulamak için kullanılır. Oturum açma işlemi, sunucudaki farklı veritabanlarında farklı SID'lere sahip olabilir. Bu durumda oturum açma işlemi, yalnızca
sys.server_principals
görünümündeki SID ile eşleşen SID'ye sahip veritabanına erişebilir. Bu sorun, iki veritabanı farklı sunuculardan birleştirildiğinde oluşabilir. Bu sorunu çözmek için DROP USER deyimini kullanarak SID uyuşmazlığı olan veritabanındaki oturum açmayı el ile kaldırın. ArdındanCREATE USER
deyimini kullanarak oturum açmayı yeniden ekleyin.
Başvurular
Geri Bildirim
https://aka.ms/ContentUserFeedback.
Çok yakında: 2024 boyunca, içerik için geri bildirim mekanizması olarak GitHub Sorunları’nı kullanımdan kaldıracak ve yeni bir geri bildirim sistemiyle değiştireceğiz. Daha fazla bilgi için bkz.Gönderin ve geri bildirimi görüntüleyin