Transactional replication – The subscription status of the object could not be changed. (Source: MSSQLServer, Error number: 14068)

Recently I’ve faced a general error on SQL Server transactional replication – The subscription status of the object could not be changed. This was popped up on no matter what changes trying to implement – add a new subscriber, drop a subscriber, add or remove any article, etc. In other words – the replication became something like read-only.

The changes I tried to implement using SSMS as well as system procedures, like “sp_dropsubscription”, but with no luck: The subscription status of the object could not be changed. (Source: MSSQLServer, Error number: 14068). Other errors by trying other procedures I’ve got:

Msg 14068, Level 16, State 1, Procedure sp_MSrepl_changesubstatus, Line 1386 [Batch Start Line 0] The subscription status of the object could not be changed.

Replication Monitor could not reinitialize one or more subscriptions that support automatic initialization. Additional Information: An exception occurred while executing a transact sql statement or batch. (Microsoft.SQLServer.ConnectionInfo)

The subscription status of the object could not be changed. Could not update the distribution database subscription table. The subscription status could not be changed. Changed database context to “TestDatabase”. (Microsoft SQL Server: Error 14068) 

Even a snapshot agent was failing without saying anything useful… Then it looked like I need to re-setup a replication from scratch. This wouldn’t be something much if only one publication on a server with few articles, etc. But in my case – there are over 10 publications with more than 100 subscriptions. All the servers are in production and to re-setup replication – would cost a lot…

Fortunately, I’ve found a way to fix the replication without re-setup. Here what I found.

First, I’ve checked is there any orphaned articles in the replication? This might be done by running the script:

use [database]
go

select object_name(objid), name, artid
from dbo.sysschemaarticles

Success! I’ve found one article exists as a replicated object (in my case udf function), but already dropped out of the database:

Please note, you can’t simply re-create a dropped article since it will get different object_id and will not fix the issue (just now though, instead of deleting the entries as described below, you may try to update article id, but at the moment I was fixing, didn’t thought about this option).

The next step – clean this up. It’s as easy as to delete a record out of a table. For sure, you need to know from what tables. Here you go, four tables in total: syssubscriptions and sysschemaarticles in a publisher database, and MSsubscriptions and MSArticles in distribution database. In my case, from the query above I’ve identified that artid = 2 is the candidate to drop. Before deleting, I’ve copied the entries to a backup table in case I’ll screw up something else and need to restore the change. Remember, all the things I was doing are on production environment…

use [database]
go

delete
from syssubscriptions 
where artid = 2

delete
from sysschemaarticles 
where artid = 2

delete
from distribution.dbo.MSsubscriptions 
where article_id = 2

delete
from distribution.dbo.MSArticles  
where article_id = 2

Success again! After the orphaned article has been deleted out of replication, now I can do all the changes again – add a new subscriber, add a new article, etc. All back to normal!

Thanks for reading!

MSSQL automated security sync on AlwaysOn environment

As all of you probably know AlwaysOn is not only good feature, but also more and complex work for a DBA. In this article I’ll share with you how to automate security (logins) sync between AlwaysOn Availability Group (AG) replicas. Why we need this? Because if you or anyone else will create/alter/edit any user on SQL server primary replica, the user will not be amended on a secondary replica and if you forget to do the same change, sooner or later you may have a big problems due to security. Please note that you don’t need to care about database level security, because this will be synced by AG itself, you need to care about server level security and server roles.

So, to automate security sync I have decided to create a trigger on the SQL server. The trigger is built of few parts: the first is server logins, the second – server roles and the last – prevent any security changes on secondary replica.

SQL server logins

First thing you should know is that all changes performed on every secondary AG replica fired up by the trigger from primary replica will be executed under SQL server service account. To ensure minimal rights to the service account you need to grant view server state and view any definition (many people grant sysadmin role to the service account, but I strongly disagree with it):

GRANT VIEW SERVER STATE TO [Domain\SQL_Service_Account]
GO
GRANT VIEW ANY DEFINITION TO [Domain\SQL_Service_Account]
GO

The next thing trigger will do is to capture all CREATE_LOGIN, ALTER_LOGIN and DROP_LOGIN events, extract other require data such us affected login, command text from the captured event and create T-SQL command which will be executed on every secondary replica.

SQL server roles

Previously I have mentioned that I strongly disagree to grant sysadmin role to SQL server service account. So how we can add login to lets say sysadmin role if the service account does not have such permission? I decided, to keep secure environment is better than to have fully automated changes on server roles. Regarding my decision, in case of ADD_SERVER_ROLE_MEMBER or DROP_SERVER_ROLE_MEMBER event the trigger will send alert email when any server role change, with details like this:

Subject: Server role changed on [ServerName1]
Message: Need to perform the same server role change on [Servername2, ServerName3, etc.] secondary replica as performed on [ServerName1] primary replica: ALTER SERVER ROLE [diskadmin] DROP MEMBER [test]

This will allow you to know if anybody else made any server role change or at least remember you to make the same change on other replicas.

Prevent any security changes on secondary replica

And finally, what if anybody will make a security change on any secondary replica? I prefer to rollback any change like this. So if you will try to amend any user on secondary replica, you will get an error message:

Unable to add/alter/drop users on SECONDARY replica. Please perform the action on PRIMARY replica.

The source code

Here is the source code of the trigger, feel free to use it and amend it to fit your needs. If you have any thoughts, feel three to contact me or write any comment.

IF EXISTS (
       SELECT *
       FROM   sys.server_triggers
       WHERE  NAME = 'Trg_TrackLoginManagement'
   )
    DROP TRIGGER [Trg_TrackLoginManagement] ON ALL SERVER 
GO


CREATE TRIGGER [Trg_TrackLoginManagement]
ON ALL SERVER
FOR DDL_LOGIN_EVENTS, ADD_SERVER_ROLE_MEMBER, DROP_SERVER_ROLE_MEMBER
AS
  SET NOCOUNT ON
  
  DECLARE @recipients VARCHAR(200)
  DECLARE @AGDatabase VARCHAR(200)
  DECLARE @AGGroupName VARCHAR(200)
  DECLARE @SQLServiceAccount VARCHAR(200)
  
  
  SELECT @recipients = 'alert@email.com'			--Change to email you will receive alert
  SET @AGDatabase = 'DatabaseName'				--Change a database name which belongs to AG
  SET @AGGroupName = 'AGGroupName'				--Change to your AlwaysON AG name
  SET @SQLServiceAccount = 'Domain\SQLServiceAccount'	--Change to SQL service account SQL server is running on
  
  IF (
         SELECT ars.role_desc
         FROM   sys.dm_hadr_availability_replica_states ars
                INNER JOIN sys.availability_databases_cluster ag
                     ON  ars.group_id = ag.group_id
         WHERE  ag.database_name = @AGDatabase
                AND ars.is_local = 1
     ) = 'PRIMARY'
  BEGIN
      DECLARE @data                  XML,
              @EventType             VARCHAR(100),
              @EventTime             DATETIME,
              @ServerName            VARCHAR(100),
              @AffectedLoginName     VARCHAR(100),
              @WhoDidIt              VARCHAR(100),
              @EmailSubject          VARCHAR(500),
              @EmailBody             VARCHAR(800),
              @EmailRecipients       VARCHAR(300),
              @text                  VARCHAR(1000),
              @sql                   VARCHAR(1000),
              @executesql            VARCHAR(1000)
      
      SET @data = EVENTDATA()
      SET @EventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(100)')
      SET @AffectedLoginName = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(100)')
      SET @text = REPLACE(
              REPLACE(
                  @data.value(
                      '(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
                      'varchar(max)'
                  ),
                  CHAR(10),
                  ''
              ),
              CHAR(13),
              ''
          )
      
      SELECT @sql = CASE 
                         WHEN @EventType = 'CREATE_LOGIN' THEN (
                                  SELECT 'create login [' + p.name + '] ' 
                                         + CASE 
                                                WHEN p.type IN ('U', 'G') THEN 'from windows '
                                                ELSE ''
                                           END 
                                         + 'with ' 
                                         + CASE 
                                                WHEN p.type = 'S' THEN 'password = ' 
                                                     + MASTER.sys.fn_varbintohexstr(l.password_hash) 
                                                     + ' hashed, ' 
                                                     + 'sid = ' 
                                                     + MASTER.sys.fn_varbintohexstr(l.sid) 
                                                     + ', check_expiration = ' 
                                                     + CASE 
                                                            WHEN l.is_expiration_checked > 0 THEN 'ON, '
                                                            ELSE 'OFF, '
                                                       END 
                                                     + 'check_policy = ' 
                                                     + CASE 
                                                            WHEN l.is_policy_checked > 0 THEN 'ON, '
                                                            ELSE 'OFF, '
                                                       END
                                                ELSE ''
                                           END 
                                         + 'default_database = [' 
                                         + p.default_database_name 
                                         + CASE 
                                                WHEN LEN(p.default_language_name) > 0 THEN '], default_language = [' 
                                                     + p.default_language_name 
                                                     + ']'
                                                ELSE ']'
                                           END
                                  FROM   sys.server_principals p
                                         LEFT JOIN sys.sql_logins l
                                              ON  p.principal_id = l.principal_id
                                  WHERE  p.name = @AffectedLoginName
                              )
                         WHEN @EventType = 'ALTER_LOGIN' THEN @text
                         WHEN @EventType = 'DROP_LOGIN' THEN REPLACE(@text, CHAR(13), '')
                         WHEN @EventType = 'ADD_SERVER_ROLE_MEMBER' THEN @text
                         WHEN @EventType = 'DROP_SERVER_ROLE_MEMBER' THEN @text
                         ELSE ''
                    END
      
      
      SELECT replica_server_name
      INTO   #secondarys
      FROM   sys.dm_hadr_availability_replica_cluster_nodes
      WHERE  group_name = @AGGroupName
             AND replica_server_name <> @@SERVERNAME
      
      WHILE EXISTS (
                SELECT *
                FROM   #secondarys
            )
      BEGIN
          SELECT TOP 1 @ServerName = replica_server_name
          FROM   #secondarys
          
          IF @EventType IN ('CREATE_LOGIN', 'ALTER_LOGIN', 'DROP_LOGIN')
          BEGIN
              SET @executesql = 'sqlcmd -S ' + @ServerName + ' -e -Q "' + @sql + '"'
              EXEC xp_cmdshell @executesql
          END
          
          DELETE 
          FROM   #secondarys
          WHERE  replica_server_name = @ServerName
      END
      
      PRINT @Eventtype + ' activity successfully transferred to secondary server.'
      
      DROP TABLE #secondarys
      
      IF @EventType IN ('ADD_SERVER_ROLE_MEMBER', 'DROP_SERVER_ROLE_MEMBER')
      BEGIN
          
          DECLARE @EmailMain VARCHAR(MAX)
          DECLARE @Secondaries VARCHAR(500)
          
          SELECT @Secondaries = COALESCE(@Secondaries + '; ', '') + replica_server_name
          FROM   sys.dm_hadr_availability_replica_cluster_nodes
          WHERE  group_name = @AGGroupName
                 AND replica_server_name <> @@SERVERNAME
          
          SET @EmailSubject = 'Server role changed on ' + @@SERVERNAME
          SET @EmailMain = 'Need to perform the same server role change on ' + @Secondaries + ' secondary replica '
          SET @EmailMain = @EmailMain + 'as performed on ' + @@SERVERNAME + ' primary replica:<BR>'
          SET @EmailMain = @EmailMain + @sql
          
          EXEC msdb.dbo.sp_send_dbmail 
               @recipients = @recipients,
               @subject = @EmailSubject,
               @body = @EmailMain,
               @body_format = 'HTML'
      END
  END
  ELSE
  BEGIN
      IF (
             SELECT SYSTEM_USER
         ) <> @SQLServiceAccount
         AND @EventType IN ('CREATE_LOGIN', 'ALTER_LOGIN', 'DROP_LOGIN')
      BEGIN
          PRINT 'Unable to add/alter/drop users on SECONDARY replica. Please perform the action on PRIMARY replica.'
          
          ROLLBACK
      END
  END
GO