Duration (Pacific Standard Time):
To (Pacific Standard Time):
Impact:
  • None
User Action:
  • None
Visual Studio Samples: Fitch and Mather 7.0

Windows Authentication and SQL Server

Fitch and Mather 7.0 uses SQL Server authentication to access SQL Server. While this is simple and provides an easy to understand sample, it is not the most secure option. Fitch and Mather 7.0 stores its connection strings (which contain database credentials) in two places: The Web.config file and the COM+ Admin Catalog (in the Constructor string of the FMStocks7.GAM.7 component). Both of these locations are readable by any user by default so it is very easy for the secrets (credentials) to be unintentionally disclosed.

A better solution is to use Windows integrated security. First, a Windows identity is needed that is known to both the Internet Information Services (IIS) server and the SQL Server database. There are two options:

  • Domain account
    If the servers are part of a domain and administrators can create a domain account for the application, then this is the best method.
  • Local accounts with synchronized passwords
    If the network is not implemented to support a domain account, then creating the same local account on both machines (with the same password) provides secure access to both IIS and the SQL Server database.

Choose one of the methods above then create an account named FMStocks_7 Application (either in the domain or in both machines) and make it part of the Guests group only. This way the application runs with the minimum privileges required.

Next, change the connection strings (in Web.config and in the COM+ admin catalog) to remove the explicit credentials and enable the use of integrated security. For example, if the connection string is the following:

Data Source=MYDBSERVER; User Id=Fitch and Mather 7.0 _login; Password=*********;
    Initial Catalog=Fitch and Mather 7.0 ;

Then change it to:

Data Source=MYDBSERVER;Integrated Security=SSPI;
    Initial Catalog=Fitch and Mather 7.0 ;

This change means that the identity the thread is running under is used to access the SQL Server database. By default, ASP.NET (the aspnet_wp.exe process) runs under the local ASPNET account, however the application code should run under the Fitch and Mather 7.0 Application account instead. The advantage of running the application code under the Fitch and Mather 7.0 Application account is that it is a Windows account and you can give it the appropriate permission for SQL Server.

Next, the account needs to be associated with the Fitch and Mather 7.0 application.

To associate the account with Fitch and Mather 7.0

  1. Configure IIS.
    1. From the Start menu point to Programs, then Administrative Tools, and click Internet Services Manager.
      The Internet Information Services window opens.
    2. On the Tree tab, navigate the tree in search of the FMStocks7 virtual directory. (This virtual directory location varies depending on the installation.) Once located, right-click FMStocks7 and select Properties.
      The FMStocks7 Properties dialog box appears.
    3. In the FMStocks7 Properties dialog box, choose the Directory Security tab. Under Anonymous access and authentication control, click the Edit button.
      The Authentication Methods dialog box appears.
    4. Under Anonymous access, click the Edit button.
      The Anonymous User dialog box appears.
    5. Clear Allow IIS to control password. This is required so that IIS stores the credentials and has them available when it authenticates against SQL Server.
    6. Under Username and Password, replace the anonymous user account with FMStocks7_Application and enter its password.
    7. Finally, click OK three times to finish.
  2. Enable impersonation in Web.config by adding the following line:
    <system.web>
       <identity impersonate="true" />
          ....
    

The final step is configuring SQL Server to give the Fitch and Mather 7.0 Application account the right permissions in the FMStocks7 and FMStocks7_GAM databases (if you are running the two databases in different machines you need to repeat this process on each machine):

To configure SQL Server permissions in FMStocks7 and FMStocks7_GAM databases

  1. Open SQL Server.
    • From the Start menu point to Programs, then Microsoft SQL Server, and click Enterprise Manager.
      The SQL Server Enterprise Manager window opens.
  2. Create a SQL login.
    1. On the Tree tab, navigate the tree in search of the Security folder beneath the server's name and expand it.
    2. Right-click the Logins folder and select New Login.
      The SQL Server Login Properties – New Login dialog box appears.
    3. In the Name box enter FMStocks7 Application.
    4. Under Authentication, locate the Domain box and choose the local machine name (e.g. MYDBSERVER) or your domain, depending on where the Fitch and Mather 7.0 Application account was created.
    5. Set Security Access to: Grant access.
    6. Under Defaults, set Database to FMStocks7.
    7. Select the Database Access tab and make sure that FMStocks7 and/or FMStocsk7_GAM are checked in the respective Permit fields.
    8. Under the Database roles for FMStocks7 and/or FMSTocks7_GAM select the db_denydatareader and db_denydatawriter permissions to prevent this account from accessing the tables directly.
  3. Delete the FMStocks7_login since it will no longer be used.
    1. Remaining on the Tree tab in the SQL Server Enterprise Manager window, double-click Logins under the Security folder.
      The various logins are displayed in the right pane of the SQL Server Enterprise Manager window.
    2. Locate the entry for FMStocks7_Login and delete it by right-clicking it and choosing Delete.
      The FMStocks7_Login dialog box appears. Choose Yes to confirm the deletion.
  4. Grant Execute permissions to all the stored procedures.
    1. Remaining on the Tree tab in the SQL Server Enterprise Manager window, navigate the tree in search of the FMStocks7 database located beneath the server name, Databases folder.
    2. Locate and open Stored Procedures in the FMStocks database folder and for each non-system stored procedure do the following:
      Double-click the stored procedure (for instance, the Account_Add entry).
      The Stored Procedure Properties dialog box appears.
      Click the Permissions button.
      The Object Properties – FMStocks7 dialog box appears.
      In the Permissions table, locate the EXEC column and give the FMStocks7 Application account EXEC permissions by selecting the box.

To verify that the SQL Server permissions are configured appropriately, run the application and use the SQL profiler to verify that the account being used is Fitch and Mather 7.0 Application.

The following now happens for every request:

  1. An anonymous request comes in, IIS impersonates the account you specified for the anonymous user (Fitch and Mather 7.0 Application) and passes the request to aspnet_wp.exe. Note that although Fitch and Mather uses Forms authentication, as far as IIS is concerned all requests are anonymous.
  2. Because you configured the application to use impersonation (through the Web.config change), ASP.NET impersonates the IIS identity (Fitch and Mather 7.0 Application) in the current thread.
  3. When the application opens a SQL Connection, it uses integrated Windows authentication to execute the stored procedures under the Fitch and Mather 7.0 Application login.

Distributed Scenarios

When running distributed scenarios, remember the following:

BLL remoted
All of the changes mentioned above for the Web server need to be done in the application tier (where the BLL runs) instead.

GAM remoted (.NET remoting)
All of the changes mentioned above for the Web server need to be done in the application tier (where the GAM runs) also.

GAM remoted (DCOM)
The changes mentioned above need to be done in the Web server only. In addition, complete the following steps in the machine where the GAM is running (where the GAM COM+ server application resides).

To set up the GAM

  1. Open Component Services and modify the account for the FMStocks7.GAM COM+ component.
    1. From the Start menu point to Programs, then Administrative Tools, and click Component Services.
      The Component Services window opens.
    2. On the Tree tab, navigate the tree in search of the FMStocks7.GAM folder beneath Component Services, My Computer, COM+ Applications.
    3. Right-click the FMStocks7.GAM entry and choose Properties.
      The FMStocks7.GAM Properties dialog box appears.
    4. Select the Identity tab, choose This User, and supply the Windows credentials for FMStocks7 Application in the User and Password boxes. Click OK to accept the changes.
  2. Change the FMStocks7.GAM.7 constructor string.
    1. Under the Components folder of the FMStocks7.GAM Application in the Component Services window (see Step 1 above), right-click the FMStocks7.GAM.7 component and choose Properties.
      The FMStocks7.GAM.7 Properties dialog box appears.
    2. Select the Activation tab. Under Object Construction, locate Constructor string and change the string to:
      Provider=SQLOLEDB;Data Source= MYDBSERVER;Integrated Security=SSPI;Initial Catalog=Fitch and Mather 7.0 _GAM;

See Also

Architectural Overview | Distributed Deployment Scenarios | Security | .NET Remoting Security