Security problems in the initial synchronization

Hot Topic

How can I create a DTS package to update a table with data from an Excel spreadsheet?
index question???
Save a large string in database
SQLWB.EXE max cpu
Partitioned View Pruning Issue
How to make data sets of one report also available for other reports?
Report Model, which approach?
Execute As error
embedding a DTS package which contains a 'Copy SQL Server Objects Task'
Question about Interactive Sort

SQL Server

Dynamic table name in destination
SQL 2005 express edition
Year to date verses Last Year to Date
SELECT permission denied on object...
Peer-to-Peer Transactional Replication and deadlock
MDX Query/Caching/Performance Questions
Alter table in a Publication
Error when using 'LIKE'
CSV file as a data source
Group By

Security problems in the initial synchronization

Hello everybody, I would be thankful if you try to help me with my problem,

I am using merge replication under SQL Server 2005.

In our system there is a central database running SQL Server 2005 Enterprise Edition with a big database published for merge replication. There are too a lot of clients running SQL Server 2005 Express Edition that pull subscriptions to the central database. How the clients create subscriptions and run the synchronization process is posted below (RMO Progamming).

When running the syncrhonization process, the Distributor connects OK, the Publisher Connects OK... but when the snapshot copy process starts, the execution is halted with an init session error "unknown user name or wrong password" (more or less because I get the message in spanish ;)

I think that the problem is snapshot folder sharing permissions, but I do not kow how to solve it.

Thank you very much!

=========================== Subscription creation

subscription = new MergePullSubscription();

subscription->ConnectionContext = subscriberConn;

subscription->PublisherName = m_publisher_name;

subscription->PublicationName = m_publication_name;

subscription->PublicationDBName = m_publication_database_name;

subscription->DatabaseName = m_subscription_database_name;

subscription->HostName = m_host_name;

subscription->SynchronizationAgentProcessSecurity->Login = m_win_login;

subscription->SynchronizationAgentProcessSecurity.Password = m_win_password;

subscription->CreateSyncAgentByDefault = true;

subscription->Create();

publication->MakePullSubscriptionWellKnown(m_subscriber_name, m_subscription_database_name, SubscriptionSyncType::None, MergeSubscriberType::Local, 0);

=========================== Syncrhonization

subscription->SynchronizationAgent->Distributor = m_publisher_name;

subscription->SynchronizationAgent->DistributorSecurityMode = SecurityMode::Standard;

subscription->SynchronizationAgent->DistributorLogin= m_sql_user;

subscription->SynchronizationAgent->DistributorPassword = m_sql_user_password;

subscription->SynchronizationAgent->Publisher = m_publisher_name;

subscription->SynchronizationAgent->PublisherSecurityMode = SecurityMode::Standard;

subscription->SynchronizationAgent->PublisherLogin= m_sql_user;

subscription->SynchronizationAgent->PublisherPassword = m_sql_user_password;

subscription->SynchronizationAgent->Subscriber = m_subscriber_name;

subscription->SynchronizationAgent->SubscriberSecurityMode = SecurityMode::Standard;

subscription->SynchronizationAgent->SubscriberLogin= m_sql_user;

subscription->SynchronizationAgent->SubscriberPassword = m_sql_user_password;

subscription->SynchronizationAgent->HostName = m_host_name;

subscription->SynchronizationAgent->Status += new AgentCore::StatusEventHandler(this, &ReplicationManager::SynchronizationStatusChanged);

subscription->SynchronizationAgent->Synchronize();


Answer this question

Security problems in the initial synchronization

  • Thoras

    Grant the sync process account permissions on the snapshot share.

    Go to your snapshot share and grant read permissions to: m_win_login



  • CaptainSmudge

    Is not a SQL Server problem. The Windows credentials (domain, user and password) that the client machine uses to log into the filesystem of the server machine can be different from SQLReplicatorTEDDY (the windows user that have read permissions on the snapshot folder). I must change this credentials before execute synchronization to ensure a successfully snapshot folder copy operation.

  • Pete Nelson

    As you have already found out. there is no agents on the sqlexpress server so that the sp_change_subscriptions call failed.

    going back to the permission issue to access the snapshot folder, does the account running the sync process has the permission to the snapshot folder

    thanks

    yunwen



  • bilalso

    You have reason! because brakepointing the synchronization method the properties: subscription->SynchronizationAgentProcessSecurity->Login and subscription->SynchronizationAgentProcessSecurity->Password are null.

    I tried to change it in the syncronization method and I get the exception: Property "merge_job_login" update with value "ISENDS-MALAGA\SQLReplicatorTEDDY" failed.

    I tried to change it using the Management Studio with: sp_change_subscription_properties @publisher = 'ISENDS-MALAGA', @publisher_db = 'candidotest 1.0.9', @publication = 'acquisition', @property = 'merge_job_login', @value = 'ISENDS-MALAGA\SQLReplicatorTEDDY'

    But I get another error: Msg 14262, Level 16, State 1, Procedure sp_MSrepl_check_job_access, Line 63

    The specified job ('(null)') does not exist.

  • praveench2k

    I have an special windows accuount in the central server with read permissions in the snapshot folder (m_win_login and m_login_password are that account credentials) but when sync process connects the central server to copy the snapshot folder files uses the client windows account instead m_win_login and m_win_password. By creating a new windows acocunt in the central server with the same login and password as the client windows account that runs the program the system goes ok, but I think this is not the correct way to solve the problem. I don't know why the process get the client process credentials instead m_win_logion and m_win password :(


  • dga

    I know you have set the process account and password when creating the subscription.

    Can you try to set it again before synchronizingt too



  • ihd4me

    I'm sure that the problem is the SQL Server 2005 Edition in the client servers. I'm using SQL Server 2005 Express Edition, therefore the SQL Merge Agent is not created.

  • Security problems in the initial synchronization
Answer this question