Authentication with MSSQL 2005 Mobile Merge Replication

Hi,
I'm using MSSQL 2005 Mobile subscriber to partitioned publication (with
merge replication). In my case the partition is security-related, so it's
important to ensure that different subscribers will get only their own data.
Since I can't use SUSER_NAME(), and since HOST_NAME() can be overriden, what
are the options to authenticate the user properly?
Thanks,
Vladimir K.
You can use suser_name, it maps to publisherlogin, I also don't quite
understand what you mean by host_name can be overridden - it can be
overridden by the hostname parameter in your merge replication class
(SQLCeReplication) to define whatever you want, which could be the account
name you wish to filter by.
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
"Vladimir Kofman" <vladimir.kofman@.gmail.com> wrote in message
news:u9lqmIRSHHA.4832@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I'm using MSSQL 2005 Mobile subscriber to partitioned publication (with
> merge replication). In my case the partition is security-related, so it's
> important to ensure that different subscribers will get only their own
> data. Since I can't use SUSER_NAME(), and since HOST_NAME() can be
> overriden, what are the options to authenticate the user properly?
> Thanks,
> Vladimir K.
>
|||In SQL Mobile suser_name doesn't work for me... And the problem with
host_name is exactly as you stated: anyone can define anything, so it seems
I don't have an option to define partitions based on some security setting:
let's say I have two partitions A and B, and have two users a and b
appropriately. How do I prevent from user a to synchronize with partition B?
(and from user b with A?)
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uMU67pSSHHA.2212@.TK2MSFTNGP02.phx.gbl...
> You can use suser_name, it maps to publisherlogin, I also don't quite
> understand what you mean by host_name can be overridden - it can be
> overridden by the hostname parameter in your merge replication class
> (SQLCeReplication) to define whatever you want, which could be the account
> name you wish to filter by.
> --
> 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
>
> "Vladimir Kofman" <vladimir.kofman@.gmail.com> wrote in message
> news:u9lqmIRSHHA.4832@.TK2MSFTNGP04.phx.gbl...
>
|||how about using certificates mapped to nt accounts. This way they will have
to know the password of the account you are going to pull with. The
communication will be encrypted and you can filter on suser_name which maps
to the publisherlogin.
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
"Vladimir Kofman" <vladimir.kofman@.gmail.com> wrote in message
news:endEvgVSHHA.1228@.TK2MSFTNGP06.phx.gbl...
> In SQL Mobile suser_name doesn't work for me... And the problem with
> host_name is exactly as you stated: anyone can define anything, so it
> seems I don't have an option to define partitions based on some security
> setting: let's say I have two partitions A and B, and have two users a and
> b appropriately. How do I prevent from user a to synchronize with
> partition B? (and from user b with A?)
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uMU67pSSHHA.2212@.TK2MSFTNGP02.phx.gbl...
>
|||Thanks for your replies
But can you elaborate a bit more on the option you've suggested?
Thanks again.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eLdVCrVSHHA.2212@.TK2MSFTNGP02.phx.gbl...
> how about using certificates mapped to nt accounts. This way they will
> have to know the password of the account you are going to pull with. The
> communication will be encrypted and you can filter on suser_name which
> maps to the publisherlogin.
> --
> 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
>
> "Vladimir Kofman" <vladimir.kofman@.gmail.com> wrote in message
> news:endEvgVSHHA.1228@.TK2MSFTNGP06.phx.gbl...
>
|||You have to configure the web serve to accept client certificates. This was
supported in SQL CE 2.0, it appears to be supported in Web Synchronization
as well, but it is not clear from the documentation that it is.
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
"Vladimir Kofman" <vladimir.kofman@.gmail.com> wrote in message
news:OL9wDOWSHHA.3948@.TK2MSFTNGP05.phx.gbl...
> Thanks for your replies
> But can you elaborate a bit more on the option you've suggested?
> Thanks again.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eLdVCrVSHHA.2212@.TK2MSFTNGP02.phx.gbl...
>
sql

Authentication problem

I am setting up replication for the first time and, as can be expected,
running into problems. The scenario is a server in one domain that will be a
publisher using a remote distributor in a different domain. When trying to
configure the publisher server, I keep getting a 'server does not exist or
access denied' when it tries to configure the remote distributor. I know the
server exists, so I figure I have messed up something in the way they
authenticate with each other. There is a 2-way trust between the domains and
none of the SQL or SQL Agent Services are running under the system accounts.
Any suggestions or links to good info would be most appreciated.
Thanks,
Bob Castleman
DBA Poseur
have a look at this link
http://support.microsoft.com/default...b;en-us;321822
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
"Bob Castleman" <nomail@.here> wrote in message
news:OfeDgG1SFHA.3244@.TK2MSFTNGP15.phx.gbl...
> I am setting up replication for the first time and, as can be expected,
> running into problems. The scenario is a server in one domain that will be
a
> publisher using a remote distributor in a different domain. When trying to
> configure the publisher server, I keep getting a 'server does not exist or
> access denied' when it tries to configure the remote distributor. I know
the
> server exists, so I figure I have messed up something in the way they
> authenticate with each other. There is a 2-way trust between the domains
and
> none of the SQL or SQL Agent Services are running under the system
accounts.
> Any suggestions or links to good info would be most appreciated.
> Thanks,
> Bob Castleman
> DBA Poseur
>
|||THanks!
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OZfKQG2SFHA.2128@.TK2MSFTNGP15.phx.gbl...
> have a look at this link
> http://support.microsoft.com/default...b;en-us;321822
> --
> 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
> "Bob Castleman" <nomail@.here> wrote in message
> news:OfeDgG1SFHA.3244@.TK2MSFTNGP15.phx.gbl...
> a
> the
> and
> accounts.
>

authentication error after adding an article

Hi

-- edited 26/02 to clarify the security model --

Using HTTPS merge replication

The subscriber was able to get the snapshot ok, then synchronise fine

However after adding an article to the publication and recreating the snapshot I get this error now at the subscriber

Microsoft.SqlServer.Replication.ComErrorException (0x80004005): A failure occurred when accessing 'usp_misc_dailyTasks_3.sch' due to an operating system error [2='The system cannot find the file specified.'] during Web synchronization. Ensure that the -InternetLogin user when using basic authentication and the user running the merge when using Windows integrated authentication has been granted access to the snapshot share.

at Microsoft.SqlServer.Replication.MergeSynchronizationAgent.Run()

The key properties when synchronising are as follows:
agent.PublisherSecurityMode = SecurityMode.Standard
agent.DistributorSecurityMode = SecurityMode.Standard
agent.InternetSecurityMode = SecurityMode.Standard

In order to create the subscription:

subscription.UseWebSynchronization = True
subscription.InternetUrl = webSyncUrl
subscription.InternetSecurityMode = AuthenticationMethod.BasicAuthentication
subscription.SubscriberType = MergeSubscriberType.Anonymous
subscription.SyncType = SubscriptionSyncType.Automatic

Thanks
Bruce

is it possible that the merge agent was running the same time as the snapshot agent, and that the merge agent was trying to apply a file that was just deleted, but not-yet-created by the snapshot agent?sql

ATTN UK SQL REPLICATION TROUBLESHOOTERS

I work for a company one of whose clients is experiencing problems with
performance of SQL 2000 replication over a wan.
We need to bring in a consultant to both reproduce the problem in-house, and
also to solve the problem for our clients.
Please let me know if there are any consultancies that specialise in this
kind of work. Based in the Maidenhead area.
Many thanks
(I have replied to your address directly).
Regards,
Paul Ibison
|||just in case you made a typo in UK and meant US, contact me off line
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23YgEnwKeEHA.2352@.TK2MSFTNGP09.phx.gbl...
> (I have replied to your address directly).
> Regards,
> Paul Ibison
>

attaching subscription database woes

I'm trying to get to a point where I can implement structural changes to a
merge replication system. My plan is to ship copies of a subscription
database to enduser outside the corp firewall and write some code to load
the new subscription database. I've done the following:
1. create a subscription db on the same server as the publisher
2. use the 'copy a subscription database' menu choice to create a copy of
it.
3. move the copy to target server.
4. in the replication folder on the target server select: 'attach
subscription database', select the msf file, specify a name for the db.
5. run sp_addMergeSubscription on the publication server as follows:
exec sp_addmergesubscription
@.publication = 'rep_test_hub',
@.subscriber = 'k-server',
@.subscriber_db = 'rep_test_sub2',
@.subscription_type = 'pull',
@.sync_type = 'none'
6. on the publisher - drill down to the publisher list, refresh it and note
the addition of 'rep_test_sub2'. At this point the status is 'deactivated'
7. synch with the publisher on subscriber
The above works fine when both pub and sub are on the same box. When I try
this on another pc in a peer-peer network I get the following error:
Event Type: Information
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 203
Date: 1/20/2006
Time: 1:20:32 PM
User: N/A
Computer: k-HOME
Description:
SubSystem Message - Job
'K-SERVER-rep_test_hub-rep_test_hub-K-HOME-rep_test_sub6- 0'
(0x826C45BA92AD5F4E9B37842A22DB6F0B), step 1 - The process could not connect
to Distributor 'K-SERVER'. Login failed for user 'NT AUTHORITY\ANONYMOUS
LOGON'.
Why is the synch attempt using an 'anonymous logon'?
I've checked my SQL-Agent properties and it is setup for 'SQL-Server
Authentication' in the connection tab on both systems. Both server have
identical 'sa' passwords. The startup account is 'system account' on the
sub PC and a domain admin account on the hub PC. The sub PC is not part of
the domain.
Any suggestions?
Thanks.
Run SQL Server agent under a local admin account on the 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
"astro" <astro@.bcmn.com> wrote in message
news:U2bAf.38900$7S.35782@.tornado.rdc-kc.rr.com...
> I'm trying to get to a point where I can implement structural changes to a
> merge replication system. My plan is to ship copies of a subscription
> database to enduser outside the corp firewall and write some code to load
> the new subscription database. I've done the following:
> 1. create a subscription db on the same server as the publisher
> 2. use the 'copy a subscription database' menu choice to create a copy of
> it.
> 3. move the copy to target server.
> 4. in the replication folder on the target server select: 'attach
> subscription database', select the msf file, specify a name for the db.
> 5. run sp_addMergeSubscription on the publication server as follows:
> exec sp_addmergesubscription
> @.publication = 'rep_test_hub',
> @.subscriber = 'k-server',
> @.subscriber_db = 'rep_test_sub2',
> @.subscription_type = 'pull',
> @.sync_type = 'none'
> 6. on the publisher - drill down to the publisher list, refresh it and
> note the addition of 'rep_test_sub2'. At this point the status is
> 'deactivated'
> 7. synch with the publisher on subscriber
> The above works fine when both pub and sub are on the same box. When I
> try this on another pc in a peer-peer network I get the following error:
> Event Type: Information
> Event Source: SQLSERVERAGENT
> Event Category: Job Engine
> Event ID: 203
> Date: 1/20/2006
> Time: 1:20:32 PM
> User: N/A
> Computer: k-HOME
> Description:
> SubSystem Message - Job
> 'K-SERVER-rep_test_hub-rep_test_hub-K-HOME-rep_test_sub6- 0'
> (0x826C45BA92AD5F4E9B37842A22DB6F0B), step 1 - The process could not
> connect to Distributor 'K-SERVER'. Login failed for user 'NT
> AUTHORITY\ANONYMOUS LOGON'.
> Why is the synch attempt using an 'anonymous logon'?
> I've checked my SQL-Agent properties and it is setup for 'SQL-Server
> Authentication' in the connection tab on both systems. Both server have
> identical 'sa' passwords. The startup account is 'system account' on the
> sub PC and a domain admin account on the hub PC. The sub PC is not part
> of the domain.
>
> Any suggestions?
> Thanks.
>
>

attach/ detach of dbs

hi,
Is it possible to attach and detach databases
participating in replication?
regards,
bharath| hi,
|
| Is it possible to attach and detach databases
| participating in replication?
|
| regards,
| bharath
--
Hi Bharath,
From SQL Server books online:
"You cannot detach a database while it is being replicated."
May I ask what your specific requirements are?
--
Eric Cárdenas
SQL Server support|||HiEric,
The requirement is simple. I need to pass on the physical data and log files of one database participating in replication to another server( managed by another group). I do not want other databases to be brought down as well (shut down of sql). That is why this question arose...
>--Original Message--
>| hi,
>| >| Is it possible to attach and detach databases >| participating in replication?
>| >| regards,
>| bharath
>--
>Hi Bharath,
>From SQL Server books online:
>"You cannot detach a database while it is being replicated."
>May I ask what your specific requirements are?
>--
>Eric C=E1rdenas
>SQL Server support
>.
>||||
| HiEric,
| The requirement is simple. I need to pass on the physical
| data and log files of one database participating in
| replication to another server( managed by another group).
| I do not want other databases to be brought down as well
| (shut down of sql). That is why this question arose...
--
Hi Bharath,
How about backing up the subscriber/publisher database participating in the
replication? You can then restore that backup to another server and you
have the same data. The mdf/ldf will be created for you when you restore.
Would this be a suitable workaround for you?
Hope this helps,
--
Eric Cárdenas
SQL Server support

Attach Question

Hello,
I'm having problems removing replication components from a db I need to
detach and move to a different server. The db won't be using replication on
the new server. Since all replication components aren't removed Ent Mgr
won't let me detach. Can I just stop the SQL services, copy the .mdf to the
new server, attach and cleanup replication there?
Any help appreciated!
Thanks in advance.Hi Mark
Taking a backup will be better than stopping the server.
You can remove replication using sp_removedbreplication and possibly
sp_dropdistributor see the topic "How to: Disable Publishing and
Distribution" in Books Online
John
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:0EA3E109-446C-4337-963C-1B7C120F3F52@.microsoft.com...
> Hello,
> I'm having problems removing replication components from a db I need to
> detach and move to a different server. The db won't be using replication
> on
> the new server. Since all replication components aren't removed Ent Mgr
> won't let me detach. Can I just stop the SQL services, copy the .mdf to
> the
> new server, attach and cleanup replication there?
> Any help appreciated!
> Thanks in advance.