Forcing HTTPS on ASP.NET Application behing SSL offloading Elastic Load Balancer (ELB)

One of the first problems encountered when trying to move ASP.NET MVC2 Application from regular dedicated hosting to cluster of Amazon's EC2 instances with Elastic load balancer (ELB) distributing traffic between them was endless loop of 303 redirects. Seems like ASP.NET application being moved to new infrastructure had following section in rewrite rules of web.config file forcing every non-HTTPS requests to be redirected to HTTPS-one:

1 2 3 4 5 6 7 8 9

<rules> <rule name="Redirect to HTTPS" stopProcessing="true"> <match url="(.*)" /> <conditions> <add input="{HTTPS}" pattern="^OFF$" /> </conditions> <action type="Redirect" url="https://{HTTP_HOST}/{R:1}" redirectType="SeeOther" /> </rule> </rules>

Problem here is that Elastic Load Balancer is configured to offload HTTPS traffic, and forward plain HTTP request to EC2 instance, so instance's CPU is less utilized. Problem here is that rewrite condition is that incoming request is HTTPS (which is NOT), and thus endless 303 redirects occur. Fortunately, according to non-written standard, Amazons ELB  forwards 'HTTP_X_FORWARDED_PROTO' header with origin request protocol as value, thus above condition could be replaced with
'do not apply this rule if content of HTTP_X_FORWARDED_PROTO header is 'https' or in web.config:

1 2 3 4 5 6 7 8 9

<rules> <rule name="Redirect to HTTPS" stopProcessing="true"> <match url="(.*)" /> <conditions> <add input="{HTTP_X_FORWARDED_PROTO}" pattern="https" negate="true" /> </conditions> <action type="Redirect" url="https://{HTTP_HOST}/{R:1}" redirectType="SeeOther" /> </rule> </rules>

Groovy | Connect to SQL Server and execute stored procedure

To connect to Microsoft SQL Server from Groovy script, and execute stored procedure, simply follow steps outlined below

1) Download JDBC driver Jar file from https://msdn.microsoft.com/en-us/sqlserver/aa937724.aspx.
    Add jar when running groovy script with -cp parameter:

1 2	#!/bin/bash groovy -cp "./lib/sqljdbc41.jar" myScript.groovy

    OR if running compiled JVM code (output from groovyc), use java's cp param

1 2	#!/bin/bash java -cp "./lib/sqljdbc41.jar;myScript.jar" com.example.MainClas

   Note: Examples above assume you have unpacked JDBC driver jar to 'lib' directory

2) Create Groovy's SQL object by using instance method, and passing JDBC connection string to it:

1 2 3 4 5 6 7 8 9 10 11 12 13

def configuration = [ 'dbInstance' : 'localhost', 'dbPort' : 1433, 'dbName' : 'myDatabase', 'dbUser' : 'sa', 'dbPass' : 'sa123' ] def sql = Sql.newInstance( "jdbc:sqlserver://${configuration.dbInstance}:${configuration.dbPort};" + "databaseName=" + configuration.dbName, configuration.dbUser, configuration.dbPass, 'com.microsoft.sqlserver.jdbc.SQLServerDriver')

   Note : All configuration params - host, port, username and pass are placed in 'configuration' map

3) Executing stored procedure is as easy as calling 'call' method on Sql object. This method accepts following parameters:

1. String representing method execution and taking following format
   {? = call SP_NAME(?,?...)}
   Little clarification for question marks:
1. First question mark represents SP's return value (0 for OK)
2. Every following question mark inside brackets presents parameter, either input or output one
2. List for SP params:
1. For input parameters use their values
2. For output parameters specify their type, one of the groovy.sql.OutParameter constants from groovy.sql.Sql class (e.g. groovy.sql.INTEGER)
3. Closure executed upon SP execution. First parameter passed to closure is return value of stored procedure (usually 0 for OK). Every following parameter matches respecting OUT parameter in same order as declared in procedure itself.

Below is example code for both creating Sql object and executing stored procedure, asuming that stored procedure multiplies two integers (first and second parameter), and assigns result of multiplication to third (output) parameter

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

def configuration = [ 'dbInstance' : 'localhost', 'dbPort' : 1433, 'dbName' : 'myDatabase', 'dbUser' : 'sa', 'dbPass' : 'sa123' ] def sql = Sql.newInstance( "jdbc:sqlserver://${configuration.dbInstance}:${configuration.dbPort};" + "databaseName=" + configuration.dbName, configuration.dbUser, configuration.dbPass, 'com.microsoft.sqlserver.jdbc.SQLServerDriver') sql.call('{? = call MULTIPLY(?,?,?)}',[ Sql.INTEGER, 5,5 ,Sql.CHAR],{ spResult, multiplicationResult -> if(spResult == 0){ assert multiplicationResult == 25 }else { throw new RuntimeException("SP MULTIPLY FAILED WITH ERR CODE:${spResult}") } })