SQL 2012 :: Security For Web Synchronization (Merge Replication)?
Feb 8, 2015
I am trying out merge replication and using web synchronization.However, I am worried that I am missing something because the way it is set up, it strikes me as a bit too insecure.
According to the best practices and security articles on Technet, I am given to understand that:
The SQL Replication Listener (read: the application pool account that will be running the replisapi.dll) has to be the db_owner to both distribution and publisher and be on the PAL list. Windows authenication should be used. That means the merge agents wouldn't need to know the password for those logins.
The basic authenication can be used (with SSL) to authenicate into a Windows user account to then connect to the replisapi.dll.
Here's the rub - I assumed that all I needed was a basic no-rights user account to be then given the execute permission on the replisapi.dll & read permissions to kick off the process. When I browse to the replisapi.dll and authenicate using the no-rights user, I get the expected "SQL Server WebSync ISAPI" message. But when I then run the merge agent, it fails saying that login to the distribution failed for the no-rights user. If I use the application pool's account, then I am able to run merge agent successfully.
But that means I am now looking at storing the password to the application pool account on client. I might have had missed a crucial step to ensure that the logins to the distribution & publication databases are done using the application pool account, not the user authenticated via IIS basic authentication?
View 0 Replies
ADVERTISEMENT
May 9, 2015
I'm trying out a set up as following:
SQL Server 2008 standard as the publisher
SQL Server 2012 express as the subscriber
and I tend to use web synchronization; there is no domain trust.
After following the instructions at MSDN for configuring web synchronization, I have an error that I can't get past - after creating the initial snapshot on the publisher, I try to run replmerg.exe at the subscriber and I always get this error:
"The subscription to publication 'TestReplication' has expired or does not exist."
If I refresh the publisher's "Local Publications" and look within the "TestReplication", it does show that the subscriber is a known subscriber. Likewise, if I refresh subscriber's "Local Subscription", it has an entry for TestReplication publication.
I already verified that the user used by Replisapi.dll has the read permission to the snapshot folder, is a member of PAL, is db_owner of the publishing database and distribution database. I am using self-signed certificate for this test and I have already installed the certificate at the subscriber machine so that HTTPS is trusted. I can run diagnosis from subscriber so I know subscriber can reach and logs are being left at the publisher's IIS.
View 1 Replies
View Related
Mar 5, 2007
Hi..
At first I want to explain my Problem:
We have an SQL Server 2005 Database in our company. We develope an Application for customers (with VC#.NET 2005) which uses SQL 2005 Express as local database. The Application tests some devices and creates a protocoll .
As you see, the local databases must always load the actual test infos from main database (server). And the server Database must load and combine protocoll rows from different local databases (or local database must send it).
How can I solve this problem?
I could use a remote connection over tcp/ip. but it would not be reliable. I read some Info about Web Synchronization for Merge Replication. Is it possible to replicate just selected tables (one-direction (from server to client)). And is it possible replicate different client tables and combine the rows of them in server table (one direction from client to server).
I hope I could explain it. Any idea? Thanks..
View 1 Replies
View Related
Jun 12, 2015
IIS8 running on Windows Server 2012R2 with SQL Server 2012 SP2 installed. I have installed SQL Compact 3.5 SP2 Server Tools (64-bit) and set up the configuration. But when I in IE type URL.... I have set up the NTFS restrictions to the virtual directory. Doesn't Web Synchronization Works with IIS8?
View 5 Replies
View Related
Apr 1, 2008
I am designing a mobile application.I am having database in sql server 2005 and i want to synchronize this database with sql server ce database on pda.I am using merge replication method for synchronization.Please give step by step procedure for data synchronization using merge replication between sql server 2005 and sql server ce 2.0,also let me know whether sql server enterprise manager is needed or will it work without it,if needed give me the link from where to download it as it is not available in sql server 2005.
Reply soon.
Thanks in advance.
View 1 Replies
View Related
Mar 9, 2006
I am running SQL Server 2000 and SQL Server CE on the handheld device. I have created the publication on the SQL Server.
I can subscribe to the publication successfully on the handheld and it creates the .sdf file just fine.
When I then attempt to synchronize, the process will run for a long time and eventually error out. (The database has a couple large data tables that are used for value lookups)
The error says the connection was broken.
Any suggestions on correcting this?
I am thinking of breaking the replication database into 2. One with the large lookup files and one with the updated tables. I still need to get the large database with the lookup tables onto the handheld though.
Any thoughts, advice or similar experience handling a situation as this would be appreciated.
thanks,
Dan
View 1 Replies
View Related
Aug 3, 2006
Hi I want to Set up Such Merge Replication, Which can can Synchronize With out Network, Means Syncronization using CD.
Also When the Network connectivity will be available then it should do synchronization
I wants to transfer some file using CD only those changes to database which hasn't been synchronized, thats why Backup will not work.
Pls Help me, or tell me wether it is possible or not.
Tell me some work around, or ask me details,
You got to help me out, Otherwise I will loss trust in MSDN Forums.
Thanks in advance
Vishalgiri Goswami
View 3 Replies
View Related
Oct 24, 2014
I'm updating some tables in a subscriber database with a stored procedure. After the tables get updated I'd like to sync them with the other subscriber dbs and the publisher db in that same stored procedure.I can do it manually in SSMS with the View Synchronization method. Are my only alternatives a batch job or C#?
View 1 Replies
View Related
Oct 20, 2014
I have some problem about Merge Replication.i'm trying to merge Database A to Database B in local Server. So Database i want publisher contain this:
Because only Data in Table change therefore so i choosed it 100% Snapshot was generated after that ( Problem not a valid window user i already figured out )After that. I created Local Subscription in same Server ( Pull subscriptions and Subscription type: Client ),now problem is throw out."The schema script 'vwBuyADT_513.sch' could not be propagated to the subscriber."
I tried research so many time in Google but any information i found isn't useful for this problem.This problem still can ignore,Synchronization still running. But after 4-5 Hours running..This messages throw out: "The Publisher failed to allocate a new set of identity ranges for the subscription. This can occur when a Publisher or a republishing Subscriber has run out of identity ranges to allocate to its own Subscribers or when an identity column data type does not support an additional identity range allocation"
My question is:
Is there anyway solve 2 probem? :
1. "The schema script 'vwBuyADT_513.sch' could not be propagated to the subscriber."
2. "The Publisher failed to allocate a new set of identity ranges for the subscription. This can occur when a Publisher or a republishing Subscriber has run out of identity ranges to allocate to its own Subscribers or when an identity column data type does not support an additional identity range allocation"
View 7 Replies
View Related
May 10, 2015
We are working in a Merge replication environment where we have SQL Server 2005, 11 publications and 2 subscribers.We used to get lot of incidents from the Application owner for blockings, recently we faced a situation where the lead blocker is in sleeping state and the session was used by the merge agent.Checked the query that the session was running, it was sys.sp_MSenumgenerations90;1.
View 1 Replies
View Related
Oct 7, 2015
We have a publisher sending data to two remote subscribers. Each of these sites is updating a different field in a particular table with its site name and the current date stamp. This data should then sync to each database to show how up to date the last data change was. This lets us keep an eye on whether sync is good or not.
The problem I've got is one subscriber isn't copying its row to the other servers anymore. It gets the row updates from the other sites in the same table but its own updates to this field aren't getting sent across. Nothing shows up in conflict manager for it and nor should it as no other subscriber should be updating this field. If I validate the subscription the field when then get synced but again no updates after the validation will transfer. The other problem which may be related or indicating another issue is the data transfer rate shown in replication monitor is less than 0.1 rows/sec. Reinitializing isn't an option.
View 0 Replies
View Related
Nov 3, 2015
I was reading this:
"Adding an identity column to a published table is not supported, because it can result in non-convergence when the column is replicated to the Subscriber. The values in the identity column at the Publisher depend on the order in which the rows for the affected table are physically stored. The rows might be stored differently at the Subscriber; therefore the value for the identity column can be different for the same rows."
I don't understand...
If I create a table with an identity column and publish it. Can the values on the subscriber be different when the data is replicated?
Suppose I have a this table:
1 Name1
2 Name2
3 Name3
Column 1 is identity field and column 2 the name of employees.
If I publish this table , the data can be inserted on the subscriber .pe, with 2 name1 and 1 name3 and 1 name2?
What about if the identify fields is a primary key?
View 6 Replies
View Related
Dec 3, 2014
I have a database with enabling merge replication.
Then the problem is update query is taking more time.But when I disable the merge triggers then it'll update quickly.
View 1 Replies
View Related
Feb 13, 2015
There is an error in one of my merge publication. The error is,
The change for the row with article nickname 2336003 (test), rowguidcol {436456F0-F5AD-E411-80CF-5CF3FC1D2D76} could not be applied at the destination. Further information about the failure reason can be found in the conflict logging tables.
When i checked my tables I got following values in rowguid column
publication 436456F0-F5AD-E411-80CF-5CF3FC1D2D76
subscriptionD824D120-23AD-E411-80E3-00155D0E1001
conflict tables 689C6A61-5359-4BB5-BECD-B03F5F94D79A
View 0 Replies
View Related
Jul 26, 2015
We have a table in an SQL Server 2012 database that stores tree-like structures. Simplified for the purpose of my question, it has the following format:
Id int identity,
ParentId int,
GroupId int
Each record of the table represents an object identified by Id. An object may or may not have a parent in the same table, such that object.ParentId = parentObject.Id. A root object has ParentId = NULL. There are multiple root objects, so the table in fact stores multiple trees. What’s important is that the tree depth is not fixed, i.e. theoretically there can be any number of ancestor generations for an object. GroupId is a property of a root object; in theory none of the children of a root object has to have GroupId <> NULL; it can be assumed that any child has the same GroupId value as its root object.
A sample table having two roots (one grandparent and one parent), one non-root parent/child and 4 child roots:
Id ParentId GroupId
----------------------------------------------------------
1 NULL 200 root grandparent
2 1 NULL non-root parent/child
3 2 NULL child
4 2 NULL child
5 NULL 300 root parent
6 5 NULL child
7 5 NULL child
The table is not normalised, i.e. there’s no separate {root_object : group} table. However I don’t think normalising the table would solve the problem.
Now the problem. We need to set up merge replication from the table above (Master table) to the table of the same format in another DB. We need to replicate only those rows of the Master table that have a certain fixed GroupId value, e.g. 200 in the example above. If we ensure that GroupId in all descendant objects of a root object has the same value in the table as the root object itself that would be trivial. The table would look like this:
Id ParentId GroupId
----------------------------------------------------------
1 NULL 200 root grandparent
2 1 200 non-root parent/child
3 2 200 child
4 2 200 child
5 NULL 300 root parent
6 5 300 child
7 5 300 child
And the filter would look like this:
WHERE GroupId = 200
However out of performance considerations, we would like to avoid if possible filling GroupId for the descendant objects, because as it must be clear from the above, GroupId for a descendant object is quite easily deducible via a stored procedure or UDF (just need to go up the tree until ParentId = NULL). The problem is, I don’t know how to achieve this in a merge replication filter: it would only allow WHERE conditions and joins. I’ve have not had much luck with joins for merge replication in general, but here we have more complex algorithm, because the number of tree levels can be different for every object. And merge replication would not allow using UDF…
View 2 Replies
View Related
Aug 12, 2015
How to resolve the data inconsistent errors in merge replication ?
View 0 Replies
View Related
Feb 21, 2007
Hi!
I've the following replication topology:
SrvA (Publisher/Distributor) -> (Transactional Publication/Push Synchronization) -> SrvB (Subscriber, Publisher/Distributor) -> (Merge Publication/Pull Synchronization via HTTPS for remote clients) -> Client (Anonymous Subscriber)
To test synchronization with this tier I do the following:
1. Update data on SrvA
2. Push changes to SrvB (there are transactions that replicated)
3. Run RMO Merge Agent (.NET application) on Client and get messsage "No data needed to be merged", although there were changes made on publisher SrvA and SrvB
I saw the thread where similar problem have been discussed (http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=664153&SiteID=17). The sugestion there was to create subscription using wizard rather then T-SQL script. But all my clients are remote and have only HTTPS connection to the IIS over 443. And in the MSDN there was the script that I used to create pull subscription for web synchronization.
Guys, HELP! I almost gave up - do not see the reason for the case in the documentation, settings and code.
Thank you.
Alexander.
View 5 Replies
View Related
Nov 23, 2006
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();
View 8 Replies
View Related
Apr 21, 2008
well, i m been working in sql 2005 since 2yrs but i m new in sql replication....
can anyone tell me how we come to know that there is replication for the synchronization between 2servers? or between publishers and subscibers?
means how its set up?
can anyone tell me? thanks.
View 4 Replies
View Related
May 8, 2007
In SQL 2005 when i expand the local publication , Under replication when i try to view Synchronization Status i see the below message.
The initial snapshot for publication '%Database' is not yet available.
can any one help me on this issue.
Thanks
View 3 Replies
View Related
Jun 8, 2015
What is impact, if i stop and start Synchronization, for one subscription.
When i start, will it resync all tables by dropping and recreating at subscription.
On Publisher,
from Location Publiction,
right click on subscription,
select View Synchronization Status, From here, stop and start
View 3 Replies
View Related
Dec 4, 2014
I use from sql server 2008. and c#
what is the best connectionstring?
I don't know if i use Persist Security Info and Integrated Security or not?
And if yes then their value must be true or false?
View 1 Replies
View Related
Aug 16, 2014
How can I synchronize SQL Server with SQL Server analysis Services?
View 0 Replies
View Related
Apr 19, 2007
Hello,
We have an environment with 7 servers that are running replication with one another and I'm wondering if there are any tools or experiences that any of you might have that may assist in the auditing of these servers. The data should be in synch accross the boards for all tables, but sometimes problems can arise such as replication not being set up properly, stored procedure's being out of synch, or data gliches etc.
In dealing with these issues we have an in-house written program which analises each table on each server and takes a snapshot and does column by column compare. We also have another program that will synch the data up (basically a delete/insert statement on the publisher). This process can take up to 3 weeks for our quarterly update of every table. I'm wondering if anybody has used any tools such as in SSIS or a third party tool and has done or is doing something similar to what we are doing now. If so, are there any tips you wouldn't mind sharing on how our process might be sped up?
Thanks,
Phil
View 4 Replies
View Related
May 26, 2015
What is the main difference between snapshot and transactional and merge replication?
View 5 Replies
View Related
Aug 30, 2007
Hi all,
I know that adding a column using ALTER TABLE to add a column automatically allows SQLSERVER 2005 to replicate the schema changes to the subscribers, however, I would like to add a new column to an existing article that is being used for merge replication, however, I don't want this column to be replicated. Re-initialising the subscriptions is not a option. Help would be appreciated.
I am using SQLSERVER 2005 (SP1).
View 3 Replies
View Related
Apr 13, 2015
I got this situation where my network admin observerd that there is a high network utilization between 2 nodes in our AG (the primary node & the DR site, 2 separate locations of course); then he advised to compress the data transfer between those 2 nodes as the previous DBA already did that before!
Ok, I have no clue about this, so decided to google it, got nothing. My backup is already compressed through some third party app (just in case if that matters to the subject).
View 3 Replies
View Related
Oct 9, 2007
I am working on bringing our disaster recovery site to be a live site. Currently we replicate to one of out servers (server B) with merge replication (from server A). Server A also does one way transactional replication form some table to several other servers including servers at the DR site.
This setup is not going to be fast enough for what we need so I am wondering if a table is receiving merge replication will the merge updates also replicate down the transaction path??
Example...
Server B update a row and merges to Server A. With this update them replicate (via transactional) to Server C??
thanks...
View 5 Replies
View Related
Feb 14, 2002
Hi everbody,
I setup the Merge Replication , it is working perfectly. But i have one problem now it is updating both ways. I nedd one way. Any body tell me which parameter i have to change.
Thanks in advance
View 1 Replies
View Related
Aug 12, 2002
Hi,
My production box is running on NT4.0,SP6, SQL Server7.0,SP2. We implemented Merge replication. Working fine last 7 months. Last weekend i disabled replication, Successfully removed Distributor and Publishor. After that try add new fileds but won't allowed me. It's give the error message. I Also found Some Conflict_tables found almost 20 tables. All system Tables. Can delete these these tables, if i delete any problem my database.
I added filelds many times but this time i got errors.
Please help me anybody.
View 6 Replies
View Related
Jul 31, 2000
I have just installed replication on our production server to Merge Replicate with a Laptop server that will travel from time to time. I have now noticed that we cannot add or change any fields or attributes on the tables which are being replicated (which are all tables in the DB). This is a problem because we are changing and adding columns all of the time. Is there a way around this issue like shutting down the replication service or something? I have been unsuccessful in finding a way around this other than removing replication while we make changes.
Thank you in advance for any help!
View 1 Replies
View Related
Jan 22, 2001
I have implemented a Merger replication on our development server and I get a fillowing error when I try to update one of the table in publisher.
"Transaction cannot start while in firehose mode"
What does this mean.
Thank You,
John
View 1 Replies
View Related
