0

I am doing merge replication between SQL Server CE, 3.5 SP2 and SQL Server 2012 via IIS / Websync. Everything works perfectly when I use DB Authentication to connect with the publisher. If I switch it to NT Authentication, I get the following error in my script:

Initializing SQL Server Reconciler has failed. Try again.

...and the following error is logged on the IIS server sync log:

Command=SYNC Hr=00004818 Login failed for user 'domain\user. 18456

The reason I am trying to change this is because (and correct me if i'm wrong) I don't want to store the credentials in my sync script (powershell). I was hoping that if I was using NT authentication, it would use the credentials this script is running under in task scheduler - or whoever is running it manually. Basically, that is my goal - however it can be achieved - to not store creds in the script.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Progger
  • 2,266
  • 4
  • 27
  • 51
  • Does this domain user you're executing it as have the necessary to connect to the database? Do you see any failed login attempts in SQL Server? – JodyT Mar 21 '13 at 18:49
  • so this is interesting. when I set 'PublisherSecurityMode' to NTAuthentication, the sql log shows 'login failed for NT AUTHORITY\ANONYMOUS LOGIN'. if I don't set that, then it says login failed because i'm trying to use sql authentication with a windows account. – Progger Mar 21 '13 at 18:55
  • I believe the merge replication in SQL Server has an option to allow anonymous access. [Check this link](http://msdn.microsoft.com/en-us/library/ms178668.aspx). If this help i'll add it as an answer. – JodyT Mar 21 '13 at 19:23
  • You must set PublisherSecurityMode= NT, and configure the virtual directory for NT auth (using the Wizard) – ErikEJ Mar 21 '13 at 19:27
  • @ErikEJ - I set PublisherSecurityMode to NTAuthentication. do I need to supply PublisherLogin and PublisherPassword with that? I was hoping it would just pick up the creds of the user running it. – Progger Mar 21 '13 at 19:31
  • No, if you use a Domain account, and all involved machines are domain joined, it should work – ErikEJ Mar 21 '13 at 20:22
  • They are domain-joined, but the IIS box and SQL box are in different domains. The wizard did not fix it, but I got it to work like this: 1) enabled anonymous, 2) under 'advanced settings' of the vdir, I set the physical path credentials with the sql NT login. this does the job but I don't know if this is the right/secure way. can't seem to get it to work any other way tho (without putting creds in my script). – Progger Mar 21 '13 at 20:28
  • also, the above method looks like it will only use the creds hard-coded into IIS rather than the creds used by whoever is running the script. – Progger Mar 21 '13 at 20:41

2 Answers2

0

The only way I got this to work is when I ran the wizard and chose "anonymous" for web access. Only then was I prompted to put in credentials which will be used for NT Auth (stored on the web server). This means that all clients connect anonymously and use the same creds for SQL. They cannot each have their own SQL credentials. They can, however, each have their own IIS credentials.

Progger
  • 2,266
  • 4
  • 27
  • 51
0

To use Windows authentication, you must use Basic auth on the IIS Server (maybe protect with SSL), but the client must suply a Windows username and password. You can then set PublisherSecurityMode to NTAuthentication and the Windows account wil then log on to the database server. So you must either store creds in the script or prompt the person running the script for credentials, and then set InternetLogin and InternetPassword properties accordingly.

ErikEJ
  • 40,951
  • 5
  • 75
  • 115
  • Interesting. I actually have it working (see above) without storing creds in the script and without being prompted. I have my creds stored in the app pool in IIS. IIS is set to anon. Only downside to this is anyone who has access to the IIS server has access to the DB. Not much of a problem with me because i'm using internal certs and IIS is locked to IP. Another downside is they all use the same credentials. – Progger Mar 25 '13 at 14:38