More Mixed-Mode Authentication Tips
After reading Geoff Craig's "Best Practices for Mixed Authentication" (August 2003, InstantDoc ID 39439), I wanted to share a few tips of my own. First, administrators often choose mixed-mode authentication instead of Windows authentication for Web environments because many properly secured environments don't have domain relationships between their neutral zone and the databases that reside inside the firewalls. In regard to the article's section about brute-force attacks against the sa password, you can counter such attacks by using a simple procedure that tracks failed user logins. We've set up alerts to page a DBA if the procedure detects more than three failed login attempts.
The article warns about xp_cmdshell being an open window to the OS, but sp_OACreate and other sp_OA* stored procedures also let you create multiple threaded ActiveX objects to do whatever you want on the local system or remote systems. Simply put, you should disable all xp_ and sp_OA* procedures or remove them when possible.
Craig's article focuses on securing the sa password, but you also have to secure your environment correctly. A user account that has access to xp_cmdshell or any other procedure can do a lot of damage, as can users allowed to schedule jobs within SQL Server. One major hole to guard against: scheduling a user-created Data Transformation Services (DTS) package in SQL Server Agent; 90 percent of the time, the DBA will create the account under sa or the account he or she is logged in to under NT authentication and think nothing of it. But you need to check each package before you create and schedule it to ensure it won't cause problems within your environment. Securing your database is more than just securing the database server. You need to open up conversations with your developers and talk about these various security issues. For example, many applications are poorly written and vulnerable to hacking from within the Web browser. It's an ongoing education process.
Unexpected Behavior
In "Survive the (Relational) Divide" (August 2003, InstantDoc ID 39300), why doesn't Itzik Ben-Gan just use ORDER BY materialid in the dbo.fn_generatearr() user-defined function (UDF) instead of creating the dbo.fn_insertpos() function? The assignment SELECT yields the same results because each row is accessed in order.
I'm glad you asked this question because it seems most T-SQL programmers aren't aware that they're using an option that might not always work as they expect. The ORDER BY clause for an assignment SELECT isn't documented, so there's no way to know how SQL Server should treat it. The clause seems to work most of the time as most programmers think it should. But what if the ORDER BY took place in the plan after the assignment? According to a discussion in a private MVP forum with Microsoft developers, there's no guarantee that the assignment will be performed after the sort.
