Microsoft KB Archive/840258

= BUG: You may receive a &quot;Data provider failed while executing a provider command&quot; error message when you try to access a three-tier data access page that is bound to a security-enhanced Access database or to an SQL Server database =

Article ID: 840258

Article Last Modified on 9/27/2006

-

APPLIES TO


 * Microsoft Office Access 2003
 * Microsoft Access 2002 Standard Edition

-





SYMPTOMS
When you try to access a three-tier data access page that contains controls that are bound to the data in a security-enhanced Microsoft Access database or in a Microsoft SQL Server database, you may receive an error message that is similar to the following:

Data provider failed while executing a provider command.

Additionally, based on the type of database, you may receive one of the following error messages:

Security-enhanced Microsoft Access database
Cannot start your application. The workgroup information file is missing or opened exclusively by another user.

Microsoft SQL Server database
Login failed for user ' '



CAUSE
The problem occurs if you set the UseRemoteProvider property of the data access page to True. The Jet OLEDB:System Database parameter in the ConnectionString property that contains the path of the workgroup information file is not passed to the Microsoft Internet Information Services (IIS) server where the data access page is deployed from. Additionally, the remote provider does not prompt you for a user name and a password that you can use to access the data from the security-enhanced database.



WORKAROUND
To work around this problem, create an .htm file that does the following:
 * Accepts the user name and the password that are used to access the data on the security-enhanced database
 * Writes the user name and the password to a cookie
 * Navigates to the data access page

The data access page is modified to include a script that sets the UseRemoteProvider property to True. The script also modifies the ConnectionString property of the data access page to include the following:
 * The Jet OLEDB:System Database parameter as an extended property
 * The user name and the password that are read from the cookie

To create an .htm file, follow these steps.

Note Make sure that the data access page is stored in a folder that is created in the home directory of the default Web site on the IIS server. For example, you may use the following path:

:\inetpub\wwwroot\databases   Create an .htm file that contains HTML code that is similar to the following.

Note In the following code, replace  with the name of the server where the data access page is deployed from. Replace  with the name of the virtual directory on the IIS server where the data access page is deployed from. Replace      Save the .htm file in the folder where the data access page is stored. Name the file Authenticate.htm.</li> Open the data access page in Access.</li> On the View menu, click HTML Source.</li>  In Microsoft Script Editor, add the following code in the HEAD tag.

Note Replace <FullPathToDatabaseFile> with the full path of the security-enhanced database file. For example, you may use the following path:

<InstallationDrive>:\inetpub\wwwroot\databases\DatabaseForDAP.mdb

Note Replace <FullPathToWorkgroupInformationFile> with the full path of the workgroup information file. For example, you may use the following path:

<InstallationDrive>:\inetpub\wwwroot\databases\System.mdw <SCRIPT language=VBScript> Function readCookie(strVariableName) Dim intLocation Dim intNameLength Dim intValueLength Dim intNextSemicolon Dim strTemp ' Calculate the length and the location of the variable name. intNameLength = Len(strVariableName) intLocation = Instr(Document.Cookie, strVariableName) strTemp = Right(Document.Cookie, Len(Document.Cookie) - intLocation + 1) ' Find the position of the next semicolon. intNextSemicolon = Instr(strTemp, &quot;;&quot;) ' If not found, assume that you are at the end. If intNextSemicolon = 0 Then intNextSemicolon = Len(strTemp) + 1 End If intValueLength = intNextSemicolon - intNameLength - 2 If intValueLength=-1 then readCookie=&quot;&quot; Else readCookie = Mid(strTemp, intNameLength + 2, intValueLength) End If End Function

'You must replace this string if the data source is an SQL Server data source. Document.MSODSC.ConnectionString=&quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; & _ &quot;;Data Source=<FullPathToDatabaseFile>&quot; & _ &quot;;User ID=&quot; & readCookie(&quot;pUserID&quot;) & _ &quot;;Password=&quot; & readCookie(&quot;pPWD&quot;) & _ &quot;;Extended Properties=Jet OLEDB:System database=<FullPathToWorkgroupInformationFile>&quot; & _ &quot;;Mode=Share Deny None&quot; & _ &quot;;Persist Security Info=False&quot; Document.MSODSC.UseRemoteProvider=True </SCRIPT> Note If the data access page contains controls that are bound to an SQL Server data source, you must use a connection string that is similar to the following: Document.MSODSC.ConnectionString=&quot;Provider=SQLOLEDB.1&quot; & _ &quot;;User ID=&quot; & readCookie(&quot;pUserID&quot;) & _ &quot;;Password=&quot; & readCookie(&quot;pPWD&quot;) & _ &quot;;Initial Catalog=<DatabaseName>&quot; & _ &quot;;Data Source=<ServerName>&quot; & _ &quot;;Persist Security Info=False&quot; Document.MSODSC.UseRemoteProvider=True Note Replace <DatabaseName> with the name of the appropriate database. For example, you may use the Northwind.mdb sample database. </li> Use a client browser, such as Microsoft Internet Explorer, to open the Authenticate.htm file. Use a URL that is similar to the following:

http://Testserver/Databases/Authenticate.htm</li> In the Authenticate User Web page, type a valid user name in the User Name box, type a valid password in the Password box, and then click Open DAP.

You notice that the data access page appears correctly in the client browser.</li></ol>

<div class="status_section">

STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the &quot;Applies to&quot; section.

<div class="moreinformation_section">

Security-enhanced Microsoft Access database
<ol> In the folder that corresponds to the home directory of the default Web site on the IIS server, create a folder that is named Databases.</li> In the folder that you created in step 1, create a security-enhanced Access database. Make sure that the associated workgroup information file (.mdw) is also in the same folder.</li> Import the Employees table from the Northwind.mdb sample database to the security-enhanced database.</li> Create a data access page that is based on the Employees table.</li> Set the UseRemoteProvider property of the data access page to True.</li> Save the data access page as Employees.htm in the folder that you created in step 1.</li> On the default Web site, use Internet Services Manager to create a virtual directory that has the following properties: </li> Use a client browser to open the Employees.htm file. Use a URL that is similar to the following:

http://Testserver/DAPdirectory/Employees.htm

</li></ol>

Microsoft SQL Server database
<ol> In the folder that corresponds to the home directory of the default Web site on the IIS server, create a folder that is named Databases.</li> In the folder that you created in step 1, create an Access database.</li> <li>In the Database window, click Pages under Objects, and then click New.</li> <li>In the New Data Access Page dialog box, click Design View, and then click OK.</li> <li>In Design view, under Field List in the right pane, click Page connection properties.</li> <li>In the Data Link Properties dialog box, click the Provider tab.</li> <li>In the Select the data you want to connect to list, click Microsoft OLE DB Provider for SQL Server, and then click Next.</li> <li>On the Connection tab, provide the appropriate server name, user name, and password.

Note Make sure that the Allow saving password check box is not selected.</li> <li>In the Select the database on the server list, click the Northwind database, and then click OK. The tables in the Northwind database appear under the Field List section.</li> <li>Drag the Customers table to Design view on the data access page.</li> <li>In the Layout Wizard dialog box, click OK.</li> <li>Set the UseRemoteProvider property of the data access page to True.</li> <li>Save the data access page as Customers.htm in the folder that you created in step 1.</li> <li>On the default Web site, use Internet Services Manager to create a virtual directory that has the following properties: </li> <li>Use a client browser to open the Customers.htm file. Use a URL that is similar to the following:

http://Testserver/DAPdirectory/Customers.htm

</li></ol>

<div class="references_section">