Unable To Connect To SQL Server EndPoint Through SQL Server Authentication Using WS-Security Header

Hi folks,

I have created an EndPoint in SQL Server 2005 as per the code below.


CREATE ENDPOINT OSTC_LMS_Endpoint

AUTHORIZATION LMSEndPointUsers

STATE = STARTED

AS HTTP(

PATH = '/ostc_sql_endpoint',

AUTHENTICATION = (BASIC),

PORTS = (SSL),

SITE = 'OSTC-DEV-001'

)

FOR SOAP (

WEBMETHOD 'ostc_SQLSoapTester'

(name='OSTC_LMS_06.dbo.ostc_SQLSoapTester',

FORMAT = ROWSETS_ONLY,

SCHEMA=STANDARD),

WSDL = DEFAULT,

LOGIN_TYPE = MIXED,

SCHEMA = STANDARD,

DATABASE = 'OSTC_LMS_06',

NAMESPACE = 'http://tempUri.org/'

)

GO

USE master

GRANT CONNECT ON ENDPOINT::OSTC_LMS_Endpoint

TO [LMSEndPointUsers]

GO

USE master

GRANT CONNECT ON ENDPOINT::OSTC_LMS_Endpoint

TO [ostc-dev-001endPointUsers]

GO

-----------------------------------------------------------------------------------------------------------------
The SPROC being exposed as the webmethod: -


USE OSTC_LMS_06

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'ostc_SQLSoapTester' AND type = 'P')

DROP PROCEDURE ostc_SQLSoapTester

GO

CREATE PROCEDURE ostc_SQLSoapTester

@UsersId char(12)

AS

SET NOCOUNT ON

SELECT FIRST_NAME,FAMILY_NAME

FROM USERS

WHERE USERS_ID = @UsersId

GO



USE OSTC_LMS_06

GRANT EXECUTE ON ostc_SQLSoapTester

TO LMSEndPointUsers

GO

USE OSTC_LMS_06

GRANT EXECUTE ON ostc_SQLSoapTester

TO [ostc-dev-001endPointUsers]

GO

----------------------------------------------------------------------------------------------------------------

The computer in question is our dev server and is running as a workgroup machine with the following: -
Win Server 2003
SQL Server 2005
.net Framework 2.0
No firewalls or Proxies are in the way.

The computer has to be as a workgroup machine to reflect our live server.

The user LMSEndPointUsers is a SQL Server Login
The user ostc-dev-001endPointUsers is a machine login

We have employed the Security class as per the information given in the SQL Server documentation with the intention of using the WS-Security headers that apparently are to be used when trying to authenticate using a SQL Server login.

----------------------------------------------------------------------------------------------------------------

Code used to connect and try and reciev the dataset.


string sUserName = "userName";

string sPassword = "Password";



LMS_Endpoint.OSTC_LMS_Endpoint wsSQLTester = new LMS_Endpoint.OSTC_LMS_Endpoint();

SqlSoapHeader.Security sqlSec = new SqlSoapHeader.Security();

sqlSec.Username = sUserName;

sqlSec.Password = sPassword;

XmlWriter writer = XmlWriter.Create("Security.xml");

writer.WriteStartElement("security");

sqlSec.WriteXml(writer);

wsSQLTester.sqlSecurity = sqlSec;


DataSet dsMySet = wsSQLTester.ostc_SQLSoapTester("RH6915145507");


Basically authorization is denied 401.
Hope someone can help here as have tearing my hair out.

Thanks in advance