Two ways to know the underlying OS version number by T-SQL

The very quick one is select @@version. Another way is to take advantage of extended stored procedure xp_msver, for example, exec xp_msver ‘WindowsVersion’.

Error: Login failed for user ”. Reason: Server is in single user mode. Only one administrator can connect at this time.

I encountered this error message during a project to move system databases while having to restart SQL Server service with /f /T3608 trace flags and hence into single user mode. However when trying to log on and issues scripts for moving mssqlresource database, I constantly got denied to access by either SSMS or command line and the error message was the same as the title suggests.

After googling for awhile, here is my working solution:

1. restart SQL Server service in normal mode and change SQL Server access to local connection only (you can do this either by sp_configure or surface area configuration tool).

2. make sure that “shared memory protocol” is enabled in SQL Server configuration manager. Reason is that local connectioins to SQL Server always go through shared memory first.

And then you are all set, try to connect to SQL Server instance locally through sqlcmd and issue whatever scripts that you want. At least I didn’t encounter any trouble under these settings on that day.

Two quick tips to improve backup and restore performance from Brent Ozar

Error: “Cannot generate SSPI context” when connect to a SQL Server instance after service account change

I’ve recently encountered this problem because to update our security standards, all SQL Server service accounts have to be changed to normal domain user accounts. And after changing them from LocalSystem to new accounts, this error starts to pop up. The cause is fairly simple and the following text is from page 113 of Microsoft’s ITP training book for exam 70-443, which I happen to be reading at the moment of writing this post:

To use Kerberos mutual authentication, you must ensure that the SQL Server 2005 instances have an SPN configured in Active Directory. When you configure SQL Server to run under the local system account, this SPN is automatically created. However, if you configure SQL Server to run under a service account, you should create the SPN manually. To configure an SPN for SQL Server, use the SETSPN utility available on the Windows Server 2003 CD.

 Everytime the SQL Server service is started, the underlying service account tries to register SPN in active directory and the domain user account used as the service account might lack the permission to do so.  Click here or here to refer to Microsoft’s official guides to manually register SPN for SQL Server service. Also another article shows how to assign permissions to SQL Server service accounts to still automatically register SPN themselves. And finally, Microsoft official KB article targeted especially at the error message in the title is here.

How to connect to SQL Server Configuration Manager from another server

Very neat approach which I didn’t know up until today. Here is the link from Microsoft.

However, I am still struggling with a server where either local or remote configuration manager fail to report correct statuses of all SQL Server related services. The error message are simple text “The remote procedure call failed. [0x800706be]” locally and “Cannot connet to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 servers with SQL Server Configuration Manager.”

I am striving to find out a solution and will definitely update this post if I do.

Error “Alter failed for Server ‘[servername]‘ “in maintenance plan’s “check database integrity task”

This error happened in one of the SQL2005 SP4 instances I am managing. The root cause of this issue is that the integrity check task runs statement ‘EXEC sys.sp_configure N'user options', 0 RECONFIGURE‘ in the background and this statement conflicts with another “allow update” (with value of 1) setting to system catalogs. To put it another way, if your “sp_configure 'allow update'” is valued at 1 (meaning system catalogs can be updated) and you run “sp_configure N'user options', 0 RECONFIGURE” (which will try to update system catalogs), an error message will be reported: Ad hoc update to system catalogs is not supported. However, in SQL2005 and newer, updates on system catalogs are not possible anymore, so the false setting of ‘allow update’ to 1 is meaningless and is causing unexpected troubles like this. The solution is very simple: just configure the ‘allow update’ option from value 1 back to 0 which it should be by running “sp_configure 'allow updates', 0 reconfigure“. Also another good practice is to disable ‘allow update’ option for all 2005+ instances.

A very interesting paragraph from a Microsoft KB article about NT AUTHORITY\SYSTEM and BUILTIN\Administrators logins

The original article is here and the quoted text is as follows:

The NT AUTHORITY\SYSTEM account
The NT AUTHORITY\SYSTEM account is also granted a SQL Server login. The NT AUTHORITY\SYSTEM account is provisioned in the SYSADMIN fixed server role. Do not delete this account or remove it from the SYSADMIN fixed server role. The NTAUTHORITY\SYSTEM account is used by Microsoft Update and by Microsoft SMS to apply service packs and hotfixes to a SQL Server 2005 installation. The NTAUTHORITY\SYSTEM account is also used by the SQL Writer Service.

Also, if SQL Server 2005 is started in single-user mode, any user who has membership in the BUILTIN\Administrators group can connect to SQL Server 2005 as a SQL Server administrator. The user can connect regardless of whether the BUILTIN\Administrators group has been granted a server login that is provisioned in the SYSADMIN fixed server role. This behavior is by design. This behavior is intended to be used for data recovery scenarios.

As for the argument whether or not to delete login “BUILTIN\Administrators”, this article basically anwsers it all. Since all local administrators can still access database engine during single user mode which only occurs during disaster recovery situations, it is very safe to remove “BUILTIN\Administrators” to reduce hackers’ attack vector. However, the second bold part sounds a little bit ambiguous and I am planning to test it out myself on a text box soon.

Update on April 13, 2011

I just carried a test about the default Built-in administrators’ ability to log on to SQL Server as sysadmin when the SQL Server servcie is running under single user mode. And the test result is positive. You actually can delete the login “BUILTIN\Administrtors” and still use a local admin account to log on to SQL Server as a sysadmin under single user mode. Once the SQL Server service is backup to multiple user mode, the local admin account failed to log in.

Therefore, to have a super compliant and secure management on SQL Server sysadmin accounts, the best thing to do is to put all user accounts in an AD group and assign this group as sysadmin role and delete BUILTIN\Administrators from the list of logins in SQL Server.