Moving SQL

Best Practices for Applications Login to SQL Server?

Sat, 17 Oct 2009 16:31:00 GMT

An interesting question came up on the MSDN SQL Forums yesterday about best practices for applications to access their databases on SQL Server:

In what situations will application roles suffice, I have read about how it works, but in applications that I have seen, e.g. an exe which expects a servername as a parameter, a username and a password. How will an application role work here.

In what applications/scenarios have other SQL server experts used application roles.

If SQL server login is to be used, what is the best practice approach of how this should be managed?

In order to answer this question, it is necessary to first understand that a "SQL Server Login"and an "Application Role" are two different things.

A SQL Server Login, is a SQL Server-only account (i.e., not a Windows Login/account) that is used by supplying a username and password to SQL Server when connecting. Because such a SQL Server Login is created just for this application's use, and is used only by this application, they are typically called "Application Logins". They are popular with applications because 1) they provide a way to authenticate and authorize just the application, irrespective of the user, so that the user cannot access the database on their own without the application. And 2) they can be externally parametrized and thus managed and modified externally.

Its downsides are that gaining access to the application is tantamount to gaining access to the applications data, and even worse, gaining access to the executable image and/or executable environment gives hackers the ability to strip the application login's username and password out and use it on their own (because the Window account using it is never checked by SQL Server).

This is all in contrast to a Windows or Domain Login that uses a Trusted connection that needs no validation on connection. Typically in Client-Server architectures, this is the Users domain account that the client application is piggy-backing on to connect to the database. Authorization in the database is implemented by adding the Windows domain account or a Windows group that the application user are in (usually a group created solely for this purpose) as a Windows Login in SQL Server and then as a User in the application database. User/Group authorization of windows logins for applications access to SQL Server database is popular because 1) it takes virtually nothing in the application code to implement it, its all up to Windows and SQL Server, and 2) access to the application, or its image/environment will not give you access to the data because you have to be in the right Windows Group before SQL Server will give you that access.

Its disadvantages are that the users can access the database (and therefore usually, the data) all on their own without ever having to use the application (because SQL Server is *only* checking the Windows Login/Group and requires nothing else).

An Application Role, on the other hand is a way to "sort of" combine the security advantages of both of these approaches. An Application Role is a database-only principal (that is, non-server level, so it cannot normally cross databases) that is intended to be used by an application AFTER it has successfully connected to the database. The Application Role is invoked by using the command: EXEC sp_setapprole "rolename", "password", which causes SQL Server to drop the connections current security context and assume the Application Role's. The idea here is that the application's users' Windows Logins (or Windows Group) is authorized by SQL Server to connect to the database, but *nothing else*, it has no access to anything else in the database (tables, views, application stored procedures, etc.). This allows the application to use Trusted connections to the database based on the authorization of the user. At that point the application runs sp_setapprole to switch to the Application Role in the database, which is authorized to do everything that the database needs to do.

The advantages of this approach are that 1) Authorized users cannot access the application data on their because they do not have the approle's password. And, 2) access to the application, image or environment, does not enable a hacker to gain access to the SQL Server or the application's database, because you must still be in the right Windows Group to connect to the database.

The disadvantages are that this approach is complicated and harder to implement (both in code and in the DB) than the other two. Even worse, because it can only be implemented explicitly in code, it cannot be retrofitted in the field onto an existing product, application or other executable through external configuration, the access code has to be rewritten.

SQL Server Express summary from Pinal Dave...

rbarryyoung+movingsql@gmail.com — Wed, 26 Aug 2009 04:14:00 GMT

Pinal Dave is one of the most prolific SQL Server bloggers ever, his SQLAuthority blog alone has thousands of his articles. And today, he has posted an execllent summary of SQL Server Express what you can (or cannot) do with this FREE software for both you and your customers. Read it!

Object/Relational Mapping, The Vietnam of Computer Science?

rbarryyoung+movingsql@gmail.com — Mon, 20 Jul 2009 00:19:00 GMT

Wow, it's been over a month since I've posted... Just a quick note this time, I re-read today "The Vietnam of Computer Science" by Ted Neward from 2006. This is certianly one of the best blog posts ever on technical development and the definitive explanation of what the "Object-Relational Impedance Mismatch" is really all about.

While I generally agree with what is said in this article, I think that most of the problems mentioned can be readily addressed just byadopting a slightly more pragmatic perspective. The exception is inheritance-based design patterns which simply have no good answer in the relational model. Yes, there are answers, but they all have significant drawbacks.

If you've never read it, do yourself a favor and read it now. If you have read it before, that's no reason not to read it again...

News articles on SQL Injection

rbarryyoung+movingsql@gmail.com — Sun, 14 Jun 2009 01:52:00 GMT

A couple of news articles I cam cross this week on SQl Injection;

http://www.baselinemag.com/c/a/Security/SQL-Injections-Wreaking-Havoc-258450/

http://www-935.ibm.com/services/us/iss/xforce/trendreports/xforce-2008-annual-report.pdf

http://www.baselinemag.com/c/a/IT-Management/Six-Steps-to-Stop-SQL-Injections-129263/

http://www.blackhat.com/presentations/bh-europe-09/Guimaraes/Blackhat-europe-09-Damele-SQLInjection-whitepaper.pdf

There is some realy great stuff in here including some references to the frequency of Injection attacks this past year, a report from European Black Hats of a new technique that can take an Injection attacker from SQL Server to the OS, and some ways to protect yourself.

Let me know what you think!

VB to C# (and vice-versa) Cheat Sheet

rbarryyoung+movingsql@gmail.com — Sun, 31 May 2009 04:20:00 GMT

I just came across this, a simple table formatted cheat sheet that allows you to tranlsate VB features to C# or C# features to VB.

You can find it at: http://aspalliance.com/625

I realize that it's a few years old, but if you're like me and need to go back and forth between these two languages, but can't always remember what the equivalents for one language are in the other, then you might find this useful.

Great Tutorial on Preventing SQL Injection

rbarryyoung+movingsql@gmail.com — Thu, 28 May 2009 01:21:00 GMT

Wow. I was following a link from a forum post on SQL Injecitn that I was reaidng and it led me here. Wow. What a great resource! This is the kind of tutorial that Microsoft should have written years ago. And then started promoting it to address Injection, and started following it themselves, and encouraged SW vendors to follow. And encourage customers to require from their SW vendors.

The catch? It's from ... Oracle. That also means that the recommended solutions are very Oracle-specific. The good news? I did not see anything in there that could not be easily translated to Transact-SQL. Happy reading!

Making SQLBulkCopy faster ...

rbarryyoung+movingsql@gmail.com — Wed, 27 May 2009 15:27:00 GMT

(trying to get better about just blogging in the moment, instead of saving it up ...)

I saw a post abut the next release of ClearTrace at http://weblogs.sqlteam.com/billg/archive/2009/05/27/ClearTrace-2008.34.aspx that mentioned its heavy use of SQLBulkCopy to speed up Trace loading, and I was reminded about my own experiences with it:

SQLBulkCopy is part of SQLClient and is pretty cool. I did some performance testing of it last year, trying to match BCP and BULK INSERT's speed with it. Could only get about 50% as fast because of the amount of CPU time it was spending in type conversions.

Turns out that both the .Net and the default SQL Server text-to-numeric (and text-to-datetime) conversion routines have a lot of overhead, I suspect to handle the zillions of different text-numeric formats. Since I knew exactly what my text-numeric formats were I hand coded my own type-conversion routines (in VB no less!) and almost doubled its speed. I was curious if anyone else had noticed the same thing?

(I have attached the code in a .ZIP file here).

New US Gov Site for free downloading Fed databases

rbarryyoung+movingsql@gmail.com — Tue, 26 May 2009 20:38:00 GMT

It may seem boring, but this is actually big news. The US Federal government has just launched a website that allows anyone to download any of a large number of databases from various federal agencies. The new site is http://www.data.gov/, try it out and let me know what you think! (Hmm, formatting seems to be less than I would want...).

So I'm a Twit...

rbarryyoung+movingsql@gmail.com — Tue, 26 May 2009 20:09:00 GMT

So I'm a twit.. or a twiteratii, or a twitron, or whatever the digest-sized denzens of Twitter call themselves. I've resisted the tempatation for a long time because it really seemed like short attention-span blogging and why feed that particular beast?

Then it occured to me that maybe I would find those short tweets (or twix? or twonks?) easier than writing whole paragraphs, so I singed up. And I have to say, for SQL Server stuff it's twitterlicious, lots of high profile and interesting SQL Server MVPs and experts out there.

So my Twit-Name is (you guessed it) @RBarryYoung. And has anybody else noticed how much the twitern language resembles Smurfish? :-)

Buying the ANSI/ISO SQL Standards?

rbarryyoung+movingsql@gmail.com — Mon, 25 May 2009 17:24:00 GMT

Just a follow up to me earlier post on reading the SQL 2006 Draft Standards (here), Glenn Pauley of Sybase pointed out to me that you download the standards for much less from the ANSI Store at http://webstore.ansi.org. The ANSI/ISO SQL Standards are all code with "9075" so the easiest way is to use the Document Number search for "IEC 9075".

And the good news is that the SQL:2003 standards are just $30 for each section. The bad news is that there are 14 sections and the SQL:2008 documents are $180 apeice.

Performance Test-Harness, pt1: Capturing the execution plan.

rbarryyoung+movingsql@gmail.com — Fri, 08 May 2009 15:04:00 GMT

I am developing a T-SQL test harness for automated performance tests. The testing procedure will receive a string to execute as Dynamic SQL. I am going to add a bunch of standard initialization and measurement stuff to it, that's all easy. However, one additional thing that I want to do is to capture the Execution Plan (the actual plan is preferred) into an XML variable or column to be saved as part of the test header record.

I puzzled over how to do this for while this morning, until I came up with the following approach:

Declare @qp as XML

SELECT @qp = query_plan

From sys.dm_exec_requests

Cross Apply sys.dm_exec_query_plan(plan_handle)

Where session_id = @@spid

-- Put Code to Test Here

select @qp

True, this will include some extra/redundant stuff like the QP for the Test-Harness statments themselves, but I think that I can live with that.

CONNECT Suggestion 440375: Add Query-based bulk EXECUTE

rbarryyoung+movingsql@gmail.com — Mon, 04 May 2009 18:29:00 GMT

I added my first Suggestion to Microsoft CONNECT yesterday, you can find it here. Please support this suggestion by rating and/or voting for it. Thanks.

For convenience, I have copied it below:

Please add a query-based bulk EXECUTE command feature, with following (or similar) syntax:

[WITH [,...n]]
[ { EXEC | EXECUTE } ]
    {
     [ @return_status = ]
     { module_name [ ;number ] | @module_name_var | (string_expression) }
        [ [ @parameter = ] { value
                         | @variable [ OUTPUT ]
                         | column
                         | ( expression )
                         | [ DEFAULT ]
        ]     [ ,...n ]
    FROM
    [ WHERE ]
    [ ORDER BY { order_by_expression | column_position [ ASC | DESC ] } ]
    }[;]

The execution procedure or command string will be executed once for every row returned from the query (may be zero) using the parameters or string value(s) calculated from each row's content.

If ORDER BY is specified then each EXECUTE instance will be executed sequentially in the order specified. If ORDER BY is not specified then the EXECUTE instances will be eligible to be executed in parallel (per thresholds, MAXDOP, available cores, etc). The command will be complete only once all instances have completed.

Despite the many possible multiple batches executed by this command, it is still considered a single command following the same rules of implicit/explicit transactions. Thus all instances will rollback or commit together. TRY..CATCH over this command will only be able to catch the first error. @return_status will only contain the value returned by the last completed instance.

------------------

This provides a direct REDUCE-like capability for T-SQL. Although this ability can be approximated by WHILE loops or Dynamic SQL execution of aggregated strings, this would be significantly faster, more declarative than either, more set-oriented, more readable and maintainable. Additionally, this would be able to aggressively leverage parallelism, which no other simple or direct approach could do in SQL Server.

Benefits:

Faster Development

Improved User Interface

Improved Administration

Improved Performance

More readable; more set-oriented, more declarative.

Submitted to PASS...

rbarryyoung+movingsql@gmail.com — Sun, 03 May 2009 22:24:00 GMT

Well, I submitted my sessions to PASS from my hospital bed Friday a week ago. So I figured today, I would just go to PASS, copy my submissions and post them here, like Gail Shaw, Grant Fritchey, Jack Cobett and others have done. Unfortunately, now that the call for speakers has been closed it seems that not only is the list of all submissions no longer avaialabe, I cannot even find my own submissions! Anyone who knows how I can get to my submissions please let me know. Yeah, I know I should have saved them off myself before hand, but I was larboring under gallstones, pancreatitis, an IV od Dilaudin and a hospital supplied wireless keyboard/trackpad wrapped in a teflon baggy for sanitation. I was amazed I could get it done at all as any kind of cut and paste took me literally minutes.

So from memory, here are my submissions:

There Must Be 15 Ways to Lose Your Cursors.
The Top Ten Reasons You Aren't already using Service Broker
Injected, Inspected, Detected, Infected, Neglected and Selected!

Reading the ANSI SQL:2008 Draft

rbarryyoung+movingsql@gmail.com — Sat, 02 May 2009 17:48:00 GMT

It has always bothered me that in this age of virtually all computer reference material being available online, dynamically and free, the ANSI/ISO committees still follow the 20th century "Brick and Mortar" practices of charging exorbiant fees for their standards documents and then has made itself financially dependent on this revenue. The ANSI SQL:2008 standard is broken into the following part(with Wikipedia links):

ISO/IEC 9075-1:2008 Framework (SQL/Framework)
ISO/IEC 9075-2:2008 Foundation (SQL/Foundation)
ISO/IEC 9075-3:2008 Call-Level Interface (SQL/CLI)
ISO/IEC 9075-4:2008 Persistent Stored Modules (SQL/PSM)
ISO/IEC 9075-9:2008 Management of External Data (SQL/MED)
ISO/IEC 9075-10:2008 Object Language Bindings (SQL/OLB)
ISO/IEC 9075-11:2008 Information and Definition Schemas (SQL/Schemata)
ISO/IEC 9075-13:2008 SQL Routines and Types Using the Java TM Programming Language (SQL/JRT)
ISO/IEC 9075-14:2008 XML-Related Specifications (SQL/XML)

The part 2: Foundation, the core of the standard costs about $450, even just to download the PDF. Getting all of them would probably cost about $1500. Now I ask you, how can they even pretend to aspire to be a unversal standard for SQL when the average professional would have to pay almost 2 weeks salary to have the right to read and reference it?...

Anyway, the SQL:2008 standard came out last summer, Sybase has a nice summary of the new features here. Every couple of months I debate with myself whether or not to pay the $450 to get the Foundation PDF, but I just cannot justify it. Finally it occured to me that I could acheive a kind of compromise by downloading the free PDFs of the draft of the standard from 2006, here. Although only a draft, it is pretty close to that standard that was adopted (or so I here). More importantly, it does incorporate everything that was already in the SQL:2003 standard.

Si I have it now and will be slowly reading through it, making the occasional post about interesting things as I go.

Stuck in the HOS[ITAL

rbarryyoung+movingsql@gmail.com — Sun, 26 Apr 2009 14:05:00 GMT

For anyone who has been wondering where I've been, let's jsut say that it has been an eventful two weeks. I am writing this now from my hospistal bed, which it turns out is very difficult, so please forgive any errors.

I gave my SQL Injection speech two weeks ago (which I think that I did blog about). Then part one of my series "There must be fifteen ways to lose your cursors" was published over at SqlServerCentral.com to a firestorm of response (over 300 posts so far). Then I gave speech on the same topic to Philly.net code camp to very favoarable reviews (so far anyway).

Then last monday I had to go to the ER for intense stomach pains that were diagnosed a Gallstones. Tuesday I go to see a Surgeon about getting my Gall bladder removed. He schedules me for surgery on May 4 and puts me on an "Absolutely no Fat or Glycol" diet, meaning fruits, vegetables, water and skim milk only. Joy!

Thursday I have to leave work with stomach pains so bad I almost don't make it home. Then I have my younger son, Chris drive me to the ER again. They say that is is another Gallstone attack but as an added bonus I now have pancreaitis too.

They say they cannot remove the gall bladder until the pancreaitis goes away, but it is taking its own sweet time. So on monday they will do some surgical procedure to relieve the pancreaitis and assuming that goes OK remove my gall bladder on tuesday.

Of course part 2 of mu series comes out on Monday, so I guess that Jeff and Lynn and Gus and the others will have to field the questions.

Hopefully, I will be back soon ...

Injected, Inspected, Detected, Infected, Neglected and Selected! Re-Update! ...

rbarryyoung+movingsql@gmail.com — Thu, 09 Apr 2009 15:53:00 GMT

The downlod link in the prior update was pointing to the wrong presentation. It has been corrected there and you can get it here as well.

Injected, Inspected, Detected, Infected, Neglected and Selected! Update ...

rbarryyoung+movingsql@gmail.com — Thu, 09 Apr 2009 14:31:00 GMT

I gave this presentation to PSSUG at SCP last night to very favorable reviews. Nonetheless IMHO it is only 2/3rds done, so I hope to refine it quite a bit by the next time that I give it. You can download the slides and code here.

Note: Link was bad (old presentation), it has been fixed.

Injected, Inspected, Detected, Infected, Neglected and Selected!

rbarryyoung+movingsql@gmail.com — Mon, 06 Apr 2009 15:45:00 GMT

Halfway though the most overloaded two weeks I have had in a long time, ... I have finally finished installment #2 of my series "There Must Be 15 Ways to Lose Your Cursors" for SQLServerCentral and I will submit it to Steve soon (lat minut formatting).

Next up is my presentation to PSSUG (Philadelphia Sql Server Users Group, a PASS chapther) the Wednesday evening entitled "Injected, Inspected, Detected, Infected, Neglected and Selected! Using Dynamic SQL Safely, without SQL Injection". (Hmm, I notice that my titles keep getting longer and longer, I wonder if that's a trend?)

I'll be working on this brand new Injection speech over the next few days and then I have the PASS speaker proposals due by midnight Friday. Never mind work and three Easter week services this week. Whew!

My Big Book Week

rbarryyoung+movingsql@gmail.com — Wed, 25 Mar 2009 12:46:00 GMT

Wow, what a time for SQL books. First I received my pre-ordered SQL Server 2008 Query Performance Tuning Distilled by Grant Fritchey last week, about a week earlier than expected. Grant is a friend from SQLServerCentral and PASS 2008 and a great guy to boot. Follow the link to his blog where you will find more links to order his book online.

Then yesterday, I received my pre-ordered SQL Server 2008 Internals by Kalen Delaney, Paul S. Randal, Kimberly L. Tripp, Connor Cunningham, Adam Machanic, and Ben Nevarez two weeks ahead of schedule. The incredible line up of writers on this book has made it the most hotly anticipated new SQL book in many years.

The irony is that becuase of the demands of my current project and because I am already behind in sumbitting my PASS 2009 proposals, I probable won't be able to get to them for a while yet. Oh well. Just thumbing through, both look great though ...

Fast Concatenationof SQL Strings Re-re-visted...

Sat, 28 Feb 2009 22:42:00 GMT

Heh. Well I forgot to post the best version of the XML aggregator when I posted the other day. Then Ward Pond tagged in a post on it (here), and pointed out that I was "improving" on an old version that had been fixed by Adam Machanic among others...

Here is my "good" version that fixes the entitization problem:

SELECT (

SELECT n + ','
FROM (

SELECT 'a AS n
UNION ALL
SELECT 'b>a'
UNION ALL
SELECT 'b&a'
UNION ALL
SELECT 'b

a') r

FOR XML PATH(''), TYPE

).value('.[1]','varchar(max)')
-- =====

And here is the version currenlty being used by Ward and company...:

;WITH ColumnToPivot ([data()]) AS (
    SELECT p.ParentString + N', '
    FROM Parent p
    JOIN Child c
    ON c.ParentId = p.ParentId
    WHERE c.ChildId = 2
    ORDER BY p.ParentId
    FOR XML PATH(''), TYPE
),
    XmlRawData (CSVString) AS (
        SELECT (SELECT [data()] AS mydata FROM ColumnToPivot AS d FOR XML RAW, TYPE).value( '/row[1]/mydata[1]', 'NVARCHAR(max)') AS CSV_Column
)
SELECT LEFT(CSVString, LEN(CSVString)-1) AS CSVList
FROM XmlRawData

Finally, I heard yesterday that Adam is has a new challenge, involving, you guessed it, grouping concatenated strings. Guess I better get busy...

Fast Concatenation of Many/Large Strings, pt2 (FOR XML)

Wed, 25 Feb 2009 15:36:00 GMT

Just a quick note ...

Upon further testing and analysis, I feel confident that the best solution for string concatenation in SQL Server 2005+ is the FOR XML method, like so:

SELECT CAST(
   (SELECT TABLE_NAME+', '
   From INFORMATION_SCHEMA.TABLES
   Order By TABLE_NAME
   FOR XML PATH('')
) as VARCHAR(MAX))

It has a lot of advantages, which I will go into later. It also has one big problem(Entitization) which I will also demonstrate how to fix...

There Must Be 15 Ways To Lose Your Cursors, presentation(SJ-PSSUG)

rbarryyoung+movingsql@gmail.com — Wed, 25 Feb 2009 15:26:00 GMT

Last night I premiered my presentation "There Must Be 15 Ways To Lose Your Cursors" to the South Jersey division of the Philadelphia SQL Server Users Group.

As promised, I have uploaded the presentation and all of the SQL Scripts as a ZIP it to this site. You can download it here.

Fast Concatenation of Many/Large Strings

rbarryyoung+movingsql@gmail.com — Wed, 18 Feb 2009 20:16:00 GMT

Just a quick note on string concatenation:

Concatenation(appending) a table of many small strings together into one big string is easy in SQL Server, but slow. Fixng it is hard for the reasons that I list here. I beleive that I have "solved" this, reducing the O(n²) operation to effectively O(n*Log(n)).

A preliminary version of this technique is demonstrated here. The approach seen in these forum posts can be greatly improved by "stacking" the 2^kroll-ups concatenating all of the lower level strings together at once, instead of "cacading" them up one level at a time, as demonstrated.

I will post a more complete version later...

Calculating Distance using Latitude and Longitude

Sun, 01 Feb 2009 02:40:00 GMT

A fast and accurate way to calculate the distance in miles between two points, based on the latitudes and longitudes.

More...

Ways to Prevent SQL Injection

Fri, 30 Jan 2009 21:25:00 GMT

There are basically three (actually four) strategies for protecting against SQL Injection while allowing some use of Dynamic SQL:

1. BlackListing
2. Firewalling
3. Parametizing
4. Parsing*

"Blacklisting" is the practice of scanning the client-supplied text for certain "blacklisted" strings, such as semicolons or words like "EXEC" and "DROP", as a way of detecting injection attacks. Blacklisting is a bad practice and fails for two simple reasons, first, it is not possible to detect every possible injection attack without actually fully parsing the final Dynamic SQL execution string. No matter how many banned strings you included in your scanner, you still cannot be sure that you have accounted for every posibility. For instance, consider the alternate quotation characters in the upper half of the ASCII character set (and many more in Unicode). Do you know which ones of them can be used to close a quotation? Neither do I, and neither does anybody that I know.

Secondly, blacklisting invariably breaks the application that it is trying to protect by preventing perfectly valid queries from being executed. For instance, a query for an employee named "Sally Waldrop" is unlikley to make it through the blacklist. A report query on a client called "Executive Cleaning" is also likely to be rejected. And the more that you try to fix the first problem by adding more and more strings to the blacklist, the worse you will be making the second problem. So blacklisting is a bad choice both because you cannot be sure that it will work and because you can be sure that it will break your application.

"Firewalling" is the practice of using security settings to restrict the Dynamic SQL's execution context permissions down to only what that Dynamic SQL statement is supposed to do. Thus, for a procedure that is supposed to execute a dynamic SQL string to produce a report on the CLIENTS table, a special database User called "CLIENTS_Reports" would be created that had SELECT permission granted to the CLIENTS table, Like this:

    Grant Select on OBJECT::dbo.CLIENTS to CLIENTS_Reporter

but no rights to anything else, nor any other rights on the CLIENTS table. Then the procedure would execute the dynamic SQL like this:

    Execute(@query) As USER='CLIENTS_Reporter'

The weaknesses of firewalling are first, that it is a point solution only, as a separate database User needs to be created for each separate stored procedure that uses client text in Dynamic SQL. Using a single User for multiple stored procedures can comprise the integrity of this strategy because you may have to combine different permission sets to be sure that this one user can do everything that it might be legitimatelly called on to do. Seperate User accounts for each one is fine if you only have a few of these, but quickly becomes unmanageble with larger numbers. More discussion on this appraoch by me can be found here.

Secondly, it can be difficult with just security settings and permissions to get the fine control that you may need. For instance, an application that supports bank tellers may use dynamic SQL to access account information. While this could be managed with firewalling, you would probably also want to insure that the query could only return information for a single account at a time as any query from a teller that lists information for ALL accounts is likely to be something illegitmate. In this case, firewalling cannot do much for you.

"Parametizing" is the practice of only allowing client-supplied text to be passed through parameters and variables and never actualized as executable code. Thus, instead of code like this:

    Select @query = 'SELECT Column FROM table WHERE key=''' + @UserInput + ''''
    Execute(@query)

The code would be rewritten like this:

    Select @query = 'SELECT Column FROM table WHERE key=@p1'
    Set @P1Def = N'@P1 NVarchar(255)';
    EXEC sp_executesql @query, @P1Def, @UserQuery;

In the first case, a client string of " '; DELETE FROM CLIENTS; --" could spell disaster, however, in the parametized instance, it would just return no records.

The shortcomings of parametization are first, it requires more work and thought than other approaches. Secondly, it initially seems to be applicable to only the simplest cases of dynamic SQL (as above). However, it turns out that there are some tricky ways to use parametization that allow it to be applied safely and sucessfully to all but the most complex cases of dynamic SQL. These methods (variable execution, reconstitution, and directed expression) are beyond the scope of this article, however, it should be noted that they have some disadvantages of their own: primarily that they typically require a stricter and more complex interface between the client and the server procedure and thus cannot usually be implemented with making changes in the client as well.

The fourth stategy, "Parsing" is really only included for completeness as it is not actually implmented in any case that I know of. If it were used, parsing would invovle completely parsing the SQL syntax of dynamic text to be executed and then insuring that it conforms to restrictions defined for that particular use of dynamic SQL (for instance, that it consist of only a single SQL statement). Parsing is not used primarily because it would be just too hard to implement on SQL Server. SQL is not an easy language to parse completely and correctly and SQL is not a language that is well suited the task of syntactically and semantically parsing a string of text. Even setting aside the difficulty of it, the overhead of doing it repeatedly for a heavily used procedure could be severe. Consequently, without some substantial help in the future from SQL Server itself (or some other Microsoft facility), this approach will probably always remain impractical.

Good SQL Injection Article

Wed, 14 Jan 2009 15:41:00 GMT

Here's a good article on SQL Injection by one of my favorite authors, Michael Coles: http://www.sqlservercentral.com/articles/Security/updatedsqlinjection/2065/.

Note that most of the SQL Injection articles will be filed under the "Dynamic SQL" heading.

Rules for Shared Schema Multi-Tenant Databases

Tue, 13 Jan 2009 23:56:00 GMT

I have implemented large shared schema, multi-tenant applications & databases before and it is suprisingly straight-forward as long as you follow certain rules:

1) All application users must be easily distinguishable by tenant to SQL.

Practically speaking this means that either each tenant has a separate Login or each user has a sepearate Login to SQL Server and a Tenant_Users table to map the user back to their tenant association.

2) Every table that the application can write to must have a tenant_id column that identifies the tenant-owner of the data.

3) Every such table must have a corresponding "security" view that insure that any user can only access rows in that table from the same tenant., like so:

CREATE VIEW Secure_TABLE as
 Select * 
  From Physical_TABLE T
   Inner Join Tenant_Users U
    ON U.tenant_id = T.tenant_id
     And U.UserName = sUser_sName()

4) No application code, including client code, application stored procedures, views, etc., is permitted to directly access the physical tables. All such data access is only permitted through the Security views. (use database roles, schemas and permissions to implement this, do not rely on code).

If you notice, this approach insures that, except for the user Logon's, the application has no knowledge of the multi-tenancy, and no application changes should normally be necessary. This means that you can move a tenants data into or out of this approach without any changes to most applications, other than changing the Server.Database address. (note that to be truly transparent you would have to remove the tenant_id from the output of the security views).

The tenant_id should be made the first field of the primary key and probably also the Clustered Index (if different).

Example of Safely Using Dynamic SQL with User Selected Table Name

Tue, 13 Jan 2009 23:20:00 GMT

Recently asked question on SQLServerCentral.com:

Well here's my question. In some of the inline sql they are using variables to create the table name.
The code is in vb.net
name = "joesshop"
Example: "Select * from data_" + name
So the end result would be "Select * from data_joesshop"

Is there anyway to put this in a stored proc?

Dynamic SQL is the standard solution for this kind of problem, but Injection is an overriding concern.

Here is how you can recode this simple example into a stored procedure that protects itself from SQL Injection:

Create proc spSelectFromDataTable(

@SuffixName Nvarchar(255))

/* Procedure to demonstrate how to safely incorporate client

text parameters into a SQL command.

Note that the key to this technique is to NEVER actually

EXECute any string that has client-supplied text. Rather all

of the client text must be purified by replacing it with the known

valid names of the objects being referenced. Then Dynamic

SQL commands can be safely constructed using ONLY our own text and

these idealized replacement values.

BEGIN

Declare @ActualTableName SYSNAME

--Find the actual table name that matches the clients

-- text parameter:

Select @ActualTableName = TABLE_NAME

From INFORMATION_SCHEMA.TABLES

Where TABLE_SCHEMA = N'dbo'

And TABLE_NAME = N'data_' + @SuffixName

--Note that it is safe to use the clients parameter

-- here because it is only being used as a data value,

-- it is not being EXECuted.

IF @ActualTableName IS NOT NULL

BEGIN

--Here is where we construct and execute the Dynamic SQL

EXEC(N'Select * From '+@ActualTableName)

--Note that this execution string contains no part of

-- the clients text parameter, it has been completely

-- replaced with the known, valid, actual table name.

END

ELSE

BEGIN

Declare @msg as NVarchar(MAX)

Select @msg = @SuffixName+N' is an invalid data table name.'

RAISERROR(@msg, 11, 1)

Return

END

Comments welcome.

Ways to Leave Your Cursors

Sat, 13 Dec 2008 18:52:00 GMT

I've been thinking about writing an article that would be titled "15 Ways to Leave Your Cusors". Ideas:

Just put it in a Set, Brett (change the declare cursor select statement into a select)
Put it all together, Heather (later one; using women's names opens this up a lot too)
Put it on the Stack, Jack (recursive CTE's)
Its your last resort Mort (or While/Kyle?)
Select into a variable, Aribel (simple pseudocursor)
Use an ordered update, Nate (complex psuedocursor)
Window on OVER, Grover (Row_Number(), etc.)
Add a WITH CUBE, Noob (Rollup and Cube)
Use a temproary table, Mabel
Just begin again, Man (last one; start over with just specs)
With OUTPUT (for loops with multiple outputs; no cute rhyme yet)
Make a big string, Bing (dynamic SQL)
bring it inline, Caroline
...?

Other ideas: Joins, Exists, IN(), derived tables, Merge(?)

RSS Feeds turned on ...

rbarryyoung+movingsql@gmail.com — Sat, 13 Dec 2008 03:19:00 GMT

Well at Jack Corbett's suggestion I have tunrned on RSS feeds for the blog. I am still not sure if it will work right, so feel free to let me knowof any problems...

Shared Schema implementation of Multi-Tenancy Databases

Mon, 01 Dec 2008 17:27:00 GMT

I've been invovled in some recent discussion on Multi-Tenenacy databases and in particular, the Shared Schema implementation:

http://www.sqlservercentral.com/Forums/FindPost610335.aspx
http://www.sqlservercentral.com/Forums/Topic529170-361-1.aspx
http://www.sqlservercentral.com/Forums/Topic569567-149-1.aspx

Multi-Tenancy databases are databases with multiple customer organizations ("tenants") all running the same application and all using the same database server. The three stadard approaches to this are:

Seperate Databases
Same Database, Seperate Schemas
Shared Schemas

In the last one, all of the tenants use the same tables, but a tenant_id is added to each table to distinguish them. The is an MSDN article that explains this (http://msdn.microsoft.com/en-us/library/aa479086.aspx)

As it happens I designed and implemented a large Shared Schema Multi-Tenant DB several years ago, and learned many insights from that.

The Two-Hop rule

Wed, 26 Nov 2008 21:59:00 GMT

Way back in March I answered question from two posters about the notorious Two-Hop Rule here: www.sqlservercentral.com/Forums/Topic471172-359-1.aspx#bm473248. The thread has a good summary of the problem and cause by me, and an excellent post by Brian Kelley on how to use Kerberos to fix it.

Service Broker Article and tool from ALZDBA

Wed, 22 Oct 2008 13:58:00 GMT

A good article and monitoring script from Johan Bijnens (ALZDBA) at SQLServerCentral.com:

The Article: Adventures with Service Broker

And the monitoring script with just selects from every Service Broker DMV and catalog view: http://www.sqlservercentral.com/scripts/Maintenance+and+Management/31867/

A Lost Scripting Tool for SQL Server 2000 (and 2005?)

Tue, 21 Oct 2008 13:27:00 GMT

Jon Reade describes a little known microsoft upgrade utility (SCPTXFR.EXE) from SQL Server 2000 that can be used to script the objects in a SQL Server 2000 database. Apparently it works for SQL Server 2005 also: www.sqlservercentral.com/articles/Administering/howtoscheduleasqlserverdatabasecreationscript/1834/

Cool function to find Leap Years

Mon, 20 Oct 2008 14:52:00 GMT

Armand Posted this cool function to tell if a specific year is a leap year or not at www.mssqltips.com/tip.asp :

CREATE Function dbo.fn_IsLeapYear (@year INT) returns BIT

BEGIN

RETURN(Select

Case datepart(mm, dateadd(dd, 1, cast((cast(@year as varchar(4)) + '0228') as datetime)))

When 2 Then 1

Else 0 End)

END

Instructional Blog and Videos: script SQL Objects with Powershell

Mon, 20 Oct 2008 14:37:00 GMT

OK, here is the site, blog & post that has the instructions on how to script SQL Server Objects with powershell, first he SQLServerCentral post: http://www.sqlservercentral.com/Forums/FindPost547249.aspx

Also the video Blog post:midnightdba.itbookworm.com/PowershellScriptDBObjects/PowershellScriptDBObjects.html

This is from the Blog: midnightdba.itbookworm.com/

SQL Scripting Tables and other objects with Powershell (2008)

Mon, 20 Oct 2008 14:17:00 GMT

Here is a great Simple-Talk article that explains how to script SQL Server Objects with powershell in SQL Server 2008: www.simple-talk.com/sql/sql-tools/using-powershell-to-generate-table-creation-scripts/

Thing is, I know that there is a shorter post a SQLServerCentral that explains how to do this also, but I cannot seem to find it now...

Injection: Server & Client

Mon, 20 Oct 2008 02:52:00 GMT

Recent conversation at SQLServerCentral.com:

Garadin Said:

In ASP for example, you can do:

sql="SELECT * FROM TABLE WHERE Field ='" & MyVariable & "'"
rs.open sql conn2

Instead of

sql ="Exec dbo.MySP @Variable =" & MyVariable
rs.open sql conn2

That said, I see how the hard coded one has injection vulnerabilities, but how is the SP vulnerable if that variable is used within a structured select statement?

My Response:

Well, let's say that MyVariable was set to " '; DROP TABLE USERS --", then the sql string in your client becomes:

SELECT * FROM TABLE WHERE Field =' '; DROP TABLE USERS --

, which might be a problem .  Effecitvely, the injection happened in the client code, instead of in the SQL Server, but the result is the same.

The question that has to be asked in cases like these is "Where is MyVariable coming from?"  If it is from a user who shouldn't be allowed to drop the Users table then you (both client & server) should take steps to prevent this.  If on the other hand, it is coming from you or another DBA or Admin who could do this anyway, then you do not normally have to be concerned.  The threat with injection is that an unprivliged user can play "stupid string tricks" to hijack the SQL Server rights of the server process, either directly or indirectly (through the client).

That's why protecting against injection is really a two tier defense;

1) The client should never allow user-supplied text to become part of a SQL command that it executes, and...

2) The server should never allow client-supplied text to become part of a SQL command that it executes

#2 is most easily accomplished by forcing the client to use stored procedures and then in those stored procedures, either never use dynamic SQL or by being extremely careful in how you construct the dynamic sql, again, following the rule that client-supplied text (parameters) must never become part of the actual SQL command.  (additionally, you should use different users/schemas to firewall the access rights of each piece of the execution chain).

If you do this, then #1 can be accomplished on the client by never trying to "compose" SQL commands (effectively, dynamic SQL) but rather always calling stored procedures and passing the user-supplied text as parameters.

Typical use of Dynamic SQL for Customizable Reports

Thu, 16 Oct 2008 17:43:00 GMT

(paraphrased from a recent SqlServerCenterl reply I posted)

The procedure below:

CREATE PROCEDURE FindUserDetails(

@SelectColumns varchar(2000),

@WhereClause varchar(2000),

@OrderByClause varchar(50))

Declare @SQL varchar(2000)

Set @SQL = 'SELECT ' + @SelectColumns + '

FROM CustomersDetails

JOIN CompanyDetails ON CompanyID = UserCompanyID

' + @WhereClause + @OrderByClause

EXEC(@SQL)

is pretty much exactly what the term "customizable report" means in any application feature list (not to be confused with "Ad-Hoc Reporting" which is a much more general thing)..

To be clear: the SQL code above is a typical example of a bad use of dynamic SQL. Not because Dynamic SQL is not needed here (it probably is to get decent performance) but because it is extremely unsafe, and there is virtually nothing that can be done within the stored procedure to remedy that.

Client-supplied text should never be allowed to become part of an executable SQL command. That is how SQL Injection happens and that is what "Safe" dynamic SQL must never allow.

Retrieving the default system Trace file information

Wed, 15 Oct 2008 15:48:00 GMT

I saw this over a month ago and I can't beleive that I almost forgot to mention it...

SQL Server 2005 has a system trace running by default. Recently Jack Corbett showed me a way (here) to not only find where these default trace files are, but also how to query & retrieve them into the running system! For example, here is Jacks code that demonstrates how to find Logon events that used a Windows Group to Logon:

SELECT DISTINCT
   I.NTUserName,
   I.loginname,
   I.SessionLoginName,
   I.databasename,
   S.*
FROM
   sys.traces T CROSS Apply
   ::fn_trace_gettable(T.path, 5) I LEFT JOIN
   sys.syslogins S ON
       CONVERT(VARBINARY(MAX), I.loginsid) = S.sid    
WHERE
   S.sid IS NULL

Obviously, this gets reset after every reboot. Is this seriously cool or what?!?