MOD2000: WFD Client Requires Mixed Security for SQL Server or for MSDE (244270)

SYMPTOMS

On a client computer, when you open the Gridview.htm page of the Issue Tracking sample application in Microsoft Internet Explorer, you receive an unexpected prompt for a password if you click the Offline option at the top of the page. The unexpected prompt has the following behaviors:

The prompt does not accept any password that you provide.
If you click OK without providing a password, or if you click Cancel, the top of the page appears blank. The top of the page does not display the expected Synchronize and Subscription options.

CAUSE

After Microsoft Data Engine (MSDE) or Microsoft SQL Server 7.0 was installed on the client computer, the security mode was changed from the default Mixed Security setting (meaning Windows Security and SQL Server Security combined) to the Integrated Security setting (meaning Windows Security alone).

When you take offline a solution that was developed in Microsoft Access Workflow Designer, the SQL Server database store is required to have a Security setting of Mixed Security.

After MSDE or SQL Server is installed on a Microsoft Windows NT-based or a Microsoft Windows 2000-based computer, the MSDE or SQL Server security mode can be set to either the Mixed Security setting or the Integrated Security setting.

NOTE: On a computer running Microsoft Windows 95 or Microsoft Windows 98, you cannot change the SQL Server or MSDE security mode from the default Mixed Security setting. Therefore, you do not experience this issue on those platforms.

RESOLUTION

You must reset the security mode of MSDE or SQL Server on the client computer to the Mixed Security setting. To do so, follow these steps:

With SQL Server 7.0

On the client computer, start Enterprise Manager.
Expand Microsoft SQL Servers.
Expand the server group.
Right-click the server, and then click Properties.
Click the Security tab.
Click SQL Server and Windows NT.
Click Apply.
Click OK.

With Microsoft Data Engine (MSDE)

MSDE does not have a user interface through which you can change this setting. However, you can run SQL Distributed Management Object (SQL-DMO) code in Visual Basic for Applications (VBA) to change the security setting, as follows:

On the client computer, open Microsoft Access.
On the Insert menu, click Module.
In the new module, on the Tools menu, click References. Make sure the reference Microsoft SQLDMO Object Library is selected. Click OK.

In the module, type or paste the following code:

Function SetSecurityMode(strServer As String, _
strSecurityMode As Double, strUser As String, _
strPassword As String)

'strSecurityMode can be set to one of the following
'1 Windows Authentication only
'2 Windows and SQL Server Authentication (Mixed)

Dim objSQL As Object

    Set objSQL = CreateObject("Sqldmo.SQLServer")
    
    ' Connect to SQL server...
    objSQL.Connect strServer, strUser, strPassword
                
    ' Set Security
    objSQL.IntegratedSecurity.SecurityMode = strSecurityMode
    
    ' Disconnect from SQL server.
    objSQL.DisConnect

End Function

In the Immediate window, type or paste the following code:
NOTE: This example assumes that the sa logon has the default setting of no password. If the sa logon has a password, place the password between the last set of quotation marks ("") in the code.
```
?SetSecurityMode("(local)",2,"sa","")
						
```

MORE INFORMATION

Steps to Reproduce the Behavior

Install Access Workflow Designer Server on a Windows NT server or a Windows 2000 server.
On the server, deploy the sample Issue Tracking solution.
On another computer that is running Windows NT or Windows 2000, log on using the same Windows NT account that you used when you installed the Workflow Designer Server components. This computer will serve as the Workflow Designer client computer. Complete the remaining steps on this computer.
Install Microsoft Office 2000 Premium.
Install SQL Server 7.0 Standard edition or SQL Server 7.0 Enterprise edition.
Apply the Microsoft SQL Server SP1 patch.
Install the Workflow Designer Client tools.
Use Internet Explorer 5 to connect to the following HTML page
http://server name/modWeb/default.htm
where server name is the name of the Access Workflow Designer server computer.
Click Offline.

Note that you receive the password prompt that is mentioned in the "Symptoms" section at the beginning of this article. If you click OK without providing a password, or if you click Cancel, the top of the page appears blank.