December 15, 2008 at 1:10 pm
Hello Everyone,
As you know you can send the results of a query to your email address by using Database Mail. But can you send and email to the SQL 2005 server with a query to execute and it would send the results back to you in an email?
This was possible in SQL 7.0 as you could email the server with "sp_who" and shortly after you would get an email with the results.
I'm thinking this would be great ability to add our SQL 2005 server.
Thanks in advance for your efforts and comments.
Rudy
Rudy
December 15, 2008 at 4:35 pm
Look out for @attach_query_result_as_file and @query parameters under sp_send_dbmail for the same.
HTH
Mj
December 16, 2008 at 1:27 am
You can't receive mails using database mail, but you can use SQL mail which is basically the same as in SQL 7 or 2000. Then just schedule a job to execute sp_processmail to pick up the mails and execute the query.
[font="Verdana"]Markus Bohse[/font]
December 16, 2008 at 7:03 am
Does anyone know how you would get SQL server to receive your email and execute the query? I'm looking around for examples and am having not luck.
But the way, thanks for the quick reply(s).
Rudy
Rudy
December 16, 2008 at 8:11 am
I believe you're talking about sp_processmail, but in general it's not really recommended that you do this because you could blindly execute malicious code that does nasty things to trash your database.
December 16, 2008 at 8:20 am
Yes I understand the possible issues with this configuration. If you have assigned the right roles/rights to the right users this should not be an issue.
My reason for setting this up is so that I (as the Sr DBA) can send and execute commands like sp_who on servers when I'm out of the office. With my blackberry, I could then look an issue quickly by getting information on that SQL server/instance.
Looks like this is may be a dead end as MS does not support the SQL Mail function on 64 bit SQL servers.
Thanks,
Rudy
Rudy
December 16, 2008 at 8:25 am
kendal.vandyke (12/16/2008)
I believe you're talking about sp_processmail, but in general it's not really recommended that you do this because you could blindly execute malicious code that does nasty things to trash your database.
I've done some work using sp_processmail in past and my way of preventing the risk of executing malicious code was to create my own custoized version of sp_processmail where I would check on things like the senders address, or certain code words in the subject of the mail.
[font="Verdana"]Markus Bohse[/font]
December 16, 2008 at 8:47 am
Rudy Panigas (12/16/2008)
Yes I understand the possible issues with this configuration. If you have assigned the right roles/rights to the right users this should not be an issue.My reason for setting this up is so that I (as the Sr DBA) can send and execute commands like sp_who on servers when I'm out of the office. With my blackberry, I could then look an issue quickly by getting information on that SQL server/instance.
Looks like this is may be a dead end as MS does not support the SQL Mail function on 64 bit SQL servers.
Thanks,
Rudy
I didn't realize you're using the 64-bit version. The problem with that is that the dll's for the extended stored procedures are not compatible with 64-bit and since SQLMail is on the deprecation list MS won't make any further development for it.
But to solve your problem of managing ypur server from out of office maybe you can setup the web admin tool in a way you can access it from your Blackberry.
[font="Verdana"]Markus Bohse[/font]
December 16, 2008 at 9:10 am
Thanks for the info. I'll download and try this tool.
Rudy
Rudy
December 16, 2008 at 9:14 am
I implemented something like this several times in SQL sever. You cannot use Database mail for this, you will have to use SQL Server Mail (which Is still available in SQL 2005).
Here is a general outline of what you will have to do:
1) Enable SQL mail on the Server
2) Install Outlook on the Server.
This is one of the big problems with SQL Server Mail (SSM) because Outlook really does not like to be installed on a server. You will want a level 3 support engineer to do this for you.
3) Create an Exchange mailbox for the SQL Server
4) Go into Outlook on the Server and configure a profile for it, using the new mailbox, just like you would for a new Email user. This works best if you can log into the windows account that SQL Server runs under. You should be able to send and receive mail in Outlook at this point, so be sure to test the mail account/profile/mailbox before moving on. (also make sure to write down the names of the profile, mailbox, etc.)
5) Go into SQL Server and configure SQL Mail. There is a button to do test sends that you should use to test it now.
6) Now open a query window and write your own test send (sorry, I do not have the commands handy). Once you have confirmed that that works then create an Agent Job to execute the same command and test it that way. (beleive it or not, all of this testing is necessary because it can fail for vairous configuration reasons at every one of these test points, and sending is much easier/more reliable than receiving).
7) Now send an eMail to the SQL mail account. Open a query window and use sp_ProcessMail to try and receive it, either printing it out or saving it into a table. Test that. Now create a job to do the same thing and test it.
OK, NOW you've got SQL Server mail working. Once you've gotten this far, then you can start on the custom steps to implement your remote query utility:
8) Write a SQL Job to receive new mail Validate It, and then extract the command, execute it and send the results back to the sender:
8A) Validation is extremly important (and extremely difficuly, IMHO), because it is the only real protection and security that you have. I would be very interested in how you are going to do this.
8B) If you want this tool to be read-only, then make sure that it executes under a Login that has read-only access and only to those things that you want it to be able to see.
8C) Make sure that this account does NOT have the ability to execute DOS commands.
8D) Even with A, B, and C, you are still going to wan tto protect yourself against injection, somehow.
8E) Finally, I recommend that the query's return data be sent as an encrypted attachment.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 16, 2008 at 10:11 am
RBarry, I think you missed the part that SQLMail doesn't work with the 64-bit version.
[font="Verdana"]Markus Bohse[/font]
December 16, 2008 at 10:18 am
MarkusB (12/16/2008)
RBarry, I think you missed the part that SQLMail doesn't work with the 64-bit version.
Right, I keep forgetting that.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 16, 2008 at 12:02 pm
I've tried the SQL Web Administrator tool. It's only good for SQL 2000 and does not work with SQL 2005 and SQL 2008.
So I'm still looking for a tool.
Thanks everyone for all your time and efforts on this.
Rudy
Rudy
December 17, 2008 at 1:12 am
Rudy Panigas (12/16/2008)
I've tried the SQL Web Administrator tool. It's only good for SQL 2000 and does not work with SQL 2005 and SQL 2008.So I'm still looking for a tool.
Thanks everyone for all your time and efforts on this.
Rudy
There's an updated version on Codeplex which works with SQL 2005.
http://www.codeplex.com/SqlWebAdmin/Release/ProjectReleases.aspx?ReleaseId=8108
[font="Verdana"]Markus Bohse[/font]
December 17, 2008 at 6:07 am
Another method would be to use VBA in Outlook. At one point, I wrote a couple of VBA macros that examined the Subject line of mail addressed to me and would then connect and execute jobs on database servers based on the contents of the subject line. I would send myself an email with the "coded" subject lines, Outlook would execute the macro and jobs would send me the results via email.
It worked fairly well for simple things and I would think you could build on it to make it more sophisticated.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply