SQL Server 2008, connecting via SQL Server Authentication. I have a stored procedure in DatabaseA which calls spsenddbmail in msdb to send an email with a file attachment. The file is on the db server, not on a remote fileshare.
The SQL Server account being used is not sysadmin, but does belong to the DatabaseMailUserRole in msdb. Sending an email without an attachment is fine, but when an attachment is present I get an error: The client connection security context could not be impersonated. Attaching files require an integrated client login There are a few articles/posts about this out there, but some seem to say conflicting things. I've been looking into impersonation, and one thing that does work is in the stored procedure in DatabaseA, to do the following: EXECUTE AS LOGIN = 'sa' - or any account with sysadmin privileges EXECUTE msdb.spsenddbmail. REVERT I wasn't expecting this to work as I thought to send attachments, you needed to use Windows Authentication. However it does work, but it means the lower privileged SQL Server account needs to be granted permission to IMPERSONATE the sa (or other sysadmin account). Doing my due diligence as a dev before unleashing a DBA's nightmare into the wild.
My question is: what is a good/safe way of allowing a user authenticated via SQL Server (non sysadmin) to send email attachments from the local db server disk without opening up a security hole? Update: Re: Credentials I've created a new Windows Login, created credentials for that account via SSMS, mapped those credentials to my limited privileges SQL Server account. I get the error: Msg 22051, Level 16, State 1, Line 0 The client connection security context could not be impersonated. Attaching files require an integrated client login I must be missing something! You need to use: A credential is a record that contains the authentication information (credentials) required to connect to a resource outside SQL Server. This information is used internally by SQL Server.
Most credentials contain a Windows user name and password. The information stored in a credential enables a user who has connected to SQL Server by way of SQL Server Authentication to access resources outside the server instance. When the external resource is Windows, the user is authenticated as the Windows user specified in the credential. This way you can associate your SQL login that sends the mail with an NT credential that has file access to the attachment. I wasn't able to get this to work with Credentials, but I found another way to get it to work, without having to grant sysadmin impersonation rights to the account that the is used normally. Set the database as TRUSTWORTHY: ALTER DATABASE SET TRUSTWORTHY ON;.
Set the owner of the database a sysadmin user - in this case, I used sa EXEC your db.dbo.spchangedbowner @loginame = N'sa', @map = false. Write a stored procedure to do the sending of mail, declared as so: CREATE PROCEDURE dbo.SendMailWithAttachments WITH EXECUTE AS OWNER AS BEGIN EXEC msdb.dbo.spsenddbmail., @fileattachments = ' some remote file'; END You could also just create a schema, like 'mailXfer', that had a sysadmin owner, and then make the stored procedure in that (i.e. CREATE PROCEDURE mailXfer.SendMailWithAttachements). I did not try the credentials way, but I did get it to work with Nikki's suggestion. However, I notice this presents a security risk since once the database owner is a member of the sysadmin role, a user of the database with rights to create procedures can basically create anything with EXECUTE AS OWNER and write a query to do anything a member of 'sysadmin' could do.risky. I'll try to get it to work with credentials instead.
Bonjour, g acheter un ordinateur acer et je ne trouve pas comment ont fait pour le formater j'attend votre aide merci. Comment formater un ordinateur acer vista. Jul 19, 2008 Comment formater mon pc Acer Aspire. Sans le cd de vista tu pourra formater mais tu te retrouveras avec un ordinateur hors service qui te dira au. Oct 19, 2012 Bonjour, j'ai un ordinateur windows vista acer M1100, qui me dit qu'il na plus de place, donc pour moi la meilleur solution c'est de formater.
Edit: Just found this article that explains this issue in detail: – Oct 22 '15 at 2:22.
I've been tasked with sending a small monthly report to for one of my customers. The report has previously been run manually on the instance, the output copied to a spreadsheet and send to the customer as an attachment. I'm looking for a more permanent solution, so I intend on using spsenddbmail stored procedure to run the query and send it as an attachment. Everything works but the formatting of the message. Initially I tried to attach the output as a CSV file with a @queryresultseperator = ',' but the results were everywhere! When I run the report normally, the output looks fine in SQL.
But sending it as a CSV or just in the message body doesn't. I think it might work better if I export the output as HTML and send that as an attachment/or as XML but I don't know how to do this.
Does anyone have any suggestions? Thanks in advance! If you still need to export the file and send it as an attachment, this can also be fully automated in SQL Server. Exporting as a CSV can be achieved via. There's more details in, but the main idea is: bcp 'SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable' queryout 'D: MyTable.csv' -c -t, -S SERVERNAME -T You would then attach the file to the email in spsenddbmail.
USE msdb; GO EXEC spsenddbmail @recipients='[email protected]', @subject='Client Report', @body='Please find your latest report attached', @fileattachments='D: MyTable.csv'; You could, if you want,. Yes you can send the report via HTML format, for example as listed in MS: Scenario: This example sends an e-mail message to Dan Wilson using the e-mail address [email protected]. The message has the subject Work Order List, and contains an HTML document that shows the work orders with a DueDate less than two days after April 30, 2004. Database Mail sends the message in HTML format.
Attached File
My web and sql use different servers. In my web app the user can upload a file(s) to the web server to include as an attachment(s) when subsequently sending an email via sql dbmail. The syntax for including an attachment is: EXEC spsenddbmail @profilename='MailProfile1', @recipients='[email protected]', @subject='Test Attachment with Two Files', @body='Two files have been included in this email.' , @fileattachments='D: temp blog.jpg;D: temp long folder sample.txt' The problem is that this fileattachment directory is on my web server so my question is, what is the syntax in the @fileattachments parameter for pointing to an absolute path on a different server? Can I use the IP address such as '192.555.444.45 d temp blog.jpg'? I've done a lot of searching for this answer but can't find the solution. Any suggestions greatly appreciated.
Thank you for your response. Now my probelm appears to be security related. I have set up the share on the webserver but I can't figure out how to grant read access to that share for the request coming from the sql server. The web server and sql server (both running Windows 2008 Server) are on the same network and in the same workgroup but from the research I've done it appears that I have to add the sql account to the list of those having access to the share. In my case when I list out services in server manager on my web server machine, both SQL Server and SQL Server Agent show 'Local Sytem' in the 'Log On As' column.
So my question is what account do I add and how do I add it to the share on my web server which I'm trying to access from my sql server? I've been trying for a few days now to solve this but no luck!
Yahoo Mail Attachment File Size
So thanks for any help.