Rick Strahl's Weblog  

Wind, waves, code and everything in between...
.NET • C# • Markdown • WPF • All Things Web
Contact   •   Articles   •   Products   •   Support   •   Advertise
Sponsored by:
West Wind WebSurge - Rest Client and Http Load Testing for Windows

SQL Server 2005 Windows Security has gone out to lunch...


:P
On this page:

I’ve been experimenting with my security settings on my local machine in order to get a better understanding how User Account protection actually affects some of my applications. So for kicks I turned on UAC in the User Account options from Control Panel (User Accounts). Ran for a while then turned it back off.

 

A little later I ran one of my local Windows applications that connects to my SQL Server Database and it bonked immediately due to a database connection error – it couldn’t use the Integrated Security login provided on the connection string. WTF?

 

Somehow SQL Server’s Windows Authentication got hosed in the account switching. At first thought I figured it must be that my account is not longer a ‘real’ Administrator account so I checked the Group Policy editor and the Run all administrators in Admin Approval Mode option and it’s set to disabled. It also look like I run other Admin tasks like running the IIS Administrator or access the Services MMC. So it looks like the account is OK and SQL Server’s security Windows Security somehow lost connection to the Administrator account.

 

Trying to log into SQL Administration also fails with Windows Authentication. I can’t log into Management Studio and I had to switch to use the sa Account. When I bring up the security section I see all the users including the Administrators group. Figuring the account entry is corrupted in some way I removed it from the Logins list and tried to add it back in but it fails with an error: It tells me that Administrators is a not a valid group even though I selected if of the Group Selection list in the Security dialog. WTF???

 

So that explains the why – the Administrators account somehow disconnected from the SQL Server configuration but how do I re-connect it?

 

 

I’m not sure what to next here… I managed to get my main application back up and running by explicitly assigning it to my Windows user credentials and that works and I suppose it will work in other apps as well. But I want to figure out what the heck happened with the Admnistrators group account that’s causing to the access to simply disappear like that.

 

Anybody have any idea?

Posted in SQL Server  

The Voices of Reason


 

Dunc
January 15, 2007

# re: SQL Server 2005 Windows Security has gone out to lunch...

Make sure the various server network protocols haven't been de-activated using the SQL Server Configuration Manager.

West Wind  © Rick Strahl, West Wind Technologies, 2005 - 2024