Send Query to SQL Server thru Database Mail

Question

Send Query to SQL Server thru Database Mail

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

jgrubb SSCrazy Points: 2542 More actions · Answer 1

MG (12/17/2008)
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.

Anyone else get a shiver?

All it takes is one person looking over your shoulder and you could be in real deep. I hope one of those jobs wasn't "execute attached sql command". Imagine the possible problems, even if sanitized. There are some common, useful, routines that could effective be a DOS attack, if run during peak hours.

Yikes!

MG-148046 SSCrazy Eights Points: 9946 More actions · Answer 2

Anyone else get a shiver?
All it takes is one person looking over your shoulder and you could be in real deep. I hope one of those jobs wasn't "execute attached sql command". Imagine the possible problems, even if sanitized. There are some common, useful, routines that could effective be a DOS attack, if run during peak hours.
Yikes!

The subject line was always 'gobbledy gook" and I am paranoid enough to have changed it once a month.

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.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

MG (12/17/2008)
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.
It worked fairly well for simple things and I would think you could build on it to make it more sophisticated.

Better would be a VB.net (or C# for that matter) written Windows Service that used the MAPI interface to catch & read new eMails to a designated mailbox, "Validate" them (super important), then connect to the SQL Server and execute them.

This would have essentially the same functionality as the Read half of SQL Server Mail, except that it would not have to be on the same box as the SQL Server, so it could run anywhere in the intranet.

[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]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

MG (12/17/2008)
Anyone else get a shiver?
All it takes is one person looking over your shoulder and you could be in real deep. I hope one of those jobs wasn't "execute attached sql command". Imagine the possible problems, even if sanitized. There are some common, useful, routines that could effective be a DOS attack, if run during peak hours.
Yikes!
The subject line was always 'gobbledy gook" and I am paranoid enough to have changed it once a month.

I have used the "hashed subject Line Key" scheme before, except that it was changed every hour and had to be automatically generated by matching routines on the sender and receiver,which is something the Outlook-VBA would be good for (I had an automatic sending app, so I didn't need that).

I also didn't send commands, because I considered that way too dangerous for this simple security scheme. Instead I just sent CSv data file attachments that it was importing into a history table.

If I was going to send commands, then I would want real encryption and decryption on the contents.

[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]

Kendal Van Dyke SSCertifiable Points: 5972 More actions · Answer 5

I suppose you could also reduce the risk by mapping commands to queries and not allowing anything ad-hoc. Kind of like how yubnub[/url] works.

Kendal Van Dyke
http://kendalvandyke.blogspot.com/[/url]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

Actually, you can restrict them to stored procedures, just like an application is supposed to be, but that still has two problems:

1) That is rarely what is wanted for a remote DBA tool (even if it is to be read-only).

and

2) That is still insecure wrt "who am I really sending this data to" and "who can snoop it in-between", which may be even worse than "should I really delete this data?"

[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]

AlexSQLForums SSChampion Points: 14277 More actions · Answer 7

If this is a critical production system.

You can use sqlmobile from Idera.

http://www.idera.com/Products/SQLmobile/Default.aspx

Alex S

Rudy Panigas SSChampion Points: 10702 More actions · Answer 8

Thanks for info. Unfortunately the Idera product does not work on Blackberry technology... the last time I looked.

Rudy

Kendal Van Dyke SSCertifiable Points: 5972 More actions · Answer 9

Just visited the product URL and they have a nice graphic proclaiming "Now For Blackberry!"

Kendal Van Dyke
http://kendalvandyke.blogspot.com/[/url]

Rudy Panigas SSChampion Points: 10702 More actions · Answer 10

Oh.. it must be new. I'll give it a look.

Thanks,

Rudy

S3ssion SSChasing Mays Points: 602 More actions · Answer 11

Hi my fellow SQL-Freaks.

On this topic: Reading Email on 64bit SQL 2005

Currently we also run 64Bit SQL 2005, and same as you, as a DBA and Analyst, I also needed the functionality to to send queries to my server via email ie; sp_who etc...

There is no functionality to read emails with database mail although, using SQL mail on 32bit...but then on 64bit....not a chance, so you are slightly screwed!

So, how did I overcome this issue???

Quite simple though. 🙂

Having done quite a lot of development around IM (Instant Messaging) I was able to set up a XMPP/Jabber Server by using opensource server software "OpenFire", which conveniently runs a SQL backend DB.

Using my own developed IM Client on my BlackBerry (you can download and use opensource "BomBus" which will work on most mobile phones and BlackBerry) i simply send my query to my pre defined IM Contact, in this case SQLServer.

A job monitoring the incoming messages on the OpenFire SQL DB, then pushes the query to my 64bit SQL, which in turn then can either email me the results, and or pushes the results back to OpenFire DB, which in turns IM me the results.

I prefer to receive the email results, as the result set could sometimes be quite huge, which will cause the IM to fail as a result of message length limitations.

This works 100% for me, and I have now even gone as far as monitoring my SQL Server by sending me alerts via IM and email.

I do hope this will help some one out there, that might have had the same obsticle to overcome as I.

Feel free to contact me and enjoy

| If in Doubt...don't do it!! |