Embedding Citrix Web Interface 5.4 into 3rd Party Portals

 

Introduction

Recently I was involved in a consulting project for a customer who had an interesting debacle.  They had a Salesforce.com VF based customer portal and they wanted to provide links to both Published Applications and other SaaS\Web applications within a “Quick Links” window on the home page of portal. 

Initially the developers thought they would just use a SQL provisioning database to store user data and create custom roles within as a means to determine what application links to display to the end users and planned to just use different iFrames within the quicklinks area to pull everything together.

But I explained to them that this approach may be problematic for several reasons.

1. Loading elements in different iFrames would have different render times and depending on the application and content you were pulling in. This might result in a staggered render in the end-user browser if you were not masking this behavior through some other means like a progress bar or spinney thingy to distract them or at least give he illusion that something coherent was going on in the background. 
2. HTTP 302 Redirects are often other artifacts of multi-faceted web applications which can also cause “flickering” or “jumpy” rendering within a page iFrame / code block. 

A seldom used often forgotten feature of Citrix XenApp that has been there for ions is the ability to Publish not an application but Content.  This content can simply be a link to another web application or actual content such as a document.  This content is not accessed through the means of a Citrix XenApp ICA session but directly by the client.  There are numerous benefits to this approach of consolidation.

1. All Application links can be managed in one place through AppCenter instead of multiple role based data stores.
2. Application link visibility can be controlled by AD Group Membership which can be based on existing groups or new Groups created with roles in mind.
3. All of the available XenApp filtering and possibly “Smart Access” policies are at your disposal to add another layer of control.
4. When you add applications in XenApp, you can specify the icon for the application you desire to display to the end user.  If you are adding a Web Content application, XenApp will actually automatically check to see if the application has a favicon and use that by default or you can manually choose one in .ico format.

So then the question becomes – Do you use Web Interface (WI) or the Web Interface SDK or cobble together your own UI using nFuse calls?  Well this article and approach leverages Option 1 as I felt this was the best path given that there are many ancillary tasks that Web Interface handles and manages out of the box that you would have to deal with that are not really even related to the portal integration but affect the overall usability and function – things like Client Deployment, Workspace Control settings, Client Address Modes, Farm settings, etc… Many of these are parameters that might change during the lifecycle of an application and thus having a standard management tool to deal with them will make the overall solution management less involved.

The following steps strip-down Web Interface such that it will transparently look like a part of the portal you are embedding it into. The steps are broken down into actual content customizations and configuration options. 

Content Customizations

Here is the good news – when web interface was being integrated into Access Gateway Advanced edition NAV UI, a lot of work was actually done to make the UI portal friendly.  Since we are not using the Access Gateway NAV UI, we have to fool the WI logic into thinking you are.  Once this is accomplished, the rest of the customization steps are mainly for cosmetic and functional reasons.

Customization	Details
To make WI think it will be embedded into the NAV UI (required)	Comment-out or remove line 124 of [Site-Name]\app_code\PagesJava\com\citrix\wi\pageutils\AGEUtilities.java and replace with true condition: public static boolean isAGEEmbeddedMode(WIContext wiContext) { return getAGEAccessMode(wiContext) == AGEAccessMode.EMBEDDED; } becomes: public static boolean isAGEEmbeddedMode(WIContext wiContext) { return true; } and comment-out or remove line 132 of \app_code\PagesJava\com\citrix\wi\pageutils\AGEUtilities.java\AGEUtilities.java public static boolean isAGEEmbeddedOrIndirectMode(WIContext wiContext) { AGEAccessMode mode = getAGEAccessMode(wiContext); return (mode == AGEAccessMode.EMBEDDED) || (mode == AGEAccessMode.INDIRECT); } becomes: public static boolean isAGEEmbeddedOrIndirectMode(WIContext wiContext) { AGEAccessMode mode = getAGEAccessMode(wiContext); return true; }
To remove the Folder menu tabs and Main header  (optional)	Remove or Comment out line 13 of [Site-Name]\app_data\include\compactResourceListTabTools.inc
To remove the search pane (optional)	Remove or comment-out line 132 [Site-Name]\app_code\PagesJava\com\citrix\wi\pages\StandardLayout.java // navControl.setShowMessages(true);
To override the Workspace Control settings (optional)	Remove the !AGEUtilities.isAGEEmbeddedOrIndirectMode(wiContext)) condition on Line 1078 of [Site-Name]\app_code\PagesJava\com\citrix\wi\pageutils\include.java
To remove the horizontal lines below the application icons (optional)	Remove the border style elements on lines 565 and 860 in [Site-Name]\app_code\PagesJava\com\citrix\wi\pageutils\lowStyle.inc: #searchPane { margin: <%=wiContext.getString("LGVertBigGap")%> 0 0; padding: <%=wiContext.getString("LGVertBigGap")%> 0; } and #wscOptions { margin-top: 3px; }
To display icons and not the list view in the Applications pane (optional)	In [Site-Name]\app_code\PagesJava\com\citrix\wi\tabs\ResourcesTab.java using notepad and search for "CompactView". At the end of the line, remove "LIST" and replace with "ICONS".

Once you complete the above steps, reset IIS, close your browser, and log back in.  Your WI should look something like the following:

 

 

 

 

 

 

Required Web Interface Configuration Settings

Ok so there are other options that must be enabled in the actual configuration to make for the best end user experience.

Configuration Option	Recommendation	Details
Client Detection	Disable	I opted to disable this as this particular company already had a plan in place with their care team who were responsible for making sure the end users had the necessary hardware, software, and connectivity required to access the environment and during this process the desired Citrix client version (12.3 Online Plug-In) was going to be installed. When you enable Client Detection and optionally Deployment, there is quite an involved process that WI goes through to glean information about the end user device and whether they have a suitable ICA client installed. This involves many redirects and popups and these kinds of behaviors are not really suitable for a portal environment. A static link to a download would be a better option or you can put in place a customer process which takes care of this aspect.
Client Deployment	Native	While the Java ICA client is considered to be “zero footprint”, there are really more hurdles than you think to get that working properly.  For one, Java is notorious for being persnickety about SSL.  The newer versions even with the Windows Keystore option still have numerous issues with SSL certificates that are chained which almost all current CAs provide now. This means that you have to manually import certificates into the Java Keystore or use CLI cmds and this is hardly zero footprint.  In addition, there is a Kludge pop-up window that also has to be there during the session to maintain the session.  What I recommend doing here is just to use the Native client.   Citrix has clients for virtually every device OS out there and these will run better natively than using java as a band-aid.  Recently Citrix has come out with an HTML 5 client (announced at Synergy San Francisco in Nay 2012) which looks to be a lot more promising in terms of a zero foot print client.  This client uses HTML 5 web sockets method which is now supported by Mozilla FF an Chrome and will be supported by IE 10 in Windows 8.
Session Settings	General > User Customizations Kiosk Mode: Enabled Display settings button to users: Unchecked	This prevents any client side settings from being saved to a Cookie Value and also prevents users from changing the settings. This is more desirable for “no foot print” scenarios and keeps it consistent in the portal.

Acknowledgements

A big shout out to Mike Bednarek in Citrix SW Dev who was one of the main guys involved in the development of Web Interface over the years and knows this product better than most. I relied heavily on his knowledge of all the moving parts to tweak the UI.

Preventing Brute Force Login Attacks to the Citrix NetScaler Access Gateway or AAA for TM Login Page – Part 1

One of the more common requests I see is how to prevent brute force login attacks to the Citrix Access Gateway or NetScaler AAA for Traffic Management Login pages. Like many other web applications that have a public facing HTML form used for login, this is an assumed risk.

Part 1 of this article looks at how you can use the NetScaler HTTP Rate Limiting feature in conjunction with the Responder module to detect and respond to a potential brute force attack. In Part 2 we will look at how you can leverage CAPTCHA on the NetScaler to augment this method to provide an additional layer of protection.

Some background on the problem to solve

It is fairly simple now-a-days in the age of YouTube how-to videos and a myriad of other black art do-it-yourself tools (Brutus, THC Hydra, John the Ripper, Cain & Abel, etc…) to learn how to build and orchestrate a brute force style dictionary attack which attempts to find a set of username and password credential pairings that successfully authenticate a malicious attacker. For sites that use HTTP authentication methods such as an HTML Forms this involves, at a very basic level, a specifically crafted HTTP Post which has user/pass form field names with variables that change with each request in a loop that iterates to N! until the dictionary library of usernames and passwords has been exhausted. In statistics, this is simply referred to as Combination theory where you have a combination of n things taken k at a time without or with repetitions. This is a very simple computation for a single computer to perform and even easier when you are dealing with something distributed. All might need to do is buy or compile a decent list of URLs, usernames and passwords to reference. Implementing protection against brute force attacks is important for any organization exposing an application to the Internet and is also one of the Open Web Application Security Projects (OWASP) recommended testing procedures.

How can the NetScaler HTTP Rate Limiting feature help?

Since both AGEE and NetScaler utilize HTTP Forms authentication, they are also vulnerable to this problem. The NetScaler HTTP Rate Limiting feature can be used in conjunction with the Responder feature as a valid deterrent to help address this vulnerability.

	Below is a graphical display of the flow of a logon session with the Rate limiting method configured.
1
	End user or malicious application/user are presented with a login form to “POST” credentials to.
2
	Invalid credentials result in an error message displayed to the end user and a specific HTTP response
3
	What we want to accomplish after a set number of login attempts are exceeded within a certain time slice, the user is presented with an alternate response that prevents further posts and potentially also prevents an account lock-out if we make the threshold lower than the account security policy

Implementation on Citrix NetScaler

Follow these 6steps to limit the number of requests to the AGEE or AAATM login page. Steps for both GUI and CLI are provided.

1	Define a Limit Selector This tells us what to track or “select”. You want to select both IP and URL since we want to track hits to the same URL from the same IP
	GUI:
	CLI: add ns limitSelector aaa_err_login_selector CLIENT.IP.SRC HTTP.REQ.URL
2	Define a Limit Identifier The identifier indicates the pattern within the time slice that will trigger a hit. We choose the selector we defined above, and a mode of “REQUEST_RATE” as we want to know how many times the specific URL will be requested in the Time Slice. Since requests may not occur at a specific interval within the time slice, a BURSTY limit type is better to use than a SMOOTH one. We are not concerned about reducing bandwidth here since we want to block so that can be left to default of 0.
	GUI:
	CLI: add ns limitIdentifier aaa_err_login_identifier -threshold 3 -timeSlice 300000 -selectorName aaa_err_login_selector -trapsInTimeSlice 3
3	Define a Log Message Action This step is optional but does provides a mechanism to notify you with a specific audit message that can be forwarded to an off-box SIEM solution. Within the message you can insert dynamic tokens for the IP and URL you are tracking to identify the application.
	GUI:
	CLI: add audit messageaction aaa_login_err_alert ALERT "\"Max login attempts detected from \" + CLIENT.IP.SRC + \" to \" + HTTP.REQ.URL + \" within a 5 sec period. Possible brute force login attack\"" -logtoNewnslog YES -bypassSafetyCheck YES
4	Define a Responder Action What an malicious end-user or “bot” would see if they met the threshold defined in the limit identifier.Note – this Responder Action could be more simplified, but this one is crafted to integrate with the NetScaler Symphony Theme. If you are using a different type of HTTP Auth, you may also configure a responder policy to simply DROP or RESET the connection.
	GUI:
	CLI: add responder action aaa_err_login_blockip_5min_act respondwith "\"<html><head><title>Citrix NetScaler</title><link rel=\’SHORTCUT ICON\’ href=\’/vpn/images/AccessGateway.ico\’ type=\’image/vnd.microsoft.icon\’><meta http-equiv=\’Content-Type\’ content=\’text/html; charset=UTF-8\’>\" + \"<meta content=noindex,nofollow,noarchive name=robots><link href=\’/vpn/images/caxtonstyle.css\’ type=text/css rel=STYLESHEET></head><body>\" + \"<table style=\’height:100%; width:100%;background:#e4e7e9 url(/vpn/images/Background.png) repeat-x center left;\’ cellpadding=\’0\’ cellspacing=\’0\’><tr><td valign=\’middle\’ align=\’center\’><table cellspacing=\’0\’ cellpadding=\’0\’ border=\’0\’><tr>\" + \"<td align=\’center\’><table cellspacing=\’0\’ cellpadding=\’0\’ class=\’logonBoxWidth\’><tr><td colspan=\’3\’ class=\’headertop\’><span class=\’headerLabel\’>NetScaler Access Gateway</span><span class=\’headertm\’><sup>TM</sup></span></td></tr><tr>\" + \"<td class=\’logonBoxTopLeft\’></td><td class=\’logonBoxTopMid\’></td><td class=\’logonBoxTopRight\’></td></tr><tr><td class=\’logonBoxMidLeft\’></td><td class=\’logonBoxMid\’ align=\’justified\’ valign=\’middle\’\’>\" + \"You have reached the maximum allowed login attempts from your device at: <strong> \" + CLIENT.IP.SRC + \"</strong>. <p>You will not be permitted to logon again for 5 minutes. Please contact your system administrator\" + \".</td><td class=\’logonBoxMidRight\’></td></tr><tr>\" + \"<td class=\’logonBoxFooterLeft\’></td><td class=\’logonBoxFooterMid\’></td><td class=\’logonBoxFooterRight\’></td></tr></table></td></tr></table></td></tr></table><div class=\’BottomLogo\’><img src=\’/vpn/images/logo.png\’ height=\’25\’ width=\’60\’ /></div>\" + \"</body></html>\""
5	Define a Responder Policy for AAATM or AGEE The responder policy references the rate limit selector and matches the AAATM or VPN URLs and also detects the presence of the invalid login session cookie and fires the responder and log actions defined above.Note – If you only desire to trap AAATM or AGEE VPN you can remove the relevant condition in the defined expression. This method will also work for other web applications byt specifying a different URL.
	GUI:
	CLI: add responder policy aaa_err_login_blockip_5min_policy "(HTTP.REQ.URL.EQ(\"/vpn/tmindex.html\") || HTTP.REQ.URL.EQ(\"/vpn/index.html\")) && HTTP.REQ.COOKIE.VALUE(\"NSC_VPNERR\").EQ(\"4001\") && SYS.CHECK_LIMIT(\"aaa_err_login_identifier\")" aaa_err_login_blockip_5min_act -logAction aaa_login_err_alert
6	Globally bind the Responder Policy We have to bind globally to deal with both VPN and AAATM but it would be just as easy to bind the responder policy to a specific AAATM or VPN VServer.
	GUI:
	CLI: bind responder global aaa_err_login_blockip_5min_policy 110 END -type REQ_DEFAULT

What else can be done?

1. Utilize 2-factor authentication such as RSA SecurID, Phonefactor, CryptoCard, etc…
   When a second factor auth is provided, this immensely improves the over-all authentication security posture. The most common is a One-Time-Password (OTP) solution that requires the user to use something they know and a form of challenge which may range from a randomized code from a token to a phone call, email, or SMS message. NetScaler supports many of these 2-factor solutions as they are based on standard RADIUS. Many vendors have also gotten Citrix Ready certification which means they have verified their solution works properly with Citrix NetScaler and Access Gateway.
2. Use a 2-factor authentication method as the Primary Authentication
   The NetScaler or Access Gateway permit the configuration of Primary and Secondary authentication methods, In addition, this second factor method can be specified as the primary authentication method. The significance of this is that the 2nd factor auth would be attempted first by the NetScaler AAA daemon and will fail if the attempt if the before the directory auth is attempted.
3. Implement Captcha image verification – (See Part 2)
   Captcha stands for Completely Automated Public Turing test to tell Computers and Humans Apart. This method involves presenting a problem challenge that humans can solve but would be very difficult for a computer. Captcha was specifically developed to prevent automated software from filling out an HTML form such as those used for logon. A common type of CAPTCHA requires the user to type letters or digits from a distorted image that appears on the screen.

What does HTTP Rate Limiting not address?

• Directory Account Lock-out
  HTTP Rate Limiting will not totally help you with the problem of account lock-out unless you set the thresholds for the selector to be less than the account security policy. This is a common occurrence with brute force attacks as they often use the same username iteratively with different password combinations. This issue can combated by using a 2nd factor auth method or Captcha as described above.
• False Positive Blocking from Forward Proxies
  Users who are coming in on the same forward proxy could be blocked from access if there are multiple users attempting a login within the time slice window. This is due to all clients presenting themselves with the same SRC IP address which the limit identifier is tracking.

Download the batch configuration script

More Information

• OWASP Testing Project
• Citrix NetScaler Application Delivery Controller
• Citrix NetScaler Access Gateway
• Part 2 – Implementing Captcha on Citrix NetScaler / Access Gateway (Coming soon!)

del.icio.us Tags: NetScaler AAA TM,Citrix NetScaler,Citrix Access Gateway,HTTP Rate Limiting,HTTP Brute Force Login,OWASP

Solving OWA Attachment Security with Citrix NetScaler

What’s the problem exactly?

With the rise of the availability of web based applications, web based versions of their client-server counterparts, and workforce mobility, comes the increased risk of potentially leaving behind sensitive information on remote devices not under the control of corporate IT.  While there are no 100% solutions to this problem, organizations can implement numerous preventative measures to deal with it which are available from the developer as well as 3rd party technologies.

Microsoft Outlook Web Access is no exception to this problem and is often at the forefront of the concerns of security teams because of it’s popularity and the fact it is very often exposed to the public internet so that mobile work forces can remain productive from anywhere.  This is of course exacerbated by the fact that OWA and other web based email interfaces like it provide access to email attachments.  These attachments, when opened, are cached in a browser’s Temporary Internet Files folder or worse perhaps saved to a folder location that is easily accessible by anyone.  End-users could potentially walk-away from these devices leaving corporate information behind to possibly be exploited for malicious purposes. 

As mentioned, there is no solution that is 100% safe to the aforementioned problem.  There are numerous solutions to these issues, ranging from VPN Cleanup Agents, to access via Virtualization technologies such as Citrix XenApp or Citrix XenDesktop, to proxy devices such as Citrix NetScaler.  Each method has their own set of pros and cons and have varying levels of cost and preventative .  This article focuses on utilizing the power of Citrix NetScaler ADC

How can one solve this problem?

Starting with Exchange Server 2007, Microsoft introduced the concept of Public and Private computers allowing end-users to select whether their end device was to be trusted or not. 

OWA 2007 Login Form	OWA 2010 Logon Form

On the Exchange Server end, these options pertain to settings that can be used to control the behaviour of how attachments are dealt with.  These include the ability to block all attachment access or allow attachment access but only via the built-in document conversion utilities which convert the attachment to HTML for view right in the browser.

Disallow all attachment access or only allow file preview	Allow attachments with restrictions or certain file types only or force use of file preview

These settings in turn control the end-user experience with regards to attachment access. 

Public computer with no access to attachment

Private computer with attachment access and preview

Giving end-users the control is not the answer

All of the above are great options if you actually trust your end users to select the correct option at login to classify the type of device or location they are accessing your network from. But a more realistic solution is to profile the device, user, or location and enforce the behaviours based on this discovery information.  Some examples of the information you could base a decision on include:

• Client IP Source Address
• SSL Certificate Auth/Details
• Browser/Device Type
• User Identity or Group Membership
• Presence of Anti-Virus or Anti-Spam software

How can Citrix NetScaler ADC help? 

NetScaler has three core features which can be leveraged to secure Outlook Web Access attachment functionality based on the methods described above. The NetScaler AAATM feature (Authentication, Authorization, and Accounting for Traffic Management) enables the ability to use the NetScaler to perform authentication to user account directories based on LDAP, RADIUS, TACACS+, or Client SSL certificates.  Secondly, Access Gateway functionality provides bi-directional SSL VPN tunnelling and ICA Proxy capabilities and adds the ability to perform endpoint analysis to the aforementioned AAA features provided by AAATM.  And lastly, the NetScaler Rewriting feature allows us to alter or inject html in Requests and Responses based on conditions we define by the very extensible AppExpert policy engine. 

Instead of letting the End User control the attachment behavior, the NetScaler can be inserted in front of the solution to provide

• DMZ Authentication and Authorization
• Single Sign-On to Outlook Web Access
• Attachment control based on end user, location, or device attributes

In the configuration example below, Source Address and Group Membership are chosen for example to determine the level of attachment security applied to the session.

In addition to providing attachment security, Citrix NetScaler can be leveraged to provide High Availability, High Scalability, and Consolidation through additional on-box features such as SSL Offload, Content Switching, Load Balancing, Content Compression, and Integrated Cache. 

Sample NetScaler AAATM Configuration for OWA 2010

Sample Access Gateway Configuration (coming soon)

More Information

• Citrix NetScaler Deployment Guide for Microsoft Exchange 2010
• Citrix NetScaler AppExpert Template for Outlook Web Access
• How to Configure Single Sign-On for Outlook Web Access 2010
• How to Configure Single Sign-On for Outlook Web Access 2007

 

del.icio.us Tags: OWA Attachment Security,Citrix NetScaler,AAA Traffic Management,Access Gateway

Symphony Theme for Citrix NetScaler

View Full Album

Overview:

This theme will update the look and feel of NetScaler AAATM/Access Gateway Enterprise to the Symphony theme which you have likely already seen in Web Interface 5.4 and various other Citrix products. 

Theme Options:

After some initial feedback, some additional options were added to enhance the theme which can be decided on at install time:

Symphony1	Base Symphony Theme
Symphony2	Base Symphony Theme with a Domain Drop-down for login.  Please follow additional required steps in CTX118657
Symphony3	Base Symphony Theme with Google reCaptcha 2nd factor auth (Coming Soon!)
Symphony4	Base Symphony Theme with a Domain Drop-down for login and Google reCaptcha 2nd factor auth. Please follow additional required steps in CTX118657 and my blog post on use of captcha with NetScaler AAA TM. (Coming Soon!)

How to Implement the Symphony Theme:

Andre Perry has assisted with the creation of a very nice shell script that completely automates the installation of the theme.  This script will:

1. Automatically download the latest Symphony theme from the Citrix CDN site to the NetScaler you are implementing the theme on.
2. Extract all files to /var/vpn/customizations directory
3. Modify hard-coded theme build references with specific installed 9.3 build
4. Copy content to /netscaler/ns_gui and /netscaler/portal locations on flash
5. Add entry into nsafter.sh to invoke routine at each system startup to check the installation state and copy custom content to flash

Simply download the installation script and name the file to the theme option that you want to implement.  i.e. – Symphony1.sh.

Note – If the NetScaler or Access gateway you are installing the theme on does not have internet access, you can simply download the appropriate Symphony Theme to the /tmp directory and run the script.

Download the Symphony Theme!

	Symphony automated install script (rename this on save to reflect the theme you are installing – i.e. – Symphony1.sh
	Individual Symphony Theme Packages for Manual Installs:
	Symphony1.gz
	Symphony2.gz

Implementation and Testing Notes:

1. When testing the new skin, be sure to clear your browser cache via Ctrl-F5 if you had previously loaded the default theme
2. If you have the Integrated Cache enabled on the NetScaler, you may also need to flush or invalidate the Access Gateway specific portal content

Additional Information:

• Citrix NetScaler Application Delivery Controller
• Citrix NetScaler Access Gateway
• Google reCaptcha Project

Acknowledgements

Special shout out to Jesse Boehm and team of Techstur.com for the creation of this skin.  Techstur.com is a pioneer in the field of Interface Customization Services, adding clients’ branding elements to the Web interfaces that employees, customers and vendors rely on for application delivery and remote access. Techstur.com customizes many of the most widely-used interfaces, including all versions of Citrix Web Interface, Citrix NetScaler, Citrix Access Gateway, Microsoft Outlook Web App, Microsoft Outlook Web Access, Microsoft RD Web Access, Microsoft Threat Management Gateway 2010 and a number of others. Visit their company website techstur.com

Thanks as well to Jonathan Devenish for the slick streamlined sed command method of find and replace in the shell

And many thanks to Andre Perry for his *nix shell scripting prowess

del.icio.us Tags: Citrix NetScaler,Access Gateway,Symphony Theme

Citrix NetScaler Visio Stencils

This is for those of you out there that have struggled to find a complete set of Microsoft Visio stencils for the Citrix NetScaler product line. Enjoy!

Citrix NetScaler Visio Stencils

How to Automate SQL Mirroring

Starting with SQL 2005 SP1 Standard Edition, you had the ability to setup and configure Database Mirroring.  This was a step up from the previous Data Replication options of Log Shipping (Only available in SQL 2000 Enterprise), and Database Publishing/Subscription and more cost effective than SQL Clustering as a means to provide local redundancy for your databases.  The process of setting up DB Mirroring is a manual one.  You must either use the management Console UI Wizard, or perform the like steps through individual T-SQL Scripts.  There is currently no way to configure SQL initially to create Mirrors of your databases that you create by inputting the name of another SQL Server somewhere.

At one of my previous jobs, I was tasked with automating this for a Hosting Environment where the creation of Databases was dynamic and part of a sign-up process for a SaaS web application. Since many databases could be created throughout a given day, it was desired to have a means to automatically create redundancy for those databases via SQL Mirroring.  So I starting investigating how this could be done.  The individual steps were easily scriptable to setup mirring.  It was the creation of the Database event that I needed to have visibility on since databases could be created from the SQL Server Management Console, the Web Application Managed Code, or the Administration Management Console for the web application.  The logical place for this was on the SQL Server.  I had worked with Triggers before so I decided to start looking there.

Regular DML triggers would not work since those fire in response to UPDATE, INSERT, or DELETE statements on a table or view. DDL Triggers however, fire in response to a variety of Data Definition Language (DDL) events. These events primarily correspond to Transact-SQL statements that start with the keywords CREATE, ALTER, and DROP.   This was obviously a perfect fit for the solution as I needed to have visibility over CREATE DATABASE events.  Now I was ready.

Prerequisites and Preliminary Steps

• Setup and Configure a Secondary SQL Server (Should be identical to source server – version, service pack, and license type)
• Enable TCP/IP protocol for SQL Server in Connections
• Setup a Network Share for Backup ad Restore operations – something like \\dfsroot\sqlbackup
• Enable use of xp_cmdshell stored procedure (Surface Area Configuration Tool or Facet if using SQL 2008)

It is unfortunately not possible to just create a trigger to perform all the actions you desire – I tried that first :-).  So this method involves the creation of 4 components – an event sink table, a workload stored procedure, a Data Definition Language (DDL) Trigger, and a SQL Server Agent Job which executes the aforementioned stored procedure on a desired interval.

Script 1 – Create an Event Sink Table
This table will hold the names and events of the SQL events where a CREATE database action occurred. The event type is really no necessary since we are ony interested in the Database Name

USE [master]
GO
/****** Object:  Table [dbo].[new_db_table]    Script Date: 05/19/2008 15:26:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[new_db_table](
[name] [nvarchar](100) NULL,
[event] [nvarchar](100) NULL
) ON [PRIMARY]

Script 2 – Create the Automation Stored Procedure
This is the main script that does the job of checking the actions table, performing the initial backupsIn SQL 2005, there is no database event for an attach database action even though in reality this is a CREATE_DATABASE event.  This is resolved in SQL 2008 which will cause the trigger to add an entry in the actions table for both ATTACH and CREATE actions.

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_auto_mirror_config]    Script Date: 07/10/2008 10:56:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

— ============================================================================
— Author:        <Jeff Sani,jeffs@sitemasher.com>
— Create date: <4/9/2008>
— Description:    <This stored proc automates the configuration of db mirroring>
— Syntax:      <exec sp_auto_mirror_config>
— =============================================================================

CREATE proc [dbo].[sp_auto_mirror_config]
as
declare @dbname sysname, @bckstmt nvarchar(500), @cmd varchar(250), @bupath varchar(100)
declare @mirrorsp nvarchar(100), @mirrorsql nvarchar(500), @altersql nvarchar(250)
declare @primarysrvr nvarchar(50), @mirrorsrvr nvarchar(50), @witnesssrvr nvarchar(50), @domain nvarchar(50)

–set your sql server and backup paths here
set @bupath = ‘\\sql1\sqlbackup’
set @primarysrvr = ‘sql1’
set @mirrorsrvr = ‘sql2’
set @domain = ‘.staging.local’
set @witnesssrvr = ‘smres2’
set @mirrorsp = @mirrorsrvr + ‘.master.dbo.sp_executesql ‘

begin
if (select count(*) from new_db_table where event = ‘CREATE_DATABASE’) > 0
begin
create table #userdbs (name sysname)
insert into #userdbs select name from new_db_table
declare cdb cursor for select name from #userdbs
open cdb
fetch cdb into @dbname
while @@fetch_status = 0
begin

–Check to make sure that Auto_Close and Auto_Shrink DB Properties are correct and that Recovery is Full
set     @altersql = ‘alter database ‘ + char(91) + @dbname + char(93) + ‘ set AUTO_CLOSE off’
exec (@altersql)

set     @altersql = ‘alter database ‘ + char(91) + @dbname + char(93) + ‘ set AUTO_SHRINK on’
exec (@altersql)

set     @altersql = ‘alter database ‘ + char(91) + @dbname + char(93) + ‘ set RECOVERY full’
exec (@altersql)

–perform initial database backup
set @bckstmt = ‘backup database ‘ + char(91) + @dbname + char(93)+ ‘ to ‘ +
‘disk = N’ + char(39) + @bupath  + ‘\’ + @dbname + ‘.bak’ + char(39)
exec (@bckstmt)

–perform initial database log backup
set @bckstmt = ‘backUp log ‘ + char(91) + @dbname + char(93)+ ‘ to ‘ +
‘disk = N’ + char(39)  + @bupath  + ‘\’ + @dbname + ‘_log.bak’ + char(39)
exec (@bckstmt)

–perform database restore on linked remote mirror sql server
set @bckstmt = ‘restore database ‘ + char(91) + @dbname + char(93) + ‘ from ‘ +
‘Disk = N’ + char(39)  + @bupath  + ‘\’ + @dbname + ‘.bak’ + char(39) + ‘ with norecovery, replace’
exec @mirrorsp @bckstmt

–perform database log restore on linked remote mirror sql server
set @bckstmt = ‘restore log ‘ + char(91) + @dbname + char(93) + ‘ from ‘ +
‘Disk = N’ + char(39)  + @bupath  + ‘\’ + @dbname + ‘_log.bak’ + char(39) + ‘ with norecovery, replace’
exec @mirrorsp @bckstmt

–Initiate the mirroring on The Mirror server:
set     @mirrorsql = ‘alter database ‘ + char(91) + @dbname + char(93) + ‘ set partner= N’+ char(39) + ‘TCP://’ + @primarysrvr + @domain + ‘:5022’ + char(39)
exec @mirrorsp @mirrorsql

–Initiate the mirroring on The Primary server:
set @mirrorsql = ‘alter database ‘ + char(91) + @dbname + char(93) + ‘ set partner= N’+ char(39) + ‘TCP://’ + @mirrorsrvr + @domain + ‘:5022’ + char(39)
exec (@mirrorsql)

–Enable the mirroring session on the Witness server:
set     @mirrorsql = ‘alter database ‘ + char(91) + @dbname + char(93) + ‘ set witness= N’+ char(39) + ‘TCP://’ + @witnesssrvr + @domain + ‘:5022’ + char(39)
exec (@mirrorsql)

–cleanup
delete from new_db_table where name = @dbname
set @cmd = ‘del ‘ + @bupath  + ‘\’ + @dbname + ‘.bak’
exec xp_cmdshell @cmd
set @cmd = ‘del ‘ + @bupath  + ‘\’ + @dbname + ‘_log.bak’
exec xp_cmdshell @cmd
fetch cdb into @dbname
end
close cdb
deallocate cdb
drop table #userdbs
end
end

Script 3 – Create the Trigger
This will fire when an event occurs that matches CREATE_DATABASE and will populate the event sink table with the DB name.

/****** Object:  DdlTrigger [trg_MirrorDDL]    Script Date: 05/19/2008 15:31:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [trg_MirrorDDL]
ON ALL SERVER
FOR CREATE_DATABASE
AS
BEGIN
SET NOCOUNT ON;

DECLARE @data XML;
DECLARE @eventType sysname;
DECLARE @dbname varchar(100);
DECLARE @mirrorsql varchar(500);

SET @data = EVENTDATA();
SET @eventType = @data.value(‘(/EVENT_INSTANCE/EventType)[1]’, ‘sysname’);
SET @dbname = @data.value(‘(/EVENT_INSTANCE/ DatabaseName)[1]’, ‘sysname’);

–Add to new_db_table
Insert new_db_table(name,event) select @dbname,@eventType where not exists (select * from new_db_table where name = @dbname);
END

GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [trg_MirrorDDL] ON ALL SERVER

Script 4 – Create the SQL Agent SVC Job
Main purpose of this job is to monitor the even sink for new entries.  I had thought about having a tigger on the vent sink table, but you might not want to have the automation be on-demand so I thought a job which governed the execution of the workload stored procedure, would be better.

USE [msdb]
GO
/****** Object:  Job [Mirroring Agent]    Script Date: 05/19/2008 15:32:54 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 05/19/2008 15:32:54 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N’Mirroring Agent’,
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N’No description available.’,
@category_name=N'[Uncategorized (Local)]’,
@owner_login_name=N’SMNET\administrator’, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Run Mirroring Stored Procedure]    Script Date: 05/19/2008 15:32:55 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Run Mirroring Stored Procedure’,
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N’TSQL’,
@command=N’USE [master]
GO

DECLARE    @return_value int

EXEC    @return_value = [dbo].[sp_auto_mirror_config]

SELECT    ”Return Value” = @return_value

GO’,
@database_name=N’master’,
@flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N’Mirror Agent Schedule’,
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=5,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20080410,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

There may very well be a more eloquent way of doing this, but this method does work. Some other relevant info – While there is a really high limit of 32K databases that you can create on one SQL server, you would never want to do this as it would become a management nightmare.  Further, you will reach a limit on the host at which mirroring (and really any feature such as replicaiton that uses TCP socket connections) will cease to function.  I found this number to be 100.  Having multiple instances on the same box does not help as this does not decrease the number of sockets consumed per database for mirroring or replication.  Microsoft support recommends no more than 50 databases per server in a mirrored configuration.  So if you are provisioning for the masses, make sure you have enought physical or virtual SQL server instances to accommdate the number of customers you plan on supporting.  Hope this helps!

Server Name Indication and SSL VPN

The Problem

Many People are unfamiliar with Server Name Indication (SNI) despite having been introduced as an extension to the TLS protocol back in 2005. In a nutshell, when client computer browsers or SSL based VPNs are negotiating encryption with a server, there is no information which can be gleaned by the server in order to determine which virtual host the client is actually requesting.  This is due to the fact that the hostname of the subsequent request is contained in the encrypted header which would not be visible until after the received data was decrypted as it made its way down the stack.  This is problematic with respect to virtual hosting since each server or appliance can serve many hosts through the same address.  If it is desired to secure the data of that host through SSL, then a 1:1 mapping of hostname to IP address is currently required.

Client: (TLS Handshake) Hello, I support XYZ Encryption.
Server: (TLS Handshake) Hi There, Here is my Public Certificate, and lets use this encryption algorithm.
Client: (TLS Handshake) Sounds good to me.
Client: (Encrypted) HTTP Request
Server: (Encrypted) HTTP Reply

What about ‘STARTTLS’ or TLS ‘Upgrade’ in HTTP/1.1?

STARTTLS is another standard which is commonly used by protocols such as SMTP, POP, IMAP, and LDAP.  Back in the day, it was common practice to have parallel secure ports for most protocols.  For example,  with SMTP, POP, IMAP, and LDAP, and HTTP you have 25/465 110/995 145/993 389/636, and 80/443 respectively. The idea of  STARTTLS was born when the IETF which governs internet assigned numbers and ports decided back in 1997 at some meeting that the issuing of paralell “secure” ports for all protcols should be depricated.   With STARTTLS, when the connection to the server host is established, the client sends a plantext command with the virtual host name.  This has enough information for the server to decide which certificate to offer for the SSL/TLS handshake.

Client: (TLS Handshake) Hello, I support XYZ Encryption.

Client: (Cleartext) I am using server ‘access.mycompany.com‘

Server: (Cleartext) By The Way, I also support TLS Encryptionn.

Client: (Cleartext) Lets use Encryption, aka ‘STARTTLS’.

Client: (TLS Handshake) Hello, I support XYZ Encryption.

Server: (TLS Handshake) Hi There, Here is my Public Certificate, and lets use this encryption algorithm.

Client: (TLS Handshake) Sounds good to me.

Client & Server: (Encrypted) Exchange Data

A similar method for web browsers, and SSL VPN clients was derived in the HTTP/1.1 specification and is called TLS Upgrade. HTTP/1.1 TLS Upgrade method can be applied to upgrade an open HTTP connection. In a nutshell, the client would include this in a request:

GET http://access.mycompany.com/securestuff HTTP/1.1
Host: access.mycompany.com
Upgrade: TLS/1.0
Connection: Upgrade

The server in turn might respond with:

HTTP/1.1 101 Switching Protocols
Upgrade: TLS/1.0, HTTP/1.1
Connection: Upgrade

The main benefit with these methods are that you can have both naked and secure traffic traversing through the same  port.  Main problems to this and likely why these methods have not been adopted are that all methods would require that any proxy servers in between the client and server also support this method.  A proxy server that did not acknowledge it or perhaps strips the command (could also happen on a legacy firewall), would potentially present a man-in-the-middle attack.  A lesser issue might be that you have a user perception issue as there is a certain familiarity with port 443 being the “secure” port.  On the server end of things, you would also need to have the unsecure port open for the application in question which may not be the case.

The Solution

An extension to SSL/TLS called Server Name Indication (SNI) addresses this issue by sending the name of the virtual host as part of the SSL/TLS negotiation. This enables the server to bind the correct virtual host early and present the browser with the certificate containing a CN matching that in the SNI header.  This method also has far fewer complications associated with it as compared to TLS Upgrade or STARTTLS.  The SNI extension is described in gross detail here. With SNI, you would have a sequence like:

Client: (TLS Handshake) Hello, I support XYZ Encryption, and I am trying to connect to
‘access.mycompany.com‘.
Server: (TLS Handshake) Hi There, Here is my Public Certificate, and lets use this encryption algorithm.
Client: (TLS Handshake) Sounds good to me.
Client: (Encrypted) HTTP Request
Server: (Encrypted) HTTP Reply

But Don’t Browser’s and Servers need to support this extension in order for this to work?

Yup, that is the idea.  As with any RFC, extension, or modification you have to have adoption by the software developers, and hardware vendors which in turn are driven by the adoption or request of the technology by the IT community.  The latter is only done through education and practical application examples which is one of my main drivers for writing this blog post.  At the time of this writing, there are no known Remote Access Appliances which support this RFC extension.  Below is the list of known browsers, servers, and SSL application libraries which do support the SNI extension:

Browsers

• Mozilla Firefox 2.0 or later
• Opera 8.0 or later (the TLS 1.1 protocol must be enabled)
• Internet Explorer 7 (Vista, not XP) or later
• Google Chrome
• Safari 3.2.1 Mac OS X 10.5.6

Servers

• Apache with mod_gnutls or mod_ssl
• Cherokee if compiled with TLS support
• New versions of lighttpd 1.4.x and 1.5.x
• Nginx with an accompanying OpenSSL built with SNI support
• OS X 10.5.6

Libraries

• Mozilla NSS
• OpenSSL
  • 0.9.8f – not compiled in by default, can be compiled in with config option ‘–enable-tlsext’.
  • Unreleased 0.9.9 is likely to include SNI compiled in by default.
• GNU TLS

Unsupported Operating Systems Browsers, and Libraries

The following combinations do not support SNI.

• Windows XP and Internet Explorer 6 or 7
• Konqueror/KDE in any version
• Microsoft Internet Information Server IIS
• Sun Java System Web Server
• Microsoft.Net
• Sun Java JSEE

What SNI could add to SSL-based VPN Solutions?

So what does this mean with respect to Remote Access Solutions such as Citrix Access Gateway, F5 Firepass, or Juniper Secure Access remote access solutions?  The benefits of adopting support for SNI are wide an varying, but here is my first pass at a few:

• Since the SNI would be presented to the access appliance before the SSL negotiation finalized, specific SSL policies such as supported cipher suites, could be bound to the session.   This would be useful where you needed to meet more stringent security requirements such as FIPS level 1/2 for specific hosts, or where you had a client application which used a specific type of encryption that you needed to utilize.
• As cloud computing is becoming more prevalent, service providers are going to need a means to offer customers secure access to their applications and content.  Since many cloud services are based on anycast addresses (floating IP), CNAME records, and also servicing 1000’s of users, a 1:1 option for customer:IP is not practical or possible. SNI presents a cheap, workable alternative to having no secure offering.
• Enterprises who wish to publicly expose their intranet or line of business applications securely may want to do so through a remote access appliance, but not want to allocate multiple public IP addresses.
• Businesses who have only been allocated a single IP address and are using Port Address Translation (PAT) to serve up multiple applications.  This is actually pretty common since many businesses are provisioned with ADSL which uses DHCP assign IP addresses. Most companies use a remote access device as an all-in-one solution for outbound RNAT, inbound VPN, and line of business applications, and firewall.

I hope to see one of these vendors include support for this little-known, but extremely-practical extension to the TLS protocol in the near future.  If you manage to find your way to this post, and you are an IT professional, I would love to hear your comments.

Saving Your SQL Server Connections in MSSMS

So one of the most frustrating things I noticed when I started using SQL back in the day was that you had to connect to each SQL server individually.  This can be rather annoying when you are managing multiple SQL server farms.  So a new feature of the Microsoft SQL Server Management Studio (MSSMS) that I discovered through curiosity was the concept of “Registered Servers”.  Basically, as the name infers, these are SQL servers that you have setup connections to with the appropriate authentication type which you register with the MSSMS.   You can even create groups of these servers so that you can logically separate them by function or location.

Configuring Registered Servers and Groups

To access Registered Servers, you simply click on the View Menu and select Registered Servers. This will open another tab view next to Object Explorer which opens by default when you launch the MSSMS.

From here you are presented with the option to connect to Database Engines, Reporting Services, Analysis Services, etc.  Obviously we are talking about SQL Servers here so you want to look at Database Engines.  In the tree, you will see the Default Local Server Group.  You are not restricted to use this group and can make your own logical groups as you see fit.  The one recommendation I can make is to create groups of SQL Servers that you desire to open simultaneously. 

Once you create the groups, you will add or “Register” SQL database engines into each group.  Simply clicking on “New Server Registration” will take you to the dialog where you specify the SQL Server Name, IP address, or Server Name\Instance Name,  and the authentication required to connect to that server.

Since with Windows Authentication, the authentication token is inherited from the Logged On user, there is no need to save the credentials and no option to.  However if you choose SQL Authentication, and also desire to streamline opening all the connections simultaneously, you will need to select the option to “Remember password” which will be presented to you if you do choose the SQL Authentication type.

Opening Connections Simultaneously

Ok, So you have added all the logical groups and servers that you desire.  So what is the trick to opening these in Object Explorer?  This is actually quite simple.  All you need to do is Switch to the Registered Servers view once you open the MSSMS.  Close the initial dialog which will prompt you for connection information once the studio is loaded (This occurs due to the fact the default view is Object Explorer and there are no connections open  – at the time of this writing, there is unfortunately no way to change this or configure as an option).

Once you are at the Registred Server View, you can right-click the group of servers you desire to open connections to and select “Object Explorer” from the context menu.  This will switch you back to Object Explorer view in the MSSMS, automatically open connections to all the servers in that particular group, and expand the Object tree.

Conclusion

So while this is not totally streamlined, the method presents a better alternative to having to open each connection individually.  I am hoping that in the future the MSSMS development team will at least provide a means to open Registered Server Groups from the initial start-up dialog so that you have one less step in the process. It would also be nice to be able to highlght multiple Server Objects in the Object Explorer and be able to dynamically create a Registered Server Group from those selections.  Enjoy!