/*----------------------------------------------------------------------
  This procedure is for setting up the loopback for SqlEventLog on Azure
  SQL DB. It consists of two parts. The first part sets up user, credential
  and external data source for the loopaack, and grants the loopback user
  permission on log_insert_sp.
  -------------------------------------------------------------------------*/
DECLARE @sql nvarchar(MAX),
        @pwd char(40) = convert(char(36), newid()) + 'Aa1+',
        @sq  char(1) = ''''

IF EXISTS (SELECT * FROM sys.external_data_sources WHERE name = 'Loopback')
   DROP EXTERNAL DATA SOURCE Loopback

IF EXISTS (SELECT * FROM sys.database_credentials WHERE name = 'LoopbackCredential')
   DROP DATABASE SCOPED CREDENTIAL LoopbackCredential

IF user_id('LoopbackUser') IS NOT NULL
   DROP USER LoopbackUser

SELECT @sql = 'CREATE USER LoopbackUser WITH PASSWORD = ' + quotename(@pwd, @sq)
PRINT @sql
EXEC (@sql)
GRANT EXECUTE ON slog.log_insert_sp TO LoopbackUser

SELECT @sql = 'CREATE DATABASE SCOPED CREDENTIAL LoopbackCredential
      WITH IDENTITY = ''LoopbackUser'', SECRET = ' + quotename(@pwd, @sq)
PRINT @sql
EXEC (@sql)

SELECT @sql = 'CREATE EXTERNAL DATA SOURCE Loopback WITH (
   TYPE = RDBMS,
   LOCATION = ' + quotename(@@servername + '.database.windows.net', @sq) + ',
   DATABASE_NAME = ' + quotename(db_name(), @sq) + ',
   CREDENTIAL = LoopbackCredential)'
PRINT @sql
EXEC (@sql)
go
/*-----------------------------------------------------------------------
  This is a T-SQL version of slog.lookback_sp for Azure SQL Database written
  by Daniel Verzellesi.
  -----------------------------------------------------------------------*/
CREATE PROCEDURE slog.loopback_sp
                 @server    sysname,
                 @dbname    sysname,
                 @logid     bigint OUTPUT,
                 @msgid     nvarchar(36),
                 @error     int,
                 @severity  tinyint,
                 @logprocid int,
                 @msgtext   nvarchar(2048),
                 @errproc   sysname,
                 @linenum   int,
                 @username  sysname,
                 @appname   nvarchar(128),
                 @hostname  nvarchar(128),
                 @p1        nvarchar(400),
                 @p2        nvarchar(400),
                 @p3        nvarchar(400),
                 @p4        nvarchar(400),
                 @p5        nvarchar(400),
                 @p6        nvarchar(400)
AS

    DECLARE @retval TABLE (logid int, shard sysname)

    INSERT @retval (logid, shard)
    EXEC sp_execute_remote
    @data_source_name  = N'Loopback',
    @stmt = N'slog.log_insert_sp @logid, @msgid, @errno, @severity, @logprocid, @msgtext, @errproc, @linenum, @username, @appname, @hostname, @p1, @p2, @p3, @p4, @p5, @p6',
    @params = N'@logid     bigint,
                @msgid     varchar(255),
                @errno     int,
                @severity  tinyint,
                @logprocid int,
                @msgtext   nvarchar(2048),
                @errproc   sysname,
                @linenum   int,
                @username  sysname,
                @appname   nvarchar(128),
                @hostname  nvarchar(128),
                @p1        nvarchar(400),
                @p2        nvarchar(400),
                @p3        nvarchar(400),
                @p4        nvarchar(400),
                @p5        nvarchar(400),
                @p6        nvarchar(400)',
        @logid = @logid,
        @msgid = @msgid,
        @errno = @error,
        @severity = @severity,
        @logprocid = @logprocid,
        @msgtext = @msgtext,
        @errproc = @errproc,
        @linenum = @linenum,
        @username = @username,
        @appname = @appname,
        @hostname = @hostname,
        @p1 = @p1,
        @p2 = @p2,
        @p3 = @p3,
        @p4 = @p4,
        @p5 = @p5,
        @p6 = @p6;

    SELECT TOP 1 @logid = logid FROM @retval