Mirrored Accounts

ASP.NET Application with mirrored accounts.

If your Web server and database server are not in the same or trusted domain, then you cannot use windows based authentication. The workaround is to use mirrored accounts (other than storing username/pwd in the config file). To use this, create a local account with the same user name and password on each server and then create a SQL Server login for the local account on the database server (article collected from MSDN articles).

Create a local account (on Application Server)

1. Create a local Windows account.
2. Run the following Aspnet_regiis.exe command to assign the relevant ASP.NET permissions to the account: aspnet_regiis.exe -ga machineName\userName 
3. Use the Local Security Policy tool to grant the Windows account the Deny logon locally user right.
4. Use IIS Manager to create an application pool running under the new account's identity and assign the ASP.NET application to the pool.

Create an application pool that runs using a custom service account

1. Start Internet Information Services (IIS) Manager.
2. In the left pane, expand the local computer and then expand Application Pools.
3. Right-click the Application Pools node, click New, and then click Application Pool.
4. In the Add New Application Pool dialog box, type TestPool in the Application Pool ID text box. Leave the Use default settings for new application pool option selected, and click OK. This creates a new application pool called TestPool.
5. Right-click the new application pool. and click Properties.
6. Click the Identity tab.
7. In the Application pool identity section, click Configurable.
8. Type CustomASP in the User name text box.
9. Type the password for the CustomASP account in the Password text box, and click Apply.
10. The Confirm Password dialog box appears. Type the password again, click OK, and then click OK again.

Create a local account (on Database Server)

Create a local account on the database server using the same username and password as the account you created on the Web server (in the previous step).

1. Create a local Windows account.
2. Use the Local Security Policy tool to grant the Windows account the Deny logon locally user right.

 

Grant database access to the Custom Service account

1. Create a SQL Server login for the Network Service account. If your database is on a separate server, create the login for the domainName\WebServerMachineName$ identity. You can use Enterprise Manager or run the following SQL statement in the osql command line tool to create the SQL login.

            exec sp_grantlogin 'domainName\WebServerMachineName$'

    2.  Create a database user in the required database, and map the login to the database user. Alternatively, you can run the       following SQL statements:

use targetDatabase

go

exec sp_grantdbaccess 'domainName\WebServerMachineName$'

go

1. Grant permissions to the role. Ideally, you should grant execute permissions to selected stored procedures and provide no direct table access.

Change In Web.config

The connection string used with Windows authentication must include either the Trusted_Connection=Yes attribute,  as shown here.

<connectionStrings>

<add name="MyDbConn1"

connectionString="Server=MyServer;Database=MyDb;Trusted_Connection=Yes;"/>

</connectionStrings>