Solved

SQL 2005 and 2008 replication error

Posted on 2011-03-16
12
719 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
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

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…
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…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

785 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