Solved

sp_send_dbmail trigger with Access 2013 webapp SharePoint 2013

Posted on 2013-11-16
23
724 Views
Last Modified: 2013-12-11
I am trying to create a process where an email is sent on data changes to specified tables within the application. After creating the trigger, I consistently get an 'Access Denied' when altering table from the sharepoint app. DB Mail seems to be functioning from the SQL server itself, I am able to send and receive the test emails. I pulled the error log, and here's what I believe is the pertinent snippet:

Message : Established SQL security context. Database User: 'AccessWriter'.

Message : CommandType=Text; CommandText=CREATE TABLE #Context (ID INT PRIMARY KEY, isTopLevel BIT)  INSERT INTO 
          #Context VALUES (1,1)   UPDATE [Access].[tbl_workorders]  SET [Customer Stated Issue] = @p0 WHERE [ID] = @p1 
          AND [Work Order Number] = @p2 AND [Customer ID] = @p3 AND [Employee ID] = @p4 AND [Asset ID] = @p5 AND 
          [Description of Work] = @p6 AND [Date Performed] = @p7 AND [Quickbooks Invoice] IS NULL  AND [Asset ID 2] IS 
          NULL  AND [Asset ID 3] IS NULL  AND [Workorder Agreement] IS NULL  AND [Customer Stated Issue] = @p8 AND 
          [Date Submitted] = @p9 AND [Invoice Total] = @p10 AND [Payment Notes] IS NULL  AND [Payment Type] = @p11 AND 
          [ID] = @p12 SELECT @@rowCount    SELECT [tbl_workorders].* ,[tbl_customers:Customer ID->ID].[Customer Name] 
          AS [Customer ID:tbl_customers:ID:Customer Name], [tbl_e...

Message : System.Data.SqlClient.SqlException (0x80131904): The EXECUTE permission was denied on the object 
          'sp_send_dbmail', database 'msdb', schema 'dbo'.     at 
          System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 
          wrapCloseInAction)     at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject 
          stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)     at 
          System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader 
          dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)     
          at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()     at 
          System.Data.SqlClient.SqlDataReader.get_MetaData()     at System.Data.SqlClient.SqlCommand.Fini...

Open in new window


I have added the db mail role to the user accounts associated with the sharepoint instance, and also tinkered with the permissions on the database role 'AccessWriter' to no avail.

Help?
0
Comment
Question by:J-Rodder
  • 13
  • 10
23 Comments
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 39666491
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]

Open 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.
0
 

Author Comment

by:J-Rodder
ID: 39666620
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.
0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 39666678
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 :)
0
 

Author Comment

by:J-Rodder
ID: 39666741
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
0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 39667221
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

Open in new window

0
 

Author Comment

by:J-Rodder
ID: 39667249
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
0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 39667297
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.
0
 

Author Comment

by:J-Rodder
ID: 39667326
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.
0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 39667558
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.
0
 

Author Comment

by:J-Rodder
ID: 39669517
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'
0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 39669590
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' 

Open 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.
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:J-Rodder
ID: 39669636
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'

Open 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.
0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 39669673
I changed the body text as well in my example above to send the results of that function as the email body.
0
 

Author Comment

by:J-Rodder
ID: 39669702
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.
0
 

Author Comment

by:J-Rodder
ID: 39669710
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

Open in new window

0
 

Author Comment

by:J-Rodder
ID: 39669720
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
0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 39670075
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]

Open in new window

0
 

Author Comment

by:J-Rodder
ID: 39670389
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
>>

Open in new window

0
 

Author Comment

by:J-Rodder
ID: 39677987
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?
0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 39678794
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

Open 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.
0
 

Author Comment

by:J-Rodder
ID: 39678921
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.
0
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 500 total points
ID: 39679449
I don't have any contained databases handy to check this, but did you check the values in the master..sysusers and sys.syslogins tables? If it's contained, I suspect you'd see it in the second, but it might be worth checking here:
USE YourDatabaseName
SELECT sid, name, loginname
  FROM sys.syslogins

Open in new window


That will show you all the database logins, which may be different from the server users. When I get home, I will create a contained database on my SQL 2012 test instance and see if I can get the SID to display.
0
 

Author Closing Comment

by:J-Rodder
ID: 39712365
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.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now