Microsoft
MicrosoftHi narenan ,
1. If CHECK_POLICY=ON in Primary Instance, then StoredProcedure [dbo].[CreateLoginsInSecondary]
script is getting failed on secondary instance, in case of "Alter Login" with below error
Error: a hashed password cannot be set for a login that has check_policy turned on
Solution: Need to add one more condition to check "CHECK_POLICY" while creating Alter command
2. In case of Password change on Primary Instance then StoredProcedure [dbo].[CreateLoginsInSecondary] script is getting failed because of incorrect format of a "ALTER LOGIN" command
Error: Incorrect syntax near 'PASSWORD'. [SQLSTATE 42000] (Error 102) Incorrect syntax near 'PASSWORD'. [SQLSTATE 42000] (Error 102). The step failed.
Solution: Because of substring statement in "ALTER LOGIN" command, either it needs to start from 1 or else need to provide extra space infront of "Password" in below code
Code Block:
N' ALTER LOGIN [' + sp.[name] + N'] WITH DEFAULT_LANGUAGE=[' + ISNULL(l.default_language_name, N'us_english') + N'] '+
Substring(
(
CASE
WHEN CONVERT(NVARCHAR(max), l.password_hash, 2) != CONVERT(NVARCHAR(max), sl.password_hash, 2)
THEN N', PASSWORD=0x' + CONVERT(NVARCHAR(max), l.password_hash, 2) + N' HASHED'
ELSE N' '
END
)
Fantastic process and code - used it many times when I've had to sync.
Recently seen an issue when syncronizing Entra ID User accounts - the job runs without an error (returns code 0) but the generated SQL statement has syntax error and fails to run.
Here is my corrected code for the Creating DDL Statements for New Logins within the CreateLoginsInSecondary SP
-- Insert DDL for new logins
PRINT 'Creating DDL Statements for New Logins';
INSERT INTO @DDLStmts
SELECT
CASE
WHEN l.[type] = 'S' THEN
N' CREATE LOGIN [' + l.[name] + '] WITH PASSWORD=0x'
+ CONVERT(NVARCHAR(MAX), l.password_hash, 2) + N' HASHED'
+ N', CHECK_POLICY = ' + CASE WHEN l.is_policy_checked = 1 THEN N'ON' ELSE N'OFF' END
+ N', CHECK_EXPIRATION = ' + CASE WHEN l.is_expiration_checked = 1 THEN N'ON' ELSE N'OFF' END
+ N', SID=0x' + CONVERT(NVARCHAR(MAX), l.[sid], 2)
+ N', DEFAULT_DATABASE = [' + l.default_database_name + N']'
+ ISNULL(N', DEFAULT_LANGUAGE = [' + l.default_language_name + N']', N'')
+ N';'
WHEN l.[type] IN ('E', 'X') THEN
+ N' CREATE LOGIN [' + l.[name] + '] FROM EXTERNAL PROVIDER'
+ N' WITH DEFAULT_DATABASE = [' + l.default_database_name + N']'
+ ISNULL(N', DEFAULT_LANGUAGE = [' + l.default_language_name + N']', N'')
+ N';'
WHEN l.[type] = 'R' THEN
N' CREATE SERVER ROLE [' + l.[name] + ']'
+ N', DEFAULT_DATABASE = [' + l.default_database_name + N']'
+ ISNULL(N', DEFAULT_LANGUAGE = [' + l.default_language_name + N']', N'')
+ N';'
END
FROM @Logins l
WHERE l.[name] NOT IN (SELECT [name] FROM master.sys.server_principals)
AND l.[type] IN ('S', 'E', 'X');The problem was that FROM EXTERNAL PROVIDER requires the WITH clause for Default Database and Language instead of a comma.
The current scripts are version 2.0 with a modification date of 7/15/2024 and I think already had that fixed? It looks like there were other changes, so I am not 100% sure. If you read this and could check, I would like to know if the current scripts still have something that needs to be fixed or not.
Replying to myself: No, it isn't fixed in the newer modification date.
