September 5, 2010 at 3:57 am
Hi all,
I am struggling with practice 6 in the 'Configuring Replication Security' chapter of the 70-431 textbook.
I thought I had successfully completed practices 1-5, but evidently something is amiss.
Practice 1: Delete the Unsecure Replication
Practice 2: Prepare a Secure Environment
Practice 3: Use Scripts to Configure Publishing and Distribution
Practice 4: Finish the Secure Environment
Practice 5: Configure a Snapshot Publication
Practice 6: Configure a Subscription
Practice 7: Test the Replication Configuration
The gist of the error message is '@job_login must have a valid Windows login'. I can provide more specifics on what the practices involve, but as things stand I wouldn't know which details are relevant.
Thanks,
🙂
Mark Cranmer
The full error message when trying to execute the subscription in Practice 6 is:
===================================
SQL Server could not create a subscription for Subscriber 'SERVER1'. (New Subscription Wizard)
===================================
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)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand)
at Microsoft.SqlServer.Replication.ReplicationObject.ExecCommand(String commandIn)
at Microsoft.SqlServer.Replication.ReplicationObject.CommonCreate()
at Microsoft.SqlServer.Replication.Subscription.Create()
at Microsoft.SqlServer.Management.UI.CreateSubscriptionWizard.CreatePushSubscription(SubWizardSubscriber subscriber, SubWizardPublication pubInfo)
at Microsoft.SqlServer.Management.UI.CreateSubscriptionWizard.CreateSubscriptions(Boolean executeNow)
===================================
'@job_login' must be a valid Windows Login in the form : 'MACHINE\Login' or 'DOMAIN\Login'. Please see the documentation for 'sp_addpushsubscription_agent'.
Changed database context to 'ReplTesting'. (.Net SqlClient Data Provider)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=21797&LinkId=20476
------------------------------
Server Name: SERVER1
Error Number: 21797
Severity: 16
State: 1
Procedure: sp_MSrepl_addpushsubscription_agent
Line Number: 220
------------------------------
Program Location:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
September 5, 2010 at 4:17 pm
What value are you supplying for @job_login? Is it a valid/active account on the machine/domain the systems are a part of?
Joie Andrew
"Since 1982"
September 5, 2010 at 5:09 pm
Hi Joie
In order to address that question, I need to delve into the scripts produced by SSMS at various stages of the practice sequence. Please bear in mind that T-SQL is pretty new to me so please forgive me for not spotting what in all probability is fairly obvious.
All extracts containing '@job_login'
file: CreateProductsPublicationSecure.sql
exec sp_addpublication_snapshot @publication = N'Products', @frequency_type = 8, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 1, @frequency_subday = 1, @frequency_subday_interval = 1, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = N'Server1\ReplSnapAgent', @job_password = null, @publisher_security_mode = 1
file: CreateProductsSubscriptionSecure.sql
use [ReplTesting]
exec sp_addsubscription @publication = N'Products', @subscriber = N'SERVER1', @destination_db = N'SubsTesting', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'Products', @subscriber = N'SERVER1', @subscriber_db = N'SubsTesting', @job_login = N'\\Server1\ReplDistAgent', @job_password = null, @subscriber_security_mode = 1, @frequency_type = 8, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 1, @frequency_subday = 1, @frequency_subday_interval = 0, @active_start_time_of_day = 3000, @active_end_time_of_day = 235959, @active_start_date = 20100905, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'
GO
file:CreatePublication.sql
use [ReplTesting]
exec sp_addsubscription @publication = N'Products', @subscriber = N'SERVER1', @destination_db = N'SubsTesting', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'Products', @subscriber = N'SERVER1', @subscriber_db = N'SubsTesting', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 8, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 1, @frequency_subday = 1, @frequency_subday_interval = 0, @active_start_time_of_day = 3000, @active_end_time_of_day = 235959, @active_start_date = 20100830, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'
GO
mmm...this last null value looks suspect?
Anyway, thanks for you help.
Mark 🙂
September 5, 2010 at 5:32 pm
exec sp_addpushsubscription_agent @publication = N'Products', @subscriber = N'SERVER1', @subscriber_db = N'SubsTesting', @job_login = N'\\Server1\ReplDistAgent', @job_password = null, @subscriber_security_mode = 1, @frequency_type = 8, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 1, @frequency_subday = 1, @frequency_subday_interval = 0, @active_start_time_of_day = 3000, @active_end_time_of_day = 235959, @active_start_date = 20100905, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'
GO
When you are running this stored procedure (if this is exactly what is being run on your exercises), you are specifying this as the value for the @job_login parameter:
\\Server1\ReplDistAgent
This syntax and values does not seem correct. If you are specifying an account on a local machine, it should be in the form of:
Server1\RepDistAgent
This is also the same format if it is a domain account, exept that Server1 would be replaced by the name of the domain.
Speaking of Server1 and ReplDistAgent for that matter, I just want to verify something. Is Server1 the name of the machine you are running this script on, or the name of the domain the server is a member of? And is ReplDistAgent an account on that machine/domain? Because if not, even if the syntax is correct it will not work because the account would not be valid. A pre-existing account needs to be specified for the parameter.
Joie Andrew
"Since 1982"
September 9, 2010 at 3:19 pm
ooops, I now know what I did wrong...and it was a bit stupid! :blush:
I ran through the practices a number of times in order to address various issues that arose along the way, and after each sequence I deleted the publication and 'disabled publishing and distribution'. I had simply failed to reset ReplDistAgent as dbo for Distribution again.
It all seems so simple once you know what you did wrong... 🙂
Thanks for the support. I have gained some understanding of decyphering error messages along the way.
Mark
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply