Solved

SQL 2005 and 2008 replication error

Posted on 2011-03-16
12
731 Views
Last Modified: 2012-06-21
Hi Expert,
I'm trying to do replication in SQL server, I'm keep getting below message. Could you advise?

Error messages:
Message: Login failed for user 'sa'.
Stack:    at Microsoft.SqlServer.Replication.ServerConnectionEmulator.Connect()
   at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.EstablishPublisherConnection()
   at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
   at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
   at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: MSSQLServer, Error number: 18456)
0
Comment
Question by:bominthu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 20

Expert Comment

by:Marten Rune
ID: 35154692
Replication should use domain account, not SA account.

For a guide, look at:
http://www.techrepublic.com/blog/howdoi/how-do-i-configure-transactional-replication-between-two-sql-server-2005-systems/123

//Marten
0
 
LVL 4

Author Comment

by:bominthu
ID: 35154768
i used domain admin account only.But get sa account error.
0
 
LVL 20

Expert Comment

by:Marten Rune
ID: 35154833
Question/suggestions for you. Pleas respond back with answers to the five questions belov!

1. Do you log failed logins, if not enable it!
2. Try a profiler filtered on the domain login you use for replication
3. Does this account have read, write and list files and folders permissions on the snapshot location and the files and folders under it. Do you see any activity?
4. Try to logon with SA on both your machines to see if password has been changed, does it work?
5. Report in thread version/SP/hotfixes and underlying OS for your machines!

Regards Marten
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 4

Author Comment

by:bominthu
ID: 35155011
1 .account is enabled
2. How to do that ?
3. Yes, account is domain admin Publication is always 100% complete without error. Only subscription has problem. (error is log in fail to sa but i don't even use sa to log in/ SQL svr can't access to scriber)

4. I can log in to machine B but haven't tried in second machine A as i don't know sa pw but it is enabled)

5.machine A is SQL 2005 Standard SP3, machine B is SQL 2008 Enterprise Evluate edition , I'm able to connect to SQL 2005 from SQL 2008 server but unable to connect to SQL 2008 from 2005 server)
0
 
LVL 20

Accepted Solution

by:
Marten Rune earned 500 total points
ID: 35155060
Do you have failed logins other than SA?
Use profiler, start in SSMS under Tools, or under
Start-meny\Program\Microsoft SQL Server 2005\Performance Tools
The icon SQL ServerProfiler

Look at: http://www.codeproject.com/KB/dotnet/SQLServerProfiler.aspx
to get started.

Note the profiler is an overhead to sql, so if your production is stressed, experiment and learn it using a test system!

Now when you begun using profiler, can you see anything from login the domain account used for replication. I hope you have a dedicated domain account for this!

Regards Marten
0
 
LVL 20

Expert Comment

by:Marten Rune
ID: 35155065
Has replication ever worked, or is this a new setup?

//Marten
0
 
LVL 4

Author Comment

by:bominthu
ID: 35155641
yes it is a new setup
0
 
LVL 4

Author Comment

by:bominthu
ID: 35155925
Hi Marten,
I manage to solve the problem. It's Ok now. Could you let me know how to make sure replication is completed for next days?

Do I need to always keep open SQL server Management studio in order to replicate successfully?

How can I make sure it is replicating continously ?

Thanks,
BMT
0
 
LVL 20

Expert Comment

by:Marten Rune
ID: 35156552
No SSMS does NOT need to be open. Its just used to manage one or many sql instances.

Check replication status:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_20993524.html?sfQueryTermInfo=1+10+30+check+replic+statu

//Marten
0
 
LVL 4

Author Comment

by:bominthu
ID: 35162070
Hi Marten,
WHen I run those query from the link you mentioned above, I only get message (Command(s) completed successfully.)
How can I know which database replication is completed successfully?
I have setup 3 databases for erplication.
Could you advise ?

Tks.
BMT
0
 
LVL 20

Expert Comment

by:Marten Rune
ID: 35163895
Yes I will, but what kind of replication is set up?
Merge replication, transactional replication???

Regards Marten
0
 
LVL 4

Author Comment

by:bominthu
ID: 35308725
i choose other way to backup database.
don't use replication.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question