SQL 2005 and 2008 replication error

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)
LVL 4
bominthuAsked:
Who is Participating?
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
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
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
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
 
bominthuAuthor Commented:
i used domain admin account only.But get sa account error.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
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
 
bominthuAuthor Commented:
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
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Has replication ever worked, or is this a new setup?

//Marten
0
 
bominthuAuthor Commented:
yes it is a new setup
0
 
bominthuAuthor Commented:
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
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
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
 
bominthuAuthor Commented:
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
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Yes I will, but what kind of replication is set up?
Merge replication, transactional replication???

Regards Marten
0
 
bominthuAuthor Commented:
i choose other way to backup database.
don't use replication.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.