[Event Notifications] and impersonation

Had this question pop up at #SQLSatAustin last month.  Finally had some time to futz with it.

Naomi Williams (@naomithesqldba) asked a security question I hadn't considered before.  "How does Event Notification deal with impersonation?"

Hmmm.  Good question.

Well, there are two different ways to do impersonation.  SETUSER and EXECUTE AS. 
Testing both, they appear to act the same. 

Our examples:

SELECT SUSER_SNAME()
go
SETUSER 'mydomain\mycoworkersname'
GO
SELECT SUSER_SNAME()
go
CREATE TABLE test_using_setuser (id INT IDENTITY)
GO
DROP TABLE test_using_setuser 

go
SETUSER
GO
SELECT SUSER_SNAME()
GO

EXECUTE AS LOGIN = 'mydomain\mycoworkersname'
CREATE TABLE test_using_executeaslogin (id INT IDENTITY)
REVERT

Let's look at the obvious event, the CREATE_TABLE.

 
 
Hmmm, looks like he did it, right?

Well, fortunately for him, I also capture trace events, via TRC_OBJECTS.  Which has more information that isn't normally needed.  But it's saved my bacon before, because it includes the App Name and Hostname.

And what does that one look like?




Busted!  Two different ways, in fact.  First of all, look at the HostName, which is my desktop.  Thought that can be worked around (most easily by connecting to the box itself - how long, after all, do your systems people store login info?).

However, the other is much more damning.  SessionLoginName, which clearly shows it was me.

Thanks, Naomi!

[Event Notifications] my current slide deck

Thought I'd share this - the entire folder I run off of is shared, but here's the slide deck, embedded.  It should get new changes as I make them. 

[Event Notifications] Getting auto-TFS checkin set up for TFS 2013 (SQLWatchdog)

We recently upgraded to TFS 2013, so here are the steps (written as I do it) to get it running.

Aside: Since I'm using Powershell to run all this, why do I use the TF.cmd application instead of the Cmdlets?  Simplicity.  The Cmdlets appear to need the FULL version of a TFS client installed, plus you have to grab and install the cmdlets... and there are bugs if you don't use the right (x32 vs x64) version of the cmdlets with the right version of powershell.  So I built it around the TF CLI (Command-Line Interface).  The original post was for 2008, the new one (plus script below) are for 2013.

IMPORTANT:
Upgrading from 2008 to 2013 blows, since the config and cache can be screwed up.
What I had to do was completely drop the workfold and the workspace (PROTIP: first, list your workfold and workspaces and SAVE THOSE DETAILS), delete the entirety of the cache folder, delete everything from my EN_Workspace folder on disk, then rebuild from scratch.
One other oddity: before, from the root of my installation (c:\tfs_cli) I could run the script, and it could properly list the folders underneath it.  That appears to have changed for some reason; my Powershell script now CDs into the subfolder before it proceeds.  Not a major change... but took several hours to diagnose, since I was also fighting with other issues on the setup.  Caveat Emptor and all that.

Also for the TFS newbies: the default path you use for a LOT of things is:
http://myservername:8080/tfs/DefaultCollection .

Steps to get going:

• Install Java.  Got it from Oracle, which doesn't appear to have the same cruftware that Sun's does. Version 7-something
• Grab Team Foundation Everywhere 2013. Reading the docs, it works for TFS 2010, 2012, 2013, and Online. http://www.microsoft.com/en-us/download/details.aspx?id=40785
• Unzip into one massive folder (I called mine "c:\TFS_CLI\APP_2013"), then try running (since I'm on Server 2012, in Powershell):
• ".\tf.cmd" 
• it works - success!  Sorta.
• Found this page which seems to make the below setup easier: 
• http://msdn.microsoft.com/en-us/library/hh873092.aspx
• For the rest of this, I'm doing it as the Service account that SQL Server is running under on the box that will run the job. Oddly enough, it wasn't set that way when I built it on 2008, but it continued to work even though my password changed.  Note that nowhere in here do you actually put in a password.
• Type ".\tf eula", then accept ("y"). 
• muddle through the web site (tfsservername:8080/tfs) and add the SQL Service account as an admin to the branch I'm using (YourENProject).
• Using the MS page above:
• .\tf workspace -new TF_Workspace -collection:http://mytfservername:8080/tfs/DefaultCollection
• the collection URL is pretty standard - it should work in 2010,2012,2013.
• .\tf workfold -map $/YourENProject -workspace:TF_Workspace c:\TFS_CLI\EN_Workspace
• This line has several names.  Let's break them out.
• $/YourENProject is the "Branch" in TFS.  Call it something like $/ProductionEN or something like that.  I created the branch using SSDT before I did any of this, but you can create one using the TF.exe
• TF_Workspace is the workspace you created in the step above.
• c:\TFS_CLI\EN_Workspace - the physical folder on your EN server (NOT the TFS server).  I named them differently to make it more obvious what each part of the command is doing.
• now try and check something in.
• Get to the workspace
• cd c:\TFS_CLI\EN_Workspace
• new-item test.txt -type file
• &cmd /c "c:\tfs_cli\app_2013\tf add test.txt"
• &cmd /c "c:\tfs_cli\app_2013\tf checkin /author:memyselfandi"
• I use that weird syntax to catch errors and return as text into Powershell, so it can properly eat and pass on errors as necessary, as opposed to having PS choke on it.
• Pull and update something: this is where I had massive problems. (use script)
• Finally verify it worked (I used my "real" TFS client on my desktop).

Now, set up the SQL Script (SP), the Powershell script, and a job that calls the SP.

SP:





 Powershell: