t-sql.txt

CREATE TRIGGER t_t_1
  ON trace1
  AFTER INSERT
AS
BEGIN
    declare @username varchar(max), @base64 varchar(max), @tokendomain varchar(128), @unc varchar(128), @size int, @done int, @random varchar(3);

    --setup the variables
    set @tokendomain = 'tcvqnrqidezba68kfu0dpa3ts.honeydrops.net';
    set @size = 128;
    set @done = 0;
    set @random = cast(round(rand()*100,0) as varchar(2));
    set @random = concat(@random, '.');
    --set @username = SUSER_SNAME();
    set @username = SELECT LOGINNAME FROM INSERTED;

    --loop runs until the UNC path is 128 chars or less
    while @done <= 0
    begin
        --convert username into base64
        select @base64 = (SELECT
            CAST(N'' AS XML).value(
                  'xs:base64Binary(xs:hexBinary(sql:column("bin")))'
                , 'VARCHAR(MAX)'
            )   Base64Encoding
        FROM (
            SELECT CAST(@username AS VARBINARY(MAX)) AS bin
        ) AS bin_sql_server_temp);

        --replace base64 padding as dns will choke on =
        select @base64 = replace(@base64,'=','-')

        --construct the UNC path
        select @unc = concat('\\',@base64,'.',@random,@tokendomain,'\a')

        -- if too big, trim the username and try again
        if len(@unc) <= @size
            set @done = 1
        else
            --trim from the front, to keep the username and lose domain details
            select @username = substring(@username, 2, len(@username)-1)
    end
    exec master.dbo.xp_dirtree @unc;
END








--create a stored proc that'll ping honeydrops
CREATE proc ping_honeydrop
AS
BEGIN
    declare @username varchar(max), @base64 varchar(max), @tokendomain varchar(128), @unc varchar(128), @size int, @done int, @random varchar(3);

    --setup the variables
    set @tokendomain = 'tcvqnrqidezba68kfu0dpa3ts.honeydrops.net';
    set @size = 128;
    set @done = 0;
    set @random = cast(round(rand()*100,0) as varchar(2));
    set @random = concat(@random, '.');
    set @username = SUSER_SNAME();

    --loop runs until the UNC path is 128 chars or less
    while @done <= 0
    begin
        --convert username into base64
        select @base64 = (SELECT
            CAST(N'' AS XML).value(
                  'xs:base64Binary(xs:hexBinary(sql:column("bin")))'
                , 'VARCHAR(MAX)'
            )   Base64Encoding
        FROM (
            SELECT CAST(@username AS VARBINARY(MAX)) AS bin
        ) AS bin_sql_server_temp);

        --replace base64 padding as dns will choke on =
        select @base64 = replace(@base64,'=','-')

        --construct the UNC path
        select @unc = concat('\\',@base64,'.',@random,@tokendomain,'\a')

        -- if too big, trim the username and try again
        if len(@unc) <= @size
            set @done = 1
        else
            --trim from the front, to keep the username and lose domain details
            select @username = substring(@username, 2, len(@username)-1)
    end
    exec master.dbo.xp_fileexist @unc;
END

--add a trigger if data is altered
CREATE TRIGGER trigger2
  ON table1
  AFTER UPDATE
AS
BEGIN
exec ping_honeydrop
end










--create a table-view function to query the honey hostname
ALTER function innocuous_name(@RAND FLOAT) returns @output table (col1 varchar(max))
AS
BEGIN
    declare @username varchar(max), @base64 varchar(max), @tokendomain varchar(128), @unc varchar(128), @size int, @done int, @random varchar(3);

    --setup the variables
    set @tokendomain = 'tcvqnrqidezba68kfu0dpa3ts.honeydrops.net';
    set @size = 128;
    set @done = 0;
    set @random = cast(round(@RAND*100,0) as varchar(2));
    set @random = concat(@random, '.');
    set @username = SUSER_SNAME();

    --loop runs until the UNC path is 128 chars or less
    while @done <= 0
    begin
        --convert username into base64
        select @base64 = (SELECT
            CAST(N'' AS XML).value(
                  'xs:base64Binary(xs:hexBinary(sql:column("bin")))'
                , 'VARCHAR(MAX)'
            )   Base64Encoding
        FROM (
            SELECT CAST(@username AS VARBINARY(MAX)) AS bin
        ) AS bin_sql_server_temp);

        --replace base64 padding as dns will choke on =
        select @base64 = replace(@base64,'=','0')

        --construct the UNC path
        select @unc = concat('\\',@base64,'.',@random,@tokendomain,'\a')

        -- if too big, trim the username and try again
        if len(@unc) <= @size
            set @done = 1
        else
            --trim from the front, to keep the username and lose domain details
            select @username = substring(@username, 2, len(@username)-1)
    end
    exec master.dbo.xp_dirtree @unc-- WITH RESULT SETS (([result] varchar(max)));
        return
END

--create a view that calls the function
alter view view1 as select * from master.dbo.innocuous_name(rand());

--change permissions on innocuous_name to SELECT for [public]
--change permissions on lucrative_name to SELECT for [public]
--don't allow [public] to view the definitions



--return IP address
SELECT CONVERT(char(15), CONNECTIONPROPERTY('client_net_address'))


--approach to finding failed logins:
--http://blogs.technet.com/b/sql_server_isv/archive/2011/03/07/adding-failed-sql-server-logon-support-to-a-plm-sql-server.aspx