SQL Server 2005: Restoring the backup of a database that uses encryption

I have addressed this topic in previous threads and comments (here, here, and here, for example), both on this blog and on various forums, but it looks like when you need the answer, it can be hard to dig out. So I’m hoping that by placing these steps in a dedicated post, they will become easier to find. When…

12

SQL Server 2005: How to recover when the service master key (SMK) is not accessible

I wrote earlier today a reply on this topic on the public forums, but now that I checked, the reply appears to have got lost, although I still entertain the hope it may only have got delayed and will appear there in 24 hours. Anyway, this is the reason why I prefer to write longer…

8

SQL Server undocumented password hashing builtins: pwdcompare and pwdencrypt

First, I must say that I don’t know why these exist in an undocumented form. They have been around for a long time and a search on their names gets me back pages of hits. Being undocumented means that their actual implementation may change slightly from one version of SQL Server to another, mainly because the…

1

Basic SQL Server Security concepts: SIDs, orphaned users, and loginless users

I am grouping here two topics (orphaned users and loginless users) that are actually very different, but I have often seen confusion between them, so I am covering them together in an attempt to dispel that confusion. In a previous discussion of logins and users, I pointed out that the way a login gets mapped to users…

0

SQL Server 2005: A note about the use of certificates

To avoid any confusion, this post is not about the use of certificates for securing the communication between a client machine and the server; instead, this refers to the use of certificates created via the CREATE CERTIFICATE DDL. I am prompted in writing this post by a recent question I just saw, which I know I answered many…

5

SQL Server 2008: Transparent data encryption feature – a quick overview

I have kept silent on this feature while it was being developed, but as it has now been publicly advertised in various ways (being mentioned here, here, here, and here, for example), I think it is probably time to write a bit about it. Given that my posts so far have covered SQL Server 2005, I’ll point…

4

Security and copy protection

I have been watching the SQL Server Security forum for several years now and there is one question that gets spawned about once a month under different titles. It invariably begins with a request for guidance on how to secure access to a database, which sounds like a reasonable security inquiry, but after a while it becomes…

0

Basic SQL Server Security concepts – ownership chaining: good and evil; schemas

At some point during SQL Server’s history, its designers must have confronted the following problem: how to give someone permission to see parts of a table without giving him any permission on the table? Slices of a table are easily defined using views, so the problem becomes one of giving SELECT permission on a view without granting a SELECT permission on the…

0

Basic SQL Server Security concepts – permissions and special principals: sa, dbo, guest

In a previous post, I talked about the various types of principals in SQL Server. Let’s have a further look in this post at permissions and at some of the hardcoded principals that ship with any installation of SQL Server. Permissions are what allow principals (logins, users, roles, etc) to perform (or not perform) activities in SQL Server. Permissions…

11

Beyond cracking: cybercrime

If you are following the security news, you will not be surprised by what I cover in this post. It’s old news already for most people working in security. But it’s worth discussing this more, to raise awareness. In a nutshell, the idea is that breaking computers is ceasing to be mainly an entertainment form for people whose computers skills…

3