Delen via

Aanmeldingen en wachtwoorden overdragen tussen exemplaren van SQL Server

  • Artikel

In dit artikel wordt beschreven hoe u de aanmeldingen en wachtwoorden overdraagt tussen verschillende exemplaren van SQL Server die worden uitgevoerd in Windows.

Oorspronkelijke productversie: SQL Server
Oorspronkelijk KB-nummer: 918992, 246133

Introductie

In dit artikel wordt beschreven hoe u de aanmeldingen en wachtwoorden overdraagt tussen verschillende exemplaren van Microsoft SQL Server. De exemplaren kunnen zich op dezelfde server of op verschillende servers bevinden en hun versies kunnen verschillen.

Waarom moet u aanmeldingen overdragen tussen SQL Server-exemplaren?

In dit artikel zijn server A en server B servers.

Nadat u een database hebt verplaatst van een SQL Server-exemplaar op server A naar een SQL Server-exemplaar op server B, kunnen gebruikers zich mogelijk niet aanmelden bij de databaseserver op server B. Daarnaast kunnen gebruikers mogelijk het volgende foutbericht krijgen:

Aanmelden is mislukt voor gebruiker 'MyUser'. (Microsoft SQL-server, fout: 18456)

Dit probleem treedt op omdat de aanmeldingen van het SQL Server-exemplaar op server A niet bestaan in het SQL Server-exemplaar op server B.

Houd er rekening mee dat fout 18456 om vele andere redenen optreedt. Zie MSSQLSERVER_18456 voor meer informatie over deze oorzaken en hun oplossingen.

Stappen voor het overdragen van de aanmeldingen

Gebruik voor het overdragen van de aanmeldingen een van de volgende methoden, afhankelijk van jouw situatie.

Methode 1: Scripts genereren via SSMS op de bronserver en wachtwoorden handmatig opnieuw instellen voor SQL Server-aanmeldingen op de doelserver

U kunt aanmeldingsscripts genereren in SQL Server Management Studio (SSMS) met behulp van de optie Scripts genereren voor een database.

Als u scripts wilt genereren via SSMS op de bronserver en wachtwoorden handmatig opnieuw wilt instellen voor SQL Server-aanmeldingen op de doelserver, voert u de volgende stappen uit:

  1. Maak verbinding met server A die als host fungeert voor de bron-SQL Server.

  2. Vouw het knooppunt Databases uit.

  3. Klik met de rechtermuisknop op een gebruikersdatabase en selecteer Taken>genereren scripts.

  4. De introductiepagina wordt geopend. Selecteer Volgende om de pagina Objecten kiezen te openen. Selecteer Hele scriptdatabase en alle databaseobjecten.

  5. Selecteer Volgende om de pagina Scriptopties instellen te openen.

  6. Selecteer de knop Geavanceerd voor aanmeldingsopties voor scripts.

  7. Zoek scriptaanmelding in de lijst Geavanceerd, stel de optie in op Waar en selecteer OK.

  8. Ga terug naar Scriptopties instellen onder Selecteren hoe scripts moeten worden opgeslagen en selecteer Openen in nieuw queryvenster.

  9. Selecteer Volgende twee keer en selecteer Vervolgens Voltooien.

  10. Zoek de sectie in het script met aanmeldingen. Normaal gesproken bevat het gegenereerde script tekst met de volgende opmerking aan het begin van deze sectie:

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

    Notitie

    Dit geeft aan dat de aanmeldingen voor SQL Server-verificatie worden gegenereerd met een willekeurig wachtwoord en standaard zijn uitgeschakeld. U moet het wachtwoord opnieuw instellen en deze aanmeldingen opnieuw inschakelen op de doelserver.

  11. Pas het aanmeldingsscript van het grotere gegenereerde script toe op de doel-SQL Server.

  12. Voor aanmeldingen met SQL Server-verificatie stelt u het wachtwoord opnieuw in op de doel-SQL Server en schakelt u deze aanmeldingen opnieuw in.

Methode 2: Aanmeldingen en wachtwoorden overdragen naar de doelserver (Server B) met behulp van scripts die zijn gegenereerd op de bronserver (Server A)

  1. Maak opgeslagen procedures waarmee u de benodigde scripts kunt genereren om aanmeldingen en hun wachtwoorden over te dragen. Hiervoor maakt u verbinding met Server A met behulp van SQL Server Management Studio (SSMS) of een ander clienthulpprogramma en voert u het volgende script uit:

    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

    Notitie

    Met dit script worden twee opgeslagen procedures in de hoofddatabase gemaakt. De procedures worden sp_hexadecimal en sp_help_revlogin genoemd.

  2. Selecteer in de SSMS-queryeditor de optie Resultaten naar tekst.

  3. Voer de volgende instructie uit in hetzelfde of een nieuw queryvenster:

    EXEC sp_help_revlogin

  4. Het uitvoerscript dat door de sp_help_revlogin opgeslagen procedure wordt gegenereerd, is het aanmeldingsscript. Met dit aanmeldingsscript worden de aanmeldingen gemaakt met de oorspronkelijke beveiligings-id (SID) en het oorspronkelijke wachtwoord.

  5. Bekijk en volg de informatie in de sectie Opmerkingen voordat u verdergaat met het implementeren van stappen op de doelserver.

  6. Zodra u alle toepasselijke stappen in de sectie Opmerkingen hebt geïmplementeerd, maakt u verbinding met de doelserver B met behulp van een clienthulpprogramma (zoals SSMS).

  7. Voer het script uit dat is gegenereerd als uitvoer van sp_helprevlogin server A.

Opmerkingen

Lees de volgende informatie voordat u het uitvoerscript uitvoert op het exemplaar op server B:

Informatie over wachtwoord-hashing

  • Een wachtwoord kan op de volgende manieren worden gehasht:

    • VERSION_SHA1: deze hash wordt gegenereerd met behulp van het SHA1-algoritme en wordt gebruikt in SQL Server 2000 tot en met SQL Server 2008 R2.
    • VERSION_SHA2: deze hash wordt gegenereerd met behulp van het algoritme SHA2 512 en wordt gebruikt in SQL Server 2012 en latere versies.

  • In het uitvoerscript worden de aanmeldingen gemaakt met behulp van het versleutelde wachtwoord. Dit komt door het HASHED argument in de CREATE LOGIN instructie. Dit argument geeft aan dat het wachtwoord dat wordt ingevoerd nadat het PASSWORD argument al is gehasht.

De wijziging van domeinen afhandelen

Bevinden uw bron- en doelservers zich in verschillende domeinen? Lees het uitvoerscript zorgvuldig. Als server A en server B zich in verschillende domeinen bevinden, moet u het uitvoerscript wijzigen. Vervolgens moet u de oorspronkelijke domeinnaam vervangen door de nieuwe domeinnaam in de CREATE LOGIN instructies te gebruiken. De geïntegreerde aanmeldingen die toegang krijgen in het nieuwe domein hebben niet dezelfde SID als de aanmeldingen in het oorspronkelijke domein. Daarom zijn gebruikers zwevende gebruikers van deze aanmeldingen. Zie Problemen met zwevende gebruikers (SQL Server) en ALTER USER oplossen voor meer informatie over het oplossen van deze zwevende gebruikers.

Als server A en server B zich in hetzelfde domein bevinden, wordt dezelfde SID gebruikt. Daarom is het onwaarschijnlijk dat gebruikers zwevend zijn.

Machtigingen om alle aanmeldingen weer te geven en te selecteren

Standaard kan alleen een lid van de vaste serverfunctie sysadmin een SELECT instructie uitvoeren vanuit de sys.server_principals weergave. Tenzij een lid van de vaste serverfunctie sysadmin de benodigde machtigingen verleent aan de gebruikers, kunnen de gebruikers het uitvoerscript niet maken of uitvoeren.

Standaarddatabase-instelling wordt niet gescript en overgedragen

Met de stappen in dit artikel worden de standaarddatabasegegevens voor een bepaalde aanmelding niet overgedragen. Dit komt doordat de standaarddatabase mogelijk niet altijd bestaat op server B. Als u de standaarddatabase voor een aanmelding wilt definiëren, gebruikt u de ALTER LOGIN instructie door de aanmeldingsnaam en de standaarddatabase als argumenten door te geven.

Omgaan met verschillende sorteervolgordes tussen de bron- en doelservers

Er kunnen verschillen zijn in sorteervolgordes tussen de bron- en doelservers, of ze zijn mogelijk hetzelfde. U kunt elk scenario als volgt aanpakken:

  • Hoofdlettergevoelige server A en hoofdlettergevoelige server B: De sorteervolgorde van server A is mogelijk niet hoofdlettergevoelig en de sorteervolgorde van server B kan hoofdlettergevoelig zijn. In dit geval moeten gebruikers de wachtwoorden in hoofdletters typen nadat u de aanmeldingen en de wachtwoorden hebt overgedragen naar het exemplaar op server B.

  • Hoofdlettergevoelige server A en niet-hoofdlettergevoelige server B: De sorteervolgorde van server A is mogelijk hoofdlettergevoelig en de sorteervolgorde van server B is mogelijk niet hoofdlettergevoelig. In dit geval kunnen gebruikers zich niet aanmelden met behulp van de aanmeldingen en de wachtwoorden die u overbrengt naar het exemplaar op server B, tenzij aan een van de volgende voorwaarden is voldaan:

    • De oorspronkelijke wachtwoorden bevatten geen letters.
    • Alle letters in de oorspronkelijke wachtwoorden zijn hoofdletters.

  • Hoofdlettergevoelig of niet hoofdlettergevoelig op beide servers: de sorteervolgorde van zowel server A als server B is mogelijk hoofdlettergevoelig, of de sorteervolgorde van zowel server A als server B is mogelijk niet hoofdlettergevoelig. In deze gevallen ondervinden de gebruikers geen probleem.

Aanmeldingen verwerken die al bestaan op de doelserver

Het script is ontworpen om te controleren of de aanmelding aanwezig is op de doelserver en alleen een aanmelding te maken als dat niet zo is. Als u echter het volgende foutbericht ontvangt wanneer u het uitvoerscript uitvoert op het exemplaar op server B, moet u dit handmatig oplossen door de stappen in deze sectie te volgen.

Msg 15025, Level 16, State 1, Line 1
De server-principal 'MyLogin' bestaat al.

Op dezelfde manier kan een aanmelding die zich al in het exemplaar op server B bevindt, een SID hebben die hetzelfde is als een SID in het uitvoerscript. In dit geval ontvangt u het volgende foutbericht wanneer u het uitvoerscript uitvoert op het exemplaar op server B:

Msg 15433, Level 16, State 1, Line 1 geleverde parameter sid is in gebruik.

Voer de volgende stappen uit om het probleem handmatig op te lossen:

  1. Lees het uitvoerscript zorgvuldig.
  2. Bekijk de inhoud van de sys.server_principals weergave in het exemplaar op server B.
  3. Los deze foutberichten zoals nodig op.

Vanaf SQL Server 2005 wordt de SID voor een aanmelding gebruikt voor het beheren van toegang op databaseniveau. Soms kan een aanmelding verschillende SID's hebben wanneer deze is toegewezen aan gebruikers in verschillende databases. Dit probleem kan optreden als databases handmatig van verschillende servers worden gecombineerd. In dergelijke gevallen heeft de aanmelding alleen toegang tot de database waarin de SID van de database-principal overeenkomt met de SID in de sys.server_principals weergave. U kunt dit probleem oplossen door de databasegebruiker met de niet-overeenkomende SID handmatig te verwijderen met behulp van de INSTRUCTIE DROP USER . Voeg vervolgens de gebruiker opnieuw toe met de CREATE USER instructie en wijs deze toe aan de juiste aanmelding (server-principal).

Zie CREATE USER en CREATE LOGIN voor meer informatie en om servers te onderscheiden van database-principals.

Verwijzingen