Best Practices
Best Practices
(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!
Kimberly Tripp, SQL Server guru and MVP, was on .NET Rocks last week. The show is available for download here.
A brief description:
Kimberly Tripp offers critical insights into SQL Server 2000 addressing Indexing, optimizing procedural code, the nightmare of row-based operations, and more. This show is 2.5 hours long!
I just noticed SqlJunkies is one of the sponsors for WIN-DEV 2004 this year (October, 2004). That's great!
I will be speaking this year under the Security for Developers track. These are my topics.
The Database and Data Access track, chaired by Bob Beauchemin, looks like it is packed with some great information. Here is a list of the topics:
- A Day of SQL Server 2005 and ADO.NET 2.0
- Locking or Versioning? Snapshot Isolation in SQL Server 2005
- SQL Server 2005 Security Enhancements
- XQuery in SQL Server 2005 and System.Xml
- XML data type
- Trees in SQL
- User-defined types and aggregates in SQL Server 2005
- .NET Coding Drilldown in SQL Server 2005
- T-SQL New Features in SQL Server 2005
- Advanced DDL tricks
- Understanding and Optimizing Performance with ADO.NET
- Web Services in SQL Server 2005
- ADO 2.0 classes for providers
- Writing database-independent SQL in ADO.NET applications
- SQL Server Reporting Services Overview
- SQL Server Service Broker
These are presented by the best in the industry: Bob Beauchemin, Joe Celko, Girish Chander, Mark Fussell, Dan Sullivan, Rob Steward, Brian Welcker, Jason Carlson, and Neils Berglund.
Don't miss it!
By way of Clemens:
You read it here first. Kimberly Tripp blogs (rss). If you do anything with SQL Server: Subscribe!
Subscribed!
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