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:
- You’re using SQL Server 2005 (and likely other versions as well).
- You’re running in a WORKGROUP environment
- You need or want to use WINDOWS AUTHENTICATION and not just SQL Authentication
- You set up a login on your server and your remote machine that exactly match in username and password
- You’ve enabled SQL Ports 1433 and 1434 through your firewall.
- You can connect via Windows Auth just fine when you run SQL Management Studio from the server
- 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.
6 Comments
I bought an Alienware M18x from the oetlut center, upon receiving the item I noticed that the graphics cards were not crossfiring properly, I called Dell who sent out an engineer.The engineer tried to replace the primary graphics card and in doing so damaged the motherboard resulting in the computer being completely unusable and just showing a flickering black screen. The engineer said i would be offered a refund or replacement.I then got in touch with Dell who told me that due to the system being a refurbishment they would only be prepared to offer me a refund and would pick up the laptop from me. I did not think this was fair as all I wanted was a working laptop and felt that Dell owe it to me for a replacement (seeing as their engineer broke my laptop!).I argued my case to the complaints department who basically couldn’t give two shits about the situation.After numerous E-mails and complaints, I lost the will to live and just accepted the refund, to further rub salt into the wound Dell retracted their postage statement and said I would have to pay to post the laptop back to them that they broke in the first place!.Thanks Dell, you have made me feel like a piece of excrement you would scrape off the bottom of your shoe. I hope others heed this warning and choose a different supplier to avoid months of frustration and mistreatment.From my experience I have learned that I will NEVER, EVER, buy a computer from Dell again.If you require any more info on my experience then feel free to E-mail me at