Email Security SQL Server

One security vulnerability that people don’t often realise , is that SQL server can email information out through the likes of  sp_send_dbmail. It goes without saying that you should take steps to stop emails externally and then if its a requirement ensure that its to approved email domain only.

The following bit of code shows the domains that emails have actually been emailed to, the code below shows several email addresses ( mock up of  MSDB.dbo.sysmail_allitems email addresses.

If you notice there are multiple emails on one line

Email Address Example

The following code will strip out all the email addresses individually and remove the Email address to just leave the domain name. So quickly you can check if you are getting emails sent places they shouldn’t be. You can even add in your companies email address so it turns into an exception report!

WITH CTE_Email
AS
(
--SELECT recipients FROM MSDB.[dbo].[sysmail_allitems]
SELECT Email AS Recipients FROM
(values
('Martin.Croft@SQLServerSolutions.com'),
('Someone@MyWorksDomain.co.uk;SomeoneElse@MyWorksDomain.co.uk'),
('PersonalEmail@Yahoo.co.uk'),
('Evil@NastyHackers.co.uk'),
('Bill@Acceptable3rdParty.com;Helpdesk@Acceptable3rdParty.com')
) EmailAddress(Email)

)


SELECT DISTINCT
SUBSTRING(recipients,CHARINDEX('@', recipients)+1,LEN(recipients)) AS EmailDomains
FROM
(
SELECT
LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS recipients
FROM
(
SELECT CAST('<XMLRoot><RowData>' + REPLACE(recipients,';','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS Email
FROM CTE_EMAIL
)t
CROSS APPLY Email.nodes('/XMLRoot/RowData')m(n)
)EmailAddress

Which results in the following

Email Address Domains

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s