Did you know… When we rename a domain group in Active Directory, we can have some issues related to the login in SQL Server

  • Article

Sometime back I got to work on an interesting issue related to the login on the SQL Server replication. 

The Active Directory group we used to grant access to a snapshot publication does not show up in the Publication access list anymore when viewing the properties of the publication. However, the AD group does show up in the MSPublication_Access table for the snapshot publication in the Distribution database.

There is a disparity between what the Replication property settings and what is actually in the MSPublication_Access table. When we try to add the group to the PAL in the GUI, it doesn't give any error message and completes. But when we again check the PAL, it doesn't show up.

 

CAUSE
======
The login was changed in the AD sometime back. The change was that the login was renamed and not deleted and recreated afterwards. So the SID remained same for the changed login in the AD. But because of this change in the AD, SQL Server was never aware of the change of the name. The login with the old name still existed in the server and mapped to the same SID. Then somehow the login was deleted and recreated at the SQL Server level. So when the login was created again at the SQL Server, the new name was created on the SQL Server but the same Old SID was associated with it.

But the entry for the login remained there in the mspublication_access table. This login was never removed during the deletion of the login from the SQL Server. Afterwards whenever they used GUI to check the PAL, it was unable to find the login name from the SQL Server logins and hence didn't show up in the PAL GUI. But whenever they tried to add the login in the PAL, it was able to find an existing record with the same SID and hence failed to add the same. But it never gave any error message.

RESOLUTION
============
Delete the record manually from the MSPublication_Access table in distribution database. Then again add the login to the SQL Server and PAL.

Sanjaya Padhi
SE, Microsoft SQL Server