- Privacy and search engines
-
It is no secret that search engines keep track of searches made. Any website logs accesses and most websites track your activity via cookies for reasons involving both your benefit and that of the site you're accessing. You may be surprised to find out that even video players used for online videos come with their own version of cookies that are not under the control of browsers. In other words there's a lot of tracking going around. As someone that has been raised in a communist country, I still remember my parents cautioning me to not discuss "politics" at school and no matter what, under any circumstances, ever make any mention of listening to Radio Free Europe. So, I can say that in a sense privacy has been instilled in me from quite a young age and that makes me very sensitive to comments basically saying that "if you're good, you have nothing to hide" - because that makes the assumption that you have nothing to hide from other good guys. Yes, but what about the bad guys and what about good guys later turning bad?
All this makes me feel uneasy when I hear dismissive talk of privacy from people that should know better (won't say who - important people). Bruce Schneier writes often about the value of privacy and if you find appeal in the idea that privacy is just for bad people, then it might help read one of his articles on privacy. I'll just end with a famous quote from a famous American: “Anyone who trades liberty for security deserves neither liberty nor security”.
- 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 information. Here are some tips on how to find more.
First, if you have a command line tool that displays the current context, like whoami or a utility for dumping the security contex, you can just execute that under xp_cmdshell. That's pretty easy. But what if there is no such tool available? One thing you can try in this case is to loop back into SQL (assuming the xp_cmdshell account has access to the database - it usually does) and just ask SQL for more information with queries like the following:
EXEC
xp_cmdshell 'osql -E -Q"select suser_sname()"'
EXEC
xp_cmdshell 'osql -E -Q"select * from sys.login_token"'
Don't forget to pass in the server/instance name using the -S option, if you are not dealing with a default instance. These should give you plenty of information about the xp_cmdshell context and should help you figure out any access permission issue.
Given that I am on the topic of xp_cmdshell, here's how the command can be enabled using T-SQL:
sp_configure
'show advanced options', 1
reconfigure
sp_configure 'xp_cmdshell', 1
reconfigure
- bing adds twitter integration
-
See it work at: http://www.bing.com/twitter.
[UPDATE 10/22/2009]:
Reactions:
http://googleblog.blogspot.com/2009/10/rt-google-tweets-and-updates-and-search.html
http://www.businessinsider.com/henry-blodget-well-what-do-you-know-google-is-actually-nervous-about-microsoft-bing-2009-10
- New attack on AES-256
-
A new attack improves significantly on previous attacks against AES-256, see: http://schneier.com/crypto-gram-0908.html#8. This doesn't mean that AES-256 is broken yet, but the surprising bit here is that AES-128 is not susceptible to this particular attack. Don't panic if you are using AES-256 and read Bruce Schneier's commentary carefully - for example, note that the attack is against a 10 round AES-256, while standard implementations use 14 rounds.
It's interesting to see that the strength of AES-256 is eroded by new attacks, but AES-128 doesn't suffer from them yet. It's another example that larger (or more expensive) isn't necessarily also better.
- 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 usually its creator, but a different owner can be specified at creation time using the AUTHORIZATION clause. The owner of an object has all possible permissions on that object and, most importantly, cannot be denied those permissions while he continues to be an owner.
CONTROL permission: The CONTROL permission can be used to easily grant all permissions on an entity to some principal. It's the next best thing after ownership of the entity, but it's not quite as powerful as ownership. The main difference is that a grantee of CONTROL can still be denied some other permissions on the entity. For example, I can be granted CONTROL on a table, while at the same time I can be denied SELECT on that table, preventing me from selecting from it - this can never happen to the owner, because the owner cannot be granted or denied permissions.
Changing ownership: To change the owner of an entity, we can use the ALTER AUTHORIZATION statement. Changing ownership of an object creates some interesting scenarios in terms of what should be allowed and what should not be. Basically, owners should not be able to "dump" their objects to other users who may not want them and users should not be able to "steal" objects from those that own them. To accomplish this, two checks are needed: one controls to whom ownership can be given to - we verify that the performer of the change has some right on the new owner (we check for IMPERSONATE for users and logins, ALTER or membership for roles, etc); the other check controls from whom ownership can be taken away: we require the performer of the change to have TAKE OWNERSHIP permission on the object.
Note as a corollary that a grantee of CONTROL permission on an object has the ability to take ownership of the object because CONTROL implies the TAKE OWNERSHIP permission as well - the way to prevent this would be to grant CONTROL and deny TAKE OWNERSHIP.
TAKE OWNERSHIP is thus used to selectively grant someone the ability to willingly become the owner of an object or to facilitate the change of ownership of that object.
Side effect of ownership change: A potentially surprising side effect of changing ownership of an object is that all permissions granted on that object will be lost. As always, it's a good idea to script all permissions granted on an entity before changing its ownership, so that the grants can be re-executed by the new owner, if appropriate.
- TechCrunch anatomy of the Twitter attack
-
http://www.techcrunch.com/2009/07/19/the-anatomy-of-the-twitter-attack/
The first step of registering an old email account to receive the password from a current account was a nice and easy way to break into an email acount. After that, things pretty much fell like dominoes, but it's nice to see how inconspicuous a smart attacker can be. This story also reminds us the painful fact that the only way you can know about some security breach is if the attacker is destructive, or less skilled, or wants you to know. The victims of this attack could have spent the rest of their lives without knowing that their information is being monitored.
- bing has launched!
-
I haven't posted anything new for some time, but now I have some news related to my current area of work: bing is Microsoft's new search engine, it has launched yesterday, and you can now find it at www.bing.com. Give it a try and let me know what you think about it.
- 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 Group, 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.
