sp_send_dbmail trigger with Access 2013 webapp SharePoint 2013

The error is pretty specific - have you granted execute permission on that system stored procedure?

USE msdb
GRANT EXECUTE ON [sp_send_dbmail] TO [DOMAIN\SharePoint_Proxy_User]

Select allOpen in new window

You may also need grant this user access to the MSDB database (though with no explicit permissions) as that's where this stored procedure lives.

The trigger will execute as whatever use runs the DML statement - in this case, most likely the SharePoint proxy user running the services. Grant rights for that user to execute the stored procedure in this error message and you should be able to send mail.

I've done this before, for SharePoint specifically - it works without issue for us once we granted to required permissions.

Well you certainly sound like the person I need to help me. I configured sharepoint using just a couple of accounts, domain\spadmin and domain\spservices. After making many applications, database connections, report pages with ssrs, I am not even sure which user I need to be looking at.

How do I determine exactly which account would be the one doing the executions on SQL?

"have you granted execute permission on that system stored procedure" Do I insert that into my trigger query somewhere, or do I just run that against the correct user in the database?

Sorry for the questions, I tend to know just enough to be dangerous, still learning.

No problem - knowing enough to be dangerous is tempered by your recognition that it's where you currently are :)

You can use SQL Profiler to watch and see which user is taking the action - watch for the event "User Error Message" under "Errors and Warnings" when you attempt to send a message, and you'll see the permission denied on a particular user - grant access to that user.

As a more brute force method, you could just try to grant permissions to one of them and see if it works - if it doesn't, just revoke it (same syntax, just using "REVOKE EXECUTE ON..." and try the other one :)

Okay, I didn't even know the profiler existed. I had been thinking it would be really nice to be able to "sniff" traffic as it were, that's pretty slick. Ok, well I think I found the relevant portion, but instead of a domain\user account for the action, it looks like maybe some local user?
Untitled.png

It's for sure a super-handy tool :) It does add a bit of overhead (I've never had it impact production noticeably, but I've heard from others that it does)

I've never seen a Windows SID in that column, actually, so I'm not sure exactly what account that corresponds to - I would have expected it to be one of the other two accounts you mentioned, which you can see above and below those lines. That said, I'm actually not familiar with Access Services - what is that? Some googling suggests it a way to edit Access Databases that are hosted within SharePoint databases, but I wonder if this is why it's running as a different user.

It is possible the user making the changes is coming from a different domain, or has their login set up in some other kind of non-standard way? The only reason I've seen a SID not resolve to a username is if the server can't contact a domain controller, or if it's a login for a domain that the server doesn't have access or rights to use. Wondering if the question is a bit more complicated here.

Alternatively, you could use the following statement to grant access to PUBLIC, which would ensure that at least what you're attempting to do works functionally - if it allows you to send mail, I'd revoke the rights, but then you know you're working around a permissions issue and that's it, rather than something standing in the way beyond permissions:

-- Grant the rights
GRANT EXECUTE ON [sp_send_dbmail] to PUBLIC

-- Test your process to make sure mail gets sent properly

-- Revoke access (otherwise anybody who has a login can spam from your server)
REVOKE EXECUTE ONM [sp_send_dbmail] FROM PUBLIC

Select allOpen in new window

I will give that a go now. Access Services is an account that actually shows up if I right click on the database itself and go to properties. Remember, this is a database that is created from an Access 2013 application hosted on sharepoint. Hence my confusion as to exactly what user I need to be modifying. Attached is a pic of the DB properties:



*edit**

Ok yes, it's a permissions thing. Setting it to public, I got an email trigger executed from within the sharepoint app without any complaint.
database-properties.png

Aha - I suppose I could have re-read the title to your question to see what Access Services was :) I'm glad to hear the PUBLIC permissions resolve it, so you know it's only figuring out the actual user, rather than something more complicated.

Any chance you can look at the users listed for that database on your SQL Server and make an educated guess? Alternatively, I found a walk-through for installing access services:

http://blogs.msdn.com/b/kaevans/archive/2013/07/14/access-services-2013-setup-for-an-on-premises-installation.aspx

It looks like the second screenshot in the "Create an Access Services service application" section shows the user account configuration - pulling up that screen in SharePoint may tell you what user accounts Access Services is configured to run as, and that's the user you'd grant EXECUTE rights to.

It's a bit silly but I can't seem to get sharepoint to divulge to me what that account is *now*. I am 99% sure that I used either the spadmin or spservices account when creating that pool initially though, and I tried granting permissions to both of those users using the commands you gave earlier. I'll leave this open for a bit, since at the very least you helped me learn some new tools and drilled this down to certainly being a permissions issue. I know this is the can of worms that is opened up when using new products and not being adept.

That unfortunate that SharePoint is keeping secrets :) Can you look at the users that have access to that database and see if one sticks out?

To figure it out for sure, you could grant email rights to public, and then modify your trigger to include both the SUSER_SNAME() and CURRENT_USER values. Once that's done, you can just see who SQL Server says it was that ran it, and then revoke rights from public and reassign them to that user.

I am confused as to where/how to modify the trigger without breaking the syntax. Here's what I am currently using for the test:

USE [db_0c3a6033_2f41_478d_8cda_36bb94f95dd9]
GO
/****** Object:  Trigger [Access].[WO_EMAIL]    Script Date: 11/18/2013 1:14:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

      EXEC msdb.dbo.sp_send_dbmail
                                  @profile_name = 'INTERSPACE',
                                                @recipients = 'jclark@contoso.com',
                                                @body = 'body test',
                                                @subject = 'subject goes here'

Just two small changes in your case:

USE [db_0c3a6033_2f41_478d_8cda_36bb94f95dd9]
GO
/****** Object:  Trigger [Access].[WO_EMAIL]    Script Date: 11/18/2013 1:14:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

      DECLARE @body VARCHAR(4000) -- new line
      SELECT @body = SUSER_SNAME() -- new line too

      EXEC msdb.dbo.sp_send_dbmail
                                  @profile_name = 'INTERSPACE',
                                                @recipients = 'jclark@contoso.com',
                                                @body = @body,  -- this line changed
                                                @subject = 'subject goes here' 

Select allOpen in new window

You'll only need to run it once to get the email telling you who the current user (in the context of the trigger) is, and then you'll have your answer. Unless it just gives you the SID again, and then I suppose you're no further along.

USE [db_0c3a6033_2f41_478d_8cda_36bb94f95dd9]
GO
/****** Object:  Trigger [Access].[WO_EMAIL]    Script Date: 11/18/2013 1:14:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
			DECLARE @body VARCHAR (4000)
			SELECT @body = SUSER_SNAME()

	EXEC msdb.dbo.sp_send_dbmail
	                            @profile_name = 'INTERSPACE',
								@recipients = 'jclark@interspacetech.net',
								@body = 'body test',
								@subject = 'subject goes here'

Select allOpen in new window

sends the email, but no additional information contained in the body? I tried executing query from the application as well as from SQL Management and same result.

I changed the body text as well in my example above to send the results of that function as the email body.

Derp. That makes sense considering we put an alias for body in there...

I am losing my mind. I deleted the old trigger. Last time, when I ran the query the trigger was created and saved automatically. Now when I do it, nothing saves. (I do get an email with my correct accoutn executing though)

If I go to create new trigger, I get a lot of stuff in a template, not sure where to stuff it at this point? I'm sorry for the handholding, but it feels like this is getting close to fruition.

here's something I didn't look at before though, there's already a trigger created that was made when I did an "After Update" macro from within the Access 2013 application. It looks like the 'AccessWriter' is the user that is being declared, and is the likely user that would be trying to execute the email.

USE [db_d4200252_41ed_47d5_8f29_ba378098b58b]
GO
/****** Object:  Trigger [Access].[_dm_tbl_workorders_AFTER_INSERT]    Script Date: 11/22/2013 1:09:21 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [Access].[_dm_tbl_workorders_AFTER_INSERT]
ON [Access].[tbl_workorders]
WITH EXECUTE AS 'AccessWriter'
AFTER INSERT
AS
BEGIN
	SET NOCOUNT ON
	SET XACT_ABORT OFF
	
	DECLARE @_dm_actionRunning NVARCHAR(128)
	DECLARE @_dm_macroRunning NVARCHAR(128)
	
	DECLARE @_dm_traceOn BIT = 0
	IF AccessSystem.ApplicationPropertiesSelect('DataMacroTracing') IS NOT NULL
		SET @_dm_traceOn = 1
	
	DECLARE @_dm_lastLoggingID INT
	IF @_dm_traceOn > 0
		SELECT
			@_dm_lastLoggingID = COALESCE(Max(ID), 0)
		FROM
			[Access].[Trace]
	
	DECLARE @_dm_savePoint as VARCHAR(36)
	SET @_dm_savePoint = CONVERT(VARCHAR(36), NEWID())
	
	DECLARE @_dm_initialTranCount INT
	SET @_dm_initialTranCount = @@TRANCOUNT
	IF @_dm_initialTranCount > 0
		SAVE TRAN @_dm_savePoint
	ELSE
		BEGIN TRAN
	IF OBJECT_ID('tempdb..#Context') IS NOT NULL
	BEGIN
		DECLARE @_dm_isTopLevel BIT
		SELECT @_dm_isTopLevel = isTopLevel
		FROM #Context
		WHERE ID = 1
	
		IF @_dm_isTopLevel = 1
		BEGIN
			UPDATE #Context
			SET isTopLevel = 0
			WHERE ID = 1
			SET @_dm_initialTranCount = 0
		END
	END
	SET @_dm_macroRunning = N'tbl_workorders:On Insert'
	BEGIN TRY
		DECLARE @WO_ID Decimal(28,6)
		DECLARE @_dm_cancelRecordChange Bit
		
		DECLARE @_dm_temp1 Int
		DECLARE @_dm_temp2 NVarChar(220)
		DECLARE _dm_cur1 CURSOR LOCAL STATIC FOR
			SELECT
				[ID],
				[Work Order Number]
			FROM
				[INSERTED]
			
		OPEN _dm_cur1
		
		FETCH NEXT FROM
			_dm_cur1
		INTO
			@_dm_temp1,
			@_dm_temp2
		WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @_dm_actionRunning = N'SetLocalVar'
			SET @WO_ID = ROUND(@_dm_temp1 + 1500.0, 0.0)
			IF @_dm_traceOn > 0
				EXEC [AccessSystem].[LogActionTrace] @_dm_macroRunning, N'SetLocalVar', N'WO_ID', @WO_ID, N'', N''
			
			SET @_dm_cancelRecordChange = 0
			IF @_dm_traceOn > 0
				EXEC [AccessSystem].[LogActionTrace] @_dm_macroRunning, N'EditRecord', N'', N'', N'', N''
			SET @_dm_actionRunning = N'SetField'
			SET @_dm_temp2 = STUFF(CAST(@WO_ID AS int), CAST(1.0 AS int), CAST(0.0 AS int), N'WO')
			IF @_dm_traceOn > 0
				EXEC [AccessSystem].[LogActionTrace] @_dm_macroRunning, N'SetField', N'[tbl_workorders].[Work Order Number]', @_dm_temp2, N'', N''
			SET @_dm_actionRunning = N'EditRecord'
			IF @_dm_cancelRecordChange = 0
				UPDATE
					[tbl_workorders]
				SET
					[Work Order Number] = @_dm_temp2
				WHERE
					[ID] = @_dm_temp1
			FETCH NEXT FROM
				_dm_cur1
			INTO
				@_dm_temp1,
				@_dm_temp2
		END
		CLOSE _dm_cur1
		DEALLOCATE _dm_cur1
	END TRY
	BEGIN CATCH
		DECLARE @_dm_sqlException NVARCHAR(128) = N'SQL exception'
		DECLARE @_dm_errorNumber INT = ERROR_NUMBER()
		DECLARE @_dm_errorMessage NVARCHAR(4000) = ERROR_MESSAGE()
	
		IF @_dm_TraceOn > 0
		BEGIN
			/* Before rollback happens, cache the tracing info so that we can refill later */
			DECLARE @tracingCache TABLE(
				[ID] INT IDENTITY PRIMARY KEY,
				[MacroName] nvarchar(128),
				[ActionName] nvarchar(128),
				[Operand] nvarchar(4000),
				[Output] nvarchar(max),
				[TargetRow] nvarchar(4000),
				[Timestamp] datetime2(3),
				[RuntimeErrorMessage] nvarchar(4000))
	
			INSERT INTO @tracingCache
				SELECT
					[MacroName],
					[ActionName],
					[Operand],
					[Output],
					[TargetRow],
					[Timestamp],
					[RuntimeErrorMessage]
				FROM
					[Access].[Trace]
				WHERE 
					[ID] > @_dm_lastLoggingID 
		END;
	
		IF @_dm_initialTranCount > 0
		BEGIN
			IF XACT_STATE() <> -1
				ROLLBACK TRAN @_dm_savepoint
		END
		ELSE
			ROLLBACK TRAN
	
		IF @_dm_TraceOn > 0 AND XACT_STATE() <> -1
		BEGIN
			/* After rollback happens, refill the Tracing table with the cached info */
			INSERT INTO [Access].[Trace](
				[MacroName],
				[ActionName],
				[Operand],
				[Output],
				[TargetRow],
				[Timestamp],
				[RuntimeErrorMessage])
				SELECT
					[MacroName],
					[ActionName],
					[Operand],
					[Output],
					[TargetRow],
					[Timestamp],
					[RuntimeErrorMessage]
				FROM
					@tracingCache
	
			/* By default, RAISERROR raises an error message with an error number of 50000.
			If the caught error's ID is 50000, then it is a user raised error
			Else the caught error is a SQL exception (the Error ID won't be 50000 for SQL exception) */
			IF @_dm_errorNumber <> 50000
				/* Log the current running macro/action with the SQL exception error */
				EXEC [AccessSystem].[LogActionTrace] @_dm_macroRunning, @_dm_actionRunning, '', '', '', @_dm_sqlException;
		END;
	
		THROW;
	
	END CATCH
	
END

Select allOpen in new window

Huzzah. I did manage to figure out the Ctrl-Shift-M in the template to fill in the values and by looking at the existing trigger I got one that worked! The user is the same one we saw in the trace, however.

S-1-9-3-3441133423-1089707912-2279264388-2724292752

That's unfortunate - can you try the functions from the end of this article to see if any of them give you a better name? They all provide slightly different contexts, and perhaps one of them will give you the username you're looking for, though SUSER_SNAME has always worked great for me.

http://technet.microsoft.com/en-us/library/ms191126(v=sql.105).aspx

Also (and I'm totally going out on a limb here) - any chance you can just grant rights to that sid?

USE msdb
GRANT EXECUTE ON [sp_send_dbmail] TO [S-1-9-3-3441133423-1089707912-2279264388-2724292752]

Select allOpen in new window

Didn't allow me to set it on the SID directly. I'll try some from that list but you're right it looks like the only valid one really is the one we tried. Maybe there's a way to convert a SID?

 http://technet.microsoft.com/en-us/library/ff730940.aspx
 
I'll have to continue this over the weekend, thank you so much for all your help thus far!


**EDIT**

Tried it, seems to return blank when I ran it in PS.

>> $objSID = New-Object System.Security.Principal.SecurityIdentifier `
>>     ("S-1-9-3-3441133423-1089707912-2279264388-2724292752")
>> $objUser = $objSID.Translate( [System.Security.Principal.NTAccount])
>> $objUser.Value
>>

Select allOpen in new window

Looked at it again today. I found all my SIDs from the system looking at the registry keys located here.

None of those SIDs matched. For good measure I searched all the registry for that SID, came up empty. We are a small shop, I am seriously contemplating just allowing PUBLIC for the email function at this point. I mean what, someone would have to compromise the SQL server, and use it to send spam via my exchange server?

The risk of granting it is that anybody who has a login to the server (legitimate or as a result of compromise) could send mail from your server to any address reachable by Exchange - potentially, they could use your database server to send SPAM or other messages. Since you're a small shop, that risk may be minimal - that's up to you.

However, you can use the following view to keep tabs on how much mail is being sent, potentially setting up a monitoring alert that will either send you a summary each day or tell you if the number of messages is over a certain threshold:

SELECT *
  FROM msdb..sysmail_allitems

Select allOpen in new window

If you put some kind of monitoring in place, the risk of enabling this feature is probably reasonable, since you'd be alerted in short order to any abuse.

Sorry I couldn't help you identify the owner of the SID in question - that's an odd case, and if you ever discover who it belongs to and are so inclined, I'd love to know who it belongs to and how you found out.

I will certainly do that. I don't feel quite so bad now, it's frustrating fighting something without all the prequisite tools, you certainly gave me a handful of them in the process.

I think the answer may lie in the fact that the user doing the writing only lives inside of the database itself, and not in the overall SQL server. I believe the term used is 'Contained Database'

http://technet.microsoft.com/en-us/library/ff929071.aspx

I believe that is what Access 2013 uses when creating the database on SQL 2012. Obviously there's just a piece we are missing, since PUBLIC works.

Closing this and awarding points. No real resolution, I had a friend of mine that is a DBA look at it. We got as far as being able to write a stored procedure. I created a new field in the tables in question, called "process_flag". I used Access to create "after update" and "after insert" triggers to insert a "1" into that field. Stored procedure looks for all rows containing the flag, creates an email with a query attachment, and is supposed to be able to then change that field to a 0. That's where it sticks, assuming again because it being a contained database it doesn't like any other accounts writing to it. I think we tried inserting an EXECUTE AS 'AccessWriter' but I don't believe that worked either.