BUG: You may receive a "Data provider failed while executing a provider command" 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 (840258)


The information in this article applies to:

  • Microsoft Office Access 2003
  • Microsoft Access 2002

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 '<username>'


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:

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

    Note In the following code, replace <ServerName> with the name of the server where the data access page is deployed from. Replace <VirtualDirectory> with the name of the virtual directory on the IIS server where the data access page is deployed from. Replace <DataAccessPage> with the name of the data access page. For example, you may replace "<ServerName>/<VirtualDirectory>/<DataAccessPage>" with "testserver/databases/SecureDAP.htm." 
    <HTML>
<HEAD>
<TITLE>Authenticate User</TITLE>
<SCRIPT language="VBScript">
Sub writeCookie(strVariableName, varVariableValue)
Document.Cookie = strVariableName & "=" & varVariableValue
End Sub
Sub openDAP()
Dim varUserID
Dim varPWD
varUserID = document.all.txtUserID.value
varPWD = document.all.txtPWD.value
writeCookie "pUserID", varUserID
writeCookie "pPWD", varPWD
'Change "ServerName" to the name of your server.
window.navigate("http://<ServerName>/<VirtualDirectory>/<DataAccessPage>")
End Sub
</SCRIPT>
</HEAD>
<BODY>
<FORM id="form1">
<TABLE>
<TR>
<TD>User Name:</TD>
<TD><input type="text" name="txtUserID" size="20"></TD>
</TR>
<TR>
<TD>Password:</TD>
<TD><input type="password" name="txtPWD" size="20"></TD>
</TR>
</TABLE>
<P><input type="button" value="Open DAP"
name="B2" onClick="openDAP()"></P>
</FORM>
</BODY>
</HTML>
  2. Save the .htm file in the folder where the data access page is stored. Name the file Authenticate.htm.
  3. Open the data access page in Access.
  4. On the View menu, click HTML Source.
  5. 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, ";")
' 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="" 
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="Provider=Microsoft.Jet.OLEDB.4.0;" & _
";Data Source=<FullPathToDatabaseFile>" & _
";User ID=" & readCookie("pUserID") & _
";Password=" & readCookie("pPWD") & _
";Extended Properties=Jet OLEDB:System database=<FullPathToWorkgroupInformationFile>" & _
";Mode=Share Deny None" & _
";Persist Security Info=False"
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="Provider=SQLOLEDB.1" & _
";User ID=" & readCookie("pUserID") & _
";Password=" & readCookie("pPWD") & _
";Initial Catalog=<DatabaseName>" & _
";Data Source=<ServerName>" & _
";Persist Security Info=False"
Document.MSODSC.UseRemoteProvider=True
    Note Replace <DatabaseName> with the name of the appropriate database. For example, you may use the Northwind.mdb sample database.
  6. 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
  7. 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.

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the "Applies to" section.

MORE INFORMATION

Steps to reproduce the problem

Security-enhanced Microsoft Access database

  1. 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.
  2. 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.
  3. Import the Employees table from the Northwind.mdb sample database to the security-enhanced database.
  4. Create a data access page that is based on the Employees table.
  5. Set the UseRemoteProvider property of the data access page to True.
  6. Save the data access page as Employees.htm in the folder that you created in step 1.
  7. On the default Web site, use Internet Services Manager to create a virtual directory that has the following properties:
    PropertyValue
    Virtual directory aliasDatabases
    Web site content directory<HomeDirectoryOfDefaultWebSite>\Databases
  8. Use a client browser to open the Employees.htm file. Use a URL that is similar to the following:

    http://Testserver/DAPdirectory/Employees.htm

Microsoft SQL Server database

  1. 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.
  2. In the folder that you created in step 1, create an Access database.
  3. In the Database window, click Pages under Objects, and then click New.
  4. In the New Data Access Page dialog box, click Design View, and then click OK.
  5. In Design view, under Field List in the right pane, click Page connection properties.
  6. In the Data Link Properties dialog box, click the Provider tab.
  7. In the Select the data you want to connect to list, click Microsoft OLE DB Provider for SQL Server, and then click Next.
  8. 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.
  9. 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.
  10. Drag the Customers table to Design view on the data access page.
  11. In the Layout Wizard dialog box, click OK.
  12. Set the UseRemoteProvider property of the data access page to True.
  13. Save the data access page as Customers.htm in the folder that you created in step 1.
  14. On the default Web site, use Internet Services Manager to create a virtual directory that has the following properties:
    PropertyValue
    Virtual directory aliasDatabases
    Web site content directory<HomeDirectoryOfDefaultWebSite>\Databases
  15. Use a client browser to open the Customers.htm file. Use a URL that is similar to the following:

    http://Testserver/DAPdirectory/Customers.htm

REFERENCES

For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

291783 How to deploy data access pages over the Internet

300699 Common data access page deployment errors

Modification Type:MinorLast Reviewed:9/27/2006
Keywords:kbui kbScript kbProvider kbJET kbhttp kbhtml kbDAPScript kbDAP kbControl kbBrowse kbtshoot kbbug KB840258 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.