Which account on SQL 2005 web sync via https

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

gstar - 23 Jul 2008 15:02 GMT

Hi, environment is SQL2005 [wkg_sql] non domain & IIS6 [wkg_iis] non
domain.

My steps are same as the steps in the following three articles:
How to: Configure a Publication to Allow for Web Synchronization (SQL
Server Management Studio)
http://technet.microsoft.com/en-us/library/ms345211(SQL.90).aspx

How to: Configure IIS for Web Synchronization
http://technet.microsoft.com/en-us/library/ms152511(SQL.90).aspx

How to: Configure a Subscription to Use Web Synchronization (SQL
Server
Management Studio)
http://technet.microsoft.com/en-us/library/ms345214(SQL.90).aspx

I have now completed this well over 15 times but still have no success
in configuring this in a non domain environment. After the setup I can
connect from a client using https://iis_server/sqlreplication/replisapi.dll?
after logging in but cannot access the diag section, its states access
denied.

I feel the problem lies in the wizard, it asks that you give the sql
repli folder read priveledges to the IIS account. In a non domain
environment that would be impossible as you cannot browse to the IIS
server??

Anyone know what account IIS can access the sql snapshot directory on?

Desperate!

Reply to this Message

Kyle Brockmann - 24 Jul 2008 09:29 GMT

Well - to take a stab in the dark on this one, it seems the account you are
using to invoke replisapi.dll does not have with sufficient privileges.

Be sure to use only basic authentication, and ensure that the login you're
using has
permission to access the UNC file share you are using to expose your snapshot.

In my experience, the Replicator Authentication Group only has sufficient
permissions when you are running in a domain environment.

Although its very bad security practice in your case, circa the manual, we
used an Administrators Group member over a secure connection.

> Hi, environment is SQL2005 [wkg_sql] non domain & IIS6 [wkg_iis] non
> domain.
[quoted text clipped - 26 lines]
>
> Desperate!

Reply to this Message

gstar - 24 Jul 2008 10:17 GMT

Thanx Kyle, that is exactly my issue though, I cant add an account
that doesnt exist!...

> Well - to take a stab in the dark on this one, it seems the account you are
> using to invoke replisapi.dll does not have with sufficient privileges.

Correct, it doesnt have privileges because its not in the same
domain..

> Be sure to use only basic authentication, and ensure that the login you're
> using has
> permission to access the UNC file share you are using to expose your snapshot.

Same as above, how can I gove an IIS users access privileges on a sql
server in a different domain?

> In my experience, the Replicator Authentication Group only has sufficient
> permissions when you are running in a domain environment.
>
> Although its very bad security practice in your case, circa the manual, we
> used an Administrators Group member over a secure connection.

Again as above..I need to fins out how others have added a non domain
account to the snapshot folder. Sorry if I have misread, but have you
achieved this in the same environment? If so could you maybe explain
just the process of how you setup your accounts in IIS & SQL and then
assigned them to snapshot folder?

Thanx again..

G

Reply to this Message

Kyle Brockmann - 24 Jul 2008 14:48 GMT

A few points you may need to be aware of:

The Default Domain and Realm fields in the IIS Directory Security section of
the Virtual Folder/Site you are using to host replisapi.dll must be set to
the System Name of the system you are running IIS on.

I'm led to believe from your post that the system you are hosting IIS on is
not a member of the domain that SQL Server is running on. Web Replication was
intentionally designed with the idea that IIS can be running on a seperate
system to the one hosting the Database engine. replisapi.dll uses the SQL
Native Client to establish a connection to the database engine.

The following should give you an overview of the permissions required for
the local account you specify for the connection to operate under - pay
special attention to the NTFS section:

----
Web Service Extension 'SQL Server' will be enabled with the following options:

Add 'C:\Inetpub\wwwroot\replication\replisapi.dll' to the enabled file list
of the Web Service Extension.

'WEBREPL, Kyle' will get the following NTFS permission:

'Read & Write' on physical path 'C:\Inetpub\wwwroot\replication'.
'Read & Execute' on the ISAPI DLL
'C:\Inetpub\wwwroot\replication\replisapi.dll'.
'Read' on share physical path '\\servername\replication'.
'Read' on the share '\\servername\replication'.

------

The access denied message you are receiving is not for access to
replisapi.dll - to get just "Access Denied" in black letters in a browser
means that the ISAPI extension already has permission to execute, but the
principal it is executing under cannot access the SQL server or the folder
hosting your snapshot.

Read the section entitled "Web Synchronization" at the bottoms of the
following:

http://msdn.microsoft.com/en-us/library/ms147881.aspx

It leads me to believe that the Basic Authentication user and password are
passed in clear text via the extension to the domain hosting the SQL Server;
which implies that the same credentials are used to access the database. The
words "Because of the limitations of Windows impersonation..." should be of
particular interest to you.

Good luck.

> Thanx Kyle, that is exactly my issue though, I cant add an account
> that doesnt exist!...
[quoted text clipped - 27 lines]
>
> G

Reply to this Message

gstar - 24 Jul 2008 16:28 GMT

Thanx again Kyle,
Interesting paragraph you posted below with my interpretaion below:

> Web Service Extension 'SQL Server' will be enabled with the following options:
>
[quoted text clipped - 3 lines]
>
> 'Read & Write' on physical path 'C:\Inetpub\wwwroot\replication'.

On IIS this is done.

> 'Read & Execute' on the ISAPI DLL

On IIS this is done.

> 'C:\Inetpub\wwwroot\replication\replisapi.dll'.

On IIS this is done.

> 'Read' on share physical path '\\servername\replication'.
> 'Read' on the share '\\servername\replication'.

Now this is where I am confused. How have you added "'WEBREPL, Kyle' "
access rights on SQL server machine if that account only exists on the
IIS box? Becasue tyhe 2 servers are not in domains I cannot select
that IIS user account on teh SQL machine.

Hope that makes sense?

Gary

Reply to this Message

Kyle Brockmann - 25 Jul 2008 09:54 GMT

Well Gary,

The server on which I setup web replication isn't on a domain either.

WEBREPL is the account under which my application pool is running, and Kyle
is my user account on that server.

> Thanx again Kyle,
> Interesting paragraph you posted below with my interpretaion below:
[quoted text clipped - 25 lines]
>
> Gary

Reply to this Message

gstar - 28 Jul 2008 15:07 GMT

On Jul 25, 9:54 am, Kyle Brockmann
<kyle[dot]brockmann[at]nirph[dot]com> wrote:

> Well Gary,
>
> The server on which I setup web replication isn't on a domain either.
>
> WEBREPL is the account under which my application pool is running, and Kyle
> is my user account on that server.

Hi Kyle,
Am I being really thick here? I understand WEBREPL & Kyle accounts
exist on IIS server therefor you assign permmisions no problem.
However WEBREPL & Kyle accounts would not exist on the SQL server, so
how are you assigning NTFS permissions to that user for the path \
\server\replication [patch to SQL directory on SQL server] ?

Cheers

G

Reply to this Message