Thursday, July 8, 2010

Logon Failed in SSMS on Windows 2008 (or Windows 7)

We recently moved our SQL Server from Windows Server 2003 to Windows Server 2008. When I tried to connect through SSMS on the server I got a rather odd error:

Now, I'm a SQL Administrator and am able to connect to the server from my local PC (Windows XP). I check the SQL Event log and see the following:
. After some Googling I find an article referencing UAC. It suggests right clicking on SSMS and running as administrator. I do this, accept the annoying message, and yowza, the system works.

Now, this is all well and good, but I really don't want to have to right click and run as administrator each time. Plus, as infrequently as I actually log on to the server to use SSMS, I'm sure I'll forget and end up googling this exact same error again. So, I poked around a little bit and found out that you can force the .exe to always run as administrator

  • SSMS Properties
  • Compatability Tab
  • Show Settings for all users

    Notice how this is now for the ssms.exe file, not for the shortcut anymore
  • Check "Run this program as administrator"

Now, whenever you run SSMS on the server, you will get the annoying message and it will work. I'm a little surprised that Microsoft didn't include that in the install package for SQL Server 2008, but at least there's a pretty easy fix.

Wednesday, July 7, 2010

HTTP Error 401.1 - Unauthorized: Access is denied due to invalid credentials

I get a call this morning that one of my applications isn't working. I go to check it and sure enough I'm getting the following: "HTTP Error 401.1 - Unauthorized: Access is denied due to invalid credentials". This application impersonates a specific Windows account so I check the usual stuff...

  • Password change : Nope
  • Security on the filesystem: All set properly
  • Long username problem : Nope, not the problem here
  • Account Disabled: nope

So, I'm running out of ideas.. so, I try to recycle the App pool (smart me, created a separate application pool just for this particular application). The pool comes back online and still no go.

I start looking through the log files and I'm not getting any more information except for the "invalid credentials". I take a look at the account again and chuck it into domain admins just to get this up and running. Recycle IIS this time and poof... same error

So, I've now ruled out any possible unauthorized access issue. So I take one final look th the user properties and I notice that even though the account isn't locked out, the password has lo and behold expired. Then I notice that the checkbox for "Password never expires" is not checked. I check the checkbox, apply the changes, and poof.. everything works

So, the lesson here, boys and girls, is to make sure you make sure your service accounts passwords do not expire