Category Archives: Security

Random Security Trap 002

For post number two in my “Random Security Trap” series, let’s take a look at an issue that may cost you a few minutes of head-scratching if you haven’t seen it before. First, the business setup: you need to enable a group of users to have read-only access to a database, but also have controlled insert permissions to one table. A common way to solve this is to create a database user, add it to the database db_datareader role, create a stored procedure to manage the insert operation, and then grant execute permission on the stored procedure to the database user. And, in most cases, that solution works just fine.

However… what if the insert operation is a little more complicated and uses dynamic SQL to perform the insert? Well, in that case, you may fall right into the trap. Let’s demonstrate.

We’ll begin by creating a SQL login to the server and then using that login for a database user with read-only permission.

-- set context to the master database
USE [master];
GO

-- if the ReadOnlyLogin already exists, drop it
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'ReadOnlyLogin')
    DROP LOGIN ReadOnlyLogin;

-- create a ReadOnlyLogin login
CREATE LOGIN ReadOnlyLogin
WITH
    PASSWORD = N'ReadOnly'
    , DEFAULT_DATABASE = devDB
    , CHECK_EXPIRATION = OFF
    , CHECK_POLICY = OFF
    ;

-- set context to the working database
USE devDB;
GO

-- if the ReadOnlyUser database user already exists, drop it
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'ReadOnlyUser')
    DROP USER ReadOnlyUser;

-- create a ReadOnlyUser database user for the ReadOnlyLogin login
CREATE USER ReadOnlyUser
FOR LOGIN ReadOnlyLogin
WITH DEFAULT_SCHEMA = dbo;

-- add the database user to the datareader role
EXECUTE sp_addrolemember
    @rolename = N'db_datareader'
    , @membername = N'ReadOnlyUser'
    ;

Okay. Now that we have our login and database user created, let’s create a table so we can demonstrate controlling insert operations. Since we’ll end up simulating logging in as the ReadOnlyLogin, we’ll use the table to capture information about our testing operations.

-- if the sample table exists, drop it
IF OBJECT_ID(N'tbl', N'U') IS NOT NULL DROP TABLE tbl;

-- create the sample table
CREATE TABLE tbl (
    ID INT IDENTITY(1,1) NOT NULL
    , DateStamp DATETIME2(3) CONSTRAINT DF_tbl_DateStamp DEFAULT(GETDATE())
    , [Login] SYSNAME
    , ContextLogin SYSNAME
    , [User] SYSNAME
    , IsDynamic BIT
    );

With our table in place, let’s create a procedure to insert data to the table. We’ll capture information about the login and execution context as a means of confirming the execution state related to our business case.

-- if the insert procedure already exists, drop it
IF OBJECT_ID(N'uspInsert', N'P') IS NOT NULL DROP PROCEDURE uspInsert;
GO

-- create the demo procedure
CREATE PROCEDURE uspInsert (
    @bolUseDynamic BIT = 0
    )
AS
BEGIN
    IF @bolUseDynamic = 1
    BEGIN
        -- insert a record via dynamic SQL
        DECLARE @sql NVARCHAR(MAX);
        SET @sql = N'INSERT INTO tbl ([Login], ContextLogin, [User], IsDynamic)
            VALUES (ORIGINAL_LOGIN(), SUSER_NAME(), USER_NAME(), 1);'
        ;
        EXECUTE (@sql);
    END
    ELSE
        -- insert a record via static SQL
        INSERT INTO tbl([Login], ContextLogin, [User], IsDynamic)
        VALUES (ORIGINAL_LOGIN(), SUSER_NAME(), USER_NAME(), 0)
        ;
END
GO

The last step in our setup is to grant execute permission to the database user.

-- grant the ReadOnlyUser permission to execute the procedure
GRANT EXECUTE ON uspInsert TO ReadOnlyUser;

Okay. Everything is in place. Let’s run a test! We’ll run the first test under our own login simply to confirm that the procedure works and captures the information we need to see.

-- test the procedure
EXECUTE uspInsert @bolUseDynamic = 0;
EXECUTE uspInsert @bolUseDynamic = 1;

-- confirm results
SELECT * FROM tbl;

So far, so good. Next, let’s run the same two procedure executions under the context of the ReadOnlyLogin. To achieve this, we could reconnect under the other login. Alternately, let’s just impersonate it instead!

-- test the procedure
EXECUTE AS LOGIN = 'ReadOnlyLogin';
EXECUTE uspInsert @bolUseDynamic = 0;
EXECUTE uspInsert @bolUseDynamic = 1;
REVERT;

-- confirm results
SELECT * FROM tbl;

As you can see, the second procedure execution failed when executed under the context of the ReadOnlyLogin:

Msg 229, Level 14, State 5, Line 1
The INSERT permission was denied on the object 'tbl', database 'devDB', schema 'dbo'.

Why did it fail? Because even though the dynamic SQL is executed within a stored procedure on which the ReadOnlyLogin is granted permission, dynamic SQL executes within a separate session. The login impersonation is passed through, but the sql is no longer strictly within the context of the procedure. And outside of the procedure, the ReadOnlyLogin does not have permission to insert into the “tbl” table.

As ever, let’s be sure to clean up after ourselves.

-- drop objects
IF OBJECT_ID(N'uspInsert', N'P') IS NOT NULL DROP PROCEDURE uspInsert;
IF OBJECT_ID(N'tbl', N'U') IS NOT NULL DROP TABLE tbl;
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'ReadOnlyUser')
    DROP USER ReadOnlyUser;
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'ReadOnlyLogin')
    DROP LOGIN ReadOnlyLogin;

For additional information on pitfalls of dynamic SQL related to execution context, give this post a read.

Advertisements

Double-Hop Linked Servers

Having made use of linked servers in a few previous posts, let us take a closer look at a common problem with them: double-hop authentication failure when using Windows Authentication. This issue presents itself most commonly when a user connects to a SQL server, but then is unable to use a linked server connection to connect to a second SQL server, even though the user has the necessary permissions to make the connection. This applies even when the linked server is configured to pass the login’s current security context to the target server.

If, rather than passing through Windows Authentication, remote login credentials are specifically defined in the linked server, then the connection should succeed, but… you also have a potentially large security hole! It could be as bad as an entirely un-secured username and password if the credentials were specified in a provider string; to investigate:

-- Select information about linked servers
SELECT name, data_source, provider_string
FROM sys.servers
WHERE server_id <> 0
;

Or it could be something potentially even more difficult to manage, as might be the case if you have many logins that need to be controlled so as NOT to have access to the linked server. Very quickly it could get messy indeed.

But, back to using Windows Authentication with pass-through login context….

Consider the scenarios shown in the graphic below to better understand the problem.

Single-hop works, double-hop does not

Single-hop works, double-hop does not

The first row shows an end user can connect to a SQL Server (SQL A) using SQL Server Management Studio (SSMS) running on their local machine.

On the second row we see that the same user can also connect in the same way to a second SQL Server (SQL B).

Furthermore, on row three we see that the user can connect to SQL A via a terminal and use a linked server connection that is defined on SQL A to connect to SQL B. So far so good.

However, when the user tries a “double-hop” by connecting from a local machine to SQL A and then from SQL A to SQL B through the linked server… then the connection fails. Generally the user will receive error 18456: “login failed for anonymous logon”.

The problem, in this case, is actually rather easy to understand and resolve. What happens in our final example is that SQL A does not pass credentials on to SQL B, and hence SQL B rejects the connection of what it sees as an anonymous user.

Why doesn’t SQL A pass through the credentials? Because the service account underwhich SQL A is running was not configured to do so!

Trust AD user for delegation

Trust the SQL Server service account for delegation

There is a “Delegation” tab on the active directory (AD) user account properties whereby this behavior can be specified. The default is “Do not trust this user for delegation”, which means do not pass credentials through. But there is also a setting to “Trust this user for delegation to any service (Kerberos only)” which will allow credentials to be forwarded. You could also get more granular and specify individual services to be trusted.

Double-hop works with trusted delegation

Double-hop works with trusted delegation

I’ve too often heard “you can’t use Windows Authentication with linked servers” as an excuse for SQL login proliferation. Let’s put a stop to that, if we can. SQL logins are necessary in certain cases, but in general we’d like as few as possible for both ease of management across the enterprise as well as enhanced security management.

Whole-Value Substitution

In a previous post we discussed the fundamentals of encryption and made reference to why it is important to use an additional authenticator: to prevent a security breach through a technique called “whole-value substitution”. In whole-value subsitution, the attacker does not actually decrypt any sensitive information, but they exchange one encrypted value for another based on inference of the underlying data.

Let’s walk through an example to understand the concept better.

Demonstration Preparation: For demonstration purposes, let’s create a table to store personnel information such as employee name and salary. We will store the salary in encrypted form only. We will assume there already exists a valid symmetric key named “keyPersonnel” that is encrypted by a certificate named “certPersonnel” (if you are not familiar with creating a symmetric key or certificate, refer to my post on encryption fundamentals). For this example we will assume our database is named “DB” and will be working within the “dbo” schema.

-- Set context to the DB database
USE DB;
GO

-- Open the symmetric key
OPEN SYMMETRIC KEY keyPersonnel
    DECRYPTION BY CERTIFICATE certPersonnel
;

-- If the temp table we will create already exists, drop it
IF OBJECT_ID('tempdb..#TBL') IS NOT NULL
    DROP TABLE #TBL
;

-- Create a temp table
CREATE TABLE #TBL (
    ID INT IDENTITY(1,1) NOT NULL
    , Name VARCHAR(20) NOT NULL
    , Salary VARBINARY(128) NULL)
;

Demonstration: To demonstrate whole-value substitution, we will walk through two scenarios. In the first we encrypt the salary information without using an authenticator and then show how a whole-value substitution attack works. In the second scenario we will use an authenticator with encryption and then demonstrate how whole-value substitution fails.

-- =============================================================
-- Scenario 1 : no authenticator, whole-value substitution works
-- =============================================================

-- Populate the temp table with sample data
--   Note that our DBA makes less money than the CEO

INSERT INTO #TBL (Name, Salary)
VALUES
    ('DBA', ENCRYPTBYKEY(
        KEY_GUID('keyPersonnel')
        , CONVERT(VARBINARY, $50000)))
    , ('CEO', ENCRYPTBYKEY(
        KEY_GUID('keyPersonnel')
        , CONVERT(VARBINARY, $250000)))
;

-- Select and decrypt the data to confirm the values
SELECT
    ID
    , Name
    , Salary
    , 'DecryptedSalary' = CONVERT(MONEY, DECRYPTBYKEY(Salary))
FROM #TBL
;

As you recall, whole-value substitution involves replacing one encrypted value for another. In our example, an intruder viewing the data may not have been able to decrypt the salary and see the actual value of the DBA’s and the CEO’s salary. However, the intruder would be able to see the names. And, from the names alone, the intruder could assume that the CEO’s salary was higher. Thus the intruder could attempt to replace the DBA’s encrypted salary with the CEO’s encrypted salary, without ever knowing the value of either. Let’s see if it works….

-- Perform the whole-value substitution and replace the
--   DBA's salary with the CEO's salary

UPDATE #TBL
SET Salary = (
    SELECT Salary
    FROM #TBL
    WHERE Name = 'CEO')
WHERE Name = 'DBA'
;

-- Select and decrypt the data to validate the results
--   after the whole-value substitution

SELECT
    ID
    , Name
    , Salary
    , 'DecryptedSalary' = CONVERT(MONEY, DECRYPTBYKEY(Salary))
FROM #TBL
;

The whole-value substitution attack was successful: the DBA’s salary is now the same as the CEO’s, and the intruder did it without ever having decrypted the sensitive data.

Now let’s take a look at how simply adding an authenticator to the encryption function can prevent this type of attack.

-- ===============================================================
-- Scenario 2 : authenticator used, whole-value substitution fails
-- ===============================================================

-- Reset the temp table
TRUNCATE TABLE #TBL;

-- Repopulate the temp table
--   First insert the employee names

INSERT INTO #TBL (Name)
VALUES
    ('DBA')
    , ('CEO')
;

Now we add and encrypt the employee’s salary information, and we use the employee’s unique identifier to authenticate the encryption. In other words, a unique piece of information associated with the employee (their ID) is included in the encryption key used to secure their salary information. The result is that the encrypted value should only be decryptable when it is associated with the employee for whom it was encrypted; i.e. whole-value substitution fails.

-- Add the employee salary information, encrypting it
--   with the employee's unique identifier as an authenticator

UPDATE #TBL
SET Salary =
    CASE Name
        WHEN 'DBA' THEN ENCRYPTBYKEY(
            KEY_GUID('keyPersonnel')
            , CONVERT(VARBINARY, $50000)
            , 1 -- TRUE, we will use an authenticator
            , CONVERT(VARBINARY, ID)) -- the ID column is the authenticator
        WHEN 'CEO' THEN ENCRYPTBYKEY(
            KEY_GUID('keyPersonnel')
            , CONVERT(VARBINARY, $250000)
            , 1 -- TRUE, we will use an authenticator
            , CONVERT(VARBINARY, ID)) -- the ID column is the authenticator
        END
;

First, let’s confirm that the data was encrypted and that we can decrypt it.

-- Select the employees and decrypt the salary
SELECT
    ID
    , Name
    , Salary
    , 'DecryptedSalary' = CONVERT(MONEY, DECRYPTBYKEY(
        Salary
        , 1
        , CONVERT(VARBINARY, ID)))
FROM #TBL
;

Now let’s confirm that we actually do need to use the authenticator in order to decrypt the data. Let’s try decrypting it without the authenticator and see what happens.

-- If encrypted with authenticator, do you really need
--   the authenticator in order to decrypt? (yes!)

SELECT
    ID
    , Name
    , Salary
    , 'DecryptedSalary' = CONVERT(MONEY, DECRYPTBYKEY(Salary))
FROM #TBL
;

While the previous two queries basically confirm that authenticated encryption will prevent a whole-value substitution attack, let’s try it anyway and see if it works.

-- Perform the whole-value substitution and replace the
--   DBA's salary with the CEO's salary

UPDATE #TBL
SET Salary = (
    SELECT Salary
    FROM #TBL
    WHERE Name = 'CEO')
WHERE Name = 'DBA'
;

-- Query the data to see if the salary information was updated
SELECT
    ID
    , Name
    , Salary
    , 'DecryptedSalary' = CONVERT(MONEY, DECRYPTBYKEY(
        Salary
        , 1
        , CONVERT(VARBINARY, ID)))
FROM #TBL
;

As you see from the previous query, the DBA’s decrypted salary is now NULL. The whole-value substitution attack did not prevent the DBA salary from being updated, but it did produce a result that is far more easily identified than an actual substituted salary would have been. We have minimized the damage potential.

The next two statements are not strictly necessary: simply ending your session is sufficient. However, why not get in the habit of actively cleaning up after yourself? It shows positive control of your environment and could pay dividends in a situation when it really matters.

-- Drop the temp table
IF OBJECT_ID('tempdb..#TBL') IS NOT NULL
    DROP TABLE #TBL
;

-- Close the symmetric key
CLOSE SYMMETRIC KEY keyPersonnel;

Encryption Fundamentals

Database security is like security in general: every step you take offers a degree of protection, but no single step nor combination of steps can ever offer “complete” or “guaranteed” security. The goal is simply to make it sufficiently difficult to extract sensitive information so as to deter intrusion or delay it long enough to detect and actively counter it.

Think of securing your house: first you install doors, then you add a lock and key, then you might add a second lock with a second key, then a security system, then a guard, then an armed guard, and so on until you are satisfied with the security measures. However, even with all of that in place, all it takes is someone with two keys, the security system code, and enough force to overpower your armed guard and then your house is compromised. Bottom line: no security system is without weakness, but the more measures you have in place the more it deters and prevents would-be attackers, hence the more secure it is.

Of course, the balance is the cost and, in the case of databases, the performance overhead. Adding highly secure encryption to every aspect and byte of data in a database can be done, but it results in a correspondingly high amount of time and resources (CPU processing, etc.) to interface with the database with authorized credentials. Sometimes that level of security is warranted; and sometimes it is not.

SQL Server has built-in security measures that are robust, configurable, and offer multiple layers of security. One of the most basic implementations is securing a column of data with a symmetric key. In a future post I will discuss more details on the different types of security, but, as a basic example, let’s walk through this common implementation.

Demonstration Preparation: For demonstration purposes, let’s create a table to store personnel information such as name and social security number (national id). We will store the social security number in plain-text as well as in encrypted form in this example to make it easier to confirm our underlying data. For this example we will assume our database is named “DB” and will be working within the “dbo” schema.

-- Set context to the DB database
USE DB;
GO

-- If the personnel table already exists, drop it
IF OBJECT_ID('DB.dbo.tblPersonnel') IS NOT NULL
    DROP TABLE DB.dbo.tblPersonnel
;

-- Create the personnel table
CREATE TABLE DB.dbo.tblPersonnel (
    ID INT IDENTITY(1,1) NOT NULL
    , Name NVARCHAR(50) NOT NULL
    , SocialSecurityNumber CHAR(11) NOT NULL
    , SocialSecurityNumberEncrypted VARBINARY(128) NULL
    , CONSTRAINT PK_tblPersonnel PRIMARY KEY CLUSTERED (ID))
;

-- Insert a record into the table
INSERT INTO DB.dbo.tblPersonnel (
    Name
    , SocialSecurityNumber)
VALUES (
    'Somebody'
    , '123-45-6789')
;

-- Select the records from the table to confirm values
SELECT
    ID
    , Name
    , SocialSecurityNumber
    , SocialSecurityNumberEncrypted
FROM DB.dbo.tblPersonnel
;

Solution: The basic encryption hierarchy that we will use goes from the service master key to the database master key to a database security certificate to a database symmetric key to the encrypted data column itself. If it sounds complicated, that is good. Recall that security measures are (and should be!) multi-level, with each level providing one more hurdle to overcome for those that want unauthorized access.

Let’s walk through the levels one at a time.

The service master key is created automatically by SQL Server and is created for the operating system account under which the SQL Server service runs. This is an important point because, if the system account for SQL Server is changed, the existing service master key will not work. There are, of course, ways to correct that, but just be aware that once the service master key is created, changes to the SQL Server service account will have an impact on security. Run the following query to see your server’s service master key.

-- View the symmetric keys for the master database
SELECT *
FROM master.sys.symmetric_keys
;

You’ll see the service master key listed with the name ##MS_ServiceMasterKey## and a symmetric_key_id of 102. If your server does not have a service master key, don’t worry. It will be created automatically after we create a database master key in the next step.

The database master key is unique to each database on a server and will not exist until it is specifically created. The database master key is encrypted by the service master key. Thus, as mentioned previously, if the service master key doesn’t already exist, creating a database master key will automatically result in a service master key being created. Let’s take a look and see if there is a database master key in our database. Run the following query to see all of the symmetric keys for a database and look for a name of ##MS_DatabaseMasterKey## and a symmetric_key_id of 101.

-- View the symmetric keys for the DB database
SELECT *
FROM DB.sys.symmetric_keys
;

Now let’s create a database master key if one does not already exist.

-- If there is no database master key, create one
--   Please use a more secure password than in the
--   example; remember, this is just a SAMPLE!

IF NOT EXISTS (
    SELECT *
    FROM DB.sys.symmetric_keys
    WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@$$word'
;

One of the most often overlooked tasks is to backup your encryption keys. If you lose your credentials or need to perform disaster recovery, you’ll be glad you have it. But don’t just create a backup and leave it on the production server. Archive your backup keys on a separate server, preferably under equal or greater security and in a geographically separate area. Remember — this is part of good disaster recovery and security practice and should be treated with appropriate seriousness! In the example below we will export the database master key to a folder on our M: drive and the backup will be encrypted with a password distinct from the password on the key itself. More levels of security! Do you see a pattern developing here? 😉

-- Backup your database master key!
BACKUP MASTER KEY TO FILE = 'M:\MasterKeys\DB_MasterKey'
    ENCRYPTION BY PASSWORD = 'P@$$word2'
;

Next comes our certificate. A certificate exists at the database level and can be encrypted by either the database master key or a password. For this example we will encrypt with the database master key.

-- Create a certificate for the data to be encrypted
--   By not specifying a password, SQL knows to use the
--   database master key to encrypt the certificate
CREATE CERTIFICATE certPersonnel
    WITH SUBJECT = 'Personnel Records'
;

-- Backup your certificate!
BACKUP CERTIFICATE certPersonnel
    TO FILE = 'M:\Certificates\DB_certPersonnel'
;

Now we’ll use our certificate to encrypt a symmetric key. A symmetric key can be used to encrypt as well as decrypt data, compared to an asymmetric key pair in which one key (private) can encrypt and decrypt and a second key (public) can only decrypt. The primary benefit of using a symmetric key is speed: it is a relatively quick process to encrypt/decrypt with a symmetric key and thus it is suitable for use in high-volume data of moderate sensitivity.

-- Create a symmetric key using the certificate for encryption
CREATE SYMMETRIC KEY keyPersonnel
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE certPersonnel
;

Finally, the setup is complete and we are ready to encrypt a column of data. We’ll start by opening our symmetric key. When a key is opened it stays open for the session unless it is specifically closed. The following statement will open our symmetric key; it could be wrapped in an IF statement to check if the key is already open, but that is not necessary as, if it is already open, an additional OPEN statement will have no effect.

-- Open our personnel symmetric key
OPEN SYMMETRIC KEY keyPersonnel
    DECRYPTION BY CERTIFICATE certPersonnel
;

Now for the fun part… let’s encrypt some data! We’ll use the ENCRYPTBYKEY function and, for this example, we will NOT use the optional authenticator. Generally I would always use an additional authenticator to prevent a whole-value substitution attack; but, for the sake of simplicity in this example, we will omit the authenticator. However, refer to a future post to understand why the optional authentictor is so important.

-- Encrypt the data
--   Note: this encryption is unsecured against whole-value
--   substitution; see follow-up post for more information

UPDATE DB.dbo.tblPersonnel
SET SocialSecurityNumberEncrypted = ENCRYPTBYKEY(
    KEY_GUID('keyPersonnel')
    , SocialSecurityNumber)
;

Now let’s see what the data looks like. In the following query, we’ll also include the syntax to decrypt the encrypted column.

-- Select the data and decrypt the encrypted column
SELECT
    ID
    , Name
    , SocialSecurityNumber
    , SocialSecurityNumberEncrypted
    , SocialSecurityNumberDecrypted =
        CONVERT(CHAR, DECRYPTBYKEY(SocialSecurityNumberEncrypted))
FROM DB.dbo.tblPersonnel
;

From the query results you can see both the original social security number, the encrypted social security number, and the decrypted version of the encrypted social security number. If all went well, you’ll find the decrypted and un-encrypted values are the same. If the encrypted value or decrypted value is null, we missed something along the way.

Also note that if the symmetric key is not open within the session, the decryption will silently fail (in other words, the decryption will return a NULL value, but no error message).

-- Close the symmetric key; it will close automatically
--   when the session ends, but cleaning up after yourself
--   is a good habit to have!

CLOSE SYMMETRIC KEY keyPersonnel;

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.