Azure ARM: naïve single VM deployment

The aim is to get a real simple VM deployment working using a json template, and in doing that to sacrifice any guidelines on authoring best practice, so we can focus on getting familiar with the nuts and bolts of ARM deployment.

Download the template and PowerShell helper from here.

Assume you need Windows 10 Anniversary edition PowerShell versions for these client/deployment actions.

Open a PowerShell admin prompt in the download location, and run [.\deploy.ps1].

You will be prompted for your Azure login, a resource group and location (e.g. “uksouth”), and an admin password for the VM.

If all goes fine, then you will be able to rdp to the VM, and be able to start up SSMS.

Finally, a couple of pictures I took to focus the mind when trying to reduce the template to the bare minimum to get a working VM:

The next day… in fact I have started to remove the literals as I get more familiar. Again, I refer you the MS Azure docs above.

SQL Server Management Studio (SSMS)

This is now a) free, b) no longer part of the SQL Server media. Get it here.

ssms01.PNG

(Right now WordPress image upload seems to have  a glitch where it uploads in reverse order of capture, so read these pictures bottom to top)

SqlServer: tally table performance


drop table #t;
create table #t (n int);
select 'Step 1', GETDATE();
;with [seed](n) as (
select 1 UNION ALL SELECT 1),
l01(n) as (
select 1 from [seed] a cross apply [seed] b),
l02(n) as (
select 1 from l01 a cross apply l01 b),
l03(n) as (
select 1 from l02 a cross apply l02 b),
l04(n) as (
select 1 from l03 a cross apply l03 b),
l05(n) as (
select 1 from l04 a cross apply l04 b),
myTally(n) as (select row_number() over (order by n) from l05)
select count(1) from myTally
where n < 10000000;
select 'Step 2', GETDATE();
-- that took about 5 seconds
;with [seed](n) as (
select 1 UNION ALL SELECT 1),
l01(n) as (
select 1 from [seed] a cross apply [seed] b),
l02(n) as (
select 1 from l01 a cross apply l01 b),
l03(n) as (
select 1 from l02 a cross apply l02 b),
l04(n) as (
select 1 from l03 a cross apply l03 b),
l05(n) as (
select 1 from l04 a cross apply l04 b),
myTally(n) as (select row_number() over (order by n) from l05)
insert into #t (n)
select n from myTally
where n < 10000000
-- that took over a minute (but not apples and apples)
select 'Step 3', GETDATE();

SqlTally01

SqlServer: grouping a date(time) column by month/year

Using AdventureWorks – search elsewhere in my blog for references to that.

The reference to the US locale is that I should be using an explicit conversion – this style is certainly bad practice, but not important for the example. The focus on the 141 value is to show that this was the smallest number of sales in a month/year. The grouping tells me this was for June 2011. So to prove my logic is right, I then select all the rows for June 2011… and they total 141. QED.

-- count sales for each month/year, ordering by 
-- highest sales in a month/year, descending

with salesByMonthYear as (
select 
 DATEPART(month,modifiedDate) salesDateMonth ,
 DATEPART(year,modifiedDate) salesDateYear ,
 count(1) SalesForThisMonthYear
from [AdventureWorks2014].[Sales].[SalesOrderDetail]
group by DATEPART(month,modifiedDate) ,
 DATEPART(year,modifiedDate) 
)
select * from salesByMonthYear
order by SalesForThisMonthYear desc

-- (assuming US locale... bad practice...)
SELECT *
 FROM [AdventureWorks2014].[Sales].[SalesOrderDetail] where 
 ModifiedDate between '6/1/2011' and '06/30/2011'

SQL Injection: hex and blacklisting

I was given a list of SQL keywords. The idea was that a hacker trying to break the database via a UI or Fiddler/server would be defeated because that (black)list would contain every SQLServer keyword short of ‘select’ (that’s another story). So it would be something like (‘drop’,’delete’,’truncate’) etc. And of course the example assumes that there is no stored procedure with parameters, or ORM, or whilelisting to protect the database. Just effectively plain old unfiltered SQL hitting the database and being allowed to execute dynamic sql, with no parameter binding. So I was being asked to validate the list as being comprehensive.

Well, no blacklist in the world is going to be comprehensive enough, I think. That is because you can obfuscate your intention by passing a hex string as a parameter to e.g.

convert(varchar(max)...

or somesuch. (I guess you could blacklist [convert] etc.)

Anyway, pressing on with my (flawed 🙂 ) evil call…

Suppose you could pass this in your textbox or Fiddler call…

SqlInjection01

Well, you clearly couldn’t say it was harmless… as you just don’t know. In fact this is the result when executed against SQLServer 2014:

SqlInjection02

This is how I built the hex string:

SqlInjection03

, and this is the full picture:

SqlInjection04

 

SQLServer: analytic function – RowNumber()

Using AdventureWorks as before…

analfunc01

 

analfunc02

drop table t_product_persistent;
drop table t_product_staging;
select ProductModelID, Name, 'Persistent' as CatalogDescription into t_product_persistent
 FROM [AdventureWorks2014].[Production].[ProductModel]
select ProductModelID, Name, 'Staging' as CatalogDescription into t_product_staging
 FROM [AdventureWorks2014].[Production].[ProductModel]
-- delete all even keys from persistent
delete from t_product_persistent where ProductModelID%2 = 0
 select * from t_product_persistent order by ProductModelID
 select * from t_product_staging order by ProductModelID
-- Apply the rule: if the key exists on the persistent table, then use that row, else use the row on the 
-- staging table. Net expected result: the ProductModelId is unique across the combined set.
-- Small steps towards the goal...
-- Grab the superset from both tables
select * from (
 select p.ProductModelID, p.Name, p.CatalogDescription, 1 as TypePriority from t_product_persistent p
 union all
 select s.ProductModelID, s.Name, s.CatalogDescription, 2 as TypePriority from t_product_staging s
) x
order by ProductModelID
-- Grab the superset from both tables, adding a column to hold the ranking
select x.ProductModelId, x.name, x.CatalogDescription, x.TypePriority,
 ROW_NUMBER() over (partition by x.ProductModelId order by x.TypePriority) xx
from (
 select p.ProductModelID, p.Name, p.CatalogDescription, 1 as TypePriority from t_product_persistent p
 union all
 select s.ProductModelID, s.Name, s.CatalogDescription, 2 as TypePriority from t_product_staging s
) x
order by ProductModelID
-- Finally just keep the Priority 1s..
;with ModelWithPriority as (
 select x.ProductModelId, x.name, x.CatalogDescription, x.TypePriority,
 ROW_NUMBER() over (partition by x.ProductModelId order by x.TypePriority) xx
 from (
 select p.ProductModelID, p.Name, p.CatalogDescription, 1 as TypePriority from t_product_persistent p
 union all
 select s.ProductModelID, s.Name, s.CatalogDescription, 2 as TypePriority from t_product_staging s
 ) x
)
select * from ModelWithPriority where xx = 1