Random Security Trap 001

The downside of having been around for a while is that you (hopefully) have learned something. When new features are released it can be easy to stumble right over the obvious. Here is a tale of one such bloodied nose. ūüėČ

The Setup: There is a physical server (Box 1) running Windows Server 2008 R2. On Box 1 is installed SQL Server 2008 R2 (SQL 1) as a default instance. A second physical server (Box 2) is also running Windows Server 2008 R2; Box 2 also has installed SQL Server 2008 R2 (SQL 2) as a default instance. Both boxes are on the same domain. There is a Windows user account configured as a Windows Domain Administrator (User).

The Twist: User can log into Box 1 and can log into SQL 2 from Box 1, but cannot log into SQL 1 from Box 1. User can log into Box 2 and can log into SQL 1 from Box 2, but cannot log into SQL 2 from Box 2. Furthermore, when connected to SQL, no databases are accessible and very few SQL artifacts are visible, despite User being a domain administrator.

The Solution: It turns out that this unexpected circumstance was the result of the overlap of two “new” features; user account control (UAC) in Windows and the default security model in SQL Server.

Issue 1 – UAC: Windows Vista and Windows Server 2008 were the first Windows operating systems to introduce UAC, a rule set that, simply put, prevents administrative¬†privileges¬†from being inherited by default. Windows Server 2008 R2 also includes UAC. Thus, even though User is logged into Box 1 as an administrator, when launching SQL Server Management Studio (SSMS) normally, it runs with the permissions of a normal user. Hence, trying to login to SQL 1 from Box 1 will not work because User does not have a defined SQL login (recall that User is connected to Box 1 as a member of the Windows Domain Administrators group). To work within the UAC constraints, User simply needs to¬†explicitly¬†run SSMS¬†as an administrator and thus specify that administrative permissions are inherited by SSMS. This will enable SQL login… but User will still only have access as a public user — not as a member of the sysadmin role.¬†(Note that User can connect to SQL 2 from Box 1 without explicitly running SSMS as an administrator because credentials are passed externally despite UAC; credentials are only restricted by default on the machine into which the user is connected.)

Issue 2 Рdefault SQL security: Beginning with SQL Server 2008, the BUILTIN\Administrators group is no longer added as a SQL sysadmin by default. This change is part of an intentional effort by Microsoft to separate SQL administrative privilege from Windows administrative privilege. Thus, if a Windows administrator attempts to login to SQL, they will be granted access as a public user only. To address this issue, create a SQL login for Windows administrators and assign it the sysadmin role (or whichever role is desired). Keep in mind that a Windows administrator SQL sysadmin login was not created by default for a reason; consider the security implications carefully before creating such a login.

To enable User to have the expected experience requires addressing both issues. Issue two without one will expand visibility when logged in to SQL, but will not allow connection to the local SQL instance; issue one without two will allow connection to the local SQL instance, but User will still have very little SQL access as a public user.

The Lesson: Stay current on application features by version. It is almost always the case that subsequent software versions offer a superset of features over the prior versions; but here we have a case where two separate applications had a “regression” of¬†privilege¬†to enhance security, and the experience resulting from the application overlap was unusual-looking indeed.

Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: