obtain the website visitors AD username and run SQL commands via another AD service account

damientaylor

New member
Joined
Nov 2, 2020
Messages
1
Programming Experience
10+
I am new to vb.net development. However I have run into a bit of an issue that I am hoping has a simple answer.

Issue:
The requirement is to be able to obtain the users AD username by enabling Windows Authentication and Pass Thru Authentication.

This works perfectly when I set this up and the website is able to obtain my username via the command when I access the site:
System.Security.Principal.WindowsIdentity.GetCurrent()

However I also have a requirement to access SQL server without storing the password in the connection string via the web.config file. No storing of plain text passwords.

I have therefore created a new AD account (domain\appsportaluser) and changed the connection string to the following:

<add connectionString="Server=xxx1-s-sql01;Database=testdb;Integrated Security=true" name="connectionstring_testdb" />

However it appears for this to work I also need to setup a service account and need to set this to (a) the identify of the Application Pool and also (b) set the website connect as to this same AD credential.

Therefore we setup an AD account: domain/Appsportaluser and set it to the application pool identify and the connect as on the website.

Testing this works fine and allows the SQL queries to be run successfully.

However the issue is that by setting this, the AD account returned by the following command is domain/appsportaluser instead of the actual users AD username in the following function:
System.Security.Principal.WindowsIdentity.GetCurrent()

Therefore I have no longer an ability to authenticate the user against the AD account as it is no longer passing the AD account to the application.

Really looking for some advise on how I can achieve both running SQL queries against AD credentials that are entered into the Pool or Website (meaning that I don’t need to store the credentials in a file) and (b) the ability to be able to obtain the website visitors AD username in my code.


I have tried setting the application pool to the "domain/Appsportaluser" hoping this would be used by the application to run the SQL accounts via and left the website connect as to "Pass Through Authentication". This allows my username to be passed to the site, however when loading a SQL based page it results in an error as follows:


Application information:
Application domain: /LM/W3SVC/13/ROOT-4-132487074001499862
Trust level: Full
Application Virtual Path: /
Application Path: D:\VirtualWeb\ITO\
Machine name: xxx1-S-WEB10

Process information:
Process ID: 6840
Process name: w3wp.exe
Account name: domain\appsportaluser

Exception information:
Exception type: SqlException
Exception message: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)

----------------------------------------------------------------------


Any advise would be highly appreciated.

Thanks,
Damien
 
Top Bottom