(SQL Server) NTLM Client and Server Code

Demonstrates the NTLM authentication algorithm for both client and server.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @ntlmClient int
    -- Use "Chilkat_9_5_0.Ntlm" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Ntlm', @ntlmClient OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @ntlmServer int
    -- Use "Chilkat_9_5_0.Ntlm" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Ntlm', @ntlmServer OUT

    -- The NTLM protocol begins by the client sending the server
    -- a Type1 message. 
    DECLARE @type1Msg nvarchar(4000)

    EXEC sp_OASetProperty @ntlmClient, 'Workstation', 'MyWorkstation'
    EXEC sp_OAMethod @ntlmClient, 'GenType1', @type1Msg OUT


    PRINT 'Type1 message from client to server:'

    PRINT @type1Msg

    -- If the server wishes to examine the information embedded within the
    -- Type1 message, it may call ParseType1. 
    -- This step is not necessary, it is only for informational purposes..
    DECLARE @type1Info nvarchar(4000)
    EXEC sp_OAMethod @ntlmServer, 'ParseType1', @type1Info OUT, @type1Msg


    PRINT '---'

    PRINT @type1Info

    -- The server now generates a Type2 message to be sent to the client.
    -- The Type2 message requires a TargetName.  A TargetName is
    -- the authentication realm in which the authenticating account
    -- has membership (a domain name for domain accounts, or server name
    -- for local machine accounts).
    EXEC sp_OASetProperty @ntlmServer, 'TargetName', 'myAuthRealm'

    DECLARE @type2Msg nvarchar(4000)
    EXEC sp_OAMethod @ntlmServer, 'GenType2', @type2Msg OUT, @type1Msg
    EXEC sp_OAGetProperty @ntlmServer, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @ntlmServer, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ntlmClient
        EXEC @hr = sp_OADestroy @ntlmServer
        RETURN
      END


    PRINT 'Type2 message from server to client:'

    PRINT @type2Msg

    -- The client may examine the information embedded in the Type2 message 
    -- by calling ParseType2, which returns XML.  This is only for informational purposes
    -- and is not required.
    DECLARE @type2Info nvarchar(4000)
    EXEC sp_OAMethod @ntlmClient, 'ParseType2', @type2Info OUT, @type2Msg


    PRINT '---'

    PRINT @type2Info

    -- The client will now generate the final Type3 message to be sent to the server.
    -- This requires the Username and Password:
    EXEC sp_OASetProperty @ntlmClient, 'UserName', 'test123'
    EXEC sp_OASetProperty @ntlmClient, 'Password', 'myPassword'

    DECLARE @type3Msg nvarchar(4000)

    EXEC sp_OAMethod @ntlmClient, 'GenType3', @type3Msg OUT, @type2Msg
    EXEC sp_OAGetProperty @ntlmClient, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @ntlmClient, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ntlmClient
        EXEC @hr = sp_OADestroy @ntlmServer
        RETURN
      END


    PRINT 'Type3 message from client to server:'

    PRINT @type3Msg

    -- The server may verify the response by first "loading" the Type3 message.
    -- This sets the various properties such as Username, Domain, Workstation,
    -- and ClientChallenge to the values embedded within theType3 message.
    -- The server may then use the Username to lookup the password.  
    -- Looking up the password is dependent on your infrastructure.  Perhaps your
    -- usernames/passwords are stored in a secure database.  If that's the case, you would
    -- write code to issue a query to get the password string for the given username.
    -- Once the password is obtained, set the Password property and then 
    -- generate the Type3 response again.  If the server's Type3 response matches
    -- the client's Type3 response, then the client's password is correct.

    DECLARE @success int
    EXEC sp_OAMethod @ntlmServer, 'LoadType3', @success OUT, @type3Msg
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @ntlmServer, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @ntlmClient
        EXEC @hr = sp_OADestroy @ntlmServer
        RETURN
      END

    -- The Username property now contains the username that was embedded within
    -- the Type3 message.  It can be used to lookup the password.
    DECLARE @clientUsername nvarchar(4000)
    EXEC sp_OAGetProperty @ntlmServer, 'UserName', @clientUsername OUT

    -- For this example, we'll simply set the password to a literal string:
    EXEC sp_OASetProperty @ntlmServer, 'Password', 'myPassword'

    -- The server may generate the Type3 message again, using the client's correct
    -- password:
    DECLARE @expectedType3Msg nvarchar(4000)
    EXEC sp_OAMethod @ntlmServer, 'GenType3', @expectedType3Msg OUT, @type2Msg


    PRINT 'Expected Type3 Message:'

    PRINT @expectedType3Msg

    -- If the Type3 message received from the client is exactly the same as the
    -- expected Type3 message, then the client must've used the same password,
    -- and authentication is successful

    EXEC @hr = sp_OADestroy @ntlmClient
    EXEC @hr = sp_OADestroy @ntlmServer


END
GO