I came across an environment recently that had multiple domains with particular trust levels between the them. I then, found out, that when deploying projects via Octopus, there was a project step that was creating a windows login from one domain to another because Octopus had admin permissions across the board (Oh yes, I already know what you are thinking). My initial thought was to contact network team and get an explanation on why the trust levels, however what would that really solve for the immediate need? I then wanted to investigate all the permissions Octopus should truly have, however I did not have time to investigate all of the appropriate permission that would be required. I thought, what a perfect chance to start my own personal challenge of #100daysofcode from twitter.
How could I protect the SQL environment in each domain to ensure that never happens again? How can I ensure that when deploying via Octopus, the individual doing the deployment can be notified of the failure in a clear and concise way? I then stepped into a whelm that I have not touched in quite sometime, triggers, yes I said it, triggers. More specifically DDL triggers. I have had multiple reasons why I have not had to deal with triggers too much, but finally I had enough of this and had to put my foot down as results wanted, were not being driven.
What can DDL triggers do for you? DDL triggers operate on CREATE, ALTER, DROP, and other DDL statements and stored procedures that perform DDL-like operations. They are used to perform administrative tasks and enforce business rules that affect databases. They apply to all commands of a single type across a database, or across a server.
I currently utilize extended events for a lot of auditing and these items are always recorded and emailed the following morning for audit review. I finally got tired of waiting and wanted an email alert immediately, of when this particular event occurred and want to ensure that Octopus also recorded the alert to notify the individual doing the deployment.
Here is the script I came up with:
CREATE TRIGGER ddl_trig_triedcrossdomainlogin
ON ALL SERVER
-- Declare variables
DECLARE @mailSubject Nvarchar(100);
DECLARE @mailBody Nvarchar(MAX);
DECLARE @data XML;
DECLARE @text Nvarchar(max);
DECLARE @user Nvarchar(max);
DECLARE @newuser NVARCHAR(MAX);
SET @data = EVENTDATA();
SET @newuser = @data.value('(/EVENT_INSTANCE/ObjectName)', 'nvarchar(max)');
SET @user = @data.value('(/EVENT_INSTANCE/LoginName)', 'nvarchar(max)');
SET @text = @data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)', 'nvarchar(max)')
-- Set the email data
SET @mailSubject = 'New windows login was tried to be created on: ' + @@SERVERNAME;
SET @mailBody = 'A new windows login was detected to be created on SQL Server from another domain: <b>' + @@SERVERNAME + '
'User name: <b>' + ISNULL(@newuser, 'Null User Name') + '
'T-SQL was <b>' + ISNULL(@text, 'Null SQL') + '
'Added by user: <b>' + ISNULL(@user, 'Null') + '
</b>' + +
'On date: <b>' + CONVERT(nvarchar, getdate(), 13) + '
'Please verify why this login was attempted to be created!' + '
--Send the mail
@recipients=N'', --put your recipients list here
@body = @mailBody,
@profile_name = '', --put your sql profile name here
@body_format = HTML;
IF @user like ('%Octopus%') --This would be the login that the application is running under
@newuser like ('%DEV%') --This would be the account that the application is trying to create a login for
PRINT 'You are not allowed to create a dev login in this environment.'
ENABLE TRIGGER ddl_trig_triedcrossdomainlogin
This is what Octopus looks like: