How to use Windows authentication to connect to database from webserver if they are not in trusted domain or firewall blocks the windows authentication.

It is quite normal in asp.net applications to store the user name and password credentials to the database on webservers WEB.CONFIG File and often a penetration test review highlights this as a major issue. I am not a great fan of obfuscation and encryption as it does not give any performance advantage and two way encryptions can be cracked if someone is really serious about it. The quick alternative is to rely on windows authentication by marking “Integrated Security=SSPI” in the connection string instead of user name and password. It is quite easy to achieve if both SQL server and Web server are in the same trusted domain.

I had come across cases where infrastructure admin places the SQL on isolated box with no domain awareness or windows authentication to the SQL box is explicitly blocked by fire wall rule. The simple windows authentication to connect to sql box now became difficult. 

There is a way to get around this issue by using account mirroring feature in windows and it is explained as follows. The trick is to create a low privileged local user on both machines with same credential and allowing sql access to this user.

Create a Local User on web and SQL server Machines with same credentials

• Create a Local User in Web server and make sure that password never expires is selected.
• Go to Administrative Tools> Computer management> Local Users and Groups> Users Folder and create a new local user “test” (make sure Password never expires is selected)

• Perform the Same operation above on SQL server box and make sure the username and passwords match.

Assign the Local User on web and SQL server with roles

• Add the local user to the IIS_IUSRS group. (You can either use Computer management console or running aspnet_regiis.exe -ga “test” in command line).
• Local Users and Groups> Users Folder and select the new user “test” and open the properties. Now Select "Member Of" tab to add Roles to the local user "test".
• I have added user to the “Remove Desktop Users” to allow initial login and this role will be removed after initial remote login using the "test" user credentials.

• make sure same steps to add roles to "test" user are performed in SQL server machine as well.
• Login to Web and SQL machine using "test" user (RDP or direct login). This will activate the user for use.

Now we have the local user setup complete, we can move onto using this users identity for the IIS Apppool.

Set up IIS Apppool Identity

•  Open the IIS manger console and select advance settings of the app-pool. 

• Open the Identity value field to set custom identity instead of ApplicationpoolIdentity.
• Now enter the local windows user credential for user "test".

• Since the App pool identity is set to the local user credential the sql server can be accessed setting the windows authentication. For Authorisation part we need to allow the mirrored windows user account (i.e. the test account created on SQL box) to access SQL server resources.
• Create an SQL Login for the newly created local user "test"

USE master

GO

/*create login*/

CREATE LOGIN [SQL-Computer-Name\test] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

GO

• Create a SQL user on the database of interest mapping to the sql user login .i.e "test"

USE [DatabaseofInterest]

GO

/*create user*/

CREATE USER [SQL-Computer-Name\test] FOR LOGIN [SQL-Computer-Name\test] WITH DEFAULT_SCHEMA=[dbo]

GO

• Create a sql role with limited previlege and assign to the sql user

/*create new role*/

CREATE ROLE [WebsiteLeastAccessRole] 

GO

/*grant access to role*/

GRANT EXECUTE, SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO WebsiteLeastAccessRole

GO

/*assign the role to user*/

 EXEC sp_addrolemember 'WebsiteLeastAccessRole', 'SQL-Computer-Name\test';

GO

This completes the set up to allow connecting web and sql server using windows authentication credentials. This will reduce the chance to store the sql credentials at webserver reducing the chance of compromised credentials. But the responsibility is now to the network admin to ensure the network is secure enough and if the web machine is compromised the attacker can fire commands to sql as well.