SQL Server Security
Writing about SQL Server 2000 and SQL Server 2005 ("Yukon") Security issues
(Cross-posted)
Have you tried installing SQL Server 2005 yet? If so, how did it go for you?
There is a survey the SQL Server team would like you to fill out:
The Yukon setup team is looking for feedback related to your setup and installation experience. They've setup a survey that asks for a few demographics as well as your experience and any issues with the setup of the Betas or CTPs.
They are looking for error messages as well as your impressions, so if possible, do an install and take the survey soon afterwards. Save your errors, jot some notes, etc., while installing SQL Server 2005.
If you want to help improve the setup process and spare some others the pain of problems, please take a few minutes to complete the survey. This data is very important to the survey team to capture and help improve the user experience.
I already filled out the survey -- everything was pretty smooth for me in my VPC install in an Windows XP SP2 environment. I do have some security related questions that I have posed directly to the SQL Server security team that I am hoping to get answered soon -- some "gotchas" I found that seemed odd.
As I dig into this more, I am hoping to post information on my SQL Server blog regarding SQL Server 2005 Security. Stay tuned.
(Cross-posted)
I will be speaking at DevTeach this year in Montreal, Canada on June 18-22, 2005.
My topics (so far -- waiting on a couple of other proposals, but this may be enough) are focused on various SQL Server 2005 features:
SQL Server 2005 Managed Stored Procedures
SQL Server 2005 Security
SQL Server 2005 Service Broker
This should be a fun conference and one I am looking forward to attending and speaking.
This news account talks about an Australian telecommunications company that was hit by the infamous Slammer Worm. Slammer, which exploits a vulnerability in un-patched versions of Microsoft SQL Server 2000, was first detected almost two years ago. As always, don't forget to patch, and be very careful not to open old doors because “visitors” are still lurking around out there waiting to come in.
Take a look at this resource for helping to secure your SQL Server 2000 databases: SQL Server 2000 Security Tools
No excuses!
(Cross posted)
I have added another topic to the ever growing data track for the upcoming New England Code Camp 3:
SQL Server 2005 Security
This talk will focus on many of the upcoming changes in security for SQL Server 2005.
This is going to be another great conference some new speakers (that weren't there for Code Camp 1 and 2) as well as a lot more content. Interested in speaking? Follow the link above to submit your proposal!
If you are in the New England area in October, I will be speaking at CodeCamp II in Waltham, MA at the Microsoft offices. That will take place on the weekend of October 16-17, one week before WIN-DEV 2004.
My database topic (I have a couple of others related to .NET secure development) is:
SQL Server Security (Data Track -- Level 300)
This talk will demonstrate steps and techniques to insure a freshly installed SQL Server database is secure. I will also demonstrate best practices to permit client applications to access SQL Server securely, prevent SQL Injection, and to effectively audit and implement an intrusion detection plan.
I also noticed Kent Tegels will be there speaking on couple of SQL Server 2005 Express topics that weekend. Don't miss it!
This past week, I concluded my talk on Security Coding: Best Practices at my work site. This was a continuation of Part 1 that I started last week. In particular, I dealt with SQL Injection and issues with Encryption and Cryptography.
I spent a little more time on SQL Injection, because this is a very interesting security issue to me. Even though you think your data is safe behind a few layers (UI, middle-tier), if you are not careful in how you process your calls to your database using the input from the UI, an attacker can easily gain lots of information about and from your database, including retrieving sensitive data and the structure and names of your tables. An attacker can also drop tables and kill your database completely.
SQL Injection is especially possible when concatenated strings are used for SQL queries. For example, if an input form requests a user to enter his/her name and password, and those inputs are used in a query string to determine access:
sql = “select * from users where userId = '“ + userName + “' and password = '“ + password + “'“;
An attacker can use a single quote (') in the userName input field to stop the string. After that, an “ OR 1 = 1 --” could be appended. This sets up a logical condition that will always be true. Plus, the “--” comments out the rest of the SQL query. So, the above query would be sent to the database like this:
select * from users where userId = '' OR 1 = 1 -- ' and password = ''
This would effectively bring back all user information. Very bad.
The key is checking all user input (“Never, ever trust user input“). Check that numeric fields are numeric. Check that string fields have double “single quotes“ (i.e. use a replace function to change single quotes (') into double single quotes ('')). Use stored procedures for access to any data, and when using stored procedures, implement them using the ADO command object or SQLParameter collection and classes so that variables are strongly typed. Always design and code for security from the first day, and test, test, test.
I have put together a list of further resources I have found useful in learning about SQL Injection as well as how to protect against it.
Advanced SQL Injection
Whitepaper on SQL Injection
Protecting Yourself from SQL Injection Attacks
SQL Injection FAQ