Whew! The title says it all.

I recently had a little problem with my server. Notice the 3 blown caps.

I had my 5 year old daughter take a look. Without pointing out anything in particular, I asked her “Ok, this computer isn’t working right. Any idea why?”

She looked around with a flashlight for about a minute and states “Those 3 batteries look like they’ve gotten dirty. Maybe you need to clean then up?”<g>

So, I’ve got no server as of now.

Fortunately, all my SQL databases I use for testing get backed up every night, so I still have them all. Unfortunately, the SQL Server installation itself was on that blown machine<sigh>.

I’d been wanting to virtualize my server for some time now, so this was the perfect opportunity. I fired up VMWare, cloned an existing XP VM I often use for testing, cleaned it up, put SQL Server 2005 on it, ran the SQL SP2 update, then restored all my DB backups. No problem.

With the VM running on my workstation, I loaded SQL Server Management Studio on my workstation and tried to connect to the virtual server.

(for search purposes, the message is “Login failed for user ‘argos\guest’. (Microsoft SQL Server, Error: 18456)”)

Hmm. I was pretty sure I had things set up right. So I tried SQL Authentication with the SA user and password.

I’m in.

Ok. That’s odd. And why is it trying to connect me as guest? Wait… Originally, my server was running Win2003 Server, so it had DNS setup. With it gone, I’m in workgroup mode. Ah! I didn’t have matching user accounts on this new virtual server and on my workstation. Set up a matching account, and try a remote connection request to the SQL Server from my workstation. Still no dice.

I checked the SQL ERRORLOG file (you can typically find it in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG), and I end up with this:

2009-02-05 16:11:36.36 Logon       Error: 18456, Severity: 14, State: 11.
2009-02-05 16:11:36.36 Logon       Login failed for user ‘ARGOS\Guest’. [CLIENT: 192.168.100.110]

That STATE number is the key. A state of 11 means “Valid login but server access failure” (There’s a really good table of error states here).

That was the clue I needed. After way too much googling, scratching my head and trying random things, I eventually came across this post by Jens Suessmeyer.

The short of it is that, under XP anyway, if you are running in a workgroup (ie non-domain) and you have Simplified File Sharing turned on, this will cause SQL Server to ALWAYS force remote authentication requests to fall back to the Guest user. It was only after I’d found the above post that I came across this MSKB article that describes the problem in more detail.

So, it’s off to the Folder Options dialog, I uncheck the Use Simple File Sharing option:

And, no more connection failures!

So, to sum up. If:

1. You’re using SQL Server 2005 (and likely other versions as well).
2. You’re running in a WORKGROUP environment
3. You need or want to use WINDOWS AUTHENTICATION and not just SQL Authentication
4. You set up a login on your server and your remote machine that exactly match in username and password
5. You’ve enabled SQL Ports 1433 and 1434 through your firewall.
6. You can connect via Windows Auth just fine when you run SQL Management Studio from the server
7. BUT you get a connection error indicating that your trying to connect as GUEST, when you were expecting to connect as your current username.

Then you most likely need to turn OFF Simple File Sharing on the SQL Server machine.