Solved

SQL 2005 and 2008 replication error

Posted on 2011-03-16
12
729 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

734 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