Category: SQL Server

dbdesc bug and great support from Logica2

One of my favorite database tools is dbdesc from Logica2.  I’ve reviewed it in the past.  It’s simple, inexpensive and does its job quickly and well.

Recently, I’ve had some turnover on my team, in addition to the usual bouncing around, and database scripts became out of date with what was in the database.  I got the “all clear” to refresh a database, and ended up wiping out some recent changes.  I couldn’t recreate the tables and stored procedures from their creation scripts, but fortunately I had run dbdesc earlier and had its output to work with (you can run it as a command line tool and include it in your automated builds).  Dbdesc includes the CREATE scripts in its documentation, so I copied and pasted and executed and ran into a problem.

After a little poking around, I noticed that the tables were all being created as dbo.schema.tablename.  Upon closer examination (and this took a while to find), I saw that dbdesc was outputting the CREATE as

[schema.tablename]

rather than

[schema].[tablename]

This small difference is what led to the creation errors.  Manually fixing the sql script solved my problem, and I reported the issue to Logica2.  This is where the tale of great customer support begins.

I sent in the bug report via their website at around 3pm on Friday.  About three hours later, I had a reply from Jose that he was looking into the issue.  Not a bad response time, and I wasn’t expecting any update until today (Monday).  At 11:12 pm Saturday, I get another email from Jose saying the issue has been fixed and a link to the new installer.  Very impressive, especially since it would be Monday before I was back at work and would need the software.  This was probably a very simple issue to fix, but nonetheless it received prompt attention on a weekend.  My previous build was 730, and the updated build was 737.

Thanks, Jose and Logica2, I love your products even more!

DotNetSlackers Article Published – Review: SQL Server Integration Services Using Visual Studio 2005 – A Beginners Guide

My first DotNetSlackers article has been published: Review: SQL Server Integration Services Using Visual Studio 2005 – A Beginners Guide.

Occasionally in life, the right thing shows up at the right time.  I was just ready to start some work in SQL Server Integration Services, and like a lot of DTS developers, my mind was boggled when I first opened the SSIS workbench.  My tasks looked daunting.

That same week, I received an email from Packt Publishing asking if I’d like to review this book.  Oh boy would I!  Long story short, this book was a significant help in my project.  I went from being an overwhelmed DTS guy to a pretty fair SSIS developer in a short period of time.  Check out the review for more about the book.

Like the other Packt books I’ve read, it’s an easy read, partly because of the writing style and partly because of the typography.  I don’t know how much time they spent researching the font style, kerning, etc., but their printed materials are easy on the eyes.  There are a lot of screenshots in the hands-on chapters, and being a beginner’s guide, background information on using the relevant parts of VS 2005 is included.  There are no major changes to SSIS in SQL Server 2008, so this book will be relevant for a while.

Get it at Amazon: SQL Server Integration Services Using Visual Studio 2005 – A Beginners Guide

New Sophisticated SQL Injection Attack

Although this attack targets websites powered by Microsoft SQL Server, databases such as Oracle are also vulnerable to this attack.

The attacks “are a very sophisticated form of SQL injection,” Qualys CTO Wolfgang Kandek told TechNewsWorld. “Normally, SQL injection is targeted to one table. With this attack, they used a generic mechanism of the underlying database to make it work on a much broader set of applications.”

The attacks have targeted sites running IIS and ASP that have an MS-SQL database. However, they are not exploiting a particular flaw in these applications — the exploit could have been written to target any database — Oracle or WebSphere, for example.

Rather, the code exploits what security researchers are bemoaning as an elementary lapse in Web security on the part of developers installing the databases.

In addition to this attack, the article mentions another one, specific to SQL Server, on the horizon:

“The underlying database servers are often misconfigured to have an extended stored procedure xp_cmdshell enabled,” Belani told TechNewsWorld. “This setting allows an attacker to execute commands at the operating system level post compromise via SQL injection. This level of access is hard to come by in other database servers like Oracle.”

Full story at http://www.technewsworld.com/edpick/62783.html?welcome=1209477802.

The root cause of these attacks is insecure web application design, which allows SQL code to be inserted into a page request and executed in the following database query.  This is an old technique known as SQL Injection.

If you’re not familiar with SQL Injection, you need to be, since it’s a very basic flaw with serious ramifications.  I recommend highly reading 19 Deadly Sins of Software Security for very good introductions to some very basic design issues.

Free SQL Server 2008 Account Today

Want to get your hands on SLQ Server 2008, but don’t have any hardware?  Check out www.sqlserverbeta.com.  You get RDP access to a SQL Server 2008 server, with your own account space so you can test your scenarios in a quasi-real world environment.  From their site:

So
set your sights on the horizon and take advantage of this chance to
play with real-world technology before it hits the mainstream – test it
with your real-world data, with your own custom scenarios, while it’s
still under development. This is your chance to influence the product
that you use every day – in a secure, hosted working environment.

Important Note: This Beta offer
should not be used for production systems, but can be used to fully
test and experience Microsoft¹s newest, most intelligent data platform
in a hosted environment free of charge.

Create your own brave new world –
experiment, test, explore and push SQL Server 2008 to its limits – then
report back to your fellow explorers on your experience in the
Community discussion forums and blogs. Compare your findings with your
peers and colleagues – and share your opinions and assessments.

SQL Server Beta is a joint effort between PASS, MaximumASP and Dell.  Hat tip: SQL Server Magazine.

SQL 2008 RTM Slips to Q3 2008

Over the coming months, customers and partners can look forward to significant product milestones for SQL Server.  Microsoft is excited to deliver a feature complete CTP during the Heroes Happen Here launch wave and a release candidate (RC) in Q2 calendar year 2008, with final Release to manufacturing (RTM) of SQL Server 2008 expected in Q3.

Full story at http://blogs.technet.com/dataplatforminsider/archive/2008/01/25/microsoft-sql-server-2008-roadmap-clarification.aspx

How to tell what version of SQL Server you’re running

Anytime something doesn’t work quite right, there’s always the question of
which version and service pack level of SQL Server you’re running.

For SQL Server 2005, there’s a simple query you can run which will tell you
the version, service pack and edition (standard, enterprise, etc):

SELECT  SERVERPROPERTY(‘productversion’), SERVERPROPERTY
(‘productlevel’), SERVERPROPERTY (‘edition’)

The full article, with queries for SQL Server 6.5 to 2005, is at http://support.microsoft.com/kb/321185.

SQL Prompt 3 Approaches, Pre-Order for $99

Until September 1, the current version of  is free.  The next version (v3) is now scheduled for release September 28th.  The price for v3 will be $195, BUT, you can get a pre-order discount price of $99.  You must order before v3 is released (“no exceptions” I’m being told).  To pre-order v3, contact Greg Tillman at greg.tillman [at] redgate.com, and ask him for a pre-order quote.  There is no public URL for the pre-order special.

If you have some ideas or feedback for v3, post them in the forum at http://www.red-gate.com/MessageBoard/viewforum.php?f=44.

kick it on DotNetKicks.com

Stored Procedures Do Not Prevent SQL Injection

There’s another sproc-or-not battle royale going on over at
CodeBetter.com.  Not going there, but one statement Eric
Wise made isn’t completely correct:

First, you can be sure that no sql injection attacks will ever
occur.

That’s part of a longer paragraph, but if I read the paragraph correctly, not
taken out of context.

For the most part, Eric is correct.  But, if SQL statements are
generated dynamically in a stored procedure, and in-line parameter concatenation
is part of the statement, then you’re just as susceptible to SQL
Injection.  For instance, code such as

IF @orderId IS NOT NULL
 select @sql
= @sql + ‘ AND order_id=’ + @orderId

would be susceptible to SQL Injection.  I have
seen code such as this used (including some by as reportedly high-level DBA),
and sadly written some myself.

There’s an excellent paper on the right and wrong
of dynamic SQL at http://www.sommarskog.se/dyn-search.html;
I have a summary blog post at http://aspadvice.com/blogs/rjdudley/archive/2005/06/30/2626.aspx.