Fighting sp_send_dbmail error: "Failed to initialize sqlcmd library with error number -2147467259."

We recently encountered a bunch of problems with sp_send_dbmail, and they all returned this:

Failed to initialize sqlcmd library with error number -2147467259.

Super irritating, that error, because it seems to suggest that you're doing something you're not. In fact, this error only indicates that there's some generic problem sending mail with sp_send_dbmail. Here are the problems we uncovered, and the troubleshooting steps for them.

1.  The SQL Server service account didn't have sufficient privileges on the domain.

As a rule, we don't allow our domain accounts to query the properties of other accounts.  This is true for service accounts, as well.  SQL, to do a fair number of things (not least to ensure the SQL Agent job owner is a valid domain member) requires the ability to query domain account properties.

We discovered this in troubleshooting:  when trying to use the

execute as user=

statement to make sure we didn't have a problem with database permissions, we received this error:

Could not obtain information about Windows NT group/user...

Aha: domain permissions problem.  This was causing the "Failed to initialize sqlcmd library" error.
Granting the service account additional privileges on the domain fixed this problem.

2.  The executing user didn't have sufficient privileges in the query database.

Having fixed the "execute as user" problem, we can now impersonate a DB user to execute the query in SSMS.  If you have sufficient permissions on the instance.  We're assuming you have sysadmin access, here.

When we run the sp_send_dbmail, and the executing user doesn't have permissions on the target database, we will see the "Failed to initialize sqlcmd library with error number -2147467259." error.

We can uncover this is the problem by just executing the query portion of the sp_send_dbmail stored proc, and using the "execute as user=" statement beforehand.  Specify the SQL Agent service account user in this statement, and if the user doesn't have permissions, you'll get an error message that's actually useful.

3.  The query is executing in the wrong database.

You actually see this pretty often, and the solution to this is most frequently listed possibility for fixing the "Failed to initialize sqlcmd library with error number -2147467259" error. 

The easiest solution to this problem is to specify the target database in sp_send_dbmail, like this:

EXEC msdb.dbo.sp_send_dbmail @recipients = @EmailList
       ,@execute_query_database = 'target_db_name'
       ,@subject = 'Subject'
       ,@body='Here's the body of the email'
       ,@query = @querytext
       ,@profile_name='email_profile'

I hope this helps; we spent far too long on tracking down #1 above.

Configuring a SQL Server Email Alert for SA Login Failures

SA Login 

SA Login authentication failures, essentially, should never happen. Mostly that's because we generally shouldn't be using the SA account, but rather should have dedicated application logins with appropriate privileges. Having those logins use Windows authentication instead of SQL authentication further removes SQL Server from the authentication process, which is even better.

So if the SA account isn't disabled, I want to know when there's an SA login failure.
[Generally, I want to know when there's an SA login success, as well, but we'll focus on the failures, here, because it's really low-hanging fruit.]

SQL Server Alerts

SQL Server Alerts are a very light-weight notification tool for lots of SQL Server events.  Brent Ozar has a nice run-down of some basic alerts that you ought to consider implementing; we'll add this one as another.

It's possible--and frequent--to address this problem by using login triggers.  That is certainly a valid, functional, and well-worn method.  Login triggers can introduce problems, though, especially when we have forgotten that they exist.  Moreover, this is what alerts are for, and there's something appealing to the simplicity of it all.

Scheduling a SQL Agent Job to Run on a Calculated Day that isn’t included in the Regular Scheduler Options

I’ve had a hard time coming up with a title for this post, because while the concept is easy to understand, it’s difficult to summarize.
We refresh one of our development instances on the first Monday of each month.  That’s an easy thing to schedule, as it’s an option within the SQL Agent scheduler:

We want to send a notification to the affected users beforehand, however, and since the refresh occurs in the wee hours of the morning, we can’t send out that alert on Monday.  It has to occur on the Friday before the first Monday. 
That, friends and neighbors, isn’t a built-in option.
It’s a little trickier (though in the end, not terrible) because the Friday before the first Monday could be in this month or in next month.