I am getting the following error when trying to setup Peer to Peer Replication. 

Warning: The distribution agent job has been implicitly created and will run under the SQL Server Agent Service Account.
Msg 6522, Level 16, State 1, Procedure sp_MSgeneratenosyncsubscriptionsetupscript_sqlclr, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "sp_MSgeneratenosyncsubscriptionsetupscript_sqlclr": 
System.TypeInitializationException: The type initializer for 'System.Data.SqlClient.SqlConnection' threw an exception. ---> System.TypeInitializationException: The type initializer for 'System.Data.SqlClient.SqlConnectionFactory' threw an exception. ---> System.TypeInitializationException: The type initializer for 'System.Data.SqlClient.SqlPerformanceCounters' threw an exception. ---> System.MethodAccessException: Attempt by method 'System.Configuration.TypeUtil.CreateInstanceRestricted(System.Type, System.Type)' to access method 'System.Diagnostics.SwitchElementsCollection..ctor()' failed. ---> System.Security.SecurityException: Request failed.
System.Security.SecurityException: 
   at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(RuntimeAssembly asm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandleInternal rmh, SecurityAction action, Object demand, IPermission permThatFailed)
   at System.Security.CodeAccessSecurityEngine.CheckSetHelper(PermissionSet grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandleInternal rmh, Object assemblyOrString, SecurityAction action, Boolean throwException)
   at System.Security.PermissionListSet.CheckSetDemandWithModification(PermissionSet pset, PermissionSet& alteredDemandSet, RuntimeMethodHandleInternal rmh)
   at System.Security.PermissionListSet.CheckSetDemand(PermissionSet pset, RuntimeMethodHandleInternal rmh)
   at System.Security.PermissionListSet.DemandFlagsOrGrantSet(Int32 flags, PermissionSet g
...
System.TypeInitializationException: 
   at System.Data.SqlClient.SqlConnection..ctor()
   at System.Data.SqlClient.SqlConnection..ctor(String connectionString)
   at Microsoft.SqlServer.Replication.TransSqlClrCodeGenerators.GatherMetadataAndGenerateScript()
   at Microsoft.SqlServer.Replication.TransSqlClrCodeGenerators.sp_MSgeneratenosyncsubscriptionsetupscript_sqlc...
The Subscriber was dropped.
Msg 21678, Level 16, State 1, Procedure sp_MSrepl_addpushsubscription_agent, Line 194
The parameter "@job_password" can be set to "NULL" only when "@job_login" is set to "NULL".

This only occurs when selecting "Push" for the delivery. If I select Pull it creates without errors. 

Any help would be appreciated. 

Thanks,

Dean Whitlock

Can we see your script?

The message:

Msg 21678, Level 16, State 1, Procedure sp_MSrepl_addpushsubscription_agent, Line 194
The parameter "@job_password" can be set to "NULL" only when "@job_login" is set to "NULL".

This indicates you have @job_password set to NULL and @job_login not set to NULL.  @job_password can only be set to NULL when @job_login is set to NULL.

use [KingPortals]
exec sp_addsubscription @publication = N'RepSnap', @subscriber = N'KING-SQL-CLT\CAMSDATA_CLT', @destination_db = N'KingPortals', @subscription_type = N'Push', @sync_type = N'replication support only', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'RepSnap', @subscriber = N'KING-SQL-CLT\CAMSDATA_CLT', @subscriber_db = N'KingPortals', @job_login = 'ntservers\repsnap', @job_password = N'passwordremoved', @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20130801, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'
GO

Interesting.  So you are specifying a value for the sp_addpushsubscription_agent @job_password parameter?

What version of SQL Server?

I changed the password from Null, Now I am getting this error:

Job 'KING-SQL-BRI\CAMSDATA-KingPortals-RepSnap-KING-SQL-CLT\CAMSDATA-11' started successfully.
Warning: The distribution agent job has been implicitly created and will run under the SQL Server Agent Service Account.
Msg 6522, Level 16, State 1, Procedure sp_MSgeneratenosyncsubscriptionsetupscript_sqlclr, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "sp_MSgeneratenosyncsubscriptionsetupscript_sqlclr": 
System.TypeInitializationException: The type initializer for 'System.Data.SqlClient.SqlConnection' threw an exception. ---> System.TypeInitializationException: The type initializer for 'System.Data.SqlClient.SqlConnectionFactory' threw an exception. ---> System.TypeInitializationException: The type initializer for 'System.Data.SqlClient.SqlPerformanceCounters' threw an exception. ---> System.MethodAccessException: Attempt by method 'System.Configuration.TypeUtil.CreateInstanceRestricted(System.Type, System.Type)' to access method 'System.Diagnostics.SwitchElementsCollection..ctor()' failed. ---> System.Security.SecurityException: Request failed.
System.Security.SecurityException: 
   at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(RuntimeAssembly asm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandleInternal rmh, SecurityAction action, Object demand, IPermission permThatFailed)
   at System.Security.CodeAccessSecurityEngine.CheckSetHelper(PermissionSet grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandleInternal rmh, Object assemblyOrString, SecurityAction action, Boolean throwException)
   at System.Security.PermissionListSet.CheckSetDemandWithModification(PermissionSet pset, PermissionSet& alteredDemandSet, RuntimeMethodHandleInternal rmh)
   at System.Security.PermissionListSet.CheckSetDemand(PermissionSet pset, RuntimeMethodHandleInternal rmh)
   at System.Security.PermissionListSet.DemandFlagsOrGrantSet(Int32 flags, PermissionSet g
...
System.TypeInitializationException: 
   at System.Data.SqlClient.SqlConnection..ctor()
   at System.Data.SqlClient.SqlConnection..ctor(String connectionString)
   at Microsoft.SqlServer.Replication.TransSqlClrCodeGenerators.GatherMetadataAndGenerateScript()
   at Microsoft.SqlServer.Replication.TransSqlClrCodeGenerators.sp_MSgeneratenosyncsubscriptionsetupscript_sqlc...
The Subscriber was dropped.
Msg 20021, Level 16, State 1, Procedure sp_MSrepl_addpushsubscription_agent, Line 258
The subscription could not be found.

Thanks, 

Dean Whitlock

SQL Server 2012 Enterprise Edition

Are you sysadmin?

Yes I am sysadmin

The .NET Framework error should not be occurring and is a cause for concern.  You may want to contact Microsoft Support or file an item on Microsoft Connect on that one.

In the meantime, can you try configuring the new peer node using the Configure Peer-to-Peer Topology Wizard as described in How to: Configure Peer-to-Peer Transactional Replication (SQL Server Management Studio) and see if that alleviates the error?

I got the same error using SSMS. It popped up the error screen dialog with the same .NET Error 

Thanks,

Dean Whitlock

We are running these on Server 2008 R2 using MS SQL 2012. We are going to drop back to MS SQL 2008 running on Server 2008 R2 and see if we can get it to work.

Thanks for your help 

Dean Whitlock 

We had the same problem while trying to create a P2P replication.
We are using Windows 2008 R2 and MS-SQL 2012 Enterprise Edition.

Creating subscription for 'DB2.A' on 'DB1.A'... (Error)
Messages
* SQL Server could not create a subscription for Subscriber 'DB2'. (Configure Peer-To-Peer Topology Wizard)

------------------------------
ADDITIONAL INFORMATION:

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

------------------------------

A .NET Framework error occurred during execution of user-defined routine or aggregate "sp_MSgeneratenosyncsubscriptionsetupscript_sqlclr": 
System.TypeInitializationException: The type initializer for 'System.Data.SqlClient.SqlConnection' threw an exception. ---> System.TypeInitializationException: The type initializer for 'System.Data.SqlClient.SqlConnectionFactory' threw an exception. ---> System.TypeInitializationException: The type initializer for 'System.Data.SqlClient.SqlPerformanceCounters' threw an exception. ---> System.MethodAccessException: Attempt by method 'System.Configuration.TypeUtil.CreateInstanceRestricted(System.Type, System.Type)' to access method 'System.Diagnostics.SwitchElementsCollection..ctor()' failed. ---> System.Security.SecurityException: Request failed.
System.Security.SecurityException: 
  at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(RuntimeAssembly asm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandleInternal rmh, SecurityAction action, Object demand, IPermission permThatFailed)
  at System.Security.CodeAccessSecurityEngine.CheckSetHelper(PermissionSet grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandleInternal rmh, Object assemblyOrString, SecurityAction action, Boolean throwException)
  at System.Security.PermissionListSet.CheckSetDemandWithModification(PermissionSet pset, PermissionSet& alteredDemandSet, RuntimeMethodHandleInternal rmh)
  at System.Security.PermissionListSet.CheckSetDemand(PermissionSet pset, RuntimeMethodHandleInternal rmh)
  at System.Security.PermissionListSet.DemandFlagsOrGrantSet(Int32 flags, PermissionSet g
...
System.TypeInitializationException: 
  at System.Data.SqlClient.SqlConnection..ctor()
  at System.Data.SqlClient.SqlConnection..ctor(String connectionString)
  at Microsoft.SqlServer.Replication.TransSqlClrCodeGenerators.GatherMetadataAndGenerateScript()
  at Microsoft.SqlServer.Replication.TransSqlClrCodeGenerators.sp_MSgeneratenosyncsubscriptionsetupscript_sqlc...
The subscription could not be found.
Changed database context to 'A'.
Job 'DB1-A-A-DB2-51' started successfully.
Warning: The distribution agent job has been implicitly created and will run under the SQL Server Agent Service Account.
The Subscriber was dropped. (Microsoft SQL Server, Error: 6522)

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

I had the same error initializing replication from backup and installing .NET 4.5 on the publisher, Subscriber and Distributor resolved it.

Thanks for the info. I will deploy this out to our test environment and see how it goes. 

Thanks, 

Dean Whitlock 

Installing .NET 4.5 solved the problem.

Thank you!

Hi.

  We had the same kind of problem after installing the KB2840628 fro Framework 4 from Windows Update. In the test environment the problem was fixed after uninstalling it.

  I hope it helps you. But we are searching for a better solution.

Cassio.

I am getting the same problem and using .netframework 3.5sp1