Project Cool

You may receive the error "AllowCustomPaging must be true and VirtualItemCount must be set for a DataGrid with ID DataGrid1 when AllowPaging is set to true and the selected datasource does not implement ICollection." while attempting to bind a DataGrid to a DataReader and also setting the Datagrid's AllowPaging property to True.

The error occurs since you are binding a DataReader to the DataGrid. The DataReader offers a Forward Only - Read Only access to the Data that is being retrieved from the DataSource.

Since Paging would require the ResultSet to be accesible Forward as well as Reverse (To implement the Previous / Next set of records), the DataReader cannot help in this scenario.

Even if you set the AllowCustomPaging=true, though the error disappears, you will only be able to see the first set of records and will be unable to implement the built-in paging functionality provided by the DataGrid.

The resolution for the same is to use a DataSet which offers an In memory representation of Data so that you can navigate back and forth the resultset as well as do modifications to the result set. This way you can use the built-in paging functionality in your DataGrid.

However, if you want to only use a DataReader, then you need to store all the values of the DataReader in an array etc., and write custom paging functionality to implement paging.

Cheers and Happy Programming !!!

<UPDATE November 26, 2007>

I find that a lot of people found this article a little useful.  This article is in continuation to earlier one which deals with IIS and SQL Server on the same machine.  The link for the same is

http://geekswithblogs.net/ranganh/archive/2005/05/25/40489.aspx

</UPDATE>

This article is in continuation with Part I where I explained the scenarios where this error occurs when the Webserver and SQL Server are running on the same machine.

In case you havent read that, please Read Part I of this article, before proceeding.

Let us examine the 2nd scenario as follows:-

2. IIS (Webserver) and SQL Server are on different machines.

Check the SQL Server Authentication Mode and set is to Mixed Mode Authentication, as explained in Part I of this article.

Then comes the actual interesting part. Though you configure your SQL Server to Mixed Mode Authentication and add ASPNET or NETWORK SERVICE account to the Databases as explained in Part I of this article, you will still get the error.

The reason is that, the local accounts/credentials (ASPNET in Windows 2000 Server and Windows XP Pro., Network Services in Windows Server 2003) under which ASP.NET worker process runs are local accounts (local to the web server). Therefore the database server on a separate machine will not be able to see/recognize these accounts. So if you try using the same steps mentioned above to configure a trusted connection between the web server and the SQL server, you will get the error still.

The resolution for this, is as follow:-

1. Create a Domain Account with priveleges similar to ASPNET or NETWORKSERVICE.

2. Grant this Domain Account (DomainName/UserName) access in the SQL Server Database for the necessary databases.

3. Use Impersonation in your web.config (setting identity impersonate="true") in the web.config of your application.

Now, while enabling impersonation, you can either set the username and password in the web.config itself as follows:-

<identity impersonate="true" userName="DomainName\UserName" password="password" />

However this defeats the process of security as you are again storing the password in the web.config file.

The second method is to simply set identity impersonate to true and assign the username and password in the IIS.

To do that, do the following steps

1. Type inetmgr from your command prompt and give enter.

2. This would open the IIS Control Panel.

3. Expand the appropriate nodes and select the Virtual Directory of your application.

4. Right Click and select Properties.

5. Switch to the Directory Security Tab.

6. Under Anonymous access and authentication control click Edit

7. Check the Enable Anonymous access in case you want people to access the application without logging in with Windows Logon Screen.

8. Uncheck the Allow IIS to control password and enter the DomainName/UserName and Password in the respective boxes. Usually IIS uses IUSER_MACHINENAME credentials for Anonymous access.

9. Uncheck if any other authentication mode is checked and then press Ok twice to exit.

Now the application should serve and the error "Login failed..." shouldnt appear.

Cheers and Happy Programming !!!


Note:
The steps and guidelines provided in this article have been explained in many other online resources, MSDN and KB Articles and this is just an initiative to further spread the awareness and provide help for people who get stumped by this error.

I have freely referred many resources and taken steps for writing this articles and am extending my thanks to the respective authors who have helped me in compiling this article.

<UPDATE Date=November 26, 2007>

</UPDATE>

Integrated authentication allows for SQL Server to leverage Windows NT authentication to validate SQL Server logon accounts. This allows the user to bypass the standard SQL Server logon process. With this approach, a network user can access a SQL Server database without supplying a separate logon identification or password because SQL Server obtains the user and password information from the Windows NT network security process.

Choosing integrated authentication for ASP.NET applications is a good choice because no credentials are ever stored within your connection string for your application.

However, you may receive the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection" while trying to use Integrated Windows Authentication and your application needs to access the SQL Server Database using Trusted Connection.

Lets examine two scenarios where this error can occur.

1. Your IIS (Webserver) and SQL Server are on the same machine.

The first thing to check is that whether the SQL Server is configured to use Mixed Mode Authentication or Integrated Authentication.

To do that, do the following steps :-

1. Click Start - Programs - Microsoft SQL Server - Enterprise Manager to open the Enterprise Manager.

2. Connect to the appropriate Server if the SQL is a client installation.

3. Right click on the Server Node and click on Properties.

4. This opens the Properties Dialog.

5. Switch to the Security Tab.

6. Under the Security section, make sure the Option SQL Server and Windows is selected.

7. This ensures that your SQL Server is running under Mixed Mode Authentication.

The Next step is to ensure that the ASPNET account (IIS_WPG in case of Windows server 2003) has the appropriate access to the Database.

To do that, do the following steps:

1. Open SQL Server Enterprise Manager (Start - Programs - Microsoft SQL Server - Enterprise Manager), select the appropriate server, and expand the Security folder.

2. In the Logins check whether the IIS_WPG is listed.

3. If it is not listed, right-click on Logins and select New Login

4. In the Name: textbox either enter [Server/Domain Name]\IIS_WPG or click on the ellipses button to open the Windows NT user/group picker.

5. Select the current machine’s IIS_WPG group and click Add and OK to close the picker.

6. You then need to also set the default database and the permissions to access the database. To set the default database choose from the drop down list,

7. Next, click on the Database Access tab and specify the permissions.

8. Click on the Permit checkbox for every database that you wish to allow access to. You will also need to select database roles, checking db_owner will ensure your login has all necessary permissions to manage and use the selected database.

9. Click OK to exit the property dialog.

Your ASP.NET application is now configured to support integrated SQL Server authentication.

I will explain the next scenario of Webserver and SQL Server residing in different machines, in my next article.

Read Part II

Cheers and Happy Programming.