Making the GO command live up to its fullest potential

This post has been migrated to my new blog on SQLBlog.com. You can find this post at the following address:

http://sqlblog.com/blogs/jonathan_kehayias/archive/2008/06/28/making-the-go-command-live-up-to-its-fullest-potential.aspx

Forcing a DEADLOCK_PRIORITY for a Login?

A question was asked on the MSDN Forums regarding the ability to force a DEADLOCK_PRIORITY level for a specific Login in the database engine.  This seemed at first like it would be something that was in the database engine that I had just never had the need for or opportunity to use.  One suggestion was to utilize a LOGON trigger to SET the option.  My first inclination was that this wouldn't work but rather than hitting the Books Online I decided to give it a shot with the following:

CREATE TRIGGER [EscalateDeadlockPriority]
ON ALL SERVER WITH EXECUTE AS CALLER
AFTER LOGON 
AS
BEGIN
    IF RTRIM(LTRIM(SUSER_SNAME())) = 'logintest'
    BEGIN
        --PRINT 'setting deadlock_priority high';
        SET DEADLOCK_PRIORITY HIGH;
    END 
END

Then I created the testing Login:

CREATE LOGIN [logintest] WITH PASSWORD=N'logintest', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

and finally I tested the connection by using SQLCMD from the command prompt:

sqlcmd -U logintest -P logintest -S oshodb05 -Q "SELECT deadlock_priority from sys.dm_exec_sessions where session_id = @@spid "

This resulted in a return result of 0 which means Normal Priority.  So why won't this work?  The answer is actually really simple, something I already knew had to try this out anyway, and documented in the Books Online in SET (Transact-SQL)

If a SET statement is run in a stored procedure or trigger, the value of the SET option is restored after control is returned from the stored procedure or trigger. Also, if a SET statement is specified in a dynamic SQL string that is run by using either sp_executesql or EXECUTE, the value of the SET option is restored after control is returned from the batch specified in the dynamic SQL string.

This is scoped only to the Transaction inside the Trigger, so unfortunately this is not going to solve this problem.  I did some searching online and found the following connect item for SQL Server that documents a request for this:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=263809&wa=wsignin1.0

Thankfully the response from Microsoft is that this is in consideration from Microsoft for a future release/edition, though it doesn't seem to have made it into the SQL Server 2008 product line as of RC0.

SQL Injection on the Rise

Microsoft released a new advisory today on the rising number of instances of SQL Injection that are happening on the web. 

To view the security advisory, visit the following Microsoft Web site:

http://www.microsoft.com/technet/security/advisory/954462.mspx

If you have web sites that concatenate unchecked user inputs or parameter line arguments into SQL database calls, then it is important that you fix this problem following the guidelines on this advisory.  This is not all to uncommon, nor is it overly surprising to see this more and more.  This is a common question on the forums, and more than a few posts have been dedicated to trying to help people look into these problems after being attacked.

Securing Production Servers from Developers with SSMS and Application Usernames/Passwords

This post has been migrated to my new blog on SQLBlog.com. You can find this post at the following address:

http://sqlblog.com/blogs/jonathan_kehayias/archive/2008/06/18/securing-production-servers-from-developers-with-ssms-and-application-usernames-passwords.aspx

SQL Server 2008 RC0 - Publicly Available

Today at TechEd 2008 in Orlando, Bob Muglia, Microsoft Vice President of Server and Tools, announced that SQL Server 2008 Release Candidate 0 (RC0) has been made available for public download. As previously announced, RC0 marks the final public release before SQL Server 2008 RTMs in Q3 of this year

• Download SQL Server 2008 RC0
• What's new in SQL Server 2008

SQL Error 10310

The Text for this error is:

AppDomain %i (%.*ls) is marked for unload due to common language runtime (CLR) or security data definition language (DDL)

But there is no reference for this in the BOL at all.  If anyone has a reference for this please email me.

SQL 2008 Unattended Install is Easier

One of things I use fairly often is the unattended install scripting in SQL Server.  I like to know that all my servers are built exactly the same and configured from installation identically with the same install paths, service accounts, and startup configurations.  While installing SQL 2008 RC0, I noticed the following screen:

The path at the bottom is the ini file that the installer generates to run setup.  This means that you can use the installer now to generate your ini file, and then do any remaining customization required which upon review seems to be minimal.

Fun at SQL Saturday - SQL 2008 RC0 on Subscriber Downloads

First things first.  I had a blast at SQL Saturday in Orlando, and finally putting face to the names I interact with on the SQL Forums, namely Kent Waldrop and Arnie Rowland.  I also ran into some old friends from a previous job, as well as members of the Tampa SQL Users Group which was fun as well.

Now for the big news from the event, SQL 2008 Release Candidate 0 is now available on the MSDN Subscriber Downloads.  You must have an MSDN Subscription at this point to download it, but this is supposed to change in the next day or two.  I am downloading it now, and will be updating my slide decks for my June 17th presentation to the Tampa Users Group on the Manageability Features new to 2008. 

The overviews provided at the SQL Saturday event were exciting and I can't wait to play with it tomorrow.

Convert C# to VB.NET and Vice Versa

Not really a post dealing with SQL Server, but something I have found very useful in building the SQL CLR site Examples since I want them to be available in both VB.net and C#.  Developer Fusion has a really nice tool that converts code from C# to VB and vice versa online.

http://labs.developerfusion.co.uk/convert/csharp-to-vb.aspx

For those who learned C#, the VB.net coding paradigm is somewhat confusing to figure out.  I can easily rewrite a VB.net code file into C#, but going the other way has been extremely difficult for me to do. 

Installing SQL Server 2005 on Vista Without Having to Provision Windows Users as Administrators

A lot of questions get asked on the MSDN SQL Forums about Installing SQL Server on Vista and problems with Windows Accounts in the SysAdmin role due to the UAC.  The Provisioning Tool for Vista that came with Service Pack 2 allows you to Provision a user to function as a SysAdmin inside of SQL Server. However, while answering a question on the forums that I could not seem to break through the problem without actually installing Vista and SQL Server on Vista, I found out that it is not necessary to Provision a User Account for it to be a SysAdmin inside of SQL Server.

I have always followed best practice installations for installing SQL Server whether it be a simple demo/test install or a full on production SQL Server my business will use.  That being what it is, I always use SQL Authentication because I like having the sa user with an extremely complex password for those holy crap moments when I need the extra backdoor to SQL Server in a pinch.  One of the first things I always do is remove Builtin\Administrators from SQL.  If you do this, you have to explicitly add a Windows Account to SQL Server for it to login to SQL, and you have to explicitly assign the account to the SysAdmin fixed Server Role.  Doing this removes the need to Provision the User inside of SQL Server since the account does not elevate its OS permissions as an Builtin\Administrator to access SQL.

If you are following Microsoft Security Best Practices, you shouldn't have to provision any windows accounts for them to access SQL Server.  My recommendation to anyone using Vista would be to remove the Builtin\Administrators Group from SQL Server, and to explicitly add the correct accounts to SQL as a SysAdmin thereby eliminating the need for escalating permissions.  I am surprised that Microsoft took the route of providing a provisioning tool to do what is a polar opposite of the recommendations that have been made since SQL Server 2000.  The removal of Builtin\Administrators was covered in the following articles:

SQL Server 2000 Operations Guide: Security Administration (search for Builtin\Administrators)

SQL Server 2005 Security Best Practices - Operational and Administrative Tasks

What is most interesting is that the SQL Express Blog and the Books Online both cover creating the Windows Logins as SQL Logins to avoid running under escalated privileges but no one seems to offer this advice or follow it.  Something new to consider and pass along on the forums as I answer questions.