Monday, November 24, 2008
I saw Mark's post earlier bringing up
how you're not supposed to use sp_ as the start of a stored procedure name....
Here's *why* it does that.
Go to any user database (NOT MASTER) you have and put in a proc like this:
create proc sp_addlogin
as
begin
select 'hah, PWND!'
end
go
And then try to run it.
You get an error message back:
Msg 201, Level 16, State 4, Procedure sp_addlogin, Line 0
Procedure or function 'sp_addlogin' expects parameter '@loginame', which was not supplied.
Which is a good thing. Notice that it's running the sp_addlogin in *master* and not the one in your local user database. The interesting thing is that if you try to run it directly:
exec monitoring..sp_addlogin
You get the same message back.
You cannot create a stored procedure with the same name as a system stored procedure and have it run. It doesn' t matter what database you put it into.
So the question becomes *why*. Here's why:
create proc sp_addlogin
@Loginame sysname,
@passwd sysname,
@defdb sysname,
@deflanguage sysname='',
@sid uniqueidentifier,
@encryptopt nvarchar(500)
as
begin
declare @rc int
insert into ImAnAwesomeHacker values (@Loginname, @passwd, @defdb, @deflanguage, @sid, @encryptopt)
exec @rc = master..sp_addlogin @Loginname, @passwd, @defdb, @deflanguage, @sid, @encryptopt
return @rc
end
go
(Ignore my lack of a field list on the insert, this is just an example and won't work anyway
)
And now you can capture *every* single username and password in cleartext. Grats.
Well, you can't do that, because your uberl33t haxor won't work because your stored procedure called sp_addlogin will never get called. Curses, foiled again! This does, however, illustrate pretty succinctly exaclty why SQL Server always checks master or sp_ names.
In older versions of SQL Server (i.e. 4.2 to 7.0 I think) if you named a stored proc sp_ and had one named the same in master it would *always* run the one in master. It doesn't do that anymore, but it still has to check master to see if you're trying to play games with a system proc. It only does this for stored procedures that start with sp_, so this is a very small but still somehow measurably important performance optimization. I think they changed the behavior because too many cretins use sp_ for stored procedure names and then forget to change database context away from master when they're updating stored procedures, which back in the olden days was done strictly with create/drop. The interesting problems that could result are left as an exercise for the reader.
Does anyone have anything *bad* to say about Hyperbac?
I've searched google, blown through every article there, blog entries, and everything. I can't find a single bad thing about the product.
Hell, Lance likes it. And I haven't ever seen Lance like any technology more advanced than bacon.
"Wheels suck. They square off, don't roll uphill on their own! I saw one roll backwards down a hill and over some guy's toe once. This is crap!"
Seriously.
"Fire's kinda OK, but I watched some guy get burned real bad once. I think I'll stay away from that."
And then Hyperbac:
"I've been testing that for over a year now and haven't found any problems."
Yeah. So either it (1) is completely suck free, or (2) they slipped Lance some really neat pills.
Thoughts?
I just went through an exercise to change the name of my SA login. I renamed it to something confidential, set it with a strong password, and then created a new login called 'sa' with the same password as the old SA.
"Hey, why don't you just disable it?"
Well, seems that the ISV that is using this particular server hardcoded SA into their application. If you're a manager, this is why you get a DBA involved in acquisition of these kinds of systems. Believe it or not, that's not the dumbest thing this ISV's done...
"Well, why don't you just put a strong password on SA then?"
Two reasons. First, SA is an obvious entry point into the system, and I'd rather keep any potential hackers having to guess at the supreme admin account on my server.
Second reason, and the dumbest thing I've ever seen in any software: They not only hardcoded their application to use SA, they hardcoded it to use a particular password, 4 characters, lowercase, and it's the first 4 letters of their company name.
Did I mention this ISV is a big bunch of morons? I bet they wear slip on shoes because they can't figure out knots.
Yeah.
So here are some intriguing gotchas.
First, SA probably owns most of your SQL Agent jobs. And if you rename it, the job's owner ID does not change, HOWEVER, the SQLAgent caches the login *name* not the login *SID*. So you either need to (1) restart the SQLAgent service, or (2):
use msdb
go
select 'exec sp_sqlagent_notify @op_type = N''J'', @job_id = '''
+ convert(varchar(50), job_id) + ''', @action_type = N''U'''
from sysjobs
And then run the script that pops out of that. It tells the SQL Agent that the jobs changed materially, and the SQLAgent does a rescan of sysjobs to get it's story straight. Yes, you could do the *exact* same thing with a cursor. But cursors suck, and I can avoid them here with a quick copy/paste, and I'm only doing this once, so...deal with it. If you choose to rename *and* disable the SA account, you'll want to go look up the sp_update_job stored procedure for a quick way to change all your job owners over to a new account.
Finally, there's the issue of default object usage. Since, oh, the entire reporting infrastructure of this system runs as SA (bonus credit problem: what's the implication of this if a report running as SA deadlocks with an accountant entering transactions on their user account?) and the reporting system uses stored procedures to return data, there were no permissions set on any of the reporting stored procedures, so I had to clean that up.
The only reason I did this is that we allow the ISV to have access to the server since it's in production-parallel right now so they can troubleshoot and diagnose problems (frequent) and every now and then, despite us telling them over and over not to log in as SA, change objects, or do backups, they frequently log in as SA, change objects without telling us, and do backups which jack up my ability to recover. So their lack of respect is what got them into this anyway.