Merge replication subscription error with proxy

Ok, perhaps I'm a moron.. believe me, I'd be glad to admit it if I
could just get this fixed.
We're trying to merge replicate data via https, we have installed the
CA certificate and can connect to the replisapi.dll through IE. I used
the following script to generate the subscription:
DECLARE @.publication AS sysname;
DECLARE @.publisher AS sysname;
DECLARE @.publicationDB AS sysname;
DECLARE @.websyncurl AS sysname;
DECLARE @.login AS sysname;
DECLARE @.password AS nvarchar(512);
SET @.publication = N'tables';
SET @.publisher = N'server';
SET @.publicationDB = N'database';
SET @.websyncurl = 'https://webserver/WebSync';
SET @.login = N'websql\user';
Set @.password = N'pass';
USE [LocalDB]
EXEC sp_addmergepullsubscription
@.publisher = @.publisher,
@.publication = @.publication,
@.publisher_db = @.publicationDB,
@.subscriber_type = N'anonymous';
EXEC sp_addmergepullsubscription_agent
@.publisher = @.publisher,
@.publisher_db = @.publicationDB,
@.publication = @.publication,
@.distributor = @.publisher,
@.job_login = @.login,
@.job_password = @.password,
@.use_web_sync = 1,
@.internet_url = @.websyncurl,
@.internet_login = @.login,
@.internet_password = @.password,
@.enabled_for_syncmgr = 'true';
GO
This creates the subscription locally but gives me the error
"websql\user not a valid Windows account". I tried changing the
job_login & job_password to a local account and the subscription was
added without errors. When I go to start the syncronization I get the
error: "The system cannot find the file specified." The underlying
error here is with the proxy. For some reason when it tries to connect
it isn't passing the proper credentials or something.
Anyway, any I'd appreciate any help or direction. If I find out what's
wrong I'll go ahead and post it.
JC
That's because it is creating the agent at the subscriber and when doing so,
you are specifying an account of websql\user which does not exist on the
subscriber. If you change this to a local account and then try to
synchronize, you will get security errors, because the SID of the local
account you set it to does not have a valid scope outside of the local
machine. The job login and password can be a local account and it dosn't
affect anything. However, take a look at the other credential. You are
setting the synchronization context to websql\user and that account, since
it doesn't exist on the local machine can't generate the file which is used
to synch via https. (The more accurate error message would be user doesn't
exist or access denied.)
I don't have a system setup to be able to configure https synchronization
right now. I've done this before and your issue is with the security
context. I just can't remember what I did before to fix this particular
error.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
<jbzcooper@.gmail.com> wrote in message
news:1138315588.689076.326990@.g14g2000cwa.googlegr oups.com...
> Ok, perhaps I'm a moron.. believe me, I'd be glad to admit it if I
> could just get this fixed.
> We're trying to merge replicate data via https, we have installed the
> CA certificate and can connect to the replisapi.dll through IE. I used
> the following script to generate the subscription:
> DECLARE @.publication AS sysname;
> DECLARE @.publisher AS sysname;
> DECLARE @.publicationDB AS sysname;
> DECLARE @.websyncurl AS sysname;
> DECLARE @.login AS sysname;
> DECLARE @.password AS nvarchar(512);
> SET @.publication = N'tables';
> SET @.publisher = N'server';
> SET @.publicationDB = N'database';
> SET @.websyncurl = 'https://webserver/WebSync';
> SET @.login = N'websql\user';
> Set @.password = N'pass';
> USE [LocalDB]
> EXEC sp_addmergepullsubscription
> @.publisher = @.publisher,
> @.publication = @.publication,
> @.publisher_db = @.publicationDB,
> @.subscriber_type = N'anonymous';
> EXEC sp_addmergepullsubscription_agent
> @.publisher = @.publisher,
> @.publisher_db = @.publicationDB,
> @.publication = @.publication,
> @.distributor = @.publisher,
> @.job_login = @.login,
> @.job_password = @.password,
> @.use_web_sync = 1,
> @.internet_url = @.websyncurl,
> @.internet_login = @.login,
> @.internet_password = @.password,
> @.enabled_for_syncmgr = 'true';
> GO
> This creates the subscription locally but gives me the error
> "websql\user not a valid Windows account". I tried changing the
> job_login & job_password to a local account and the subscription was
> added without errors. When I go to start the syncronization I get the
> error: "The system cannot find the file specified." The underlying
> error here is with the proxy. For some reason when it tries to connect
> it isn't passing the proper credentials or something.
> Anyway, any I'd appreciate any help or direction. If I find out what's
> wrong I'll go ahead and post it.
> JC
>
|||Mike,
Appreciate the reply and you are correct. I split the credentials into
two, one for the machine and one for the web. This brought up a second
error which I pondered for some time until a colleage suggested I look
at the security permissions for the login. I tried several things but
this is what I was doing when I got it to work:
0. Ensure that windows account SQL run under has proper authority to
execute job
1. Log into windows using the windows account SQL is using
2. Log into SQL using Windows Authentication
3. Execute the above script (after adding a second set of credentials
for the job)
4. Run replication
Some of the above steps may not be necessary.. it just worked for me.
HTH someone else.. man, I was in a bad spot.
JC