0

Just a little bit of background. We are moving away from storing our user passwords hashed in a database to authenticating directly against Active Directory. Unfortunately we have so many internal applications that updating every application is not an option at this point.

So instead of requiring all our developers to change their code, I thought I could just build a .NET CLR Assembly for SQL and just modify the existing authentication stored procedure to validate the users password against our domain controllers instead of a table.

This has been successful in our initial testing, except when the domain controller cannot be communicated with for whatever reason (shutdown, restart, network, etc). When this happens we get timeouts. I added a try/catch and while loop to just skip to the next domain controller and attempt to authenticate against it, but it still takes the first attempt well over 20 seconds to fail before it tries to authenticate against the next server. Since this is a SQL assembly/function and is being called in a stored procedure 20 seconds is way too long.

What is the best approach to modify my code and fail the authentication attempt against a server and try the next server, say, after 3 seconds? Threading somehow?

Imports System
Imports System.Data
Imports System.Collections
Imports System.Text
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes
Imports System.Runtime.InteropServices
Imports System.DirectoryServices.AccountManagement

Partial Public Class UserDefinedFunctions

    <SqlFunction(Name:="ValidCredentials")>
    Public Shared Function ValidCredentials(ByVal Domain As SqlString, ByVal Username As SqlString, ByVal Password As SqlString) As SqlBoolean
        'Validates the username and password are correct against the domain.

        Dim ServerNum As Integer = 1
        Dim Success As Boolean = False
        Dim ValidCreds As SqlBoolean = False

        'Loop through all 4 domain controllers to attempt authentication against each of them.
        While Success = False And ServerNum <= 4

            Try

                Dim pc As PrincipalContext
                If Domain = "employees" Then
                    pc = New PrincipalContext(ContextType.Domain, "DC-E-0" & ServerNum.ToString & ".employees.ourdomain.loc", "EMPLOYEES\BindAcct", "PASS")
                Else
                    pc = New PrincipalContext(ContextType.Domain, "DC-S-0" & ServerNum.ToString & ".students.ourdomain.loc", "STUDENTS\BindAcct", "PASS")
                End If

                ValidCreds = pc.ValidateCredentials(Username, Password)
                Success = True
                Return ValidCreds

            Catch ex As Exception

                ValidCreds = False
                Success = False
                ServerNum += 1

            End Try

        End While

        Return ValidCreds

    End Function

End Class
Robert
  • 3
  • 2
  • Is `ValidateCredential` throwing an exception if it cannot validate? – Simo Sep 13 '18 at 16:49
  • Yes it will throw an exception. Server not found on the network or something along those lines if DC-E-01 is turned off, rebooting, etc. When an exception is thrown it attempts to connect to the second server 'DC-E-02', so on and so on until it can eventually validate. This is expected, however if it cannot communicate with the first server it should try the second, then third, then fourth until it finds one it can auth against. The problem is the function takes too long to throw an exception. I would like to just abort that attempt after 3 seconds and move to the next server. – Robert Sep 13 '18 at 18:12

2 Answers2

0

You can set timeout value for your function on sql server 2008 or above with the following lines of code. Just put it on the top of your block:

SET LOCK_TIMEOUT 3000;  
GO

The value of the timeout is expressed in milliseconds.

According to MS Documentation:

When a wait for a lock exceeds the time-out value, an error is returned.

That will cause that your function takes less than 3 seconds or fail. Then you should catch the exception and rollback if needed.

Simo
  • 955
  • 8
  • 18
  • 1
    I don't want the SQL statement to fail. I want the method inside the assembly to fail (faster or exit) and try to auth against the next server. Until it gets a proper response from one of the 4 domain controllers. – Robert Sep 13 '18 at 18:17
  • I think you need to find a workaround, there are not too much alternatives :c – Simo Sep 13 '18 at 19:22
  • There's got to be a way to thread the calls inside the function and abort after a certain amount of time to try the next server. – Robert Sep 13 '18 at 21:04
  • 1
    Perhaps you can use @Simo 's solution and catch the specific error returned then recall your function recursively. – Mary Sep 14 '18 at 06:07
0

You can do this with a separate thread:

Set timeout to an operation

The code is in C#, but it should be straightforward to convert.

Pepper Paige
  • 117
  • 8