Poll: What are your biggest security concerns for SQL Server 2005?
I'm interested to hear what you consider the biggest potential security issues with SQL Server 2005. I've asked previously about concerns regarding SQLCLR; today I'm widening the net and asking about the entire product. If you have security concerns about SQL Server 2005, what are they? No area is too small or too insignificant to be concerned about -- let's hear what you think.
Comments
- Anonymous
February 07, 2006
Ken,
I had a thought about SQL server security the other day that I was wondering if someone could comment on. In my experience consulting on existing codebases, a) SQL injection vulnerabilities are still reasonably common despite well known techniques for countering them, b) most applications have no need to submit commands with embedded comments, c) most SQL injection attacks are made significantly easier by using comments to ignore the remainder of the statement (and thus prevent a syntax error).
What would happen if SQL server supported an optional connection string parameter, e.g. "AllowComments=false;", which would cause the parser to reject any commands over that connection which contain an embedded comment? - Anonymous
February 07, 2006
Our biggest problem is that SQL Server user accounts do not support automatic lockout after so many failed login attempts. Any publicly exposed server can be hammered away at indefinitely. Of course, we try not expose servers directly to the Internet. However, as an ISP/ASP, that's not always possible.
Windows accounts are not a solution because, even to this day, most third party applications do not support them. Even Microsoft's own products offer weak support for Windows authentication. For instance, I believe that only the latest J2EE JDBC driver from Microsoft supports windows authentication.
More importantly, however, Enterprise Manager (now Management Studio) does not offer the user the ability to specify a set of credentials other than their current credentials. None of our customers are on our network. None of our customers are logged into our domain. Therefore, none of our customers have an existing set of credentials that would get them access to the SQL Server.
To put it another way, I can secure a Web site using Windows authentication. Web surfers do not need to be logged into our domain prior to accessing the site. They do not need access to our domain controller. They simply get prompted for a user name and password when they make the initial request to the site. As far as I can tell, there is no way to do this with SQL Server and Windows authentication.
To get around this, we provide some customers with VPN access to our network. However, that does not work for everyone. Windows VPN support is spotty on anything but Windows XP. VPN requires many ports which are often blocked. Any many customers simply can't establish more than one VPN connection from behind their firewall/NAT device.
Anyway, you're headed in the right direction. SQL Server finally logs the IP address of failed login attempts. And, we can now rename the sa account. But we really need support for locking out accounts after so many bad attempts. Locking out IPs after so many failed attempts is my next biggest request. - Anonymous
February 07, 2006
Ben Roders said:
>> SQL Server user accounts do not support automatic lockout after so many failed login attempts
Auto locking would be a HORRIBLE idea because the majority of developers have all their programs use the same account. And even if each program used a separate account, everybody using a program use the same account. It is a very common scenario for such program to become updated on a per user scenario and if the login was changed, then a couple old users could easily disable the account which disables the app for everybody else.
SQL accounts are not akin to a user's account, lockdown would become an easy DOS attack.
Although I can't argue that perhaps the feature should exist (I done any SQL2005 user acount admin stuff yet) and perhaps such a feature should exist, disabled by default. - Anonymous
February 07, 2006
Doug, that's an interesting idea. I'll pass it on to the folks who look at this.
Ben, you're aware that SSMS supports SQL authentication, right? - Anonymous
February 07, 2006
I agree with Ben that Windows Authentication remains a difficult way for apps to log into a database and I'd like to see a lot more effort on Microsoft's behalf to provide better and a wider variety of examples for using it. We've made the decision to use Mixed Mode authentication for many reasons but ease of use being the main one. Also... why mixed mode? Why not SQL authentication only? If someone figures out how to get by Windows Authentication it would allow our app to be safe from that at least.
Overall I'd have to say that SQL Server 2005 is a more secure product than was SQL Server 2000 and Microsoft deserves credit for battening down some of the hatches. - Anonymous
February 07, 2006
The comment has been removed - Anonymous
February 13, 2006
> Auto locking would be a HORRIBLE idea because the majority of developers have all their
> programs use the same account. And even if each program used a separate account,
> everybody using a program use the same account.
I envision auto-locking behaving differently based on the client's location. Most mail servers, for instance, allow you to define trusted networks. Different levels of authentication and rules apply depending on where the client is authenticating from.
> It is a very common scenario for such program to become updated on a per user
> scenario and if the login was changed, then a couple old users could easily disable the
> account which disables the app for everybody else.
If various application users are authenticating with the same user name but different passwords, that sounds like a problem with the application design, but I'm probably misunderstanding something here.
> SQL accounts are not akin to a user's account, lockdown would become an easy
> DOS attack.
Only if SQL Server treated all clients equally.
> Although I can't argue that perhaps the feature should exist (I done any SQL2005 user
> acount admin stuff yet) and perhaps such a feature should exist, disabled by default.
Well, from a security standpoint, I'd say it should be enabled by default if you turn on mixed mode authentication, but at this point, I'd take anything I can get.
khen, I'm not sure what you mean by the question. I am aware that SQL Server Management Studio supports SQL Server authentication. Every application I've used does. It's the common denominator. As such, we have to use SQL Server authentication for some applications. The problem I have is that there's no decent way to protect SQL Server accounts from password guessers. We can't even figure out a way to do it with ISA Server. As far as I can tell, Microsoft doesn't make a product that would stop any random client on the Internet from running a password guesser against an exposed SQL Server indefinitely.
-ben - Anonymous
March 14, 2006
I think Ben and Travis are referring to the short comings of Application Role(s).
Currently, a user validates his credentials with the App. The App activates the Application Role and the user then interacts with the database via the AppRole's permissions. However the AppRole still uses a password. If a hacker knows the server IP and the AppRole name, he can theoretically run a password-guesser against it.
My suggestion is that a "machine login" should not utilize a password. Instead, Sql Server should generate a "certificate" that can be copied to the application server. The certicate's contents is an encrypted BLOB. To activate the AppRole, the App passes the BLOB to the database for validation -- instead of passing a password. - Anonymous
August 13, 2006
The comment has been removed - Anonymous
August 13, 2006
The comment has been removed - Anonymous
August 13, 2006
The comment has been removed - Anonymous
August 13, 2006
The comment has been removed - Anonymous
August 13, 2006
The comment has been removed - Anonymous
August 13, 2006
The comment has been removed - Anonymous
August 16, 2006
The comment has been removed - Anonymous
August 21, 2006
The comment has been removed - Anonymous
August 21, 2006
The comment has been removed - Anonymous
August 22, 2006
The comment has been removed - Anonymous
August 23, 2006
The comment has been removed - Anonymous
August 24, 2006
The comment has been removed