Oturum açma bilgilerini ve parolaları SQL Server örnekleri arasında aktarma

Özgün ürün sürümü: SQL Server
Özgün KB numarası: 918992, 246133

Özet

Bu makalede, Windows üzerinde çalışan farklı Microsoft SQL Server örnekleri arasında oturum açma bilgilerinin ve parolaların nasıl aktarıldığı açıklanmaktadır. Veritabanlarını sunucular arasında geçirirken veya SQL Server örnekleri arasında tutarlı kullanıcı erişimini sürdürmeniz gerektiğinde bu yordamları kullanın. Örnekler aynı sunucuda veya farklı sunucularda olabilir ve sürümleri farklı olabilir.

Sql Server örnekleri arasında neden oturum açma bilgileri aktarılır?

Veritabanını yeni bir sunucuya taşıdığınızda (örneğin, geçiş veya geri yükleme sırasında), veritabanı kullanıcılarını taşırsınız, ancak yeni örnekte ilgili sunucu düzeyinde oturum açma işlemleri olmayabilir. Bu koşul yalnız bırakılmış kullanıcılar oluşturur. Oturum açma bilgilerini ve parolaları aktarmak, güvenlik ve erişim sürekliliğini korumak için çok önemlidir.

Bir veritabanını A sunucusundaki bir SQL Server örneğinden B sunucusundaki bir SQL Server örneğine taşıdıktan sonra, kullanıcılar B sunucusundaki veritabanı sunucusunda 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, A sunucusundaki SQL Server örneğindeki oturum açma bilgilerinin B sunucusundaki SQL Server örneğinde mevcut olmadığından oluşur.

Hata 18456 başka nedenlerle de oluşabilir. Çeşitli nedenler ve bunların çözümleri hakkında daha fazla bilgi için bkz. MSSQLSERVER_18456.

SQL Server örnekleri arasında oturum açma bilgilerini aktarma adımları

Oturum açma bilgilerini aktarmak için, durumunuz için uygun olan aşağıdaki yöntemlerden birini kullanın.

Kaynak sunucuda SSMS aracılığıyla betikler oluşturma ve hedef sunucudaki SQL Server oturum açma işlemleri için parolaları el ile sıfırlama

Veritabanı için Betik Oluştur seçeneğini kullanarak SQL Server Management Studio'da (SSMS) oturum açma betikleri oluşturabilirsiniz.

Kaynak sunucuda SSMS aracılığıyla betikler oluşturmak ve hedef sunucuda SQL Server oturum açma parolalarını el ile sıfırlamak için şu adımları izleyin:

1. Kaynak SQL Server'ı barındıran A sunucusuna bağlanın.

2. Veritabanları düğümünü genişletin.

3. Herhangi bir kullanıcı veritabanına sağ tıklayın ve Görevler

4. Giriş sayfası açılır. Nesneleri Seç sayfasını açmak için İleri'yi seçin. Tüm veritabanını ve tüm veritabanı nesnelerini betikle'yi seçin.

5. Betik Seçeneklerini Ayarla sayfasını açmak için İleri'yi seçin.

6. Betik Oturum Açma seçenekleri için Gelişmiş düğmesini seçin.

7. Gelişmiş listesinde Betik Oturum Açma bilgileri'ni bulun, seçeneği True olarak ayarlayın ve Tamam'ı seçin.

8. Betik Seçeneklerini Ayarla'ya dönün, Betiklerin nasıl kaydedileceğini seçin altında Yeni sorgu penceresinde aç'ı seçin.

9. İki kez İleri'yi ve ardından Son'u seçin.

10. Betikte oturum açma bilgilerini içeren bölümü bulun. Genellikle, oluşturulan betik bu bölümün başında aşağıdaki açıklamayı içeren bir metin içerir:

    /* For security reasons the login is created disabled and with a random password. */

    Not

    Bu açıklama, SQL Server Kimlik Doğrulaması oturum açma bilgilerinin rastgele bir parolayla oluşturulduğunu ve varsayılan olarak devre dışı bırakıldığını gösterir. Parolayı sıfırlamanız ve hedef sunucuda bu oturum açma bilgilerini yeniden etkinleştirmeniz gerekir.

11. Daha büyük oluşturulan betikten gelen oturum açma betiğini hedef SQL Server'a uygulayın.

12. Tüm SQL Server Kimlik Doğrulaması oturum açma işlemleri için, hedef SQL Server'da parolayı sıfırlayın ve bu oturum açma bilgilerini yeniden etkinleştirin.

Kaynak sunucuda oluşturulan betikleri (Sunucu A) kullanarak oturum açma bilgilerini ve parolaları hedef sunucuya (Sunucu B) aktarma

1. Oturum açma bilgilerini ve parolalarını aktarmak için gerekli betiklerin oluşturulmasına yardımcı olan 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('dbo.sp_hexadecimal') IS NOT NULL
       DROP PROCEDURE dbo.sp_hexadecimal
   GO
   CREATE PROCEDURE dbo.sp_hexadecimal
       @binvalue [varbinary](256)
       ,@hexvalue [nvarchar] (514) OUTPUT
   AS
   BEGIN
       DECLARE @i [smallint]
       DECLARE @length [smallint]
       DECLARE @hexstring [nchar](16)
       SELECT @hexvalue = N'0x'
       SELECT @i = 1
       SELECT @length = DATALENGTH(@binvalue)
       SELECT @hexstring = N'0123456789ABCDEF'
       WHILE (@i < =  @length)
       BEGIN
           DECLARE @tempint   [smallint]
           DECLARE @firstint  [smallint]
           DECLARE @secondint [smallint]
           SELECT @tempint = CONVERT([smallint], SUBSTRING(@binvalue, @i, 1))
           SELECT @firstint = FLOOR(@tempint / 16)
           SELECT @secondint = @tempint - (@firstint * 16)
           SELECT @hexvalue = @hexvalue
               + SUBSTRING(@hexstring, @firstint  + 1, 1)
               + SUBSTRING(@hexstring, @secondint + 1, 1)
           SELECT @i = @i + 1
       END
   END
   GO
   IF OBJECT_ID('dbo.sp_help_revlogin') IS NOT NULL
       DROP PROCEDURE dbo.sp_help_revlogin
   GO
   CREATE PROCEDURE dbo.sp_help_revlogin
       @login_name [sysname] = NULL
   AS
   BEGIN
       DECLARE @name                  [sysname]
       DECLARE @type                  [nvarchar](1)
       DECLARE @hasaccess             [int]
       DECLARE @denylogin             [int]
       DECLARE @is_disabled           [int]
       DECLARE @PWD_varbinary         [varbinary](256)
       DECLARE @PWD_string            [nvarchar](514)
       DECLARE @SID_varbinary         [varbinary](85)
       DECLARE @SID_string            [nvarchar](514)
       DECLARE @tmpstr                [nvarchar](4000)
       DECLARE @is_policy_checked     [nvarchar](3)
       DECLARE @is_expiration_checked [nvarchar](3)
       DECLARE @Prefix                [nvarchar](4000)
       DECLARE @defaultdb             [sysname]
       DECLARE @defaultlanguage       [sysname]
       DECLARE @tmpstrRole            [nvarchar](4000)
       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,default_language_name = ISNULL(p.default_language_name,@@LANGUAGE)
           FROM sys.server_principals p
           LEFT JOIN sys.syslogins l ON l.[name] = p.[name]
           WHERE p.[type] IN ('S' /* SQL_LOGIN */,'G' /* WINDOWS_GROUP */,'U' /* WINDOWS_LOGIN */)
               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,default_language_name = ISNULL(p.default_language_name,@@LANGUAGE)
           FROM sys.server_principals p
           LEFT JOIN sys.syslogins l ON l.[name] = p.[name]
           WHERE p.[type] IN ('S' /* SQL_LOGIN */,'G' /* WINDOWS_GROUP */,'U' /* WINDOWS_LOGIN */)
               AND p.[name] <> 'sa'
               AND p.[name] NOT LIKE '##%'
               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 for ' + QUOTENAME(@login_name) + N'. */'
           CLOSE login_curs
           DEALLOCATE login_curs
           RETURN - 1
       END
       SET @tmpstr = N'/* sp_help_revlogin script
   ** Generated ' + CONVERT([nvarchar], GETDATE()) + N' on ' + @@SERVERNAME + N'
   */'
       PRINT @tmpstr
       WHILE (@@fetch_status <> - 1)
       BEGIN
           IF (@@fetch_status <> - 2)
           BEGIN
               PRINT ''
               SET @tmpstr = N'/* Login ' + QUOTENAME(@name) + N' */'
               PRINT @tmpstr
               SET @tmpstr = N'IF NOT EXISTS (
       SELECT 1
       FROM sys.server_principals
       WHERE [name] = N''' + @name + N'''
       )
   BEGIN'
               PRINT @tmpstr
               IF @type IN ('G','U') -- NT-authenticated Group/User
               BEGIN -- NT authenticated account/group 
                   SET @tmpstr = N'    CREATE LOGIN ' + QUOTENAME(@name) + N'
       FROM WINDOWS
       WITH DEFAULT_DATABASE = ' + QUOTENAME(@defaultdb) + N'
           ,DEFAULT_LANGUAGE = ' + QUOTENAME(@defaultlanguage)
               END
               ELSE
               BEGIN -- SQL Server authentication
                   -- obtain password and sid
                   SET @PWD_varbinary = CAST(LOGINPROPERTY(@name, 'PasswordHash') AS [varbinary](256))
                   EXEC dbo.sp_hexadecimal @PWD_varbinary, @PWD_string OUT
                   EXEC dbo.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 = NCHAR(9) + N'CREATE LOGIN ' + QUOTENAME(@name) + N'
       WITH PASSWORD = ' + @PWD_string + N' HASHED
           ,SID = ' + @SID_string + N'
           ,DEFAULT_DATABASE = ' + QUOTENAME(@defaultdb) + N'
           ,DEFAULT_LANGUAGE = ' + QUOTENAME(@defaultlanguage)
   
                   IF @is_policy_checked IS NOT NULL
                   BEGIN
                       SET @tmpstr = @tmpstr + N'
           ,CHECK_POLICY = ' + @is_policy_checked
                   END
   
                   IF @is_expiration_checked IS NOT NULL
                   BEGIN
                       SET @tmpstr = @tmpstr + N'
           ,CHECK_EXPIRATION = ' + @is_expiration_checked
                   END
               END
               IF (@denylogin = 1)
               BEGIN -- login is denied access
                   SET @tmpstr = @tmpstr
                       + NCHAR(13) + NCHAR(10) + NCHAR(9) + N''
                       + NCHAR(13) + NCHAR(10) + NCHAR(9) + N'DENY CONNECT SQL TO ' + QUOTENAME(@name)
               END
               ELSE IF (@hasaccess = 0)
               BEGIN -- login exists but does not have access
                   SET @tmpstr = @tmpstr
                       + NCHAR(13) + NCHAR(10) + NCHAR(9) + N''
                       + NCHAR(13) + NCHAR(10) + NCHAR(9) + N'REVOKE CONNECT SQL TO ' + QUOTENAME(@name)
               END
               IF (@is_disabled = 1)
               BEGIN -- login is disabled
                   SET @tmpstr = @tmpstr
                       + NCHAR(13) + NCHAR(10) + NCHAR(9) + N''
                       + NCHAR(13) + NCHAR(10) + NCHAR(9) + N'ALTER LOGIN ' + QUOTENAME(@name) + N' DISABLE'
               END
               SET @Prefix =
                   NCHAR(13) + NCHAR(10) + NCHAR(9) + N''
                   + NCHAR(13) + NCHAR(10) + NCHAR(9) + N'EXEC [master].dbo.sp_addsrvrolemember @loginame = N'''
               SET @tmpstrRole = N''
               SELECT @tmpstrRole = @tmpstrRole
                   + CASE WHEN sysadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''sysadmin''' ELSE '' END
                   + CASE WHEN securityadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''securityadmin''' ELSE '' END
                   + CASE WHEN serveradmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''serveradmin''' ELSE '' END
                   + CASE WHEN setupadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''setupadmin''' ELSE '' END
                   + CASE WHEN processadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''processadmin''' ELSE '' END
                   + CASE WHEN diskadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''diskadmin''' ELSE '' END
                   + CASE WHEN dbcreator = 1 THEN @Prefix + LoginName + N''', @rolename = N''dbcreator''' ELSE '' END
                   + CASE WHEN bulkadmin = 1 THEN @Prefix + LoginName + N''', @rolename = N''bulkadmin''' ELSE '' END
               FROM (
                   SELECT
                       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
               IF @tmpstr <> '' PRINT @tmpstr
               IF @tmpstrRole <> '' 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.

2. SSMS sorgu düzenleyicisinde , Sonuçlardan Metne seçeneğini belirleyin.

3. Aşağıdaki deyimi aynı veya yeni bir sorgu penceresinde çalıştırın:

   EXEC sp_help_revlogin
   

4. 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.

5. Hedef sunucuda adımları uygulamaya devam etmeden önce SQL Server oturum açma bilgilerini aktarırken dikkat edilmesi gereken ek noktalar bölümündeki bilgileri gözden geçirin ve izleyin.

6. SQL Server oturum açma bilgilerini aktarırken dikkat edilmesi gerekenler bölümünden geçerli adımları uyguladıktan sonra, herhangi bir istemci aracını (SSMS gibi) kullanarak hedef sunucu B'ye bağlanın.

7. A sunucusundan çıkışı sp_helprevlogin olarak oluşturulan betiği çalıştırın.

SQL Server oturum açma bilgilerini aktarırken dikkat edilmesi gereken ek noktalar

B sunucusundaki örnekte çıkış betiğini çalıştırmadan önce aşağıdaki bilgileri gözden geçirin:

SQL Server oturum açma aktarımlarında parola karmasını anlama

Parolayı aşağıdaki yollarla hashleyebilirsiniz:

• VERSION_SHA1: SQL Server bu karmayı oluşturmak için SHA1 algoritmasını kullanır. SQL Server 2000 ile SQL Server 2008 R2 arasında sürümler bu karmayı kullanır.
• VERSION_SHA2: SQL Server bu karmayı oluşturmak için SHA2 512 algoritmasını kullanır. SQL Server 2012 ve sonraki sürümler, bu hash'i kullanır.

Çıktı betiği, şifrelenmiş parolayı kullanarak kullanıcı adlarını oluşturur. HASHED CREATE LOGIN deyimindeki bağımsız değişken bu davranışa neden olur. Bu bağımsız değişken, PASSWORD bağımsız değişkeninden sonra girilen parolanın zaten hashlenmiş olduğunu gösterir.

SQL Server oturum açma aktarımları sırasında etki alanı değişikliklerini işleme

Kaynak ve hedef sunucularınız farklı etki alanlarındaysa çıkış betiğini dikkatle gözden geçirin. Çıkış betiğini değiştirmeniz ve CREATE LOGIN ifadelerdeki özgün etki alanı adını yeni etki alanı adıyla 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 işlemleriyle aynı SID'ye sahip değildir. Bu nedenle, kullanıcılar bu oturum açmalardan mahrum kalır. 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 (SQL Server) ve ALTER USER sorunlarını giderme.

A sunucusu ve B sunucusu aynı etki alanındaysa aynı SID kullanılır. Bu nedenle, kullanıcılar yalnız bırakılamaz.

SQL Server oturum açma bilgilerini görüntülemek ve seçmek için gerekli izinler

Varsayılan olarak, görünümden yalnızca sysadmin sabit sunucu rolününSELECT üyeleri bir sys.server_principals deyimi çalıştırabilir. Sysadmin sabit sunucu rolünün bir üyesi diğer kullanıcılara gerekli izinleri vermediği sürece, bu kullanıcılar çıkış betiğini oluşturamaz veya çalıştıramaz.

Varsayılan veritabanı ayarı betik oluşturmaz ve aktarılamaz

Bu makaledeki adımlar, belirli bir oturum açma için varsayılan veritabanı bilgilerini aktarmaz. Bu sınırlamanın nedeni, varsayılan veritabanının B sunucusunda her zaman mevcut olmamasıdır. Bir 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 ALTER LOGIN deyimini kullanın.

SQL Server oturum açma aktarımlarındaki sıralama düzeni farklılıklarını yönetme

Kaynak ve hedef sunucular farklı sıralama düzenlerine sahip olabilir veya aynı sıralama düzenini kullanabilir. Her senaryoya şu şekilde değinebilirsiniz:

• 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ı değildir ve B sunucusunun sıralama düzeni büyük/küçük harfe duyarlıdır. 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ıdır ve B sunucusunun sıralama düzeni büyük/küçük harfe duyarlı değildir. 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 duyarsız: A ve B sunucularının sıralama düzeni büyük/küçük harfe duyarlı şekilde ya da duyarsızdır. Bu gibi durumlarda kullanıcılar sorun yaşamaz.

Hedef sunucuda var olan SQL Server oturum açma bilgileriyle çakışmaları çözme

Betik, oturum açmanın hedef sunucuda mevcut olup olmadığını denetler ve yalnızca mevcut değilse bir oturum açma oluşturur. Ancak, B sunucusundaki örnekte çıktı betiğini çalıştırdığınızda aşağıdaki hata iletisini alırsanız, bu bölümdeki adımları izleyerek çakışmayı el ile çözmeniz gerekir.

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şleminin çıkış betiğindeki SID ile aynı sid değeri 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.

Çakışmayı el ile çözmek için şu adımları izleyin:

1. Çıkış betiğini dikkatle gözden geçirin.
2. B sunucusundaki sys.server_principals örnekteki görünümün içeriğini inceleyin.
3. Bu hata iletilerini uygun şekilde ele alın.

SQL Server 2005'den başlayarak, oturum açma için SID veritabanı düzeyinde erişimi yönetir. Bazen, oturum açma işlemi farklı veritabanlarındaki kullanıcılarla eşlendiğinde farklı SID'lere sahip olabilir. Bu sorun, farklı sunuculardan veritabanlarını el ile birleştirdiğinizde oluşabilir. Bu gibi durumlarda, oturum açma yalnızca veritabanı sorumlusunun SID'sinin görünümdeki sys.server_principals SID ile eşleştiği veritabanına erişebilir. Bu sorunu çözmek için DROP USER deyimini kullanarak eşleşmeyen SID'ye sahip veritabanı kullanıcısını el ile kaldırın. Ardından CREATE USER komutunu kullanarak kullanıcıyı yeniden ekleyin ve doğru oturum açma bilgisini (sunucu yetkilisi) eşleyin.

Daha fazla bilgi edinmek ve sunucuları veritabanı sorumlularından ayırmak için bkz . CREATE USER ve CREATE LOGIN.

Başvurular

• Yalnız Bırakılmış Kullanıcılarla İlgili Sorunları Giderme
• GİRİŞ OLUŞTUR (Transact-SQL)
• ALTER LOGIN (Transact-SQL)
• SQL Server Management Studio'da betik nesneleri