Is there a way to connect Windows C# Application to SQL Server using a Network Service Account

Question

I have a C# application that accesses a SQL Server database using AD Groups for authentication, but I have been told they want to switch that to using the same network service account as the web applications are currently using through the web server app pool. Since the Windows application does not go through the web server and app pool, I am unsure how to configure the Windows application to use the same network service account as the web applications. I have searched SO, MSDN and Google but found nothing. Is there a way to do this? Thanks for any help.

Answer 1

You will need to use impersonation. We use this in our web-application using a custom class:

Imports System.Web
Imports System.Web.Security
Imports System.Security.Principal
Imports System.Runtime.InteropServices

''' <summary>Creates an impersonation session if needed</summary>
''' <remarks>
'''        This is a clever little object, it is designed to facilitate the 'using' functionality to handle the initialisation and disposal of the impersonation
'''        context.  It will only impersonate if the connection is set up to use windows authentication otherwise it will do nothing
'''
'''        Recommended usage
'''            using new impersonate(domain, username, password)
'''                Do database Stuff
'''            end using
'''    </remarks>
Public Class Impersonate
    Implements IDisposable


Private isImpersonating As Boolean = False

Private Sub Impersonate(ByVal domain As String, ByVal username As String, ByVal password As String)
    If Me.impersonateValidUser(username, domain, password) Then
        ' all is well
    Else
        ' not so well, raise exception
        Throw New System.Exception("Unable to use provided AD authentication to connect to database")
    End If
End Sub

#Region "IDisposable Support"
    Private disposedValue As Boolean ' To detect redundant calls

    ' IDisposable
    Protected Overridable Sub Dispose(ByVal disposing As Boolean)
        If Not Me.disposedValue Then
            If disposing Then
                ' clean up the impersonation
                undoImpersonation()
            End If

        End If
        Me.disposedValue = True
    End Sub

    ' TODO: override Finalize() only if Dispose(ByVal disposing As Boolean) above has code to free unmanaged resources.
    'Protected Overrides Sub Finalize()
    '    ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
    '    Dispose(False)
    '    MyBase.Finalize()
    'End Sub

    ' This code added by Visual Basic to correctly implement the disposable pattern.
    Public Sub Dispose() Implements IDisposable.Dispose
        ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
        Dispose(True)
        GC.SuppressFinalize(Me)
    End Sub
#End Region



#Region "Impersonate"

Dim LOGON32_LOGON_INTERACTIVE As Integer = 2
Dim LOGON32_PROVIDER_DEFAULT As Integer = 0

Dim impersonationContext As WindowsImpersonationContext

Declare Function LogonUserA Lib "advapi32.dll" (ByVal lpszUsername As String, _
                        ByVal lpszDomain As String, _
                        ByVal lpszPassword As String, _
                        ByVal dwLogonType As Integer, _
                        ByVal dwLogonProvider As Integer, _
                        ByRef phToken As IntPtr) As Integer

Declare Auto Function DuplicateToken Lib "advapi32.dll" ( _
                        ByVal ExistingTokenHandle As IntPtr, _
                        ByVal ImpersonationLevel As Integer, _
                        ByRef DuplicateTokenHandle As IntPtr) As Integer

Declare Auto Function RevertToSelf Lib "advapi32.dll" () As Long
Declare Auto Function CloseHandle Lib "kernel32.dll" (ByVal handle As IntPtr) As Long


Public Sub Page_Loada(ByVal s As Object, ByVal e As EventArgs)
    If impersonateValidUser("username", "domain", "password") Then
        'Insert your code that runs under the security context of a specific user here.
        undoImpersonation()
    Else
        'Your impersonation failed. Therefore, include a fail-safe mechanism here.
    End If
End Sub

Private Function impersonateValidUser(ByVal userName As String, _
ByVal domain As String, ByVal password As String) As Boolean

    Dim tempWindowsIdentity As WindowsIdentity
    Dim token As IntPtr = IntPtr.Zero
    Dim tokenDuplicate As IntPtr = IntPtr.Zero
    impersonateValidUser = False

    If CType(RevertToSelf(), Boolean) Then
        If LogonUserA(userName, domain, password, LOGON32_LOGON_INTERACTIVE,
                     LOGON32_PROVIDER_DEFAULT, token) <> 0 Then
            If DuplicateToken(token, 2, tokenDuplicate) <> 0 Then
                tempWindowsIdentity = New WindowsIdentity(tokenDuplicate)
                impersonationContext = tempWindowsIdentity.Impersonate()
                If Not impersonationContext Is Nothing Then
                    impersonateValidUser = True
                End If
            End If
        End If
    End If
    If Not tokenDuplicate.Equals(IntPtr.Zero) Then
        CloseHandle(tokenDuplicate)
    End If
    If Not token.Equals(IntPtr.Zero) Then
        CloseHandle(token)
    End If
End Function

Private Sub undoImpersonation()
    If Me.isImpersonating Then
        impersonationContext.Undo()
    End If
End Sub

#End Region
End Class

You then call the class using code like:

using new impersonate(domain, username, password)
   ' Do database Stuff
end using

Answer 2

I think the only way to connect to SQL as Network Service is to run your application as Network Service (I don't think impersonation would work for this account). Assuming we are talking about a client application (console, Windows Forms or WPF) I see 3 options:

1. Use PsExec.exe from SysInternals to run your app as Network Service as described here.
2. Split your application to 2 parts - a windows service implementing data access and running as Network Service and a client app communicating with that service and implementing UI and application layer
3. Implement your data access as a Web Service so it can run in app pool under Network Service credentials and make your app use that Web Service to access data. I think this way is preferable in most cases, but it could require a serious refactoring.