SQL Server 2016 Reporting Services Accessibility

Last weekend I spent some time creating very basic tabular reports in SSRS 2016 to see how they handle accessibility, WCAG and Section 508 issues. It looks like things are largely unchanged since the first SQL 2008 Service Pack which introduced the AccessibleTablix property to the various render options (See this prior post). Now you will want to add the AccessibleTablix item to your HTML4, HTML5 and MHTML renderers. You will get tags linking the detail cells to the header but you will still be lacking a few of the items needed to pass a WCAG audit with flying colors (e.g. TH tags in the header row).

In my testing on the Chrome browser, the HTML 5 renderer seems to be the default used by SSRS. I assume the HTML 4 renderer still exists purely for backwards compatibility.

If you are wondering why Microsoft has not addressed accessibility better, it is probably a simple matter of priorities. When I ask people from other parts of the country (outside the DC region) about Section 508 they mostly have never heard of it. It’s similar with some of the security stuff we all have to deal with (e.g. FISMA).  Unless and until it becomes a financial problem for Microsoft (i.e. big sales are being lost because of this), we will not likely see this change. Accessibility is of great importance to anyone working with the US Federal or EU governments (including Microsoft), so I hope future versions will include improvements.

SQL Server 2016 Editions

Just a quick update now that we know that SQL Server 2016 is being released on June 1.

Joey Dantoni has helpfully parsed many pages of Microsoft verbiage to let us know that all the features I was playing with in my blog posts last year are going to be Standard Edition features. Many of you have been asking me which Edition you would need for temporal and dynamic data masking and I cynically assumed they would be Enterprise features. Microsoft has decided to be awesome and prove me wrong.

You may have heard that SQL Server 2016 finally adds some much needed updates to Reporting Services, including HTML 5 rendering (that’s right, the reports will now work on pretty much any device). But I am willing to bet that Section 508 and other accessibility needs were not addressed. I will post more on that as I learn more about the product.

What does the end of support for older .NET frameworks mean for SQL Server?

On January 12, 2016 support for many older versions of the .NET Framework ends.

Since the support policy links the .NET support to the OS, then if you are running a supported version of Windows older .NET Frameworks are supported by association. One would imagine that the RTM Windows Server 2016 will not be supporting .NET 3.5 SP1. With the installer for SQL 2016 requiring .NET 3.5 SP1, you might ask yourself, “how can Microsoft be requiring a piece of software that is reaching end of life?”

In the only place SQL Server is mentioned on this page, it says that “newer versions” of SQL Server were based on .NET 4. When I installed SQL 2016 on Windows Server 2016 today it required me to add the .NET 3.5 SP1 feature before the installation would proceed. This makes little sense to me, so if anyone can clue me in I will gladly update the information here. With IT shops likely launching a campaign to remove anything earlier than .NET 4.5.x from their networks soon we will need an official response from Microsoft on this.

 

How I Started

I am writing this as a satisfied customer of Jen and Sean McCown’s DBA Roadmap*. They have included tracks describing how they got their start and it made me think it would be good to share my own story.

Our family got a Commodore 128 for Christmas in 1985 and I had been exposed to computers at school starting in 2nd or 3rd grade. In high school I took two semesters of real college classes on Digital Logic Design and Assembly Language. But I did not choose to study CS or MIS  in college. I got two degrees in Environmental Science and German.

In spite of this, I had jobs during college that set me up for a career in IT .  I spent each summer working at my dad’s company doing a lot of IT tasks under the title of “Clerical Specialist.” I worked in Tech Support for an ISP and as the web editor of my college newspaper. I also worked in the Geography department helping the State Climatologist. One of the things I had done was create an admittedly kludgy way of parsing the data from our rooftop weather station and putting it on the World Wide Web.

Based on those experiences I landed a job at  right out of college for a startup that presented weather data from proprietary hardware on the internet.  I moved to the suburbs of Washington, DC for that job and I am still here 17 summers later.  I was working for the help-desk but got involved in Classic ASP and SQL Server 6.5 development after just a few months.

Since the company was just starting out, we had consultants who weren’t quite ready to give up their day jobs. These guys would come in and work for us after they had already finished 8 hours somewhere else. One of those consultants taught me the history of database systems and drilled into my head that the job of everyone involved with the database is to protect the data. He was also not at all shy about emailing book authors or picking up the phone to call someone from a forum who could help him with his issue.

* The DBA Roadmap is on sale this summer at 50% off using the Coupon Code Half2015

SQL 2016 Temporal Tables

Another interesting SQL 2016 feature is temporal tables. When I first had this concept explained to me I thought they had a lot in common with Slowly Changing Dimensions, and they do. There is already support for temporal tracking in PostgreSQL and Oracle, and SQL 2016 has it now as of CTP 2.2. Here is some stuff I did just now using this new feature:

CREATE DATABASE BackToTheFuture
USE BackToTheFuture

CREATE TABLE BackToTheFuture
(
ID int NOT NULL PRIMARY KEY CLUSTERED,
FavoriteColor varchar(50) NOT NULL,
SysStartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
SysEndTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON);

INSERT INTO BackToTheFuture VALUES (1, 'Green')
UPDATE BackToTheFuture SET FavoriteColor = 'Blue' WHERE ID = 1

--unfortunately these times are going to have to be hard coded when this demo runs again
--I tried and failed to make the dates dynamic

SELECT * FROM BackToTheFuture --ok so its just blue
SELECT * FROM BackToTheFuture FOR SYSTEM_TIME AS OF '8/11/15 22:16' --nothing I hadnt inserted yet
SELECT * FROM BackToTheFuture FOR SYSTEM_TIME AS OF '8/11/15 22:17' --wait, what?
SELECT *, SysStartTime, SysEndTime FROM BackToTheFuture --oh look time is in GMT
SELECT * FROM BackToTheFuture FOR SYSTEM_TIME AS OF '8/12/15 02:14' --when I still liked green
SELECT * FROM BackToTheFuture FOR SYSTEM_TIME AS OF '8/12/15 02:15' --after I started liking blue
USE master
DROP DATABASE BackToTheFuture

SQL 2016 Dynamic Data Masking

One of the features I am excited about in SQL 2016 is Dynamic Data Masking, but after a few minutes using it I have to say proceed with caution here.  As Rodney Landrum points out, all you have to work around the masking is cast the column to nchar. That means on top of masking you would also want to control access to your data through a proc or view. If I need to do that, couldn’t I just do the masking on my own with a proc or view? Anyway, here are my own noodlings with this new capability:


CREATE DATABASE Andrew
USE Andrew

CREATE TABLE MaskingDemo
(SecretString varchar(50),
NotSoSecretString varchar(50)
)

INSERT INTO MaskingDemo VALUES ('Secret','Not a secret')

DBCC TRACEON(209,219,-1)

ALTER TABLE MaskingDemo ALTER COLUMN SecretString ADD MASKED WITH (FUNCTION='default()')

SELECT * FROM MaskingDemo

CREATE USER Andrew WITHOUT LOGIN
GRANT SELECT ON MaskingDemo To Andrew

SELECT * FROM MaskingDemo; --sa can see the secret

EXECUTE AS USER = 'Andrew';
SELECT * FROM MaskingDemo; --Andrew can not see the secret
REVERT;

EXECUTE AS USER = 'Andrew';
SELECT * FROM MaskingDemo WHERE SecretString='secret'; --But Andrew can go fishing for secrets
REVERT;

EXECUTE AS USER = 'Andrew';
SELECT *, CAST(SecretString AS nchar(50)) FROM MaskingDemo;
REVERT; --oh crap, Andrew can see the secret

--what if i want Andrew to see the secret
GRANT UNMASK TO Andrew

EXECUTE AS USER = 'Andrew';
SELECT * FROM MaskingDemo;
REVERT; --now Andrew can see everything

DENY SELECT ON MaskingDemo TO Andrew

EXECUTE AS USER = 'Andrew';
SELECT * FROM MaskingDemo;
REVERT; --Andrew sees nothing

CREATE VIEW vMaskingDemo AS
SELECT 'xxxxxx' AS Secret, NotSoSecretString FROM MaskingDemo

GRANT SELECT ON vMaskingDemo TO Andrew

EXECUTE AS USER = 'Andrew';
SELECT * FROM vMaskingDemo;
REVERT; --Column is masked

EXECUTE AS USER = 'Andrew';
SELECT CAST(Secret AS nchar(50)), NotSoSecretString FROM vMaskingDemo;
REVERT; --Casting would do nothing

USE master
DROP DATABASE Andrew

CTP 2.3 Updates (9/13/15)

The trace flag is no longer required as Masking is turned on by default. The trace flag will now actual disable it if you run it. Also, the issue where the CAST will displaying the masked data has been fixed.

Epic Life Quest

I am arriving somewhat late to the Epic Life Quest party, but I thought I would create one and I am starting kind of small with things that I think could reasonably be achieved within the next 6 months.

I have already done things and traveled places that many people would be jealous of. This is more about setting goals, and wow it’s much more fun to set your own personal and career goals than to be forced to do it at work for your performance review.

Career Goals

  • Achieve a 100 Reputation on a StackExchange site. I have a colleague with >6000. George Stocker has a billion. I should be able to get to 100, right? I have never gotten far because I need to focus on writing good, focused questions and clear answers. I can’t be like a politician and respond with a rehearsed speech that doesn’t actually answer the question.
  • Blog at least 12x this year. If only I could finish all those drafts, this would be done easily. As a working grad student I missed all of Lost doing school work in the spare bedroom. This goal would take far less time.
  • Attend 3 SQL Saturdays. I am planning to attend Richmond, Baltimore and Philadelphia in the next 6 months.

Health Goals

  • Lose 10% of my body weight. This is a repeatable milestone. At one point I weighed so much less than I do now that no one recognized me. Over the last few years I have gained all that weight back.
  • Exercise 3.5 hours every week. This is based on the idea that I should get 30 minutes a day of strenuous cardio, which is easy with a treadmill in my basement and a million episodes of Star Trek the Next Generation on Amazon Prime.

Financial Goals

  • Reduce my credit card debt to [REDACTED]. This is another repeatable thing. The 2013 government shutdown and a few job changes have left me in a hole that I would rather not be in. I realize I can’t dig my way out overnight, but eventually the redacted number will be a 0.

 Family Goals

  • Play a non-electronic game with my family every week. This is always so much fun and getting the whole family around the table for an hour after dinner should not be so hard.
  • Help Jennifer learn programming. My wife is looking to re-invent herself in a technology-oriented career after watching the traditional paper government publishing business seriously shrink in our current budget environment. She is attending PyCon next month and has already gone to a few code training events for women. There is even an Indie GoGo campaign to help her and others pay for the travel to Montreal.

Recent Achievements

Things I have already achieved in the last 6 months that have been super helpful:

  • I started using LastPass. I did this in response to the PASS password snafu and it has changed my life. So much peace of mind and now I look forward to creating yet another online account. Would you believe I already have 94 sites in there?
  • I signed up for CleanTalk on this blog.  It’s amazing how quickly the spam about Coach Bags accumulates if you leave WordPress alone.
  • Volunteered at SQL Saturday DC. I moved so many tables and my back hurt so much at the end of the day, but it felt good to be contributing to a great event. The obvious next step would be to present at a SQL Saturday (Look I wrote that down, are you happy now?).

Things to Keep Up

  • GTD and Inbox Zero, adding the higher altitudes so I can keep my sights on longer term goals
  • Using my ScanSnap more and eliminating more paper

Section 508 and SSRS

Section 508 is a US Government standard for making technology more accessible to people with disabilities. It’s a must for public-facing government websites, and for companies that do business with the federal government.

One of the trickiest things in accessibility is tables. Imagine you have a 12 column wide table that goes for 50 rows.  Someone using a screen reader to have that information read to them needs context for each of the columns in the subsequent rows. You have to supply that context through metadata, linking each cell back to the header.

Microsoft began supporting basic table accessibility in SSRS when it issued two patches to the HTML4 and MHTML rendering assemblies. On SQL 2008 R2, you have these patches at version 10.50.1753 and higher. On SQL 2012 & 2014 you are all set, just heed the following note.

Important Note: Accessible rendering is not done by default. You must modify the RsReportServer.config to set AccessibleTablix=true for each render option (e.g. HTML4 and MHTML). See the KB articles for more details on how to locate and modify the file. This will work for Report Manager if you’re using native mode, but it won’t work if you’re using the Report Viewer control in a custom .NET application. For custom .NET applications, you need set the option programatically:

this.rdlViewer.InteractiveDeviceInfos.Add("AccessibleTablix", "true");

I am not 100% sure how to set this option for SSRS SharePoint integrated mode, but I would be guess in that case the RsReportServer.config setting would be picked up there.

Here is a look at how the HTML renders differently with and without AccessibleTablix=true:

Regular HTML
Without AccessibleTablix set to true, no id or headers tags
Accessible HTML
With AccessibleTablix, headers tags in each cell link back the id of the header cell

While setting AccessibleTablix will give you the links between header and cell, it will not render the header row using <th> rather than <td>. Many 508 testing programs, such as the Wave Toolbar for Firefox, will ding you for that.

It appears that MSFT hasn’t really added much to SSRS since SQL 2008 R2 except for better support for Chrome and iOS. So SSRS HTML4 and MHTML renderings are pretty much the same in 2014 and 2012 as they were in 2008 R2.  Therefore it seems unlikely that SSRS HTML4 reports will ever be fully Section 508 compliant.

The Voluntary Product Accessibility Templates (VPATS) that Microsoft has created for the various SQL Server versions support the notion that SSRS Section 508 support is only provided at a basic, limited level.

Building a new PC

I mentioned earlier that I have hilariously outdated hardware at home and I finally decided to do something about that this month.

Considering that I tend to keep my computers a very, very long time I decided it was worth over-researching my plan.  I started by reading Glenn Berry’s SQL 2014 Workstation blog post about 500 times.  I also looked at lots of things on Newegg and Micro Center’s websites and eventually decided that even with sales tax Micro Center was the way to go for everything because of all the bundle pricing.

There’s a few things to update in the Glenn Berry post.  This post was largely recycled from an earlier post, so at this point you don’t want a Z87 chipset motherboard anymore.  In fact, those motherboards are what are  on the clearance racks at Micro Center right now.  You also probably don’t want the Cooler Master HAF 912 case because it doesn’t have USB 3.0 ports on the front.

This machine replaced a Dell Dimension workstation with a Pentium D 820 processor I bought in March 2007.  It’s definitely one of those, “Why didn’t I do this sooner?” moments.  I bought the cheapest thing I could find, and it came with Vista, but could not actually run it. (IMHO, allowing PC manufacturers to place Vista stickers on machines with 512 MB of RAM was a big part of the Vista failure.  But at the time there was concern that price sensitive consumers would not make the upgrade if the machines were too expensive.)  I downgraded to XP and later went to Windows 7 64-bit after maxing the RAM at 4 GB.

Here are the major components and their costs:

I used an existing Samsung EVO SSD that I was not really getting the full impact of from the Pentium D.  For the non-system files I’m using a 7200 RPM rotational disk.

This leaves 2 slots for a future update to 32 GB of RAM, and plenty of internal slots for adding future storage.  On the Windows Experience Index the system gets full scores on all facets except graphics because I am relying on the on-chip GPU.

My ancient MacPro remains, but recently got an SSD for the system disk.  A 2-socket dual-core Xeon system is still pretty decent in 2014.