- 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 an exception in the SQL Server security model. You can quickly infer my opinion about them from the above paragraph. At Windows OS level they behave similarly to how roles behave in SQL Server - they are simply containers of permissions and privileges. But bringing them in the SQL Server world creates a hybrid - an entity that can not only be granted permissions, but can also own objects as well - a mix of roles and logins/users. In SQL Server, Windows Groups are a secondary identity with additional capabilities that are traditionally reserved only for primary identities. Suddenly, Windows Groups require handling not seen in any other security system.
From a usability point of view, it is convenient to get access to a system via membership to a Windows Groups, but from a security perspective, the actual user identity needs to be always available and must not be allowed to disappear behind the group anonymity. Imagine having to explain who deleted an important table - "Builtin\Users did it!" This leads to tradeoffs that are either not noticed or that surprise those that pay attention. For example, even though groups can own objects, an object created by someone that connected via a group membership will be owned by a login or user created under the cover - this is called implicit login/user creation and is done so that the identity of the object creator is not lost.
As far as groups work like they work in the Windows OS or like roles work in SQL Server, their behavior is easy to understand. This is because the semantics of permissions allows them to be added together and still make sense. So, if I belong to two different groups and I inherit from them two different sets of permissions, there is no confusion about what permissions I have - I simply have all the GRANT's and DENY's that those groups provide me with.
But the situation gets trickier when we want to endow groups with properties similar to what "real" principals have. The earliest situation consisted of the default language and default database settings that were added to logins. Because groups were supposed to act as logins, a solution was needed for them too. I was not around at the time these options were introduced, but I can see the result: the options were simply added to Windows Groups as well. However, the problem with having these options available for groups is that they really reflect properties and properties, unlike permissions, are not additive. If I am a member of two groups and each has a different default database, which one do I end up connecting to? Having these properties for groups was a bad choice, but now this cannot be fixed because of backward compatibility.
A new problem these days is that schemas were introduced in SQL Server 2005 and with them, a new property was added to users: DEFAULT_SCHEMA. However, the bad choice made earlier was not repeated this time and users mapped to Windows Groups were not allowed to have this property set. This causes problems with the use of Windows Groups as it is indeed desirable to be able to specify default schemas for Windows Groups, but this must be done in an unambiguous way and there is no mechanism allowing this today. Addressing this problem is currently an open issue.
Some take-offs from this discussion:
Windows Groups can simplify management but due to their hybrid nature, they come with some restrictions. If you want to use them to avoid managing a large set of logins and users, then make sure you don't provide them with permissions to create objects - object creation will trigger implicit login/user creation, defeating the initial reason to use them. If you avoid the object creation, there is still the issue of not being able to control the default schema - until a solution is provided, access to data via Windows Group membership needs to be done using schema qualified object names, Finally, Windows Groups are useful today because they provide a way to authenticate to SQL Server and can carry permissions, but they are not yet ready to be used as carriers of properties.
Additional links:
This topic in the SQL Server Security Forum
The customer feedback item requesting the capability of setting a default schema for Windows Groups - you can use this to rate the importance of fixing the default schema issue.
- A SQL Injection attack and search engines
-
A few weeks after my previous posting of a SQL Injection Advisory link, a new SQL Injection attack came up. Here's a post describing it; it also includes other useful links:
http://www.rtraction.com/blog/devit/sql-injection-hack-using-cast.html
A search for the query string "http://1.verynx.cn/w.js" (the quotes are part of the search string) shows that there are still sites infected today.
So, SQL Injection is alive and kicking - no big surprise here. But what may come as a surprise to you, if you're not aware of it yet, is that there is a further vulnerability here: vulnerable sites are discoverable using a search engine - it happens when the SQL Injection results in some link getting inserted in web pages, as is the case in this recent attack. This means another attacker can use a search engine to get a list of vulnerable sites and hack them a second time, for a more devastating effect. This is an instance of Search Engine Hacking - Google Hacking is currently the popular term, but any search engine can be used, not just google. Note that this is not really about hacking the search engine, but about using the search engine for hacking.
Here is more in-depth information on this techique of search engine hacking:
Google Hacking for Penetration Testers
Google Hacking page on Wikipedia
Google Hacking Database
Also note that search engine hacking goes beyond SQL Injection attacks - the sources mentioned above contain more examples of searching for different vulnerabilities. If you're the administrator of a Web site, you cannot afford to ignore this technique.
- New Microsoft Security Advisory on SQL Injection
-
This came up yesterday: http://www.microsoft.com/technet/security/advisory/954462.mspx. It has good information and links.
- A discussion of password authentication schemes
-
I have talked in the past about how passwords for SQL logins are protected in SQL Server (see this post). I would like to describe this scheme in a more generic way and compare it with the alternative of encrypting the passwords, because I have seen people wondering which method they should use.
First, what is authentication? Authentication is the process we go through to verify the identity of a user. It should not be confused with authorization, which is about what actions we allow an already identified user to do. Authorization happens after authentication and relies on its result.
Authentication schemes are based on information falling in three large categories: what the user knows (passwords, PINs, birth date, mother's maiden name), what the user has (id card, badge, debit card, credit card, smartcard), and what the user is (photograph, retina scan, fingerprint, voice recognition). Sometimes combinations are used - a debit card requires knowing its PIN and an identification card will have some biometrics information like a photograph and maybe height and weight information as well.
The goal of password authentication is therefore to verify a user's identity based on a password associated with him that only he should know. If more people know the password, then we cannot really authenticate the user - we're authenticating the group of people that know the password. The good thing about a password is that we can commit it to memory and we can later recall it when we need it - the fact that it resides only in memory is appealing from a security point of view because others can't yet read our minds reliably. The bad thing about a password is that we have to commit it to memory and we may fail to recall it later when we need it - this is an inconvenience that may not have any security impact by itself, but it usually leads to one because, to avoid the situation, we'll usually pick something easy to remember which might also be easy to guess. Writing it down on a piece of paper is not that bad as long as we keep that piece of paper secure. A password is also vulnerable during the moment it leaves our mind to be presented for authentication - by typing it on a keyboard or by saying it on the telephone. Note that all these observations apply to any authentication method based on what the user knows - in fact, the other ones I listed have more problems than passwords - PINs are shorter, and the birth date and the mother's maiden name are not only known to one person.
So let's look at how we can perform password authentication.
Method 1 - Store: Store the user password somewhere in the system. Whenever the user provides his password, we compare it with the stored one and, if they match, we have successfully authenticated him.
Problems: One, but big: the password is stored in clear, meaning that whoever has access to the storage place can learn the passwords stored there. This is a big problem because it means the password can be accessed by bypassing the application and just examining the disk; it will be there if the disk breaks down and gets replaced, for example. How can we improve this? A particularly bad direction would be to try to come up with our own obfuscation scheme for not storing the passwords in clear. We'll not go there, so let's look at proven technologies. We can use an encryption algorithm that has already proved its usefulness in the field.
Method 2 - Store encrypted: Have some encryption key created for the purpose of encrypting passwords. We store the user password encrypted with this key and whenever the user will provide us with his password, we'll decrypt the stored one and compare them.
Problems: A couple smaller ones: the first problem is about finding a way to protect the encryption key, the second problem is that anybody with access to the encryption key will still have access to all the passwords. Finding a way to protect the encryption key is tricky - as I mentioned in other posts, encryption doesn't really solve the problem of protecting information - it just changes the problem of protecting a lot of information into a problem of protecting a tiny bit of information - the encryption key. Whether we address this issue or not, there is no denying that it adds some complexity to the solution. I also don't like the idea that administrators of the system could know my password - yes they could send it back to me if I forget it, but they could also leak it or just use it to find out more about how I choose my passwords. So, I am not happy with this solution - how can we improve it? Well, here comes hashing! Hashing works one-way - you can get a hash from some information, but you cannot retrieve that information from the hash.
Method 3 - Store hash: Choose a hashing algorithm and store the hash of the user password. When the user provides his password, hash it and compare the result against the stored hash.
Problems: One tiny one: Hash collisions - it is possible for two different passwords to have the same hash value, but cryptographic hash algorithms make this very unlikely. Also, there's no way we can find out a password if the user forgets it - we'll have to instead change his password and provide him with the new password and a method of changing it. Of course, before doing this we have to figure out another way of authenticating him - Web applications usually solve this problem by sending the password to the email account associated with the user.
What did we gain with this method? The password is not stored in clear, we don't have any encryption keys to manage, and we have better guarantees that the system and its administrators are not able to retrieve the passwords from the stored hashes. The administrators can still monitor the system when you are providing the password, but that is more difficult than just decrypting the encrypted passwords and is an issue that all these password authentication methods have anyway.
But it's not over yet! We can make this better. To understand why, let's look at how we can attack this method and whether we can thwart some attacks.
A simple way to attempt to retrieve passwords from hashes would be to build a large collection of possible passwords - we won't be able to have all possible passwords in it, but we can collect a lot of likely passwords and we can grow this collection over time. Once we have this collection, we can hash all these passwords using the same algorithm that the application uses (we can reverse engineer it by examining the application or we can simply use the application itself to generate the hashes by creating accounts for all those potential passwords). Whatever the method, we can end up with a collection of hashes and corresponding passwords - we now have a dictionary that we can use to attempt to break other hashes by simply looking them up in it. Of course, we won't be able to break any password, but if we break one and it happens to be an administrator account, it's enough.
This sounds bad, right? You're bound to have some users with poor passwords, maybe even administrators (sa with empty password was a bane of many SQL Server installations) and this attack will break those passwords. It's important to remember that a prerequisite for this attack is to have access to the password hashes in the first place, which is not trivial to get except for the administrators. But this is still not very nice and there is actually a little trick we can do that will make a dictionary attack much more costly.
Method 4 - Store salted hash: The trick we'll use is to add some randomness to the hash generation process - this is called "salting" the hash. What we're going to do is to randomly generate a piece of information called the "salt" for each password that is set for a user. This salt will be combined with the password and we'll store both the salt and the hash in the system. Whenever the user will submit a password, we'll lookup the salt and hash, we'll combine the salt with the submitted password, we'll hash the result and we'll compare it against the stored hash.
How can we combine the salt with the password? One simple way is to prepend the salt to the password - this will work well with most hashing algorithms. Appending the salt may not be as good - it may end up affecting only part of the hash, depending on how the hash algorithm works. If you want to get creative here, look for more information about the hashing algorithm in a cryptography book.
How does this help with the dictionary attack? Well, because for each hash we have a salt value, a single dictionary won't cut it anymore - the attacker would have to build a dictionary for each salt value - and that is expensive because hashing is not a cheap operation.
How large should a salt be? If you make it too small, 1 byte, for example, it is still feasible to build 256 dictionaries to cover all possible values of that salt. So salts should be picked up to be larger values. For example, a 4 byte salt would require more than 4 billion dictionaries - this is the value used in SQL Server 2005.
- Security in a nutshell
-
Here's an attempt to succintly describe why achieving security is difficult:
The engineer wants to implement a program P that allows users to perform action A.
The hacker looks at program P and wonders how can he use it to perform actions other than A.
The security guy wants to implement a program P that allows users to perform action A and only action A.
Some observations based on this description:
- defining A precisely is harder than it may sound
- it can be non-trivial to implement P so that it performs A
- if P fails to accomplish A, it will likely accomplish something else than A
- there is a cascading effect that increases the probability of not being able to achieve the security guy's goal
- An interesting book: Scott Rosenberg's "Dreaming in Code"
-
If you are wondering why software is hard to make or if you know why, but you would like to see how others deal with the issue, you may enjoy reading Scott Rosenberg's book, "Dreaming in Code". I picked it this weekend and while I didn't finish it yet, I enjoyed what I read so far enough to decide to spread the word about it. The book requires no deep understanding of computers from its audience, so anyone should be able to pick it up and enjoy it.
- 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 failures can be broadly divided in two categories: failures to connect to SQL Server and failures to authenticate with SQL Server - I will refer to the first as protocol failures and to the second as security failures. Security failures can only occur after successfully establishing connection to the database server. Most security failures result in the 18456 error, which will always be logged by SQL Server. This means that to determine if you are likely hitting a security error, you should check the current ERRORLOG file to see if an 18456 error was logged there - the absence of such an error usually indicates the problem is elsewhere - you either failed connecting or there was some application error that was reported as a login error.
The ERRORLOG file is a text file located in the LOG folder, usually situated in the same place as the DATA folder containing the system databases. You can always find the actual location by checking the following registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.OOO\CPE\ErrorDumpDir, where OOO should be replaced by the proper database instance number.
For protocol errors, I recommend two resources:
MSDN Forum for the SQL Server Protocols Team
SQL Server Protocols Team Blog
In what follows, I will focus more on discussing the 18456 error. This error can be raised for a variety of reasons and the most important information in debugging it is to know the state value for the error. This is not obvious because the error state is always displayed to clients with a value of 1, to prevent information disclosure to unauthorized parties. The real error state value, however, is always logged in the ERRORLOG file, as in the examples below:
2008-01-28 10:34:19.02 Logon Error: 18456, Severity: 14, State: 8.
2008-01-28 10:34:19.02 Logon Login failed for user 'sa'. [CLIENT: <local machine>]
...
2008-02-14 18:23:10.10 Logon Error: 18456, Severity: 14, State: 11.
2008-02-14 18:23:10.10 Logon Login failed for user 'Domain\User'. [CLIENT: <local machine>]
Some of the most obvious states are described in this classic 18456 post from the Protocols Team Blog, which pretty much already covered this topic. The comments to the post uncover a few more states. With this knowledge, the first failure above is easily explained as being due to the use of an incorrect password, while the second failure is due to the account not having been granted access to the server.
Knowledge of the error state is crucial in finding out the possible reason of an 18456 failure, so it should be the first piece of information you gather before reporting the problem.
For security issues, the main resources are:
MSDN Forum for SQL Server Security Team
SQL Server Security Team Blog
So, when dealing with some sort of login failure, try to determine where it happened: in the application, in establishing a connection to the server, or in authenticating with the server. Checking the SQL Server error log can help you determine if you reached the server or not, and it can also provide you with additional information about the error, which is not available elsewhere. If you are hitting an 18456 error and the state is described in the Protocols post, then you should have a good idea of what the problem is; if the state is not listed, then you should post the state when you are reporting the error - I suggest to try the SQL Server Security Forum first.
- SQL Server: Password policy FAQ
-
I am starting this post to collect frequent Q&A related to password policy. I plan to keep updating the post if anything new is worth adding to it. Note that this FAQ does not cover SQL Server Compact Edition. Also note that BOL stands for Books OnLine.
Q: What is the SQL Server password policy feature?
A: Password policy is a feature introduced in SQL Server 2005 for the purpose of strengthening SQL Authentication by having it enforce the same password policies that Windows is set to enforce. This means password policy is specific to SQL logins. A password policy determines what passwords are acceptable and also when they expire or whether the account should be locked out after a number of unsuccessful login attempts due to the use of incorrect passwords.
While the primary use of password policy is to strengthen SQL Authentication, password policy is also enforced whenever setting a new password with the encryption features introduced in SQL Server 2005 - this enforcement only refers to password strength; there is no expiration/lock out enforcement for these passwords. This enforcement cannot be disabled.
Q: On what versions of SQL Server is the password policy available?
A: The password policy enforcement is done using a new API introduced in Windows 2003: the NetValidatePasswordPolicy API. Thus, password policy is only available on versions of the Microsoft Windows OS starting with the 2003 version. Note that if this API ever gets backported to older OSs, then SQL Server should automatically start using it. The best way to determine if something changed about the availability of this feature on older OSs is to check the API page for any updates.
Note that on older OSs, SQL Server does perform a few basic password complexity checks, as described at the end of this password policy BOL article. However, this is all that is available on older OSs - there are no lock out or expiration features.
Password policy is not restricted based on SQL Server edition.
Q: How do I use the password policy feature?
A: If you are creating a new login using CREATE LOGIN, you can use two clauses related to password policy: CHECK_POLICY and CHECK_EXPIRATION. If they are not specified, the default for CHECK_POLICY is ON and for CHECK_EXPIRATION is OFF. These options can be changed at a later time using ALTER LOGIN. CHECK_POLICY governs the bulk of password policy enforcement related to password strength and lock out. CHECK_EXPIRATION separately covers the enforcement of password expiration, which was considered too disruptive for the rolling out of this feature, to be enabled by default. CHECK_EXPIRATION is also required when using the MUST_CHANGE option, to force a password change on the first login through that account. CHECK EXPIRATION depends on CHECK_POLICY; other dependencies are covered in BOL.
Q: How do I manage this feature using Management Studio?
A: There should be checkboxes in a login properties dialog corresponding to the T-SQL options. A GUI can change more often than T-SQL syntax, so I prefer to describe features using the T-SQL interface. Also, I prefer to use only T-SQL for SQL Server management operations.
Q: How do I check the password policy settings on my system?
A: Execute secpol.msc, then look at Account Policies. If you are on a standalone machine and you are an administrator, you can change these settings. If your machine is joined to a domain, these settings are controlled by the domain administrator.
Q: Why can't I set the policy options for Windows logins in SQL Server?
A: Because Windows logins already benefit from password policy enforcement at the OS level.
Q: I keep getting messages about my password not being acceptable when creating a new login. How do I fix this?
A: The error message will usually indicate what the problem is; for example, it will tell you if the password is too short. To find more details about what restrictions are in place for passwords, you can check the policy settings using the secpol.msc tool, as described in a previous answer. You can also disable the policy enforcement for the login, but this is not recommended as it can make that login more vulnerable to a bruce force attack.
Q: I am running SQL Server on Windows XP/2000, etc. Does this mean I cannot benefit from password policy enforcement?
A: Pretty much, yes. On OSs that don't support the password policy API, SQL Server only performs a tiny number of checks on the password strength - to prevent you, for example, from using an empty password. The actual checks are described in the last part of this password policy BOL article. These checks are governed by the CHECK_POLICY settings, so they can be turned off or on. If you ever hit a password policy error on older OSs than Windows 2003, then you really need to revisit the method you are using to pick passwords.
Q: I am getting a password policy error even though I run on Windows XP/2000. I thought there is no password policy enforcement for these systems. What's going on?
A: You are using really weak passwords. See above answer.
- Can encryption make you more vulnerable?
-
A recent article brings up this question and argues that encrypting data at rest can open the door to a new range of security and usability problems. Speaking only of the security aspects, I both agree and disagree, so I'd like to add a few comments on this topic.
I think that the article makes a very good point that represents a great truth about security: the features you build to protect against attackers could end up being used by the attackers against you - this is the double-edged aspect of security. Once a system is compromised and the attacker gets to control it, he can use its defenses against the lawful users. This is not a new idea, but it is one that can be easily forgotten when adding new security features.
That being said, I don't think that the scenario presented in the article about ransoming data is a new or very interesting scenario. Here are the reasons why:
- An attacker can use his own encryption routines to encrypt the data - it doesn't matter if the compromised system had any encryption capabilities. Malware with the capability to encrypt data has existed for years and cyber-criminals are known to be proficient at using encryption to protect their data. Thus, ransoming is not really a new threat - it just appears new to those that also see encryption as a new technology.
- Ransoming could only work in a database system that lacks a disaster recovery procedure. Data loss is an ever-present threat for databases - even if ransoming would be new, it would be addressed by the same measures that address an old threat - data/key backups.
- The weakest link in ransoming is the ransom collection - that's where the attacker exposes himself - this makes ransoming not very tempting.
- Ransoming is also a bad idea because it tells the system owner that his system has been breached and gives him a chance to close that breach.
- If an attacker can get access to data worth ransoming, it is more easy for him to make money from selling it and keeping his access secret than by attempting to ransom it.
However, I do think that encryption can make you more vulnerable - it can do that by giving people a false sense of security if they think that just by having data encrypted, it becomes secure. I went in a bit more detail about this idea here, but the point is that deploying encryption is not only about encrypting data but about carefully considering how the data will be accessed and about securing that access - sadly, the latter part is where the security of most applications fails. Encryption addresses well certain scenarios such as stealing data at rest, but those scenarios do not necessarily represent how most data is getting compromised today.
- 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 find the information I provide here useful for other Microsoft products than SQL Server.
So, basically, the problem I am discussing here is that you are working on some project using Microsoft technology and you find out that your work might be much easier if only some feature would be available. How can you ask Microsoft to add that feature to their product? One way to do this is to contact technical support, but there are other ways that I want to address here.
Before I start, I should mention that if the problem you are confronting appears to be a security vulnerability, then there is a dedicated site on which you can provide a description of the problem to the Microsoft Security Response Center. Needless to say, security vulnerabilities are addressed with higher priority than the addition of new features and the rest of this post deals with the latter.
Also, while I am talking mainly about requesting features, the steps I present would be similar if you what you need is a bug fix.
Step 1 - Information gathering. Before a feature should be requested, it is important to do a bit of research to find answers to the following questions:
- Does the feature allow you to achieve something that is worth achieving?
- Is the feature absolutely needed or are there existing features that can be used to achieve the same goal with approximately the same effort?
- Has the feature been requested already?
The easiest way to find the answers to these questions is to ask around, and the places you can ask these questions online can be divided in two categories:
- public forums, such as the microsoft.public.* forums or forums hosted by various sites focusing on the use of a specific Microsoft product
- Microsoft forums, such as the MSDN hosted forums at http://forums.microsoft.com/msdn/default.aspx?siteid=1
It is worth keeping in mind the differences between these categories of forums. Each offers specific advantages and posting on the right forum is a great step toward getting a quick and appropriate answer. An advantage of public forums is that they can be used to ask a wider array of questions - for example, you can ask questions related to the interactions between Microsoft products and other products. Microsoft forums have the advantage that they are monitored by members of the product units, but they are usually more focused on discussing specific features of a Microsoft product.
As usual, before posting on any forum, you should take the time to search for existing threads that may already answer your questions. This is good forum etiquette that is expected on most forums and that their frequent contributors will often suggest, because it reduces the redundancy of information in the forum and thus increases its quality.
Once you have the answers to the above questions, several things can happen:
- you may find that the request is not necessary either because it does not really help solving a problem (in security it is often the case that a mechanism can be perceived as improving security when it really does not do so) or because there are other alternatives of solving the problem
- you may find that a request already exists and you may be provided with a way to track its progress or with a status update about the plans for developing and releasing it
- you may find that you may be the first customer to request the feature
If you are in the latter case, the next step is to ask formally for the feature.
Step 2 - Filing a request. Depending on the product, there may be various ways to ask for features. One way could be to just ask a member of the product team that you contacted online or at a professional conference to open an item for the request. But some products allow you to directly open requests online using the MSDN Product Feedback Center. The Feedback Center is a very powerful tool, because the item that you open is directly viewable by the product team. When opening a new feedback item, you should include all the information needed to describe the problem you are facing. You should also update the forum thread that you started at the previous step, to include a link to the feedback report that you opened. This will help you by helping others notice your report so they can vote for it - the more votes your proposal gathers, the higher the chance that it will be assigned a higher priority that will allow it to go ahead of other requests.
While in the SQL Server team, I have been on the receiving end of such requests and I have solved many of these (solving them meaning that I made the required changes to the SQL Server code); I also saw requests for existing features or for features that wouldn't accomplish anything useful, which is why I am emphasizing the importance of the information gathering first step. Some of the features I added were important enough to ship in the next service pack (like some of the features mentioned here), while others were postponed for the next release, but the important thing to note is that in all cases the requests got directly to the development team. In most cases, I also provided feedback back to the customer about the progress made and the plans for releasing the changes. My point here is that this process works better than you may expect. As far as I know, no other company provides a way for customers to directly contact the development team.
What next after filing a request?
Step 3 - Monitoring your request. After you made your request, the next thing to do is to monitor it, to provide additional information if needed, and to check for any developments. Feedback items have a great feature in that they allow a two way communication with the product team. As long as the state of your request does not change, there isn't much that you can do other than making sure that other people that ask for the same thing know about your request and vote for it. Each product will have its own schedule for releasing new features. Service packs usually contain high priority fixes so if your issue is not high priority, you can only expect it to show up in a future release. Bugs have a better chance of being resolved than new features have to be added, because they usually involve less code changes. Also, bugs usually get fixed all the time, while features tend to compete against other features for a larger set of resources and they only get worked on during specific periods in the development cycle. Finally, a problem for which a workaround exists will get a lesser priority than a problem with no workaround.
This is basically it - I hope this information helps you figure out how to make use of the resources I listed. I'll end this post with a summary of all these steps:
- Search to see if your problem has already been discussed. If yes, then join the existing discussion.
- Open a new request if your search has not uncovered one. Provide all information someone would need to understand your problem.
- Track your request, to provide additional information, if needed, and to check for status updates.
Finally, keep in mind that forums are great sources of information, but they don't help that much with tracking feature requests - the best way to do that is offered by sites like the Microsoft Product Feedback Center. So once you have a feedback item opened, make sure that other people having the same problem will vote for your feedback item.
- TSA blog
-
I just noticed that the TSA has started a blog to shed some light on the motivations for their security measures. The blog is here: http://www.tsa.gov/blog/.
- 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 the malfunctioning code.
There are two main types of encryption code related issues that I have seen popping up in forums; they usually are described as "encryption errors", which is not what they really are.
Issue 1: Corruption of encrypted value
This issue happens when the output of encryption is corrupted when stored or passed around by code. This happens because the encryption blob is truncated during a conversion or because the column in which it is stored is shorter or has the ANSI_PADDING option set to OFF. For determining the size of a column that should hold encrypted data, see this post.
Symptom: Decryption of corrupted encryption blob will return NULL.
How to debug this? Print the output of the encryption function, before it is processed in any way - this is the original encrypted blob value. Then also print the blob after each conversion or assignment; if it is inserted into a column, select back the inserted value. Any difference from the original blob value will indicate where the error happened. Most often, you don't even need to do a byte by byte comparison and you can instead just compare the lengths of the blob at different stages - you can get the length using the datalength() builtin.
Issue 2: Incorrect conversion of the decrypted value
This issue happens when encrypting a Unicode string and when later, after decryption, we try to convert the binary decryption output to a non-Unicode value. Or vice-versa. There is nothing wrong with the encryption or decryption in this case, which can be determined by following the debugging steps for Issue 1. It is easy to get this issue because the difference between nvarchar (Unicode) and varchar (ASCII) is the single letter n, which you may fail to type in.
Symptom: Decrypted data is displayed as garbage.
How to debug this? Compare the type of the data you compress and verify that the decompressed blob is appropriately converted back to that type.
- 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 encryption key just for encryption. Aaron's demo goes further and demonstrates a pitfall if one would attempt to restrict the use of an encryption key for specific data decryption. The issue happens because MARS allows interleaving around SELECT statements, and a way to prevent it is to use auto-decryption routines, which open the key in the context of the transaction rather than the session context.
- 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 not suitable for encrypting data:
(1) Asymmetric encryption is much slower than symmetric encryption.
(2) For small data like SSNs and CCNs, asymmetric encryption will result in a blob that will be significantly larger than what you would get from symmetric encryption - about 2 times larger or more.
(3) While for symmetric encryption, the largest data that can be compressed is around 8000 bytes, because of limitations in the SQL Server encryption interfaces, for asymmetric key encryption, the limit is much smaller and is due to limitations of the technology itself. The limits are: a 512 bit RSA key can encrypt up to 53 bytes, a 1024 bit key can encrypt up to 117 bytes, and a 2048 bit key can encrypt up to 245 bytes (reminder: in SQL Server, both certificates and asymmetric keys are wrappers over RSA keys).
So, by using asymmetric encryption to encrypt data, you pay additional time and space costs, and on top of that you are limited about how large a piece of data you can encrypt.
All these points are unrelated to the actual implementation of the algorithms; they are simply derived from the properties of RSA encryption, which is the only form of asymmetric encryption supported in SQL Server 2005.
Related to (3), also see this post and this post, which contain information that can help you determine the limits for RSA keys of different sizes than I enumerated above (or you can just determine those limits by trial and error).
Also related is this post about the use of certificates in SQL Server and this post about the difference between certificates and asymmetric keys.
- SQL Server 2005: How to determine the size of a column that will hold encrypted data
-
This issue has been addressed before on forums, but with the heavy traffic, it can be hard to find the proper post. So, I'll provide some explanations here as well.
Note: This article is written with symmetric encryption in mind, but the actual technique would work for asymmetric encryption too.
When encrypting a piece of data, the encrypted version will always be larger than the original data. This happens because encryption doesn't compress the data and, in addition to that, SQL Server prefixes the encrypted blob with some data, the most significant being the GUID of the symmetric key used for encryption. It is possible to compute in advance what will be the length of the encryption for a given piece of data, but rather than working with a formula, a safer and simpler method is to just perform the encryption using EncryptByKey and pass the result to the datalength() builtin function. While the contents of the encrypted blob are non deterministic due to the use of a random IV, the length of the blob will be constant if the same method is used for encryption; in fact, the length of the encryption doesn't even depend on the data that is encrypted - it only depends on the length of that data.
So, when trying to decide the size of a column that is supposed to hold encrypted data, you only need to determine, as you would normally do in the absence of encryption, what is the maximum size of unencrypted data that you are willing to store; once you know this, you can just generate a piece of data of that size, encrypt it, and get the length of the result using datalength() - that will be the maximum length that you should define for the encrypted column.
Although I remember several posts on this topic, I could only find one with a quick search. Fortunately, it's a very good post from Raul:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=908990&SiteID=1
Update (later same day): This is the original post from Raul that explains in more detail the formula you could use to determine the length of an encrypted blob without actually encrypting. The blog on which it is posted has been discontinued, but there are some good posts made to it that are worth checking out. And if you are wondering about the blog name, Yukondoit was one of the slogans used on T-shirts around the launch of SQL Server 2005, whose code name was "Yukon".
