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

Posted in Uncategorized | Leave a comment

Kerberos -The annoyance that is

This is more about how to find something than finding something if that makes sense or doesn’t. Anyway a bit of back story is probably worthwhile at this point

In the beginning 

There was once a server that wasn’t working  properly, the poor little SQL server when started would write the following out to the SQL event log ( but not in Italic)

The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x80090350, state: 4. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.

Every time it started, it would moan and whinge, this however was a lowly test server and didn’t get the time it deserved. Until someone broke ( accidentally)  some integration that was setup with another server. This was rebuilt and Kerberos was actually needed now, ( how it wasn’t before is another question for another day)

Anyway all the blogs on the internet were googled as is the way and there are lots out there regarding SPN’s and the setup and configuration and the fixing of, these  had all been followed. Its at this point that I mention there was another server, a test server just like out poor little server, only it didn’t have this same problem.

Hallelujah, I hear people exclaim, just look at our good server and our poorly servers configuration and settings , which was exactly what the DBA had done and was starting to pull out his hair, when he asked for another opinion and a another set of eyes on the issue.  We tried a couple of things to eliminate the issue without success! He exclaimed  both service accounts are setup the same. But are they ? I considered, have you seen how many setting can be made against users and computers in AD wow

Quick google for the syntax and a couple lines of PowerShell for both the poorly server and good server

 

Get-ADUser -filter {name -like "svc_Poorly_SQL"} -Properties |
Export-Csv -Path "C:\Temp\MSTEST.csv" -Delimiter ","

Allowed me to compare the setting and yep there was ones that weren’t the same, taking usernames, SID and things out of the equation , some digging was done on the setting that were different and on the poorly server the UseDESKeyOnly option had been set to TRUE, which was visible in the GUI .  There are other blogs out there which describe what this is and what this does, in this case all you need to know this was the cause of our issues.

The moral of the story

You can stare at something for ages and never see something, don’t ever be afraid to ask for someone else opinion, as you will without doubt be in the same position at some point.  You don’t necessarily have to deep dive into things if you’ve got a baseline ( working server in  this case) and the broken one ( not working one in this case), then its a matter of logic, actually what is different and a method of finding it out.

 

Posted in PowerShell, Tips & Tricks, Uncategorized | Leave a comment

SQL Solutions – Part 2 bookmarks

Bookmarks combined with SQL solutions can start to give some serious power to a script library. In the previous blog post

I covered creating a script library, he’s a copy of one that i created / use daily. SO i can quickly access script associated with Daily check , extended events or blocking etc.

SQLScriptLibrary

I have a lot of scripts and can find what I am looking for quickly this way, but what happens when I have a bunch of script with lots and lots of smaller scripts, is there a better way of accessing / indexing to find what I want.

Yes.

Using bookmarks

This windows off by default, but can be switched on through menu View(1) Bookmark Window(2)

Boomarks

The bookmark window allows you to bookmark lines of code and quickly jump to that point in the code.Below it shows you what the buttons do

(1) Creates a bookmark on the current line of code in the query window

(2) Creates a folder so you can create different topic areas as shown below

(3) & (4) move between bookmarks , this jumps the cursor to that position in the scripts

(5) & (6) moves the cursor to the next bookmark in the current folder

(7) double clicking on the individual bookmark jumps to that bit of code

Bookmarks_adhoc

So I can write English statements such as “When was SQL installed” and it would jump to the script and the line in the script of the statement that shows me that

The previous post can be located here Reusable Script Library

Posted in Configuration, Management Studio, Productivity, Tips & Tricks | Leave a comment

Horizontal Whitespace & Vertical Select

Another demo from the June SQL Pass / SSUG presentation. Some of the lesser known features of management studio is the ability to vertically select data (see below) and also delete white space the following is an example of using these.

Scenario

You have been given a set of data product number / case number which is a string value that you need to run against a query.  You could copy these into Excel concatenate and add in (‘)   quotes before and after (‘) and add in commas (,) so you get a where clause like

SELECT * FROM table WHERE value in

(

'A1001'

,'A1002'

)

Code to create some example data to use

SELECT top 50

CASE WHEN ROW_NUMBER() OVER(ORDER BY ProductNumber) % 3=1

THEN SPACE(ABS(CHECKSUM(NEWID()))%(1-10+1))+[ProductNumber]

ELSE [ProductNumber] END AS [Product number Example]

FROM [AdventureWorks2014].[Production].[Product]

This generates the following result set,  as can be shown every 3rd row (1), (2) has a random space value, to create a replication of varying product values

Productcode

If we paste this into out query,   you  can see how the formatting is with the product number

HorizontalExample

So the product number ( every 3rd row contains a random space) to replicate product data being different lengths

(coolest feature in SQL Management studio alert warning, I really love this and use it a lot)

If you press the ALT key and drag your mouse cursor down vertically in SSMS

You can do a vertical select notice the thin blue line (1)

Vertical

You can now press TAB and all the code would move right, if you type in ,’ you get

all the code wrapped in quotes and comma’s added, so if you had 300 lines , you would have got 300 comma’s added with just 3 key presses.

If you tab across on the first row (1) and do the exact same thing you have quickly added in quotes around the string values

3steps

Next to remove the white space select  Edit from the menu (1) and Advanced (2) and Delete Horizontal White Space

DeleteWhitespace

You then get all the white space removed ! Just like that

Tidy

Posted in Management Studio, Productivity, Tips & Tricks | Leave a comment

SQL Solutions – Reusable script Library

As part of the SQL Pass Manchester / Leeds presentations I did, I showed how you can create your own Script Library to help code re-use within management studio. This is step by step guide to setting up a simple reusable script library in management studio.

First things first you need to in Management Studio

Select View  from the menu(1) and Solution Explorer (2)

Solutions

To create a set of solutions to hold a script library select File (1) New (2) and Project(3)

Projects

Ensure that SQL Server Scripts (1) is selected and give a Name for the project (2) and a file location (3)

Ensure that the Create directory for solution is ticked ( if required) (4)

Give the solution a name (5)

Click OK (6)

Script_Library

This creates the solution that can be seen in the solution explorer

Solution_explorer

Right click the solution and select add / New project

New_Solutions

Give the project a name in this case I have called it Daily Scripts (1) leave the location as is (2) and click OK(3)

DailyScripts

You can repeat these steps to create a set of folder as shown below.

Monthly_scripts

You can keep adding in projects and it starts to become a useful repository for scripts ( screen shot i have for my projects), which if you open the top level project ( in this case SQLScriptlibrary it will contain all sub projects)

SQLScriptLibrary

Now if you name the script appropriately you can order then and you have a quick

easy to access code / script repository. Note you can use letters to add in related script in an order.

Security

These are now quite useful to hold scripts that you use or want use again. There are a few advantages of these projects you, as they will remember the last connections you connect to. You can add to source control, pull down and share within teams so you all have access.

These also become useful with bookmarks which is covered in the next blog post

 

 

 

 

 

 

 

 

Posted in Configuration, Management Studio, Productivity, Tips & Tricks | 1 Comment

One for the Man united fans (or not)

Always being a security conscious professional I lock my machine all the time, Yep I haven’t in the past ( and learn’t from that) as no one wants to email the infrastructure team proclaim you love them or ask one of them out for a drink and bcc in the rest of the team.

In lots of places it seems fair game if you don’t lock your machine, we’ll one thing you can do to seek revenge, especially in environments that people have access to other’s machines through an UNC path is to have a play with the SQL splash screen

This might be familiar what appears when you start Management Studio ( this one obviously is sql2014 ( other versions are available)

ssms5.splash

This is basically a bmp file called ssms.splash.bmp which lives somewhere in the SQL install path such as

C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\ssms.splash.bmp

Which you can update for people who annoy you 😉

ssms2.splash.jpg

Or this one, if you really wanted you could change it daily and watch people panic, or switch it back over when they report it to the helpdesk

ssms4.splash

And the one for the united fans out there ( or really the Liverpool ones) which I have as my current splash screen

ssms1.splash

or you could even go retro!

sql2000

 

 

 

Posted in Bit of fun, Tips & Tricks | Leave a comment

DBA / Developer Tool Set

As part of a SQL Manchester pass presentation I did called “The Proactive Database Professional” I showed  a few things that could make you more productive. One was a customisable tools set, which  I have detailed below

The first thing needed is a set of scripts, ( downloadable links at bottom of the blog from One Drive / Google Drive)  these consist of the following procedures.

The names are quite descriptive in what they do, but as shown shortly you can add much more to these to make them useful tools

DBA_tools_list

The code in the scripts are basically DMV( Dynamic Management views) and SQL that I have acquired/written over the years while being a developer / DBA / Data Architect.

One that should be on every production system is Adam Mechanics sp_WhoIsActive  procedure, which is also part of the tools.

These consist of routines that can help you monitor / check for issues or can get you out of a fix. I’ll list / show some of them in future blogs in more detail. But putting them on a development system and having a play will show you more yourself.

For each Procedure ( these are created as procedures to make them easier to use / remember – I do have a standalone version for system that you can’t roll out procedures too) I add it in the format as sp_DBA_[Name of the procedure] the method in the madness is that you can then create an index of these useful procedures.

This is done by creating 3 extended properties as below

Extended_Events

  1. Version This allows you to easily view versions on different servers and keep versions upto date
  2. Description – Says what it is on the tin, description of what the procedure does and how it can be used
  3. Parameters  – Parameters that the procedure takes

Then using the code from sp_DBA_tools procedure it displays the tools available with description , version and parameters is can take

Additionally I make this a short cut key ( Tools / Option / Environment / Keyboard)  – will need to open a new query window before it works

Short_cutkey

You will get something like the following by executing sp_DBA_tools

sp_DBA_tools

Rolling these out is easy you can roll out individually, Obviously we don’t want to do that as were being proactive database professionals right, so we can create a master roll out script. We can use SQLCMD – (Query \ SQLCmdmode in SSMS) below we specify file location (1), the best thing with this its on your local machine. So you can change to Server A or Server B and the script will still work as its looking at the same location

We then we specify the name of the SQL file which is the name of the procedure (2) and when executing it will run all the procedure in at a single time to that server. So literally its a 30 second job to roll these out onto a new server

Rollout_script

I also have a set of PowerShell scripts that allow these to be pushed out to many servers at a single time. This will be shown in the next blog post.

 

Links to zip file

GoogleDriveOne Drive

Posted in Management Studio, Productivity, Tips & Tricks, Uncategorized | Leave a comment