Authentication Error with HTTP EndPoints

[jahu]

Hello!

I'm unable to use a webservice exposed on a Sql Server 2005 by using
the MSSOAP.SoapClient in Excel 2003 with VBA. The aim I'm focuced on,
is to write a function for a Excel Sheet wich resolves some
identifiers to strings by using a SQL Server 2005. I thougt to use web
services but end up with errors, as you can read furthre down.

If my approach to connect Excel 2003 to a Sql Server 2005 is totaly
wrong, I would be very happy if some one could advise me.

The exception in VBA is about '... access denied...'. The SQL Server
logs 'HTTP authentication failed. [CLIENT: x.x.x.x]

The Endpoint I want to use is as follows:
CREATE ENDPOINT SQLEP_gmd
STATE = STARTED
AS HTTP
(
PATH = '/gmd',
AUTHENTICATION = (INTEGRATED),
PORTS = (CLEAR),
SITE = '2003dellp285002'
)
FOR SOAP
(
WEBMETHOD 'getNextReferenceSubstanceIdentifier'
(NAME='metabolomics.dbo.getNextReferenceSubstanceM pimpId'),
BATCHES = DISABLED,
WSDL = DEFAULT,
DATABASE = 'metabolomics',
NAMESPACE = 'http://2003dellp285002'
)
The stored procedure is very simple, has no input parameters and
returns a string to simplify matters.

Any commnt is apreciated very much!

With kind regards

[=?Utf-8?B?SmltbXkgV3UgW01TRlRd?=]

Hi,

If I remember correctly, you will need to specify the user and password as
part of the URL you used to initialize the MSSoap.SoapClient object. If that
isn't possible, I recommend creating the data connection using the normal SQL
database method.

Please also make sure that you have granted access to the Endpoint and the
Stored Procedure for the user you wish to connect with.

Jimmy

"jahu" wrote:

Hello!

I'm unable to use a webservice exposed on a Sql Server 2005 by using
the MSSOAP.SoapClient in Excel 2003 with VBA. The aim I'm focuced on,
is to write a function for a Excel Sheet wich resolves some
identifiers to strings by using a SQL Server 2005. I thougt to use web
services but end up with errors, as you can read furthre down.

If my approach to connect Excel 2003 to a Sql Server 2005 is totaly
wrong, I would be very happy if some one could advise me.

The exception in VBA is about '... access denied...'. The SQL Server
logs 'HTTP authentication failed. [CLIENT: x.x.x.x]

The Endpoint I want to use is as follows:
CREATE ENDPOINT SQLEP_gmd
STATE = STARTED
AS HTTP
(
PATH = '/gmd',
AUTHENTICATION = (INTEGRATED),
PORTS = (CLEAR),
SITE = '2003dellp285002'
)
FOR SOAP
(
WEBMETHOD 'getNextReferenceSubstanceIdentifier'
(NAME='metabolomics.dbo.getNextReferenceSubstanceM pimpId'),
BATCHES = DISABLED,
WSDL = DEFAULT,
DATABASE = 'metabolomics',
NAMESPACE = 'http://2003dellp285002'
)
The stored procedure is very simple, has no input parameters and
returns a string to simplify matters.

Any commnt is apreciated very much!

With kind regards

[=?Utf-8?B?SmltbXkgV3UgW01TRlRd?=]

Dig up some more details. You can specify the user credentials on the
SoapClient object. The following is a sample:
SoapClient.connectorProperty("AuthUser") = "userName"
SoapClient.connectorProperty("AuthPassword") = "PASSWORD"
// Set Auth scheme
// digest 0x8
// negotiate 0x10
// ntlm 0x2
// basic 0x1
SoapClient.connectorProperty("WinHTTPAuthScheme") = 0x2

Jimmy

"Jimmy Wu [MSFT]" wrote:

Hi,

If I remember correctly, you will need to specify the user and password as
part of the URL you used to initialize the MSSoap.SoapClient object. If that
isn't possible, I recommend creating the data connection using the normal SQL
database method.

Please also make sure that you have granted access to the Endpoint and the
Stored Procedure for the user you wish to connect with.

Jimmy

"jahu" wrote:

Hello!

I'm unable to use a webservice exposed on a Sql Server 2005 by using
the MSSOAP.SoapClient in Excel 2003 with VBA. The aim I'm focuced on,
is to write a function for a Excel Sheet wich resolves some
identifiers to strings by using a SQL Server 2005. I thougt to use web
services but end up with errors, as you can read furthre down.

If my approach to connect Excel 2003 to a Sql Server 2005 is totaly
wrong, I would be very happy if some one could advise me.

The exception in VBA is about '... access denied...'. The SQL Server
logs 'HTTP authentication failed. [CLIENT: x.x.x.x]

The Endpoint I want to use is as follows:
CREATE ENDPOINT SQLEP_gmd
STATE = STARTED
AS HTTP
(
PATH = '/gmd',
AUTHENTICATION = (INTEGRATED),
PORTS = (CLEAR),
SITE = '2003dellp285002'
)
FOR SOAP
(
WEBMETHOD 'getNextReferenceSubstanceIdentifier'
(NAME='metabolomics.dbo.getNextReferenceSubstanceM pimpId'),
BATCHES = DISABLED,
WSDL = DEFAULT,
DATABASE = 'metabolomics',
NAMESPACE = 'http://2003dellp285002'
)
The stored procedure is very simple, has no input parameters and
returns a string to simplify matters.

Any commnt is apreciated very much!

With kind regards