Ways to keep track of updates for Microsoft SQL Server

Keeping track of updates for Microsoft SQL Server can be a time consuming task. A business/enterprise using Microsoft SQL Server will typically use more than one version of the product and each of them has its own somewhat random schedule of service packs, cumulative updates and hotfixes. To add to all this we have all those ‘fun’ moments when Microsoft will release some update that will cause serious problems with existing software so now we have to know about that aspect of it as well.

So what is a SQL Server dba supposed to do? Where do we go to find all this information in reasonable time?

For quite a while now the resource that was best known for this type of data was the site below:

http://sqlserverbuilds.blogspot.com/

It’s a great site but it has some issues. For example, we don’t know who’s behind it and for how long they might keep maintaining it. There is also no good way to be alerted of changes so now we’d need to use third-party page monitoring services for that (such as http://www.changedetection.com/ or http://watchthatpage.com/).

Other similar pages exist elsewhere on the web – here are two maintained by the SQL Sentry team for the latest versions of SQL Server:

http://blogs.sqlsentry.com/team-posts/latest-builds-sql-server-2012/ – for SQL Server 2012
http://blogs.sqlsentry.com/team-posts/latest-builds-sql-server-2014/ – for SQL Server 2014

Even Microsoft apparently has such a page but it’s somewhat hidden in its vast network of tech sites:

https://technet.microsoft.com/en-us/sqlserver/ff803383.aspx – Update Center for Microsoft SQL Server

In May 2015 a newcomer site joined the party – this one is updated and maintained by the awesome folks at Brent Ozar Unlimited:

http://sqlserverupdates.com/

So far this last one seems to be the most useful one – it adds information about support end dates and it also allows one to monitor it for changes via email or RSS – very nice.

There you have it – a few ways to keep on top of Microsoft SQL Server updates and remove some of the pain from that process.

A better way to handle role permissions in ASP.NET Identity – IsInRole vs. HasPermission

ASP.NET Identity is currently the main framework used to add authentication and authorization capabilities to an ASP.NET site. After ASP.NET Identity is integrated with an ASP.NET project it creates a few database tables where relevant user data can be stored. Let’s look at some of these tables:

AspNetUsers – the table where application users are stored
AspNetRoles – this is where we store application roles (you can also think of them as groups)
AspNetUserRoles – a mapping table where we store information about what users belong to what roles

So far, so good. What we get out of the box is a way to create users, create some roles and assign users to roles. This follows the best-practice where we want to eventually assign application permissions to roles instead of individual users.

But how exactly do we handle application permissions given the functionality that ASP.NET Identity provides for us out of the box? Most developers and projects at this point follow the pattern that we’re so used to from other areas in IT – we check if a user belongs to a certain role and if so, we allow that particular operation to proceed. In ASP.NET this is done using the Authorize attribute (allow the operation to proceed if the current user belongs to a certain role):

[Authorize(Roles = "Admin")]
public ActionResult AdministratorsOnly()
{
    return View();
}

What is wrong with this approach? Let’s consider a few scenarios:

Read more

Installation and configuration checklist for Microsoft SQL Server

Below is the checklist I’ve used over the years for installing and configuring Microsoft SQL Server. It is by no means complete and there are certainly scenarios where additional steps will be required. It is intended to be a starting point, enabling somebody to not only standardize SQL Server installations but also to make sure that no critical steps will be missed.

Hopefully others will find it useful as well.

Before SQL Server Installation

  • install and patch the OS (minimum 100 GB for system drive)
  • BIOS, firmware & driver updates
    • check power management settings
      • BIOS power management should be set to OS control or disabled
      • Windows Power Plan set to ‘High Performance’
  • check processor speed using Task Manager and CPU-Z utility (make sure CPU runs at max speed)
  • RAID 1 for OS, logs and backups (but each on a separate volume), RAID 10 for data files
  • format drives for data & logs to use 64 KB allocation unit size (ideal for SQL Server) – full format, not quick
  • test drive performance with CrystalDiskMark & SQLIO / save screenshots/results for future reference
  • configure anti-virus scan software (if any) to not scan any database related files such as MDF, NDF, LDF, BAK or TRN
  • create regular user/domain accounts for each SQL Server component – main SQL service, agent, reporting, analysis, integration (SQLServiceAccount, SQLAgentAccount and so on)
    • for a dev server: probably ok to use just one SQL user account for all SQL services
    • use gpedit.msc to grant appropriate rights to the user for SQL Server Service (Computer Configuration -> Windows Settings -> Security Settings -> Local Policies -> User Rights Management)
      • Perform volume maintenance tasks
      • Lock pages in memory
        • if this is done then set sp_configure ‘max server memory’ to make sure the OS is left with enough memory to function
        • might want to not enable this on a dev server with shared duties (such as a webserver) so we don’t end up in the scenario where SQL Server will starve out the other processes for memory
  • prepare directories (on separate drives): SQLData, SQLLogs, SQLTempDB, SQLBackups
    • folder permissions
      • the install process will properly assign folder permissions to custom folders if they’re all specified initially in the SQL data directories configuration
      • remove “Everyone” (if set) from non-C drives where SQL-related folders will exist
      • for SQL Server main service user account: give full permissions to folder where SQL will be installed and the SQLData, SQLLogs, SQLTempDB, SQLBackups folders
      • for SQL Server agent user account: give full permissions to folder where SQL will be installed and SQLBackups

During SQL Server Installation

  • only install features that are really needed (especially on a production system) – don’t install something “just in case it might be needed it later”
  • use the proper user accounts (created above) for each service
    • set SQL Server Agent to auto start
    • most likely disable the SQL Server Browser service
  • on the Database Engine Configuration tab be sure to set the Data Directories to the custom ones we plan to use (not the defaults where everything is stored on the C drive)

After SQL Server Installation

Time to say goodbye to usernames and passwords for website authentication

We have to admit that our current model of using usernames and passwords for website authentication is utterly broken. It may have been fine back in the days of mainframes when users would have one account to remember but it just does not scale (safely at least) in the current day when the average user probably has tens (if not hundreds) of usernames and passwords to remember.

What do most users do in the face of the ever increasing number of websites that require authentication? They start reusing information – in particular passwords. We should all know that this is a bad idea. The best practice in this area is to have separate and strong passwords for each website account used … but how many people actually do that?

Before we put all the blame on users let’s not forget the websites themselves which are not doing a good job at all protecting all this authentication data. The best practice there would be to properly hash a user’s password in a way that even brute force attacks against that data would not be very successful … but we know that’s not happening. It seems that not a month goes by without some news story informing us that large numbers of user accounts from some website just got dumped online … and passwords were either poorly hashed or not hashed at all (simply stored in plaintext).

Other authentication models exist where some third-party is used to consolidate user accounts and allow users to logon to websites using credentials from that third-party (think OpenID, Facebook, Twitter, Google and so on). The problem with all these is the third-party: can it be trusted? will it protect our information and privacy? do we really want some corporate third-party knowing all the sites we visit simply because those sites rely on its authentication methods?

What we need is a modern authentication system that’s cryptographically secure, easy to use and preferably two-party (involving just the user and the website the user wants to visit).

Enter SQRL – Secure Quick Reliable Login (also see the Wikipedia page). The protocol typically uses a QR code, which provides authentication, where a user identifies anonymously rather than providing a user ID and password. SQRL is the brainchild of Steve Gibson – the host of the long-running and well-respected Security Now podcast. Steve has a lot of information about it at this location – https://www.grc.com/sqrl/sqrl.htm. He also recently did a presentation on SQRL at the DigiCert Security Summit 2014:

We need to start moving in this direction for website authentication because what we have right now is simply not working.