Asp Net 2.0 Security Membership And Role Management

Chapter 11

All of these pieces of TSQL use views so they don’t depend on any unsupported functionality. If you author a custom provider that requires developers to use the existing SqlMembershipProvider to register users, then you don’t need to worry about writing any other SQL. If you always create users with the Membership feature first, the necessary rows of data will already exist in the application and user tables. In essence, with this approach you are depending on ASP.NET to set things up ahead of time for you, and the only risk you are taking with your schema is a foreign key directly into an ASP.NET table.

However, what happens if you want to create your own custom Membership provider, but you still want your data to be integrated with other features such as Profile and Web Parts Personalization? Now you have the problem of getting a row of data into the user and application tables. If you wanted to, you could still require that SqlMembershipProvider be used even though someone really uses your custom provider for user management. You could register a user with SqlMembershipProvider simply to take advantage of the fact that by doing so you will get user and application rows set up properly.

That approach, though, is admittedly pretty clunky, and customers would wonder why the Membership user table holds all of this extra data. The better approach would be to insert the common data into aspnet_Users and aspnet_Applications — but, of course, the Catch-22 here is that ASP.NET 2.0 has no publicly supported way to do so. Assuming that you are fine with taking the added risk of using officially undocumented and unsupported stored procedures, you can solve this problem by using the stored procedures that already exist in the default ASP.NET schemas:

aspnet_Applications_CreateApplication — Other ASP.NET features use this undocumented and unsupported feature to automatically create an application as needed. You pass it the string value for the application name, and it returns as an output parameter the GUID for the newly created application.

aspnet_Users_CreateUser — This undocumented and unsupported stored procedure creates a row in the aspnet_Users table for a new user. You pass it the ApplicationId, username of the new user, and the settings for IsAnonymous and LastActivityDate. The procedure returns the GUID for the newly created user.

To at least mitigate the risk of these stored procedures changing or being renamed, you should limit the places where you call unsupported stored procedures. For example, if you wrote a store procedure for a custom Membership implementation and you wanted to create a new user, you could write something like this:

create procedure MyCustomUserCreation @pApplicationName nvarchar(256),

@pUsername	nvarchar(256),
@pUserId	uniqueidentifier OUTPUT
as

declare @applicationID	uniqueidentifier
declare @retVal	int
declare @rightNow	datetime

set @rightNow = getutcdate()

--this ensures the row in the application data exists

--if the application already exists, the sproc just performs --a select

412

SqlMembershipProvider

exec dbo.aspnet_Applications_CreateApplication @pApplicationName, @applicationID OUTPUT

--if for some reason the user record was already registered --just return it

select	@pUserId = UserId
from	dbo.vw_aspnet_Users u,
	dbo.vw_aspnet_Applications a
where	a.LoweredApplicationName = LOWER(@pApplicationName)
and	u.LoweredUserName	= LOWER(@pUsername)
and	u.ApplicationId	= a.ApplicationId

if (@pUserId is null) begin

exec @retVal = dbo.aspnet_Users_CreateUser @applicationID, @pUsername, 0, @rightNow, @pUserId OUTPUT

End

if (@retVal = -1) --other error handling here return @retVal

--if you make it this far, create the rest of the user --data in your custom tables

return 0

This stored procedure uses a mix of supported views and the unsupported stored procedures for creating applications and users. It starts by ensuring that a row in aspnet_Applications already exists by calling the aspnet_Applications_CreateApplication stored procedure. Internally, this stored procedure first attempts to return a row of application data if the application already exists. If the application doesn’t exist, the stored procedure creates it for you. As a result, it is safe to repeatedly call this stored procedure with the same application name, because only the very first call results in an insert.

The user creation stored procedure then checks to see if the user record was already registered in the aspnet_Users table. If the user already exists, it just fetches the existing UserId by querying the view. However, if the user is not already in aspnet_Users, then the stored procedure calls the aspnet _Users_CreateUser stored procedure to insert a row into the aspnet_Users table. Assuming that no errors occur by this point, you would then write additional code to perform the necessary inserts into your custom data tables.

On one hand, wrapping this kind of logic inside of your own stored procedure ensures that if the ASP.NET procedures change in a future release, you have to edit only this one stored procedure. On the other hand if you spam your code base with calls to the ASP.NET application creation and user creation stored procedures, you risk having to implement mass rework each time you upgrade the database with newer ASP.NET stored procedures. And, of course, in the extreme you could clone and rename the two ASP.NET stored procedures that are being used — though such an approach is likely to break if the underlying schemas for the aspnet_Users and aspnet_Applications tables change.

413

Chapter 11

Why Are There Calls to the LOWER Function?

In a few of the previous samples there is code that looks like LOWER(@pUsername) and LOWER(@pApplicationName). You might be wondering why not just perform joins directly against the UserName and ApplicationName columns in the views? If you install your database using a case-insen- sitive sort order, you don’t need to muck around with the LOWER function. However, because ASP.NET can’t control the collation orders of customer databases, many of the stored procedures in ASP.NET use columns whose sole purpose is to store the lowered representation of string data.

For example, the aspnet_Users table has a UserName column and a LoweredUserName column. If you install this schema in a database that is case-sensitive, you will see that the ASP.NET features still work in a case-insensitive manner. You could create a new user called “TEST” using SqlMembershipProvider, and you could still log in by typing in a username of “test”. This means that ASP.NET stored procedures have to perform extra work during inserts, updates, and selects to ensure that string data is being queried in a case-insensitive manner regardless of the collation order for the underlying database.

Typically, at insert time (and updates in the case of data like email addresses), various stored procedures explicitly lower the data prior to inserting it into a Lowered* column. The original casing is preserved in a separate column. So, when you create a new user, the value TEST goes into the UserName column, but the lowercased representation of test goes into the LoweredUserName column. Whenever an ASP.NET feature performs a username based query, it always lowercases the search parameter and then compares it against the LoweredUserName column. This is why some of the view samples earlier used the syntax

LoweredUserName = LOWER(@pUserName). However, when you get a MembershipUser from the database, the Username property reflects the original casing used at creation time.

The reason that the ASP.NET stored procedures enforce the lowercasing is that, for the most part, the string data managed by the various features is intended to be used in a case-insensitive manner. Usernames and email addresses are typically not expected to be case-sensitive. When you log in to a Windows desktop, for example, you can type your username in all capitals if you want, and the login still works. Similarly, you can email yourself using all capital letters, and the email will still reach you. In general, this behavior means that the following pieces of data are stored using two columns of data and are treated as case-insensitive for search and data modification purposes:

Application name

Username

Email address

Role names

Virtual paths stored by Web Parts Personalization

If you are an experienced database developer all of this probably raises a second question: Why the kludgy workaround? You may not realize it, but the database schemas for the provider-based features in ASP.NET are actually supported on SQL Server 7.0, 2000, and 2005.

Unfortunately, due to the wide range of supported SQL Server versions, there is not a single silver bullet for enforcing case-insensitivity. Only with SQL Server 2000 or later are you able to explicitly control collations on a column by column basis. Although the development team could have created a 2000/2005 table schema that was separate from the 7.0 schema, the workaround for handling lowercased data would still

414

SqlMembershipProvider

have been necessary for the 7.0 specific schema. Because supporting SQL Server 7.0 requires a workaround in all of the stored procedures anyway, it didn’t make much sense to fork the database schemas and then have to support two subtly different sets of stored procedures and tables going forward.

The Membership Database Schema

The Membership database schema (contained in InstallMembership.sql) deals with storing Membership-specific data. Where overlaps exist with the common table schema (the username and application name), the data is stored using the common tables. As a result, only one additional table is added by Membership — the aspnet_Membership table. There is also a view called vw_aspnet _MembershipUsers that maps most, though not all, of the columns on this table. The vast majority of the Membership database schemas that are installed are for stored procedures used by

SqlMembershipProvider.

The aspnet_Membership table is:
CREATE TABLE dbo.aspnet_Membership (
ApplicationId	uniqueidentifier	NOT NULL
FOREIGN KEY REFERENCES dbo.aspnet_Applications(ApplicationId),
UserId	uniqueidentifier	NOT NULL

PRIMARY KEY NONCLUSTERED

FOREIGN KEY REFERENCES dbo.aspnet_Users(UserId),

Password	nvarchar(128)	NOT NULL,
PasswordFormat	int	NOT NULL DEFAULT 0,
PasswordSalt	nvarchar(128)	NOT NULL,
MobilePIN	nvarchar(16),
Email	nvarchar(256),
LoweredEmail	nvarchar(256),
PasswordQuestion	nvarchar(256),
PasswordAnswer	nvarchar(128),
IsApproved	bit	NOT NULL,
IsLockedOut	bit	NOT NULL,
CreateDate	datetime	NOT NULL,
LastLoginDate	datetime	NOT NULL,
LastPasswordChangedDate	datetime	NOT NULL,
LastLockoutDate	datetime	NOT NULL,
FailedPasswordAttemptCount	int	NOT NULL,
FailedPasswordAttemptWindowStart	datetime	NOT NULL,
FailedPasswordAnswerAttemptCount	int	NOT NULL,
FailedPasswordAnswerAttemptWindowStart	datetime	NOT NULL,
Comment	ntext )

Many columns in the table should be familiar to you because they map directly to properties on the MembershipUser class. A brief summary of each of the column values is listed here:

ApplicationId — This column is included solely as a performance optimization for few stored procedures. Including the ApplicationId allows these procedures to perform a select directly against the aspnet_Membership table without first having to join through the aspnet _Applications table. From a data consistency standpoint though, the column isn’t necessary, because UserId represents the combination of username and application name.

415

Chapter 11

UserId — The primary key for the table. You can think of a MembershipUser as being a “derivation” of the base user record stored in aspnet_Users. The UserId column is used by SqlMembershipProvider to join back to aspnet_Users to fetch the actual username as well as the LastActivityDate for a user.

Password — Stores the password for the user in the format configured on SqlMemershipProvider. As a result, the value of this column can contain a cleartext password, an encrypted password, or a hashed representation of the password plus the salt value from the

PasswordSalt column.

PasswordFormat — This column is used internally by SqlMembershipProvider when decoding the value in the Password and PasswordAnswer columns. When you set the password format on a provider, that format is used to encode the password and password answer. The specific password format that was used is then stored by the provider in this column. If you subsequently change the password format for the provider, preexisting passwords and password answers are

still usable. SqlMembershipProvider will continue to decode and encode preexisting passwords and answers using the format that was originally used when the record was created. The possible values for this column are: 0 = clear text, 1 = hashed, and 2 = encrypted.

PasswordSalt — If you choose a hashed password format with SqlMembershipProvider, the provider will automatically generate a random 16-byte salt value and then hash passwords and password answers using a string that consists of the text and the random salt values. The result of the hashing operation is stored in the Password column. Because the salt value is always required to validate the password and password answer, it is stored in this column.

MobilePIN — Another leftover from earlier plans for more extensive support for mobile users. The idea was that in conjunction with MobileAlias from aspnet_Users, you would be able to validate a mobile user’s credentials using a custom PIN. Just as a traditional username could be too unwieldy for mobile users to type in, a traditional password could also be unwieldy. Instead, the idea was that you could validate a mobile user with just a PIN — much in the way you use ATM cards today and validate them using just a PIN code. None of this functionality was implemented in ASP.NET 2.0, but the column was left in the table in case a future release chooses to implement this.

Email — The email address for a user. SqlMembershipProvider enforces uniqueness of this value based on the requiresUniqueEmail configuration setting.

LoweredEmail — The result of calling LOWER on the email column. This ensures the provider can perform case-insensitive lookups based on email address, regardless of the collation order of the underlying database.

PasswordQuestion — If a provider is configured to use password questions and answers (that is, requiresPasswordQuestionAndAnswer is set to true in configuration), this is the column where the question is stored. Note that the question is always stored in cleartext and that, furthermore, the expectation is that the entire question is stored in this column. Some developers may instead want to have a limited list of common password questions — in which case a domain lookup table of questions would be more useful. In this case, the functionality of SqlMembershipProvider would result in the same question text repeatedly showing up in this column for many users. If you want to use a domain table to limit the number of possible password questions, you could instead store the string value of the question’s primary key in this column and write extra code to resolve this value against a lookup table.

416

SqlMembershipProvider

PasswordAnswer — The user’s secret answer to a password question is stored in this column. For security reasons, SqlMembershipProvider actually stores an encoded representation of the password answer based on the password format that was applied to the user’s password. This means that if the user’s password was stored as a hash value, a hash of the secret answer is also stored as opposed to storing the answer in cleartext. If you configure the provider to use hashing or encryption, you will need to test the effective maximum length of password answer that can be stored. For hashing and encryption, a base64-encoded representation is stored in this field. Stronger hash algorithms can result in a base64-encoded representation that is too large to store in this field because the column is an nvarchar(128). Similarly, the encrypted version of a password answer may also be too large to store in this field after taking into account the overhead of encryption and base64 encoding.

IsApproved — Stores the value of the MembershipUser.IsApproved property.

IsLockedOut — This column is set to true whenever the provider detects that too many bad passwords or bad password answers have been supplied. The provider configuration attributes maxInvalidPasswordAttempts and passwordAttemptWindow control this behavior.

CreateDate — The UTC date-time when SqlMembershipProvider was used to create the user record in the table. There can be an edge case where a different type of authentication is used initially on a website with other ASP.NET provider-based features. At a later point, the website may be switched over to use Membership with SqlMembershipProvider. In this case,

SqlMembershipProvider will only insert a user into the aspnet_Membership table because the user record already exists in aspnet_Users. For this reason, you may see that for newly created users the value of CreateDate in aspnet_Membership is different than the

LastActivityDate column in aspnet_Users.

LastLoginDate — SqlMembershipProvider stores the UTC date-time of a successful login attempt in this column whenever ValidateUser is called. When a user is first created, the provider sets this column to the same value as the CreateDate column.

LastPasswordChangedDate — The last UTC date-time when the provider changed the password stored in the Password column. When a user is first created, the provider sets this column to the same value as the CreateDate column.

LastLockoutDate — Used in conjunction with the IsLockedOut field. If the user is in a locked out state, this column contains the UTC date-time when the lockout occurred. For users that are not locked out, this field instead contains a default value of “01/01/1754.”

FailedPasswordAttemptCount — The provider keeps track of bad password attempts in this column. Even though determining account lockout for bad passwords and bad password answers uses the same configuration attributes (maxInvalidPasswordAttempts and passwordAttemptWindow), the provider keeps track of bad password attempts separately from bad password answer attempts. Any time that an account is unlocked or any time the correct password is used for an account, this field is reset to zero.

FailedPasswordAnswerAttemptCount — If the provider is configured to allow question-and- answer-based password retrieval or password resets (that is, requiresQuestionAndAnswer is set to true in configuration and either enablePasswordRetrieval or enablePasswordReset is set to true), then the provider keeps track of failed password answer attempts in this column. After a user account is unlocked, this counter is reset to zero. Any successful use of a password (that is, ValidateUser succeeded) or password answer (that is, GetPassword is called using a password answer) will also reset this column to zero.

417

Chapter 11

FailedPasswordAttemptWindowStart — When the provider keeps track of bad passwords it needs to know the start of the time window in UTC time during which it should track bad attempts. It stores the start of this time window in this column. Any time an account is unlocked, or any time the correct password is used for an account, this field is reset to a default value of 01/01/1754.

FailedPasswordAnswerAttemptWindowStart — When the provider keeps track of bad password answers, it needs to know the start of the time window during which it should track bad attempts. It stores the start of this time window in UTC time in this column. Notice how the provider keeps track of bad password answers attempts separately from bad password attempts by storing the tracking information for each type of event in a different set of columns. Any time an account is unlocked, or any time the correct password or correct password answer is used for the account, this field is rest to a default value of 01/01/1754.

Comment — A catch-all column that you can use to store miscellaneous data. Because this is an ntext column, you can actually store an immense amount of data in this field and then retrieve it from the MembershipUser.Comment property.

In addition to the single database table, the Membership feature also installs a single view: vw_aspnet_ MembershipUsers. This view maps most of the columns from aspnet_Membership one for one. However, the Password and PasswordSalt columns aren’t included in the view because the view is really intended for reporting purposes. From a security standpoint these columns were left out of the view because they are intended for only internal use by the provider and its stored procedures. The PasswordAnswer column probably should also have been left out of the view, but because the answer was actually stored in cleartext for most of the development cycle, it ended up being left in the view.

The view also joins in all of the columns from the aspnet_Users table. This makes the vw_aspnet_MembershipUsers view easier to use because most reporting queries written against this view will at the very least need the UserName column from the aspnet_Users table.

SQL Server–Specific Provider Configuration Options

Because SqlMembershipProvider connects to SQL Server, it users two SQL Server–specific configuration attributes on the provider definition:

connectionStringName — As you would expect, the provider needs to know what database and server to connect to. The value of this attribute must point at a named connection string defined up in the <connectionStrings /> section.

commandTimeout — As you work with larger databases, you may find that the default ADO.NET SqlCommand timeout of 30 seconds is too short for certain operations. For SqlMembershipProvider the Find* and Get* search methods can result in long-running queries especially with poor query parameters. You can change the command timeout that the provider uses with this configuration attribute. You can increase or decrease the amount of time that ADO.NET will wait for a SqlCommand to complete.

418

SqlMembershipProvider

Working with SQL Ser ver Express

Sometimes folks think that there is a separate set of providers for SQL Server 2005 Express different from the regular SKUs of SQL Server. SqlMembershipProvider as well as all of the other SQL-based providers in ASP.NET 2.0 work equally well against the Express and non-Express versions of SQL Server 2005. However, there are some differences in how the database schema is installed when using SQL Server Express.

SQL Server Express (SSE) is the low-end SKU of SQL Server 2005. It normally installs on a machine as a named instance: SQLEXPRESS. As a result, you can install SSE on machines running SQL Server 2000 or other versions of SQL Server 2005 without interfering with these installations. There is also a special mode of operation supported by SSE called user instancing. The internal database code shared across all of the ASP.NET SQL-based providers includes special logic in the event a provider runs against SSE that has user instancing enabled.

The idea behind user instancing is that the central SSE named instance (identified in a connection string as server=.\SQLEXPRESS) can be used to spawn additional instances of the SQL Server worker process. These spawned instances are referred to as user instances. They are referred to as “user” instances because the SQLEXPRESS named instance spawns these extra worker processes to run with the account credentials of a user — specifically the Windows user credentials that opened an ADO.NET connection in the first place.

To make use of SSE user instancing, you use a special form of ADO.NET connection string. You can see an example of a user instanced connection string by looking at the <connectionStrings /> section in machine.config:

<connectionStrings>

<add name=”LocalSqlServer” connectionString=”data source=.\SQLEXPRESS;

Integrated Security=SSPI;

AttachDBFilename=|DataDirectory|aspnetdb.mdf; User Instance=true”

providerName=”System.Data.SqlClient”/>

</connectionStrings>

The bolded portions of the connection string cause ADO.NET and SSE to handle the initial database connection in a different manner from when connecting to a regular version of SQL Server.

The data source portion of the connection string tells ADO.NET to initially open a connection against the named SSE instance. The User Instance=true portion of the connection string is a hint to ADO.NET and SSE that the connection should really be rerouted to a spawned worker process running with the account credentials currently active on the operating system thread at the time the ADO.NET connection was opened. The AttachDBFilename portion of the connection string tells SSE that once the spawned user instance is up and running, it should attach the SQL Server mdf data file at the specified location as a database in the spawned user instance.

ADO.NET actually preprocesses the AttachDBFilename syntax and substitutes in the full physical path information in place of |DataDirectory|. This syntax refers to an app-domain-level variable that host processes fill in. A client application such as a ClickOnce application will place one value inside of this app-domain variable. You can see what an ASP.NET host process uses with the following code:

Response.Write(System.AppDomain.CurrentDomain.GetData(“DataDirectory”));

419

Chapter 11

If you run this code in an IIS-based web, you will get back a path that looks something like:

c:\inetpub\wwwroot\Chapter11\SSEUsingIIS\App_Data

After ADO.NET substitutes the value of the DataDirectory app-domain variable in the connection string, it then passes the information down to SSE. So by the time SSE gets the connection string information, it is actually looking at a full physical file path to an .mdf file located somewhere within the directory structure of the web application.

SSE is able to attach a database in the user instance because within the user instance your code is running with System Administrator privileges. Because the user instance is spawned with some set of credentials, and that same set of credentials is sending commands over an ADO.NET connection, from the point of view of SSE those credentials have SA privileges. This makes sense because the credentials had the right to spawn a worker process in the first place, so the same credentials might as well have full control over any database operations within the user instance. Note that by default interactive users on a machine as well as accounts like NETWORK SERVICE and ASPNET have rights to connect to the default SSE named instance. As a result, this same set of accounts also has rights to request user instancing, thus elevating themselves to the System Administrators role within the scope of the spawned user instance.

There is still another set of rights that must be satisfied for SSE user instances to work: NTFS file ACLs. If you start out designing your application inside of Visual Studio, and if you create an App_Data directory, then Visual Studio will automatically grant Read and Write ACLs on this directory to ASPNET and NETWORK SERVICE. As a result, when SSE attempts to read or write data to or from the .mdf file the calls succeed because the credentials for the user instance have write access to the file.

However, if you just copy a bunch files to a location on the filesystem and then map an application in IIS to this file location, attempts to use SSE user instancing will probably fail. By default, the ACLs on inetpub\wwwroot don’t normally grant any Write access to the traditional web process accounts. As a result, if you rely on the automatic database creation process, you will instead end up with an error to the effect that SSE does not have write access to the database file. The simplest way to ensure that everything works properly is to create the web application inside of Visual Studio initially and let the design environment automatically place the correct ACLs on the App_Data directory for you.

When your website opens a connection with SSE user instancing requested:

1.An instance of sqlservr.exe is running initially as NETWORK SERVICE. This is the named SSE instance.

2.A new SSE user instance is spawned resulting in a second instance of sqlservr.exe running. This instance runs with user credentials based on the identity of the operating system thread that opened the connection.

3.If this is the first time that a user instance with the credentials from step 2 has ever been launched on the machine, SSE clones the master, msdb, and tempdb databases to support the user instance. If you look in the Documents and Settings directory on your hard drive, and then drill down to user name\ Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS, you will see that these three databases have been created.

4.The special logic contained in ASP.NET’s internal SQL provider code detects whether or not the

.mdf file specified in the connection string even exists at the specified file path. If the .mdf file does not physically exist, then the providers incur about a 15 second delay while they run all of the SQL installation files for the application services (that is, everything except for session state

420

SqlMembershipProvider

gets installed) against the user instance. The end result of this work is that an .mdf file is created in the file location specified by the connection string. As the last part of this work, the provider infrastructure detaches the newly created .mdf file.

5.Within the new user instance, the database file specified by AttachDBFilename is attached to the instance and registered in the metadata tables in the user instance’s master database. If you are accustomed to working with databases as a named database in other versions of SQL Server, this might seem a bit strange. However, using the attach syntax in the connection string causes the SSE user instance to attach the database on your behalf.

The connection string shown earlier exists in machine.config to allow developers that use Visual Studio to get up and running “auto-magically” with the application services. Rather than running aspnet_regsql.exe manually to install the database scripts into a specific database on a database server, you can write code against a feature like Membership, and the database will automatically be created for you.

From an ease-of-use perspective, this is actually pretty powerful and makes features like Membership so straightforward to use that developers potentially don’t need to understand or muck around with databases. Of course, this rosy scenario actually has a few streaks on the window, as you will shortly see. The automatic database creation behavior was originally intended for client applications such as ClickOnce apps. In a client environment, a user instance makes a fair amount of sense because someone is actually running interactively on a machine with a well-established set of credentials.

Furthermore, while running in a client environment there is likely to be sufficient processing power on the machine to handle the overhead of user instancing. Just running the named SSE instance plus a user instance with the ASP.NET database tables in them incurs up to about 45–75MB of memory overhead. That’s a pretty hefty wallop, but nonetheless manageable on a single desktop machine. When the user instancing capability was used for the ASP.NET application services, the main scenario was to support development in Visual Studio — in essence, this is another client application scenario, albeit in this case the client application is a development environment.

However, the SSE story on a web server starts to break down because of a few constraints with user instancing. The most obvious one is that user instancing is tied to a specific user identity, which leads to the potential for multiple user instances floating around on a server. With around a 45MB overhead when the SQL providers auto-create the database, and around 25MB of overhead once the database exists, it wouldn’t take long for a shared web server to run out of memory space. If you set up 40 application pools on IIS6 with each application pool running as a different identity, you could slurp up 1GB of memory with SSE user instances in short order.

The next issue with user instancing deals specifically with the operating system thread identity that is used when making the initial ADO.NET connection. As mentioned earlier, this identity is critical because SSE needs to ensure that cloned databases like the master database exist for these user accounts. Additionally, SSE needs the security token of the client to create a new child process running the SQL Server executable. It turns out though that for SSE to actually know where to create and look for the cloned versions of master and other databases, a Windows user profile needs to be loaded into memory.

In the scenario with a client application, the dependency on the Windows user profile is a nonissue. The second you log on to a Windows machine with some credentials, your Windows user profile is loaded. Hence, any application that you choose to run, including Visual Studio will be able to find data that is stored in the Windows user profile. What happens though for a noninteractive scenario like IIS6 applica-

421