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!
hccfsaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.