• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 460
  • Last Modified:

sql replication

Hello,

I'm trying to set up a publication in sql 2005 but i'm having no luck getting it to show up in the publication list for the subscriber. I believe it's security related. I'm doing everything locally on the same machine, and I really just need it for a one-time snapshot. Here is what I have done.

1. I created a simple publication in sql 2005 for the database I wanted to replicate. Publication Type = Snapshot; Articles = all user tables; Snapshot Agent = Create a snapshot immediately and keep the snapshot available to initialize subscriptions; Agent Security = run under my windows login [domain\user] and connect to the publisher by impersonating the process acount. That succeeded.

2. I had an empty mobile sdf file opened in my sql 2005. So for the mobile database, I right-clicked on Replication and Configure Web Synchronization. Subscriber Type = SQL Server Mobile Edition; Computer running IIS = my machine name; Create a new virtual directory = true; vdir name = DatabaseReplication; vdir path = default; Secure Communications = Do not requre SSL; Client Authentication = Clients will be authenticated Authenticated Access = Integrated Windows authentication; Directory Access = my windows login and ASPNET user (I'm on windows XP). That was done.

3. I right-clicked on my publication on the publisher to confirm security configurations. In properties, Publication Access List includes my windows login user. I also added user "sa", machine\ASPNET, and machine\IUSR_machine.

4. I right-clicked on Subscriptions on the subscriber, and New Subscriptions... Choose Publication: publisher = my server, connecting using windows authentication. And then, nothing showed up in the publications list. :(

When I checked my SQL Server Agent on the publisher, in Error Logs it had the following two errors repeatedly:

Date            2/6/2008 11:20:00 AM
Log            SQL Agent (Current - 2/6/2008 11:20:00 AM)

Message
[298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'DOMAIN\USER', error code 0x5. [SQLSTATE 42000] (ConnIsLoginSysAdmin)

Date            2/6/2008 11:20:00 AM
Log            SQL Agent (Current - 2/6/2008 11:20:00 AM)

Message
[298] SQLServer Error: 3621, The statement has been terminated. [SQLSTATE 01000] (ConnIsLoginSysAdmin)


DOMAIN\USER is my windows login. I must have missed something. Please help. Thank you!
0
hccfsa
Asked:
hccfsa
  • 4
  • 3
1 Solution
 
chapmandewCommented:
Try to use the SA account when creating the snapshot rather than a windows domain account.  One of the machines appears that it cannot recognize the domain user.
0
 
hccfsaAuthor Commented:
Hi. Thank you chapmandew.

I have tried the other security options for Agent Security configuration, which didn't work either. What I'm puzzled about is why would sql not recognize my domain user? I'm a local admin and both publisher and subscriber are on the same machine.
0
 
chapmandewCommented:
Is the publication database enabled for publication and the subscriber enabled for subscription?  

Do you have to use replication?  Can you just use SSIS to transfer the table?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
hccfsaAuthor Commented:
Hi thank you again.

I'm sorry but I'm really new to sql replication. How do you enabled the database for publication and subscription? Thanks.
0
 
hccfsaAuthor Commented:
Hi chapmandew,

I didn't forget you. :) Thank you for the link. That project got pushed back so I haven't gotten time to work on that. I will let you know as soon as I get back to that project. Thank you.
0
 
chapmandewCommented:
No problem.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now