0

Just installed a SQL Server 2016 instance fresh. Previously I've used merge replication for 2008 and 2012 without issues. I'm trying to do the same here, and while I can create the publication no problem, I get the following error when I try to create the snapshot agent. The SQL Server Agent is running, I can't find anything on the error online:

===================================

Cannot save changes to agent security settings.

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------ Program Location:

at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry) at Microsoft.SqlServer.Replication.ReplicationObject.ExecCommand(String commandIn) at Microsoft.SqlServer.Replication.Publication.CreateSnapshotAgent()
at Microsoft.SqlServer.Management.UI.PageCreatePubAgentSecurity.SaveAgentSecurity(ReplAgentType type) at Microsoft.SqlServer.Management.UI.PubPropAgentSecurity.SaveProperties(ExecutionMode& executionResult)

===================================

Could not find stored procedure 'sys.sp_MSaddreplsymmetrickey'. Changed database context to 'CoBRAClientData'. (.Net SqlClient Data Provider)

------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=13.00.1601&EvtSrc=MSSQLServer&EvtID=2812&LinkId=20476

------------------------------ Server Name: SERVER1\COBRASERVER Error Number: 2812 Severity: 16 State: 62 Procedure: sp_MSreplopensymmetrickey Line Number: 37

------------------------------ Program Location:

at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException) at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)

this happens through both sql management studio, and if i use the script:

exec sp_addpublication_snapshot @publication = N'CoBRAClientPub', @frequency_type = 4, @frequency_interval = 14, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 1, @frequency_subday_interval = 5, @active_start_time_of_day = 500, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = N'server1\repl_snapshot', @job_password = null, @publisher_security_mode = 1
Phil
  • 1,852
  • 2
  • 28
  • 55
  • Could you please add the SQL query you are running to create snapshot agent? – gofr1 Sep 03 '16 at 05:03
  • I'm not running a sql query directly. i'm using sql management studio. right click the publication/properties/agent Security. Click on 'create agent' – Phil Sep 03 '16 at 13:23
  • ah, but if i use a script,i get the same error. see update – Phil Sep 03 '16 at 13:55
  • I haven't worked with this functionality, but the text of error say `Could not find stored procedure 'sys.sp_MSaddreplsymmetrickey'.` I've searched my SQL Server 2014 and haven't find this SP. May be it would be a solution if you try to create it. The source code of this SP could be found [here](http://www.g-productions.nl/index.php?name=sp_MSaddreplsymmetrickey) – gofr1 Sep 03 '16 at 14:31
  • there's a couple problems with that. first, it doesn't like the TRIPLE_DES call in the stored proc. if i change it to AES_256, then it passes, but it gives me an error that i can't create a 'sys' procedure. if i change it from sys.sp_MSaddreplsymmetrickey to [sys.sp_MSaddreplsymmetrickey], that passes the error, but then i still get the original issue at the top about not finding the stored proc – Phil Sep 03 '16 at 14:48
  • Ah, `sys` schema, right. Forgot about this. – gofr1 Sep 03 '16 at 14:49

1 Answers1

0

Never figured out a way around this using sql 2016. Uninstalled, and installed sql 2014 (most recent service pack) and everything worked fine. I had originally tried uinstalling 2016 and installing it back again, but that didn't work. only 2014 did.

Phil
  • 1,852
  • 2
  • 28
  • 55