How Come a Local Windows Admin Can Log into SQL as a Sysadmin

Question

How Come a Local Windows Admin Can Log into SQL as a Sysadmin

DjH

SSC Eights!

Points: 843
More actions
March 25, 2008 at 10:22 am

#180352

I have a SQL 2005 instance running under a different domain user than what the server is logged in as. There is a need for a user to always be logged into the server due to a 3rd party application that always needs to be running and is not very sophisticated.
I.e. server was started up and logged in as userA, but the SQL server services are running as userB.
userA is a local administrator on the system and i have removed userA from the SQL logins.
If i remote desktop into the server as userA, i am not able to log into SQL (get the login denied message), however if i am physically at the box and logged in, i am able to do whatever (sysadmin privileges) i want in SQL, even though the user is NOT set up in SQL.
Anyone know why this is?
Thanks.
-Dan

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

I've not seen that. Do you have audit success and failure both turned on? If so, does it indicate a difference in the login names when you connect via RDP and via the local console?

K. Brian Kelley
@kbriankelley

SQLBill SSC Guru Points: 51440 More actions · Answer 2

SQLBill

SSC Guru

Points: 51440

March 25, 2008 at 3:23 pm

#793779

Builtin\Administrators role maybe?

-SQLBill

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 3

I agree with Brian that I haven't seen this. Are you using Windows Auth or SQL Auth?

DjH SSC Eights! Points: 843 More actions · Answer 4

I was not auditing successul logins, but i will turn that on in the morning and test it out to see if i get anything different. The BuiltIn\Admin was removed from SQL Security and i am using Mixed Mode for logins.

DjH SSC Eights! Points: 843 More actions · Answer 5

Ok after auditing successful logins, the local admin (SQLAdmin) shows as being able to successfully login, even though it is not setup as a SQL user.

Login succeeded for user 'Server\SQLAdmin'. Connection: trusted. [CLIENT: local machine ]

Interesting note though, i created the user SQLAdmin in SQL and denied their ability to log in and bingo, they are denied.

Still doesn't explain how it was able to connect without being a SQL user though and i would still be interested in finding out this answer.

----------------------

Update (2 hours later)

In a moment where i feel fairly dumb, one thing i did not try initially was locally logging out of the server and back in. Once i did this, the local admin did not have access to SQL. Live and learn i suppose.

DjH SSC Eights! Points: 843 More actions · Answer 6

Just a thought, but with the addition of the need for the windows group SQLServer2005SQLAgentUser to have SQL sysadmin permissions in SQL 2005, doesn't this create a fairly large security hole in SQL? Anyone who has the ability to add members to this local windows group automatically becomes a SQL sysadmin, right???

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 7

dan (3/26/2008)
Just a thought, but with the addition of the need for the windows group SQLServer2005SQLAgentUser to have SQL sysadmin permissions in SQL 2005, doesn't this create a fairly large security hole in SQL? Anyone who has the ability to add members to this local windows group automatically becomes a SQL sysadmin, right???

Yes, technically. And when it comes to clusters, those are domain groups. But the same weakness still applies. But, if you're using a domain group for SQL Server DBAs... you get the idea. Auditing group memberships becomes very important in this regard.

K. Brian Kelley
@kbriankelley