Windows 7 clients suddenly unable to connect to SQL database
Our environment in a nutshell:
Server1: Small Business Server 2003 R2 (Active Directory domain, it is the sole domain controller [DC])
Server2: Dedicated to SQL Server 2005 and the SAP Business One database
Server3: Windows Server 2003 R2 Standard Terminal Server. This provides user sessions that are the equivalent of Windows XP SP3.
Two Windows 7 workstations: 1x 64-bit, 1x 32-bit. Both are domain joined.
All above computers on a gigabit LAN.
Client product is SAP Business One 2007 A (8.00.181) SP:00 PL:49
SQL Native Client is 9.00.5000.00
SQL Server is mixed-mode authentication, however all users use trusted Windows connections. All users with access to the product are members of an "SAP Users" domain security group.
Normal operation is:
- Local Win7 workstations have client installed and connect over the LAN to the SQL Server instance on Server2
- Remote users log on to the Server3 Terminal Server, fire up sessions and connect to the SQL Server instance
Up to and including Thursday, March 12 everything was working fine for all users. As of Friday, March 13 the Terminal Server users/sessions are still working perfectly but the Windows 7 workstations can no longer connect to the SQL Server instance with trusted connections. They are still able to connect if they switch to a SQL Server logon and use the "sa" user account and its password.
Also, the users normally using the Win7 workstations can log on to the Terminal Server with their usual domain accounts, and run SAP from there, which works perfectly.
The error the Windows 7 users are seeing:
SQL State: 28000
SQL Server Error: 18452
[Microsoft][SQL Native Client][SQL Server]Login failed for user ". The user is not associated with a trusted SQL Server connection.
Prior to each logon attempt above, the Server2 SQL Server machine logs another event to its Application Event Log:
SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: xx.xx.xx.xx]
Source: SQL Server instance, event 17806. From SQL Server log, Severity 20, State 2
What I've tried so far:
- All servers have been restarted in correct order
- Running the client in Windows XP SP3 compatibility mode. No difference
- Running the client as Administrator. No difference
- Upgrading to SQL Server Native Client 11.0 via:
- Uninstall B1 client
- Uninstall v9.00 Native Client
- Install v11.00 Native Client
- Install B1 client
- Net effect is no difference
Googling the event IDs that are being logged seems to point to:
- Expired domain account. Manually checked that the 2 users accounts are not expired and they belong to the "SAP Users" group. Furthermore, the domain accounts are still working when used in Terminal Server sessions.
- Problem with Service Primary Names (SPNs). This seems to be Active Directory-related. Again, the domain accounts normally used on the Win7 workstations work fine on the Terminal Server so I'm inclined to think AD is OK
Other information that may be useful:
- No Windows updates applied between March 12 and 13 on either Win7 workstation
- Both Win7 workstations running Microsoft Security Essentials and the native Windows 7 firewall
- All servers running Trend Worry Free Business Security suite
Sorry about the length of the post but hopefully it will reduce wild goose chases. Any troubleshooting ideas greatly appreciated.
Al Doman replied
It turns out it was some sort of subtle problem with Trend antivirus running on the servers.
On all 3 servers there are 3 main protection components that are enabled by default:
- Web Reputation
- URL Filtering
What I did was to turn off all 3 components for all the servers. With Trend this can be done from the central management console. SAP then worked on one of the Win7 clients using Trusted Connection (i.e. Windows Authentication).
Then I turned the Trend components back on, on all servers, one at a time (to try to find which component was the culprit). Here’s the funny part: I turned all 3 back on, and SAP still works on the Win7 client using Windows Authentication! I even restarted it to make sure that state of affairs persists across a restart, and it’s still OK.
It's worth noting the problem was not solved shortly after it appeared by restarting all 3 servers. There was even a scheduled installation of Windows updates a couple of hours ago, which restarted all 3 servers again, and the problem still persisted. It was not fixed until the Trend components were globally disabled (on all 3 servers), then re-enabled.
Trend WBFS is version 9.0, pre SP1.