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.

    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 ('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.

    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.

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

  • 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:

    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'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ından CREATE USER deyimini kullanarak oturum açmayı yeniden ekleyin.

Başvurular