Windows 7 clients suddenly unable to connect to SQL database

[Former Member]

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:

Connection failed:

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.

[Former Member · Accepted solution]

More information and another question:

Looking at the specific logged error event [Login failed for user "] (i.e. the user is the empty string), there is a section in a blog entry at

SQL Server 2005 connectivity error messages - SQL Protocols - Site Home - MSDN Blogs

which seems to address this exact error. According to the blog it can be caused by a space after the server name in the connection string.

Is there a way to view the connection string a given client computer is using to connect to the SQL Server instance? I'd like to compare the Win7 clients that are failing, to the Server 2003 Terminal Server sessions that are working.

[Former Member · Accepted solution]

Problem found!

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:

• Antivirus/Anti-spyware
• 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.

[jbrotto]

For windows 7 use business one 8.8 family of versions as it will still be compatible with xp machines and the windows server and sql server. I am running on that version with 8.82 and no issues with our windows 7 machines. Prior versions of B1 might not support the recent versions of windows.

[Former Member]

Hi Al Doman,

In Addition to Mr. Nagarjan reply, Please also check below link.

Hope this helps

--

--

Regards::::

Atul Chakraborty

[kothandaraman_nagarajan]

Hi,

Please check this thread

Thanks & Regards,

Nagarajan

[Former Member]

Hi,

Thanks, but unfortunately that thread doesn't apply in our case. All of the changes/settings in that document were already in place on our SQL server.

In short, the issue is:

• Formerly, Terminal Server (TS) sessions and Win7 clients could both connect to the server using Windows authentication
• Now, the TS sessions can still connect via Windows authentication, but the Win7 clients cannot. The Win7 clients can connect via sa user/SQL authentication

The SQL server setup seems fine, it seems to be an issue with the Win7 clients.