August 5, 2009 at 2:28 pm
[font="Tahoma"]
Hi,
we're having problems trying to implement transactional replication in our environment:
1 x Backend Server (Windows 2008 Server SE x64 SP2 / SQL Server 2008 SE x64 SP1 CumUpd 2 / 10.0.2714.0, 32 GB RAM, 4 x QuadCore 3.6 GHz Xeon, 8 internal disks + 15 disk array)
1 x Distribution Server (Windows 2008 Server SE x64 SP2 / SQL Server 2008 SE x64 SP1 CumUpd 2 / 10.0.2714.0, 16 GB RAM, 2 x DualCore, 6 internal disks)
2 x Frontend Server (Windows 2008 Server SE x64 SP2 / SQL Server 2008 SE x64 SP1 CumUpd 2 / 10.0.2714.0, 32 GB RAM, 4 x QuadCore 3.6 GHz Xeon, 8 internal disks + 15 disk array)
The servers are all on a gigabit network, not in a domain but in the same workgroup.
Following the Replication Agent Security Model (http://msdn.microsoft.com/en-us/library/ms151868(SQL.90).aspx) we set up Windows users for each of the agents on each machine using identical names & credentials and associated those on each SQL Server instance with an internal login as well users in the apropriate places using Windows authentification.
The logins themselves as well as connectivity via SSMS or e.g. Windows Explorer all work fine, no problem at all - but when we try to get the replication going we receive the following error message during the creation of the SQL Server proxy accounts:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Create failed for ProxyAccount 'replication_snapshot_agent'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.1600.22+((SQL_PreRelease).080709-1414+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+ProxyAccount&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Proxy "SERVERNAME\UserName" is not a valid Windows user. (Microsoft SQL Server, Error: 14529)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2714&EvtSrc=MSSQLServer&EvtID=14529&LinkId=20476
Due to the fact that we can connect from any server to any other one involved (SQL as well as Windows) I don't really have any idea what to look for as root cause for the problem - in fact I don't even know what the problem could be (although I assume that it has more to do with Windows security than SQL Server) ... unfortunately the links lead to nowhere and the net was not really helpful so far either.
Did anybody else run into this before and can give me a hint ?! Any idea would be appreciated ...
Kind regards
PS: Sorry for posting this under 2005, but in 2008 there was no replication topic at all, so i thought I'd post it here ...
[/font]
August 6, 2009 at 12:40 pm
I could not find the details of your service agent account. Are they running under Local System or windows account?
-Roy
August 12, 2009 at 10:15 am
Hi Roy,
sorry for the late answer, we're in the middle of several projects and I am not directly working on this one atm, so I did not get back to you right away - nevertheless you were obviously on the right track.
We use a local system account as service account, not a domain account (since we don't have a domain for our production servers as of now) ... and therefore the 'solution' we found (after quite some digging) seems to be this:
"If you installed SQL Server Agent to run under a local system account instead of under a domain user account (the default), the service can access only the local computer. If the Log Reader Agent that runs under SQL Server Agent is configured to use Windows Authentication Mode when it logs in to SQL Server, the Log Reader Agent fails. The default setting is SQL Server Authentication. (BOL)"
Guess that's what we have to live with for now ... too bad that stuff like this is not at all mentioned e.g. in the Replication Agent Security Model 'paper' we followed, that would have avoided a lot of headaches ... 🙁
August 12, 2009 at 12:05 pm
There is still hope:-)
Let us say that your Server is not in the domain, Create a local account for example "SQLService" and run the SQL Server and the agent under that user. Now create the user in the in the subscriber locally with the same username and password. Same with Distributor. If your servers can communicate and if they can access UNC share it should actually work. (Theoretically at least. That is how you can set up webservers in a DMZ and still communicate with SQL server using Windows Authentication)
I have not tried it but I think it will work.
-Roy
August 18, 2009 at 3:38 pm
We might give it a shot, thanks for the tip ... and for the meantime we can still fall back to running certain job steps using the usual 'non safe' user account (e.g. for the queuereader_agent) ...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply