Solved

SQL 2005 and 2008 replication error

Posted on 2011-03-16
12
724 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Change this SQL to get all nodes 3 55
Checking for column changes SQL 2014 4 41
How do I partition this table on date? 5 54
SQL DATE Past due to current wek 4 22
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

732 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