SQL Server 2005: Execution Context

This post is based on an old presentation I gave several years back. A video of the presentation used to be available here, but today I couldn’t get it to work, so I am attempting to make available most of the information from the presentation within this post. Keep in mind that the demo associated with…


Finding information about which account xp_cmdshell is running as

If you ever needed to debug a permission related issue when using xp_cmdshell, you have probably realized that a crucial piece of information is about what particular account xp_cmdshell is executing under. If you are the administrator of the database, you already know the context used by xp_cmdshell, but otherwise you may not have that…


SQL Injection watch blog

I was looking for information on a new SQL injection attack when I stumbled upon this very useful blog: http://s3cwatch.wordpress.com/. It’s worth a look from time to time, to get an idea of what attacks are going on in the wild.


Basic SQL Server Security concepts: ownership, CONTROL, TAKE OWNERSHIP

I realized today that while I have discussed earlier object permissions, I have not gone into the details of object ownership. I want to cover the following here: ownership of objects, how it can be changed, and the relatively new permission CONTROL (introduced in SQL Server 2005). Ownership: This should be pretty clear – the owner of an object is…


SQL Server: Windows Groups, default schemas, and other properties

Exceptions are dangerous because people like to simplify their thinking process using rules, so exceptions always carry the risk of being overlooked. In security, exceptions are a bad thing because they make the model more complex and complex systems can break in more ways than simple systems, thus being harder to analyze and secure. Windows Groups are…


SQL Server 2005: How to debug login failures (18456, anyone?)

In my series of new posts on old topics, I decided to gather today several pieces of information that I think will help in debugging SQL Server login failures. Although most information should remain useful for future versions as well, some of it may become outdated, so I tagged this article as 2005 specific. Login…


How to request features in Microsoft products

I want to address the topic of requesting feature changes in Microsoft products, to point to some tools that can help, and to describe ways to use those tools more effectively. This post is based on my experience working on customer requests while being a member of the Microsoft SQL Server team, but you may…


SQL Server 2005: How to debug errors in code that does encryption

Encryption builtin functions in SQL Server have no known issues and, if used properly, they will produce the expected results. However, if they are used incorrectly, it can be hard to figure out what exactly is the problem, so in this post I am going to collect some hints about what to look for in…


SQL Server 2005: A great post by Aaron Morton about using MARS to access opened keys

Aaron Morton has a very interesting post and demo that show how MARS can be used to access keys temporarily opened by a procedure. This is a must-read for anyone that is interested in implementing custom restrictions around the use of encryption keys. Some time ago, I wrote a post about restricting the use of an…


SQL Server 2005: Why you should not encrypt data with certificates

I often recommended to only encrypt data in SQL Server using symmetric keys and to reserve the use of asymmetric encryption for protection of symmetric keys and for signing. In this post, I will go in more detail about why asymmetric encryption is not appropriate for protecting data. There are three reasons why asymmetric encryption is…