Michael J. Swart

December 16, 2008

My Security Blunder

Filed under: Miscelleaneous SQL — Michael J. Swart @ 2:07 pm

A nice story for the holidays.

Not too long ago I made a small error that was going to cause me to lose a number of hours of work and the headache that goes along with it. Okay, if I’m being honest it was more than a small error; it was a huge snafu! And although the only affected systems were my own development box. I still had that sinking feeling in my stomach the moment I realized my situation.

My blunder

On my development server I have SQL Server 2008 installed and I was poking around and playing with some of the more general security features. I wanted to find out what different users could do when granted various privileges. And so as part of this poking around, I granted myself (DOMAIN\MSWART) access to the server and I disabled the BUILTIN\Administrators principal.

Then after more poking, I wanted to remove my Network Service account from the set of logins. Now since the Network Service account uses my computer’s credentials, the login was called (DOMAIN\MSWART$).

You might see where this is going. I forgot the ‘$’ symbol and wound up removing any rights that I had on the machine.

What this meant for me

Luckily this was a development server and any data I cared about was backed up. The real kicker is that I had no way of accessing the database server. To me it looked like this meant going through the process of reinstalling SQL Server which is easier said than done. See Euan Garden’s experience here and Victor Hurdugaci’s experience here.

What I ended up doing instead

But guess what. Management Studio was still open and I hadn’t refreshed Object Explorer so I could still see the list of principals for my server. There was still one login that had access: NT AUTHORITY\LOCAL SERVICE.

Well what else was there to do? I created a windows service. On startup for the service, I used ADO.Net to read from a temporary text file and execute the contents on my database.

I installed the service using InstallUtil.exe I ran a couple scripts a few times (checking and reading any errors in the Event Log). After a few trials I attempted to add DOMAIN\MSWART back as an admin…

And it worked!

I was in! I could do anything I needed to do again. The whole ordeal cost me about twenty minutes (or it saved me three and a half hours if you’re the glass-is-half-full kind of person)

What did I learn?

Don’t do that… for starters. I also found out that if a service can access your database, then so can anyone who has a decent amount of control over the computer.

And from another point of view, I learned how to write quick and dirty windows services.

Update! (Jan 2, 2009)

Joe Sack pointed out (via Jason Massie’s blog here) that I could have more easily gained access by restarting the SQL Server service with the -m or -f options to put SQL Server in single user mode. Details at Microsoft’s troubleshooting page: Troubleshooting: Connecting to SQL Server When System Administrators Are Locked Out

December 11, 2008

Eliminated NULL values

I’ve gotten a bunch of questions recently about the following warning:

Warning: Null value is eliminated by an aggregate or other SET operation.

Hopefully, with this post, I’ll be able to explain what this error means and give some examples about what (if anything) you should do about it.
The aggregate or set operation is likely a SUM or COUNT. Microsoft has a list here of all the aggregate functions. So essentially, when you see this error message, it means you’re aggregating a bunch of values and one of the values that is being summed or counted is null. Consider the following example:
CREATE TABLE tempTable
(
   i float
)
GO

INSERT tempTable VALUES (1);
INSERT tempTable VALUES (2);
INSERT tempTable VALUES (NULL);
GO

SELECT COUNT(iAS [count i:] FROM temptable
SELECT COUNT(1AS [count 1:] FROM temptable
SELECT COUNT(*) AS [count *:] FROM temptable
SELECT SUM(iAS [sum i:] FROM temptable
And the results would look something like:
count i:
-----------
2
Warning: Null value is eliminated by an aggregate or other SET operation.

count 1:
-----------
3

count *:
-----------
3

sum i:
----------------------
3
Warning: Null value is eliminated by an aggregate or other SET operation.
So now you have to decide what do you want to do about it:
Change the null to a value that is not null:
This is the most popular approach. For example:
select sum(i)
becomes
select sum(ISNULL(i, 0))
or the equivalent COALESCE if you like the ANSI standard:
select sum(COALESCE(i, 0))
And
select count(i)
should be written
select count(*)
or
select count(1)
any way.
Filter out the rows that can be null
Not so popular. Often it’s because of other requirements for the query. But if at all possible, try something like this:
SELECT SUM(iFROM tempTable WHERE NOT IS NULL
Suppress the warning
The ostrich’s answer with:
SET ANSI WARNINGS OFF
Or finally:
Ignore the warning
A variation of the previous suggestion. If the results are what you’re looking for, then don’t worry about it.

Powered by WordPress