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
Özgün KB 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. Ö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?

Bu makalede, A sunucusu ve B sunucusu sunuculardır.

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.

18456 hatasının başka birçok nedenden kaynaklandığını unutmayın. Bu nedenler ve bunların çözümleri hakkında daha fazla bilgi için bkz . MSSQLSERVER_18456.

Oturum açma bilgilerini aktarma adımları

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

Yöntem 1: Kaynak sunucuda SSMS aracılığıyla betikler oluşturma ve hedef sunucuda SQL Server oturum açma parolalarını 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>Betik Oluştur'a tıklayın.

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. Betiklerin nasıl kaydedileceğini seçin altında Betik Seçeneklerini Ayarla'ya dönün ve 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, 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.

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

1. 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('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 Açıklamalar bölümündeki bilgileri gözden geçirin ve izleyin.

6. Açıklamalar bölümünden geçerli adımları uyguladıktan sonra, herhangi bir istemci aracı (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.

Açıklamalar

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

Parola karması bilgileri

• 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ğinde oturum açma işlemleri şifrelenmiş parola kullanılarak oluşturulur. Bunun nedeni deyimindeki HASHED bağımsız değişkendir CREATE LOGIN . Bu bağımsız değişken, bağımsız değişkenden sonra girilen parolanın PASSWORD zaten karma olduğunu belirtir.

Etki alanı değişikliğini işleme

Kaynak ve hedef sunucularınız farklı etki alanlarında mı? Çı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, deyimlerdeki 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 işlemleriyle 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 (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ın yalnız bırakılma olasılığı düşüktür.

Tüm oturum açma bilgilerini görüntüleme ve seçme izinleri

Varsayılan olarak, sysadmin sabit sunucu rolünün bir üyesi yalnızca sys.server_principals görünümünden bir SELECT 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.

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. Bunun 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 deyimini kullanın ALTER LOGIN .

Kaynak ve hedef sunucular arasındaki farklı sıralama düzenleriyle ilgilenme

Kaynak ve hedef sunucular arasındaki sıralama düzenlerinde farklılıklar olabilir veya bunlar aynı olabilir. Her senaryo şu şekilde ele alınabilir:

• 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ı 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ı değildir: 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.

Hedef sunucuda zaten var olan oturum açma bilgileriyle başa çıkma

Betik, oturum açmanın hedef sunucuda mevcut olup olmadığını denetlemek ve yalnızca oturum açmadıysa bir oturum açma oluşturmak için tasarlanmıştır. 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 bu sorunu 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.

Sorunu 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, veritabanı düzeyinde erişimi yönetmek için oturum açma sid'i kullanılır. Bazen, oturum açma işlemi farklı veritabanlarındaki kullanıcılarla eşlendiğinde farklı SID'lere sahip olabilir. Veritabanları farklı sunuculardan el ile birleştirildiğinde bu sorun 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, deyimiyle CREATE USER kullanıcıyı yeniden ekleyin ve doğru oturum açma bilgilerine (sunucu sorumlusu) 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
• CREATE LOGIN (Transact-SQL)
• ALTER LOGIN (Transact-SQL)
• SQL Server Management Studio'da betik nesneleri