Replication security help please...

Hello,

I am being told that my SQL server can no longer use a domain account to do replication cause it is a violation of SOX codes... So here is my question to ease my pain....

I believe that I can run the SQL server service under [local system account] with no issues but what about the SQL server agent service?

It needs rights on all the servers right?

I have found where you can configure replication to use sql authentication but then I can use snapshots...

any help would be appriciated...

oh.. I use transactional and merge if that makes any difference.

Hi William,

Replication agents run under the context of a Windows account. The agents make Windows Integrated Security connections by using this account. The account under which the agent runs depends on how the agent is started. If you start the agent from a SQL Server Agent job, the default: When a SQL Server Agent job is used to start a replication agent, the agent runs under the context of an account that you specify when you configure replication.

So SQL server agent service account do not need rights on all the servers, it could be local system account as well.

Hope it helps.

Wanwen

|||

So if I change SQL agent to run as system account but change replication to run as an account that does have access to all servers that have replication, then this should work?

|||

Yes, Please remember to change SQL agent service account through SQL server configuration manager.

Wanwen

Replication problem(integrity violation) - Cannot Sync up emulator -HELP!

Can somebody help please - i keep getting this erro when trying to sycn
up my emulator to my Merged Replication DB.
"The row update or insert cannot be reapplied due to an integrity
violation. [,,,,,]"
Anybody know what this problem could be? I have recently dropped an
recreated a lot of my tables and changed some data types but did not
think it would effect me this badly.
I have also disabled and re-enabled my merge replication db many times.
I am sure all my connection settings are correct and the problem
appears to be deeper than that.
Recently i have changed the constraint on all of my tables to "Not to
enforce relationships on insert / update". It seems like this is what
the emulator is trying to do though?
Suggestions GREATLY appreciated!!!!
Thanks,
Steve
When you disable these constraints it means they will be fired twice.
The first time do to the originating action, the second time due to the
replicated command. This is probably not what you want unless you do not
have cascading updates and deletes on your subscriber.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"steroche" <steroche@.gmail.com> wrote in message
news:1125268893.110010.148790@.g44g2000cwa.googlegr oups.com...
> Can somebody help please - i keep getting this erro when trying to sycn
> up my emulator to my Merged Replication DB.
>
> "The row update or insert cannot be reapplied due to an integrity
> violation. [,,,,,]"
> Anybody know what this problem could be? I have recently dropped an
> recreated a lot of my tables and changed some data types but did not
> think it would effect me this badly.
> I have also disabled and re-enabled my merge replication db many times.
> I am sure all my connection settings are correct and the problem
> appears to be deeper than that.
> Recently i have changed the constraint on all of my tables to "Not to
> enforce relationships on insert / update". It seems like this is what
> the emulator is trying to do though?
> Suggestions GREATLY appreciated!!!!
> Thanks,
> Steve
>

Replication problem(integrity violation) - Cannot Sync up emulator -HELP!

Can somebody help please - i keep getting this erro when trying to sycn
up my emulator to my Merged Replication DB. When i look into he error
message via the VS debugger i can see this text in the SqlException
error:
"The row update or insert cannot be reapplied due to an integrity
violation. [,,,,,]"
Anybody know what this problem could be? I have recently dropped an
recreated a lot of my tables and changed some data types but did not
think it would effect me this badly.
I have also disabled and re-enabled my merge replication db many times.
I am sure all my connection settings are correct and the problem
appears to be deeper than that.
Recently i have changed the constraint on all of my tables to "Not to
enforce relationships on insert / update". It seems like this is what
the emulator is trying to do though'
Suggestions GREATLY appreciated!!!!
Thanks,
SteveHi
Is this any help?
http://support.microsoft.com/default.aspx?scid=kb;en-us;889521
You may want to try logging the updates:
http://support.microsoft.com/default.aspx?scid=kb;en-us;312292
John
"steroche" wrote:
> Can somebody help please - i keep getting this erro when trying to sycn
> up my emulator to my Merged Replication DB. When i look into he error
> message via the VS debugger i can see this text in the SqlException
> error:
> "The row update or insert cannot be reapplied due to an integrity
> violation. [,,,,,]"
>
> Anybody know what this problem could be? I have recently dropped an
> recreated a lot of my tables and changed some data types but did not
> think it would effect me this badly.
>
> I have also disabled and re-enabled my merge replication db many times.
> I am sure all my connection settings are correct and the problem
> appears to be deeper than that.
>
> Recently i have changed the constraint on all of my tables to "Not to
> enforce relationships on insert / update". It seems like this is what
> the emulator is trying to do though'
>
> Suggestions GREATLY appreciated!!!!
> Thanks,
> Steve
>|||Im afraid not. I have tried everything at this stage and its driving me
crazy!!!!!
I have re-published my database about 10times i'd say and i always get
an error.
Generally I get one of 2 errors. If i have already created the database
the errors are:
Message "Row data cannot be set. [,,,,,]"
NativeError 28572
and
Message "Invalid row handle"
NativeError 0
However if i dont have it created previous to running it and it creates
it itself I get the error:
message "Run"
NativeError 28557
Message "The row update or insert cannot be reapplied due to an
integrity violation. [,,,,,]
NativeError 28549
What the hell is going on' I have checked all the tables to make sure
that the checkbox "Enforce Relationship for replication" IS checked so
the NOT FOR REPLICATION bug doesn't apply here.
I have checked over all my datatypes and relationships and the data
that they contain to ensure that there can be no FK conflicts.
I know the table is being created as i can see it on the CE Query
analyser but it contains no tables so this probably gives rise to the
second error but why is it not creating the db properly in the first
place'
I heard that compacting the db might help - how do i do that'|||Hi
Unfortunately I am not an expert on replication!
Hilary Cotter who has replied in microsoft.public.sqlserver.replication
knows alot more about replication than I.
John
"steroche" wrote:
> Im afraid not. I have tried everything at this stage and its driving me
> crazy!!!!!
> I have re-published my database about 10times i'd say and i always get
> an error.
> Generally I get one of 2 errors. If i have already created the database
> the errors are:
> Message "Row data cannot be set. [,,,,,]"
> NativeError 28572
> and
> Message "Invalid row handle"
> NativeError 0
> However if i dont have it created previous to running it and it creates
> it itself I get the error:
> message "Run"
> NativeError 28557
> Message "The row update or insert cannot be reapplied due to an
> integrity violation. [,,,,,]
> NativeError 28549
> What the hell is going on' I have checked all the tables to make sure
> that the checkbox "Enforce Relationship for replication" IS checked so
> the NOT FOR REPLICATION bug doesn't apply here.
> I have checked over all my datatypes and relationships and the data
> that they contain to ensure that there can be no FK conflicts.
> I know the table is being created as i can see it on the CE Query
> analyser but it contains no tables so this probably gives rise to the
> second error but why is it not creating the db properly in the first
> place'
> I heard that compacting the db might help - how do i do that'
>|||Check that your subset filters and merge filters in the publication are
correctly constructed.
You are potentially sending down some data on a foreign key that is getting
filtered out.
Rob
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:F13AC93E-5354-4A2F-A066-2B3D22AA6D8F@.microsoft.com...
> Hi
> Unfortunately I am not an expert on replication!
> Hilary Cotter who has replied in microsoft.public.sqlserver.replication
> knows alot more about replication than I.
> John
> "steroche" wrote:
>> Im afraid not. I have tried everything at this stage and its driving me
>> crazy!!!!!
>> I have re-published my database about 10times i'd say and i always get
>> an error.
>> Generally I get one of 2 errors. If i have already created the database
>> the errors are:
>> Message "Row data cannot be set. [,,,,,]"
>> NativeError 28572
>> and
>> Message "Invalid row handle"
>> NativeError 0
>> However if i dont have it created previous to running it and it creates
>> it itself I get the error:
>> message "Run"
>> NativeError 28557
>> Message "The row update or insert cannot be reapplied due to an
>> integrity violation. [,,,,,]
>> NativeError 28549
>> What the hell is going on' I have checked all the tables to make sure
>> that the checkbox "Enforce Relationship for replication" IS checked so
>> the NOT FOR REPLICATION bug doesn't apply here.
>> I have checked over all my datatypes and relationships and the data
>> that they contain to ensure that there can be no FK conflicts.
>> I know the table is being created as i can see it on the CE Query
>> analyser but it contains no tables so this probably gives rise to the
>> second error but why is it not creating the db properly in the first
>> place'
>> I heard that compacting the db might help - how do i do that'
>>

Replication problem(integrity violation) - Cannot Sync up emul

Hi
Unfortunately I am not an expert on replication!
Hilary Cotter who has replied in microsoft.public.sqlserver.replication
knows alot more about replication than I.
John
"steroche" wrote:

> Im afraid not. I have tried everything at this stage and its driving me
> crazy!!!!!
> I have re-published my database about 10times i'd say and i always get
> an error.
> Generally I get one of 2 errors. If i have already created the database
> the errors are:
> Message "Row data cannot be set. [,,,,,]"
> NativeError 28572
> and
> Message "Invalid row handle"
> NativeError 0
> However if i dont have it created previous to running it and it creates
> it itself I get the error:
> message "Run"
> NativeError 28557
> Message "The row update or insert cannot be reapplied due to an
> integrity violation. [,,,,,]
> NativeError 28549
> What the hell is going on' I have checked all the tables to make sure
> that the checkbox "Enforce Relationship for replication" IS checked so
> the NOT FOR REPLICATION bug doesn't apply here.
> I have checked over all my datatypes and relationships and the data
> that they contain to ensure that there can be no FK conflicts.
> I know the table is being created as i can see it on the CE Query
> analyser but it contains no tables so this probably gives rise to the
> second error but why is it not creating the db properly in the first
> place'
> I heard that compacting the db might help - how do i do that'
>Check that your subset filters and merge filters in the publication are
correctly constructed.
You are potentially sending down some data on a foreign key that is getting
filtered out.
Rob
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:F13AC93E-5354-4A2F-A066-2B3D22AA6D8F@.microsoft.com...
> Hi
> Unfortunately I am not an expert on replication!
> Hilary Cotter who has replied in microsoft.public.sqlserver.replication
> knows alot more about replication than I.
> John
> "steroche" wrote:
>sql

Replication Issue Syntax error or access violation

I am replication on a SQL Server 2000 env on Windows Server 2003. I randomly
get an access violation error stating "Syntax error or access violation" ODBC
error 37000. After further investigation using sp_browsereplcmds I see that
a blank field in the db is sending the following repeated about 30 times.
''+{fn CHAR(0)}+''+{fn CHAR(0)}
I am using sql server generated procs. I also have 4 other publishers using
the same table without an errors. I am looking for a solution rather than a
work around. Any ideas?
Thanks,
TK
This looks like a bug. Can you call PSS on it.
If you are looking for a work around, study the transaction which caused
this sequence of commands. Sometimes if you set columns to "", you can get
some weird occurences. Most of these problems have been patched however.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"TK" <TK@.discussions.microsoft.com> wrote in message
news:7DAEE7FE-2CA7-495B-8E8D-4B65EB40BA30@.microsoft.com...
>I am replication on a SQL Server 2000 env on Windows Server 2003. I
>randomly
> get an access violation error stating "Syntax error or access violation"
> ODBC
> error 37000. After further investigation using sp_browsereplcmds I see
> that
> a blank field in the db is sending the following repeated about 30 times.
> ''+{fn CHAR(0)}+''+{fn CHAR(0)}
> I am using sql server generated procs. I also have 4 other publishers
> using
> the same table without an errors. I am looking for a solution rather than
> a
> work around. Any ideas?
> --
> Thanks,
> TK