One of the most serious problems I see are SQL injections. While most SQL injections have become less of threat since SQL 2005 it doesn’t mean that they have totally gone away. While SQL injections are not the easiest attacks to pull off there are automated tools to help out and a determined hacker can present formable opponent.

Since the hacker injection leverages the connection of your web application to connect to your database server via web coding there are important places we can look at. Look at your IIS logs common SQL language. As an example the following:

• USE
• SELECT
• SYS.TABLES
• sp_password
• xp_cmdshell

should never be passed in from our web client. If you see these in your IIS logs then you have an attempted attack. If may not signify that it was successful but attempted. To find out whether it was successful I look at following IIS log entries to see what data was being returned to your web client.

How can this happen? It really comes down to the web application passing SQL code to the database unchecked. Normally these well come from help or search type pages. If a hacker can type:

USE master SELECT * FROM sys.databases

If passed successfully then the hacker can retrieve all the databases on your SQL server.

Now that we the database names we can list all of the tables via this command:

USE SomeDatabase SELECT * FROM sys.tables

Finally, we can list the columns from all the tables in that same database:

USE AdventureWorksSELECT * FROM sys.columns

From here we can look for any interesting column name like: ccnumb, ssn, order, etc…

Remember, if they can retrieve the information they can also delete the information on your SQL server.

If you would like to create an ad-hoc backup without disrupting your current backup jobs you will have to use the new copy only option for SQL 2005. This is command line only operation that you can’t do within the SQL gui window.

We will assume the following:

• old database name: DataBase1
• old database logical name: Database1
• old database transaction log name: Database1_log
• new database name: DataBase2

To create the back up open a query windows and excute a statement as follows:

backup database DataBase1 to disk=’c:\database1.bak’ with copy_only

To restore it do the following in the query window:

restore database [DataBase2]
from disk = N’c:\Database2.bak’
with file = 1
,move N’Database1′
to N’c:\Database1.mdf’
,move N’Database1_log’
to N’C:\Database1_log.ldf’
, nounload, stats = 10

This is place a new database called DataBase2 with the MDF and LDF in the c: directory. You can of course place it wherever you desire but I shortened this for clarification.

One of the most common reason I have seen for SQL slow down is improper maintenance of the databases indexes. While I won’t get into the specifics of an index. I’ll just say that the closest analogy is a table of contents for a book.

Once I have a idea of the database that is being used I will run the following in a query window:

exec sp_MSforeachtable @command1=”print ‘?’ DBCC showcontig (‘?’)”

It will return alot of information. The important information to focus on is the page density percentage. If the values falls within the 85-100% range then you can probably getting away with a reindex of the SQL index. If the range falls below the 85% then I would recommend a full off-line rebuild of the indexes.

You can find a definition and creation here:
http://msdn2.microsoft.com/en-us/library/ms188783.aspx

You can find a much more detail index maintenance explination here:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

With the Launch of the newest version of SQL 2005 there are several new features of note and some missing components that you should be aware of. Microsoft has developed separate of its editions: express, workgroup, standard, and enterprise. I have noted several clients who choose workgroup as a less expensive alternative to standard edition. In most cases this is quite a suitable choice. After all the workgroup version contains many impressive features such a database size without limitations and the capable of running up to 3GB of RAM. All of these things come with some limitations. Chief among these of the absence of profiler and the database tuning advisor.

As a work around I have found you can run the critical SQL profiler from either a separate workstation or an existing SQL server with standard or enterprise editions. Use this piece of critical software to analyze your queries to fine tune your databases performance. Remember that the key values to note are duration and read times. I’ll write more in depth about these values at a later time.

The database advisor is unfortunately a neat feature that will still be out of your reach with the workgroup edition.

At the very least this will give you a good tool in your arsenal when trying to solve your SQL issues.

Working in a diverse environment where backup and restores are handled by a different department sometimes small problem can rise into huge monsters if we overlook the details. A recent problem I ran across consisted of restore job where the databases appeared as stuck in loading stage,but not fully functional. After a couple of attempts to restore the database what started as one failed restore turned into three databases all in a loading state. The normal steps of action included dropping the table, but every time I tried the SQL server responded with a message that the database was loading and could not be deleted.

After much research and hitting a couple of brick walls. A next tried to think about this particular problem from a perspective of an isolated database instance without any mitigating factors. I tried a couple of options, which in this case were not successful, but might work under different circumstances.

First, I tried to restore an existing database onto the loading database. This failed again with the error indicating I could perform the action because of loading database.

Next, I spoke to other system administrators and reasoned that in fact the database had been restored with the ‘norecovery’ option. This is by design to put the database into this loading state. It assumes that you will be restoring later transactional file backups on top of the full restore just performed. In fact since we didn’t have those transaction backups this left me rather confused. I again consulted my co-workers and they indicated that I should again try a restore but the the ‘recovery’ option set.

I opened the SQL query manager and ran the following code:

restore database <DATABASE> with recovery

Once I executed this code the database came online and then in turn I could delete the unneeded databases.