Just as normal connections to SQL server support SQL user credentials, when logging into SQL 2005 through Native Web Services SQL user credentials can be used as well. This is done by utilizing the SOAP Headers functionality of SOAP.
Please see SQL Books Online under topic “SQL Server Authentication over SOAP” for instructions on how to update the client application. This topic can be reached through the index by “Native XML Web Services” -> “SQL Server Authentication”. At the end of the topic there is a reference to “Adding SOAP Headers to Client Applications”. You will also find a link to sample client side code implementing the SQL 2005 supported SOAP headers within the “Adding SOAP Headers to Client Applications” topic.
Grant the Windows user connect permission to the endpoint
GRANT CONNECT ON ENDPOINT::Sample_EP TO [machineName\userName]
Grant the Windows user execute permission on the stored procedure
GRANT EXEC on funcString to [machineName\userName]
Grant the SQL user access to the database containing the stored procedure
EXEC sp_grantdbaccess @loginame = [sqlUser]
Grant the SQL user connect permission to the endpoint
GRANT CONNECT ON ENDPOINT::Sample_EP TO [sqlUser]
Grant the SQL user execute permission on the stored procedure
GRANT EXEC on funcString to [sqlUser]
You can verify that the endpoint is working by using an web explorer tool (such as Internet Explorer) and type in the URL of the endpoint. For example, https://myMachine.domain.com/sql/sqlauth?wsdl. When prompted for credentials, specify the credentials of the Windows user. If the endpoint is working, then you should see the WSDL document. Please make sure that the Windows Firewall is not blocking the port (443).
Below outlines the client application configuration needed to send the SQL user credentials (assuming client application developed with Visual Studio 2005):