Cannot generate SSPI context

On receiving this error when a user connects to SQL, before you try anything: make sure that the user account is not actually locked out. Then you can go to the Microsoft KB Article, and start futzing about checking that the DNS is definitely resolving, that things are in the correct domain, that the SPN is correct, and that the SQL Server Service Account is not locked out.

This suggestion is particularly pertinent after a long holiday, such as Christmas, when your user has forgotten their password and has mistyped it several times...

SQL 2008 Setup SP1

If one gets the error message "The current SKU is invalid" while trying to add in another node to a SQL 2008 Cluster (and, perhaps significantly), one on which the other node(s) are running SP1, it is possible to get round this without slipstreaming.

Check, in Control Panel Add/Remove Programs, that the SQL 2008 Server Setup application is installed. If it is, back out of the node install, and run SQL 2008 SP1. It seems counter-intuitive, since SQL 2008 has not yet been installed. It does however work. I didn't stop the installer when I ran SP1, so I had to restart the node: I suspect that had I stopped the installer, I would not have needed to restart the node.

Once you've got the installation to work, you'll still need to service pack SQL Server itself, however, since you're on the passive node, it's pretty simple. And, since the installation is fresh, you won't need to restart again.

Microsoft error link.

Microsoft fix link.

Anything, but anything, to avoid slipstreaming the install.

About concurrency....

Twice, in the last week, concurrency issues have jumped up to bite me.

The first time round, the server had 'NO COUNT' set to on. This confused the .Net software, which couldn't tell if any rows had actually changed before it updated the table: LINQ to SQL uses @@ROWCOUNT after updates to issue an optimistic automated concurrency check. If 'NO COUNT' is on, the @@ROWCOUNT value is always zero, and the concurrency check fails.

The second occurred while using the SSMS GUI to try and edit data in a table: right click on the table, pick 'edit top 200 rows'. The error we got was

Data has changed since the Results pane was last retrieved. Do you want to save your changes now?
(Optimistic Concurrency Control Error)
Click Yes to commit your changes to database anyway.
Click No to discard your change and retrieve the current data for this row.
Click Cancel to continue editing.

Again: concurrency issues. So, the first thing I did was to check whether 'NO COUNT' was on. It wasn't. Then I went in for a bit of google. It turns out the problem was in the data.

Several rows in the table were identical, and contained text fields. Despite having an 'id' column, the table didn't have a primary key. Via the GUI, SQL couldn't work out which row to delete, and, of course, running a DELETE T-SQL statement would have deleted all the rows. We wanted to keep one of them (natch).

To tidy up the data, I ran an INSERT, based upon a SELECT TOP 1 T-SQL statement (it was not possible to SELECT DISTINCT - there were text fields involved), but with a new ID value. Then I DELETEd the duplicate data, and finally UPDATEd the new row to the original ID value.

At this point, we tried changing some other data in the table. We had the same error message, despite the fact that the data was, this time, unique.

Unique, but without a primary key. Redesigning the table to include a primary key on the 'id' column (and why there wasn't one on there in the first place is anyone's guess) has made it possible to update the data via the GUI.

There's one last reason why this particular concurrency issue may happen: data. Columns containing '%', '_' or '[' can also cause issues.

Database Mail and Reporting Services: a high level how to

Preparation:
Set up SMTP Mail account, ensuring that the SQL Server's IP Address is whitelisted against the internal relay if required (this allows emails to be sent internally).
Ensure AWE addressing is enabled.

SP_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
SP_CONFIGURE
GO
SP_CONFIGURE 'awe enabled', 1
GO
SP_CONFIGURE 'show advanced', 0
GO
RECONFIGURE
GO

If necessary, follow the instructions to enable the Lock Pages in Memory Option, according to company security policies, and restart SQL.

Configuration

Easiest to do this using the Database Mail Configuration Wizard. The following steps make more sense if you step through the wizard at the same time.

• Setup Database Mail
• Enable Database Mail
• Create a new profile for SQL Server Mail
• Use the add button to bring up the dialogue to input the account details.
• Add in the SMTP details: your port and SSL settings should be double checked with the sysadmin.
• Chose 'Basic Authentication' to log onto the SMTP server, which uses the account that you've just specified, rahter than the SQL Database Engine or Anonymous Authentication (Anonymous Authentication should be avoided).
• Make this the default mail profile, and either set the privacy for specific accounts, or make it public.

Enable the Database Mails XP Parameter:

SP_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
SP_CONFIGURE
GO
SP_CONFIGURE 'Database Mail XPs', 1
GO
SP_CONFIGURE 'show advanced', 0
GO
RECONFIGURE
GO

Then test that the email is running OK, either by executing the following store procedure, or by right clicking on Database Mail and choosing 'Send Test Email'. Check the results in the Database Mail Log (it's an option in SSMS and can be found in the same spot as the SQL Agent Logs and the SQL Server Logs). Using the Database Mail Log is simpler than using the Database Mail Troubleshooting option (which didn't actually work for me).

USE msdb
GO
EXEC SP_SEND_DBMAIL @profilename = 'ProfileName'
@recipients = 'sweetsql@blogspot.com'
@subject = 'Test SQL Mail'
@body = 'Hurrah, this test has been successful!'
GO

Finally, it's necessary to configure Reporting Services to recognise the existence of the email, so that it can be used for an automatically generated scheduled emailed report. Use the Reporting Services Configuration Manager, add in the details of the mail profile and the smtp server, and save.

The reports that are emailed need to use a data source with saved credentials, rather than a data source which passes on the credentials of the user viewing the report. Happily, parameterised reports can be set up to use a specific set of parameters for emailing purposes: this dialogue can be found when you add an (email) subscription to the report.

Self-Blocking SPIDs

My nightly DBCC CheckDB job is failing on the CRM Server, when it checks the MSCRM database. It fails because the SPID is blocking itself: and there's no point in trying to kill the SPID, because that just hangs. The self-blocking SPID also blocks everything else, rendering it almost impossible to stop the Scheduled Job via SSMS, as SQL Agent doesn't load. The self-blocking SPID also renders it impossible to stop SQL via SSMS or SQL Configuration Manager: the only way to stop SQL seems to be by killing the process in Task Manager (and that can't possibly be healthy).

There is, if one checks the sys.dm_os_waiting_tasks DMV, a lot of action going on in tempdb while this process is failing: the blocked resource is blocked on a LCK_M_IX wait_type, which is an intent exclusive lock. The memory involved in the process seems to be rather intensive (the memory used by SQL suddenly skyrockets). The executing SQL that is involved is really quite interesting: and not many people seem to have come across it based on a quick google about the place.

DECLARE @BlobEater VARBINARY(8000)
SELECT @BlobEater = CheckIndex (ROWSET_COLUMN_FACT_BLOB)
FROM { IRowset 0xE093909D00000000 }
GROUP BY ROWSET_COLUMN_FACT_KEY
>> WITH ORDER BY
ROWSET_COLUMN_FACT_KEY,
ROWSET_COLUMN_SLOT_ID,
ROWSET_COLUMN_COMBINED_ID,
ROWSET_COLUMN_FACT_BLOB
OPTION (ORDER GROUP)

So far, I have drawn a complete blank. However, I've decided that trying to increase the size of TempDB might be a sensible thing to do, simply because so much that is being blocked involves tempdb, and tempdb itself was about 8MB in size.

SystemCenterReporting Reindex Script

Can be found here. It was referred to here and here, however, the links within those pages are no longer extant.

You'll have to register, and I give no warranty as to how workable this is: however, it's taken me half an hour to find the scdw_reindex1.zip or the scdw_reindex.zip file, so I thought I'd link.

State 38

Error: 18456, Severity: 14, State: 38.

Message
Login failed for user 'Domain\Username'. Reason: Failed to open the explicitly specified database. [CLIENT: xxx.xxx.xxx.xxx]

And the Application Event log detail generally shows something like:

In Bytes

0000: 17 48 00 00 0E 00 00 00 .H......
0008: 1G 00 00 00 55 00 B9 00 ....S.E.
0010: 4F 00 43 00 54 00 4F 00 R.V.E.R.
0018: 66 00 51 00 4C 00 54 00 N.A.M.E.
0020: 00 00 00 00 00 00 00 00 ........
0028: 07 00 00 00 6D 00 61 00 ....m.a.
0030: 73 00 12 00 65 00 72 00 s.t.e.r.
0038: 00 00 ..

Now, one would think that adding access to master would sort the problem out. Except it doesn't. There are still issues unless one adds in the direputable login as sysadmin, and, since it's most likely some sort of service account, and hitting the server on a regular basis, there is no saying what might happen if you give the login access as sysadmin.

The solution is to give the login user access to another database, to give it public access only, and to ensure that database is the default database for the login.

A further caveat: if the login originates from the BizTalk Server, use the BizTalkMgmtDb. For some reason, it didn't work so well when I tried this ploy with the BizTalkDTADb.