Calling SQL Native Web Service failed as unauthorized

I created a native HTTP end point in SQL Server 2005 Developer edition. I
also created a simple console application to call the web service by add web
reference to generate the proxy client code. It works on the same local
host.
However, I could not add web service reference if I develop the same console
application in on a different machine. Both machines are on the same domain
and logged in using the same account.
I tried to use the Windows authentication.
What shall be set on both SQL Server side and also client side to make the
web service accesible from a different machine?
When adding web reference, I was asked for use id and passwords as well as
domain. It kept asking even after I entered the information.
So I tried to manually generate the proxy code based on the wsdl. The
application threw an exception of "assembly does not allow partially trusted
callers" when the constructor of the proxy client class was called.
Any idea on how to develop client to a SQL native web service?
Thanks.
Hao
It is also interesting that when I type the url into the web browser on a
different machine.
http://hostname:9100/sqlNativeHTTPendpointTest/test?wsdl
I was asked to enter the crendentials. No matter what I entered, it does not
work.
Hao
"Hao" <Hao@.newsgroup.nospam> wrote in message
news:%23gR$A7hdHHA.2316@.TK2MSFTNGP04.phx.gbl...
>I created a native HTTP end point in SQL Server 2005 Developer edition. I
>also created a simple console application to call the web service by add
>web reference to generate the proxy client code. It works on the same local
>host.
> However, I could not add web service reference if I develop the same
> console application in on a different machine. Both machines are on the
> same domain and logged in using the same account.
> I tried to use the Windows authentication.
> What shall be set on both SQL Server side and also client side to make the
> web service accesible from a different machine?
> When adding web reference, I was asked for use id and passwords as well as
> domain. It kept asking even after I entered the information.
> So I tried to manually generate the proxy code based on the wsdl. The
> application threw an exception of "assembly does not allow partially
> trusted callers" when the constructor of the proxy client class was
> called.
> Any idea on how to develop client to a SQL native web service?
> Thanks.
> Hao
>
|||Hello Hao,
Are you doing this on Vista by chance?
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Hello Hao,
I understand that when you try to Add web reference to a sql http web
service, you are required to input domain credential. Though you have the
correct domain account/passowrd, you are repeated to prompt to input again.
If I'm off-base, please let me know.
Based on my experience, this issue might be caused by integration
authentication. Your development machine might not have the delegation
right for kerberbose authentication. I suggest that you try to use NTLM
authentication on SQL server side to work around the issue. For example"
CREATE ENDPOINT SQL_Products
STATE = STARTED
AS HTTP
(
PATH = '/AWproducts',
AUTHENTICATION = (NTLM),
PORTS = (CLEAR),
CLEAR_PORT = 9999
)
FOR SOAP
(
WEBMETHOD 'ProductList'
(NAME='AdventureWorks.dbo.prProductList'),
BATCHES = DISABLED,
WSDL = DEFAULT,
DATABASE = 'AdventureWorks',
NAMESPACE = 'http://Adventure-Works/Products'
)
If you want to enable integrated authentication. You may try the following
method:
In the user Active Directory property, Account is sensitive and cannot be
delegated, must not be selected.
If you have any further questions or concerns on the issue, please feel
free to let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hello Peter Yang [MSFT],
This is why I asked if its Vista. the stuff off the Local Intranet zone there
with its "automatic login"
That said, the proxy generated would be told to PreAuthenticate and use the
DefaultCredentials from CredentialCache.
Sure, the account may not be delegation-enabled, but how frequently is that
encountered?
Thanks,
Kent
|||Thanks for the work around of setting NTLM. It worked. Both machines are
XPs.
What is the reason that it did not work if I wanted to enable all these
authentication mode?
AUTHENTICATION = (NTLM, KERBEROS, INTEGRATED)
Thanks.
Hao
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:18f2bcb1a0f88c94459b94bcee8@.news.microsoft.co m...
> Hello Peter Yang [MSFT],
> This is why I asked if its Vista. the stuff off the Local Intranet zone
> there with its "automatic login"
> That said, the proxy generated would be told to PreAuthenticate and use
> the DefaultCredentials from CredentialCache.
> Sure, the account may not be delegation-enabled, but how frequently is
> that encountered?
> Thanks,
> Kent
>
|||Hello Hao,
As you know, Integrated is actually a negotiation of Kerberos/NTLM. It
seems that the issue is caused by that KERBEROS does not work for the
Webservice authentication. Please check the delegation property of the
logged on user in AD.
Also, When Kerberos authentication is used, the instance of SQL Server must
associate a Service Principal Name (SPN) with the account it will be
running on. Please make sure you have it registred in AD. For more
information, see Registering Kerberos Service Principal Names by Using
Http.sys.
Registering Kerberos Service Principal Names by Using Http.sys
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/29054cc2-31c5-46d6-a381-d10a
1ebc05b7.htm
For setspn tool, please refer to:
http://technet2.microsoft.com/WindowsServer/en/library/b3a029a1-7ff0-4f6f-87
d2-f2e70294a5761033.mspx?mfr=true
If the issue persists, please try to check security event log both on
client/server to see if there is any errors.
Another very useful utility for troubleshooting Kerberos issues
is Kerbtray.exe, which is part of the Windows 2000 Resource Kit. By using
Kerbtray, you can see Kerberos tickets that were granted out of the local
cache. To download this utility, visit the following Microsoft Web site:
Kerbtray.exe: Kerberos Tray
http://www.microsoft.com/windows2000/techinfo/reskit/tools/existing/kerbtray
-o.asp
If you have any update, please feel free to let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hello Hao,
I'm still interested in this issue. If you have any comments or questions,
please feel free to let's know. We look forward to hearing from you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.