Help! My SQL Server 2008 ‘sa’ account got locked out and I can’t do anything with my databases!

I recently had an issue where the sa account for one of my SQL Servers got locked out. I assume the reason for the lock out was that the account was marked with Enforce password policy and someone had got the password wrong too many times. Regardless, I was no longer able to log in as sa.

Unfortunately there was a database on my server that only the sa account had access to, and it was starting to look like there was no way to get access back. To make matters worse, none of the other accounts had the correct access level to alter the sa account, and without the sa account, I was unable to elevate the access level of existing accounts. My server has mixed-mode authentication enabled but even domain administrator accounts couldn’t touch the sa account. It seemed like the perfect catch-22.

I Googled a lot of pages, many of which weren’t helpful, but then I found this:

Connect to SQL Server When System Administrators Are Locked Out

Good old Microsoft for once came up with the goods with an easy to follow, step-by-step guide to gaining access again.

The solution involved temporarily setting up single-user mode for the server, and then logging in to both the server and SQL Server Management Studio using an account with local machine administrator access. I was then able to remove the lock on the sa account, reset the password and gain access once more. Phew!

For all but production database servers, I would definitely recommend removing the Enforce password policy on all sa accounts in future. In the meantime, I hope this post has been useful.

Taking databases offline in SQL Server 2012

Whenever I try and use the Take Offline feature in SQL Server 2012 via SQL Server Management Studio, the progress window hangs, and no matter how long I wait, nothing seems to happens.

I found an alternative way to do this today, which solves the problem nicely. Simply run the following T-SQL:

ALTER DATABASE {DatabaseName} SET OFFLINE WITH ROLLBACK IMMEDIATE

…and the database goes offline, ready for other operations such as database restores to be performed.

Changing the default naming convention for foriegn keys when using Fluent NHibernate

By default, NHibernate will assume that foreign keys should be named using the convention ReferencedTableName_id (e.g. Person_id). I personally don’t like this conversion and would prefer ReferencedTableNameId (e.g. PersonId).

Fortunately, this is pretty easy to achieve by extending the ForeignKeyConvention class in Fluent NHibernate:

using System;
using FluentNHibernate;
using FluentNHibernate.Conventions;

namespace NHibernate.Helpers.Conventions
{
    public class CustomForeignKeyConvention : ForeignKeyConvention
    {
        protected override string GetKeyName(Member property, System.Type type)
        {
            if (property != null)
            {
                return property.Name + "Id";
            }

            if (type != null)
            {
                return type.Name + "Id";
            }

            // If both are null, we throw an exception:
            throw new ArgumentNullException("property", "The property and type parameters cannot both be null");
        }
    }
}

This needs to be wired into your SessionFactory creation code as follows:

sessionFactory = Fluently.Configure()
    .Database(DatabaseConfiguration)
    .Mappings(x => x.FluentMappings.AddFromAssembly(MappingsAssembly))
    .Mappings(x => x.FluentMappings.Conventions.Add(new CustomForeignKeyConvention()))
    .ExposeConfiguration(x => new SchemaUpdate(x).Execute(false, true))
    .BuildSessionFactory();

Note that the example above will also update the database schema.

Once you’ve done that, all foreign keys will use the desired naming convention.

Getting the auto-incremental ID of a record in SQL Server

Time for another SQL Server aide-memoire…

I can never remember which command to use in T-SQL to get the ID of the most recently added record when using auto-incrementing IDs. There are three choices:

  • @@IDENTITY
  • SCOPE_IDENTITY()
  • IDENT_CURRENT(‘{TableName}’)

However, as described in great and excellent detail by the ‘SQL Authority’ in one of his blog posts, the one to use is SCOPE_IDENTITY(). This to restrict the ID to one generated in the current scope, rather than accidentally getting an ID generated by a trigger further down the line.

Enabling ‘xp_cmdshell’ in SQL Server

I was doing some work with SQL Server today and came across a solution I’ve used a lot over the years. This is a perfect candidate for my “Aides-Memoires” category.

Part of the task involved copy a file from one location to another using T-SQL. This is simple to achieve:

DECLARE @CommandText varchar(1000)
SET @CommandText = 'copy [source] [destination]'
EXEC master..xp_cmdshell @CommandText

However, when I executed the code above I got the following error:

“SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, search for ‘xp_cmdshell’ in SQL Server Books Online.”

The solution to this is simple. Run the following command to enable the use of xp_cmdshell:

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

After running the T-SQL above, the code to copy the file worked as expected.

NOT EXISTS queries

This is the second post in my “Aides-Memoires” category. It’s a bit more complex than my first aides-memoires post on linking JavaScript and CSS, but still pretty basic.

This time I’m tackling the NOT EXISTS query in SQL. Again this is something that I often forget the syntax for and have to Google.

Aside: I haven’t actually had to write a NOT EXISTS query for a number of years now, and with the proliferation of ORMs such as NHibernate and Microsoft Entity Framework I rarely write raw SQL now anyway. However, there are always going to be times when highly complex queries and operations are required, and when execution time is paramount. At those times the most efficient way to get the job done is often to execute SQL directly on the database server using stored procedures, particularly when temporary tables and indexes are required.

For those moments, here’s how to write a NOT EXISTS query…

Note that I’m using T-SQL in SQL Server for these examples, although they will probably work with most relational databases without too much tweaking given the basic nature of the SQL used.

First let’s make some tables:

CREATE TABLE [Table1]
(
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](100) NOT NULL,
	CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED 
	(
		[Id] ASC
	)
)

CREATE TABLE [Table2]
(
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](100) NOT NULL,
	CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED 
	(
		[Id] ASC
	)
)

Now we populate some test data. Note that records are missing from Table2:

TRUNCATE TABLE Table1
TRUNCATE TABLE Table2

INSERT INTO Table1 (Name) VALUES ('One')
INSERT INTO Table1 (Name) VALUES ('Two')
INSERT INTO Table1 (Name) VALUES ('Three')
INSERT INTO Table1 (Name) VALUES ('Four')
INSERT INTO Table1 (Name) VALUES ('Five')

INSERT INTO Table2 (Name) VALUES ('One')
INSERT INTO Table2 (Name) VALUES ('Four')
INSERT INTO Table2 (Name) VALUES ('Five')

SELECT * FROM Table1
SELECT * FROM Table2

Note that I am clearing down the tables each time I add data to ensure a clean starting position, and returning the contents of both tables to show what’s been added. Running the query gives the following results:

NOT EXISTS 1

NOT EXISTS 2

To find rows that exist in Table1 but not Table2, use the following:

SELECT 
	Name
FROM
	Table1 T1
WHERE 
	NOT EXISTS
    (
        SELECT  
			Name
        FROM    
			Table2 T2
        WHERE   
			T1.Name = T2.Name
    )

As expected, this returns the following results set:

NOT EXISTS 3

Finally, to find missing rows and insert them into Table2, use the following:

INSERT INTO Table2
(
	Name
)
SELECT 
	Name
FROM
	Table1 T1
WHERE 
	NOT EXISTS
    (
        SELECT  
			Name
        FROM    
			Table2 T2
        WHERE   
			T1.Name = T2.Name
    )

On execution, this reports:

(2 row(s) affected)

…and a quick look into the Table2 using:

SELECT * FROM Table2

Returns:

NOT EXISTS 6

…as expected. Note that the values are in an odd order, proving that Two and Three were inserted last.

Calculate an average using NHibernate

Yesterday I was writing a dashboard application to summarise data held in a SQL Server database. Part of the dashboard involves showing the average of a certain value in rows added in the last hour, day, week and month. In the good old days this would have been easy; I would have written a stored procedure involing the AVG function. However, as I was using NHibernate I thought I would try and do this without writing any SQL (or indeed HQL). While I am a big fan of NHibernate this was much less straightforward than writing some simple SQL.

Here is the code I came up with:

public double GetAverage(DateTime dateFrom, DateTime dateTo)
{
	var query = Session.QueryOver().SelectList(list => list.SelectAvg(item => item.Value))
		.Where(x => x.Date >= dateFrom && x.Date <= dateTo);

	try
	{
		return query.List().First();
	}
	catch (GenericADOException exception)
	{
		// Note that if there are no results for the period (and the average is null) then we will
		// get a specific error back from NHibernate. We filter this out and return 0:
		if (exception.Message.StartsWith("Unable to perform find[SQL: SQL not available]", StringComparison.OrdinalIgnoreCase) && 
		    exception.InnerException != null && 
		    exception.InnerException.Message.StartsWith("Value cannot be null", StringComparison.OrdinalIgnoreCase))
		{
		    return 0;
		}

		// In all other cases we re-throw:
		throw;
	}
}

I discovered that if the query is ran over a period containing no data NHibernate throws an exception, hence the error trapping which returns 0 if the exact error referring to zero records is encountered.

Interestingly, when I turned on Log4Net SQL logging I found that NHibernate generates exactly the same SQL I would have written myself in the good old days:

SELECT avg(cast(this_.Value as DOUBLE PRECISION)) as y0_ 
FROM [Item] this_ 
WHERE 
	(this_.Date >= @p0 and this_.Date <= @p1);
@p0 = 28/03/2013 20:17:04 [Type: DateTime (0)], 
@p1 = 28/03/2013 21:17:04 [Type: DateTime (0)]

Nice work NHibernate!