The Last Pickle

Again with the security, this time while workout out how SOAP HTTP Endpoints work and I have two issues with it.

First, its only possible to change the execution context of the call when it reaches the target stored procure or function and only then by defining the object using ‘Execute As’. By this time the request has passed through four levels of security, two for authentication and two for authorisation. I think a better design would have been to allow the HTTP Endpoint itself to ‘Execute As’, so that the external principal is given access to the endpoint which then elevates to a different context to make the call to the target procedure or function. In that way the external principal would have not have any permissions, or even a login, inside SQL Server.

Secondly when a permission exception is thrown by SQL Server the entire error is returned to the caller; which includes the machine name, server (major.minor) version, database and procedure name. If an attacker did not already know a SQL Server was behind the SOAP endpoint they would now, they may also know if the server was patched and what DB they need to get access to. Exceptions returned to external systems should limit or remove ALL internal information from their messages, especially if they may be accessed from the internet. (My test was to connect to a local machine, it may be different when connecting to a remote machine.)

Allow me to explain. An inbound request passes through the following Authorisation and Authentication layers, the Test boxes in the diagram map to tests conducted later on.

The inbound request must have the following properties:

1. The HTTP Request must supply credentials for a valid Windows account, SQL Server authentication is not supported for the HTTP Request (it can be used with SOAP Headers but thats another story). The request is only authenticated so any valid Windows account will do. Digest authentication supports only Domain accounts, for my tests I used NTLM authentication as I only had a Local account.
2. The Windows credentials must map to a valid SQL Server Windows Login on the target server. At this point the principal is authenticated by logging into the SQL Server, there is no authorisation as a resource has not been requested.
3. The SQL Server principal (mapped from the Windows Principal) must have Connect permission on the Endpoint. This is the first authorisation the request undergoes.
4. The SQL Server principal (mapped from the Windows Principal) must have Execute permission on the stored procedure or function the Endpoint exposes. The request is made using the SQL Server login mapped from the Windows Principal and the mapped Database User.

To test a request through these layers I created a simple SOAP endpoint that returns the current login and user as follows.

use master;
go

-- Endpoints are server scoped objects, so we need a login to own them.
-- Setting the owner will help avoid the permissions assigned to the owner 
-- accidently been used. 
create login
    endpoint_owner
with 
    password = 'pword'
go

-- usp to be called from the soap endpoint
drop procedure dbo.usp_getContext
go

create procedure dbo.usp_getContext
as
select
    SUSER_NAME() as 'LOGIN', USER_NAME() AS 'USER NAME'
go

-- create an endpoint to return the principal names  
DROP ENDPOINT soap_endpoint;
GO

CREATE ENDPOINT 
    soap_endpoint 
AUTHORIZATION
    endpoint_owner
STATE = STARTED
AS HTTP
    (
        PATH = '/sql', 
        AUTHENTICATION = (ntlm), 
        PORTS = ( clear ), 
        SITE = '*'
    )
FOR SOAP 
    (
        WEBMETHOD 'GetContext' 
        (
            name='master.dbo.usp_getContext' 
        ),
        WSDL = DEFAULT,   
        SCHEMA = STANDARD,
        DATABASE = 'master',
        NAMESPACE = 'http://tempUri.org/',
        LOGIN_TYPE = WINDOWS
   ); 
GO

-- Run tests 1 and 2

-- For test 3
-- create a login for the incoming HTTP request user pickle\soap_test
create login
    [pickle\soap_test]
from windows
go

-- For test 4
-- grant pickle\soap_test permission to connect to the end point
grant 
    connect
on
    endpoint::soap_endpoint
to
    [pickle\soap_test]
go


-- For test 6
-- grant exec on usp_getContext to the HTTP request user pickle\soap_test
-- this also creates a DB user with the same name
grant execute on object::usp_getContext to [pickle\soap_test]
go

This creates a SOAP endpoint at http://localhost/sql that will return a WSDL document to a client when queried with http://localhost/sql?wsdl , this query will work from any browser. The tests below involve querying the url with a browser and creating a c# application to query the GetContext method. The test numbers correspond to the “Test” boxes in the model which show the request’s progress through the security stack.

Test 1: Unknown Windows Principal

For the first test I opened the url in Internet Explorer and when requested to login used the invalid windows principal pickle\soap_test . This failed and I was asked twice more to login, ultimately the request was rejected by the mechanism described in my post on Tracing SQL SOAP Endpoints through HTTP.sys

Test 2: Known Windows Principal, Unknown SQL Login

I created a login windows account called soap_test on my pickle machine, I did not create a SQL Login for this account. This time a browser request to http://localhost/sql?wsdl using the pickle\soap_test account resulted in a SOAP error that Login failed. An extract of the response is below.

<SOAP-ENV:Fault 
    <SOAP-ENV:Code>
        <SOAP-ENV:Value>SOAP-ENV:Sender</SOAP-ENV:Value> 
        <SOAP-ENV:Subcode>
            <SOAP-ENV:Value>
                sqlsoapfaultcode:LoginFailure
            </SOAP-ENV:Value> 
            <SOAP-ENV:Subcode>
                <SOAP-ENV:Value>
                sqlsoapfaultcode:AccessDenied
                </SOAP-ENV:Value> 
            </SOAP-ENV:Subcode>
        </SOAP-ENV:Subcode>
    </SOAP-ENV:Code>
    <SOAP-ENV:Reason>
        <SOAP-ENV:Text xml:lang="en-US">
        There was an error in the incoming SOAP request 
        packet: Sender, LoginFailure, AccessDenied
        </SOAP-ENV:Text> 
    </SOAP-ENV:Reason>
    <SOAP-ENV:Node>
        http://localhost:80/sql?wsdl
    </SOAP-ENV:Node> 
    <SOAP-ENV:Role>
        http://schemas.microsoft.com/sqlserver/2004/SOAP
    </SOAP-ENV:Role> 
    <SOAP-ENV:Detail /> 
</SOAP-ENV:Fault>

Test 3: Known Windows Principal, Known SQL Login, No Connect Permission

I created a mapped SQL Server login for the pickle\soap_test Windows Principal using the TSQL in the sample above. The browser request received the same SOAP fault as test 2.

Test 4: Known Windows Principal, Known SQL Login, Connect Permissions - WSDL request

I granted the pickle\soap_test SQL principal Connect permission on the SOAP endpoint. A request using a browser is now returned the full WSDL document for the web service.

This is as far as I could go with a browser, from now on a simple dotnet app that called the GetContext web service action was used.

Test 5: Known Windows Principal, Known SQL Login, Connect Permissions - Execute request

Without changing the permissions I made a request to execute the GetContext web service action using a simple dotnet application. A SOAP error was returned through the dotnet stack that included the following information.

<SOAP-1_2-ENV:Code>
   <SOAP-1_2-ENV:Value>SOAP-1_2-ENV:Receiver</SOAP-1_2-ENV:Value>
   <SOAP-1_2-ENV:Subcode>
       <SOAP-1_2-ENV:Value>
    sqlsoapfaultcode:UnknownSqlServerError
    </SOAP-1_2-ENV:Value>
   </SOAP-1_2-ENV:Subcode>
   </SOAP-1_2-ENV:Code>
       <SOAP-1_2-ENV:Reason>
           <SOAP-1_2-ENV:Text xml:lang="en-US">
        A server error occurred while 
        processing SOAP request:  Receiver, UnknownSqlServerError
        </SOAP-1_2-ENV:Text>
           </SOAP-1_2-ENV:Reason>
           <SOAP-1_2-ENV:Node>
        http://localhost:80/sql
        </SOAP-1_2-ENV:Node>
           <SOAP-1_2-ENV:Role>
        http://schemas.microsoft.com/sqlserver/2004/SOAP
        </SOAP-1_2-ENV:Role>
           <SOAP-1_2-ENV:Detail>
               <sqlresultstream:SqlMessage>
                   <sqlmessage:Class>14</sqlmessage:Class>
                   <sqlmessage:LineNumber>1</sqlmessage:LineNumber>
                   <sqlmessage:Message>
                The EXECUTE permission was denied on the object 
                'usp_getContext', database 'master', schema
                'dbo'.
                </sqlmessage:Message>
                   <sqlmessage:Number>229</sqlmessage:Number>
                   <sqlmessage:Server>PICKLE</sqlmessage:Server>
                   <sqlmessage:Source>Microsoft-SQL/9.0</sqlmessage:Source>
                   <sqlmessage:State>5</sqlmessage:State>
               </sqlresultstream:SqlMessage>
           </SOAP-1_2-ENV:Detail>

There is a ridiculous amount of information in here, none of this should be given out to a request that may have have come from the wild wild internets; just tell the caller the request is not authorised rather than everything it needs to do to get authorised.

Test 6: Known Windows Principal, Known SQL Login, Connect Permission, Execute Permission

For the final test I granted the pickle\soap_test principal execute permission on usp_GetContext using the TSQL in the sample above. This time the execute request completed and showed that both the login and user context with pickle\soap_test.