December 22, 2009 at 8:58 am
Hi All,
I'll try not leave out any details: I have a snapshot database push replication set up between two 2005 (64 bit) boxes using SQL authentication to connect to the subscriber and it goes once a week like clockwork. Publisher and Distributor are on the same server. I have a new SQL 2008 cluster (active/passive) that is configured for windows authentication only and I can't seem to get this replication going to that one. The 2005 publisher server and the 2008 cluster are in a domain together, though on different subnets. A domain user was added as dbo to the subscriber database on the cluster, and also on the publisher database and the distributor database, added to the publication PAL and given read access to the snapshot folder.
I've tried setting up another push subscription to go to the cluster but I only get the following error that makes no sense to me at all:
Creating Subscription(s)...
- Creating subscription for 'DBSQLCL1\DBSQLCL1' (Error)
Messages
* SQL Server could not create a subscription for Subscriber 'DBSQLCL1\DBSQLCL1'. (New Subscription Wizard)
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.
Changed database context to 'TransitSchedule'.
Job 'MTDBDN1T-TransitSchedule-TransitSchedule-DBSQLCL1\DBSQLCL1-11' started successfully.
Warning: The distribution agent job has been implicitly created and will run under the SQL Server Agent Service Account. (Microsoft SQL Server, Error: 3930)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4035&EvtSrc=MSSQLServer&EvtID=3930&LinkId=20476
- Starting the synchronization agent(s) (Stopped)
I've also tried setting up a pull subscription from the cluster and it almost completes everything but errors out when it runs saying it can't connect to the distributor. I've even gone as far as making this domain account administrator on both boxes and sysadmin in both SQL instances, to no avail.
The push subscription I tried to accomplish with SSMS on my desktop. I can't attempt it on the 2005 server because it doesn't have 2008 SSMS and 2005 SSMS can't make the connection to to the 2008 instance to set up the subscription. I tried the pull subscription from the 2008 both from my desktop SSMS and from SSMS on the cluster server. Pretty much the same results.
This is actually (perhaps shamefully) new territory for me in disabling sql authentication on an instance and trying to do it the right way. I thought since they were in the same domain that this would be easy, but wouldn't you know. If anyone recognizes the one little permission (or whatever) that I seem to be missing here, I would be extremely grateful.
many thanks,
John
December 23, 2009 at 3:58 am
I seem to remember hitting a similar problem. Can you tell me what you have in the connection settings for the subscriber?
Open subscription Properties - Security section
You choose the agent process account and the subscriber connection information (as attached)
Oraculum
December 23, 2009 at 10:15 am
This is resolved. The account running the SQL Agent service was a local account while the account set in the subscription for the replication agents was a domain account. (The box was newly added to a domain.) The documentation seems to suggest that this should be good enough, but changing the Agent service account to a domain account solved the problem. It seems the SQL Agent has to be able to satisfy itself that the domain account credentials I provided for the replication tasks are valid.
Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply