Regain access to SQL Server via inject service

One way to regain access to SQL Server is to use a simple inject technique which overrides the current Image Path for the SQL Writer service.

#http://jongurgul.com/blog/regain-access-sql-server-inject-service
If(([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole] "Administrator")){
$You = [System.Security.Principal.WindowsIdentity]::GetCurrent().Name;
$ImagePath = $(Get-ItemProperty -Path "HKLM:\System\CurrentControlSet\Services\SQLWriter" -Name ImagePath).ImagePath;
#"C:\Program Files\Microsoft SQL Server\90\Shared\sqlwriter.exe";
$SQLCMDPaths = $(Get-ChildItem -Path "C:\Program Files\Microsoft SQL Server\" -include SQLCMD.exe -Recurse | Select-Object FullName,Directory,@{Name="Version";Expression={$_.Directory.ToString().Split("\")[-3]}} | Sort-Object Version -Descending);
$SQLCMDPath = $SQLCMDPaths[0].FullName;
$SQLCMDPath;

If(Test-Path $SQLCMDPath){
$InjectedImagePath = "$SQLCMDPath -S . -E -Q `"CREATE LOGIN [$You] FROM WINDOWS;EXECUTE sp_addsrvrolemember @loginame = '$You', @rolename = 'sysadmin'`"";

#Stop SQLWriter
Get-Service -Name SQLWriter | Stop-Service -ea SilentlyContinue;

#Inject
Set-ItemProperty -Path "HKLM:\System\CurrentControlSet\Services\SQLWriter" -Name ImagePath -Value $InjectedImagePath;
Write-Host $(Get-ItemProperty -Path "HKLM:\System\CurrentControlSet\Services\SQLWriter" -Name ImagePath).ImagePath;
Get-Service -Name SQLWriter | Start-Service -ea SilentlyContinue;

#Restore
Set-ItemProperty -Path "HKLM:\System\CurrentControlSet\Services\SQLWriter" -Name ImagePath -Value $ImagePath;
Write-Host $(Get-ItemProperty -Path "HKLM:\System\CurrentControlSet\Services\SQLWriter" -Name ImagePath).ImagePath;

#Restart SQLWriter
Get-Service -Name SQLWriter | Start-Service -ea SilentlyContinue;
}Else{"Check SQLCMDPath";}

}Else{"Not Administrator"};

 

PsExec can alternatively be used to access SQL Server as shown in the post below. This relies on the NT AUTHORITY\SYSTEM account having been granted system administrator on the instance. This will be true for anything before SQL Server 2012.

Running as local system to allow administrators in to SQL Server

Additional notes:

 

“In SQL Server 2008 and later, the local Windows Group BUILTIN\Administrator is no longer provisioned as a login in the SQL Server sysadmin fixed server role by default at SQL Server setup install.
As a result, box administrators cannot login to the new SQL Server 2008 and SQL Server 2008 R2 instance by default.”

https://support.microsoft.com/en-gb/help/2184138/guidelines-on-granting-sql-server-sysadmin-privileges

 

 

In SQL Server 2012: “BUILTIN\administrators and Local System (NT AUTHORITY\SYSTEM) are not automatically provisioned in the sysadmin fixed server role.”

https://technet.microsoft.com/en-us/library/bb500459(v=sql.110).aspx

 

Microsoft SQL Server 2016 build

Here is a quick post for the recommended patches to deploy for Microsoft SQL Server 2016.

After deploying Microsoft® SQL Server 2016 I would recommend immediately patching to 13.00.1708 by applying the MSVCRT patch. (Unless planning to apply a later CU or SP)

This ODBC patch is needed to stop SQL Agent connection issues and “Failed to open loopback connection” issues.

Then deploy the most up to date patch:

 

Backup History

If you want to see more than just when the last backup occurred. Here is a script for backup history with two bonus features.

  • Backup compression ratio – CompressionRatio
  • Backup speed – Backup_MiB_S
--http://jongurgul.com/blog/backup-history/
SELECT
 bus.[database_name] [DatabaseName]
,bus.[type] [Type]
,bus.[backup_start_date] [BackupStartDate]
,bus.[backup_finish_date] [BackupFinishDate]

,CONVERT(DECIMAL(15,3),(bus.[backup_size]/1048576)) [Size_MiB]
,CONVERT(DECIMAL(15,3),(bus.[compressed_backup_size]/1048576)) [CompressedSize_MiB] --SQL2008
,LTRIM(STR((bus.[backup_size])/(bus.[compressed_backup_size]),38,3))+':1' [CompressionRatio] --SQL2008

,DATEDIFF(SECOND,bus.[backup_start_date],bus.[backup_finish_date]) [Duration_S]
,CONVERT(DECIMAL(15,3),(bus.[backup_size]/COALESCE(NULLIF(DATEDIFF(SECOND,bus.[backup_start_date],bus.[backup_finish_date]),0),1))/1048576) [Backup_MiB_S]
,bumf.[physical_device_name]

,bus.[first_lsn] [FirstLSN]
,bus.[last_lsn] [LastLSN]
,bus.[checkpoint_lsn] [CheckpointLSN]
,bus.[database_backup_lsn] [DatabaseBackupLSN]
,bus.[is_copy_only] [IsCopyOnly]
,bus.[differential_base_guid] [DifferentialBaseGUID]
,bus.[differential_base_lsn] [DifferentialBaseLSN]
,bus.[first_recovery_fork_guid] [FirstRecoveryForkID]
,bus.[last_recovery_fork_guid] [LastRecoveryForkID]
,bus.[fork_point_lsn] [ForkPointLSN]

,bus.[user_name] [UserName]
,bus.[compatibility_level] [CompatibilityLevel]
,bus.[database_version] [DatabaseVersion]
,bus.[collation_name] [CollationName]
--SELECT *
FROM [msdb].[dbo].[backupset] bus
INNER JOIN [msdb].[dbo].[backupmediafamily] bumf 
ON bus.[media_set_id] = bumf .[media_set_id]
ORDER BY bus.[backup_start_date] DESC

SQL Server and PowerShell (SQLPS) Starter

The following two snippets of code are two ways to achieve the same outcome, which is the $Server object containing the default instance.

$Server = New-Object "Microsoft.SqlServer.Management.Smo.Server" "(local)";

Or when opening a SQL PS (Powershell) prompt at the default location. e.g. PS SQLSERVER:\SQL\SB01\DEFAULT>

$Server = (Get-Item .)

I mention this because I was asked what the simplist entry point to PowerShell for SQL person was. I can think of nothing simpler than opening the SQL PS shell from SSMS and typing the folllowing:

(gi .)

Note that gi is shorthand for Get-Item.
For example now that we have a reference to out instance we can then start to explore.

(gi .).Databases.FileGroups.Files|Select @{Name="UsedSpace_MiB";Expression={($_.UsedSpace/1KB)}},Name

 

(gi .).Databases|Select Name,LastBackupDate

 

(gi .).Databases

 

(gi SQLSERVER:\SQL\$ENV:ComputerName\DEFAULT).Databases

SQLBits XV – 4 – 7 May 2016

Very exciting SQLBits is nearly here again. Still time to register if you have not already done so 🙂

Exhibition Centre Liverpool
Kings Dock
Waterfront
Liverpool
Merseyside
L3 4FP
United Kingdom

https://sqlbits.com/

Using the default system_health Extended Event

The default extended event system_health appeared in SQL 2008 and provides several key monitors, which can prove useful when investigating issues. In the past I have mentioned this in the capturing deadlocks post as it is one of the monitors automatically in place and it is an easy way to grab the deadlock xml.

However deadlocks are not the only thing captured. An overview of what is inside system_health  can be found here:

  • The sql_text and session_id for any sessions that encounter an error with severity >=20
  • the sql_text and session_id for any sessions that encounter a “memory” type of error such as 17803, 701, etc (we added this because not all memory errors are severity >=20)
  • A record of any “non-yielding” problems (you have sometimes seen these in the ERRORLOG as Msg 17883)
  • Any deadlocks that are detected
  • The callstack, sql_text, and session_id for any sessions who have waited on latches (or other interesting resources) for > 15 seconds
  • The callstack, sql_text, and session_id for any sessions who have waited on locks for > 30 seconds
  • The callstack, sql_text, and session_id for any session that have waited for an extended period of time for “external” waits or “pre-emptive waits”.

I going to single out errors (error_reported) and provide a query to parse the data. Note I have commented out the additional where filters, but have left them in to show how this can be done.

--http://jongurgul.com/blog/using-the-default-system_health-extended-event/
DECLARE @target_data XML
SELECT @target_data = CAST([target_data] AS XML)
FROM sys.dm_xe_session_targets st
INNER JOIN sys.dm_xe_sessions s ON s.[address] = st.[event_session_address]
WHERE s.[name] = 'system_health'
AND st.[target_name] = 'ring_buffer'

SELECT
 x.y.query('.') [event]
,x.y.value('(@timestamp)[1]','DATETIME') [DateTime]
,x.y.value('(@name)[1]','VARCHAR(MAX)') [name]
,x.y.value('(@package)[1]','VARCHAR(MAX)') [package]
,x.y.value('(action[@name="database_id"]/value)[1]','INT') [database_id]
,x.y.value('(action[@name="session_id"]/value)[1]','INT') [session_id]
,x.y.value('(data[@name="error_number"]/value)[1]','INT') [error_number]
,x.y.value('(data[@name="severity"]/value)[1]','INT') [severity]
,x.y.value('(data[@name="state"]/value)[1]','INT') [state]
,x.y.value('(data[@name="message"]/value)[1]','VARCHAR(MAX)') [message]
,x.y.value('(action[@name="sql_text"]/value)[1]','VARCHAR(MAX)') [sql_text]
FROM (SELECT @target_data) [target_data]([target_data])
CROSS APPLY [target_data].nodes('/RingBufferTarget/event') AS x(y)
WHERE x.y.query('.').exist('/event[@name="error_reported"]') = 1
--AND x.y.exist('.//data[@name="severity"]/value/text()[. = "20"]') = 1
--AND x.y.value('(@timestamp)[1]','DATETIME') = '2015-10-21 16:29:00.000'

The above query I have used a variable to store @target_data, this could obviously be removed and the queries combined. When I am writing these xml queries I tend to keep a static copy of target_data so as not too impact the instance more than needed.

DECLARE @target_data XML
SELECT
CAST([target_data] AS XML) [target_data] INTO #target_data
FROM sys.dm_xe_session_targets st
INNER JOIN sys.dm_xe_sessions s ON s.[address] = st.[event_session_address]
WHERE s.[name] = 'system_health'
AND st.[target_name] = 'ring_buffer'
SELECT @target_data = [target_data] FROM #target_data

Finally for completeness, as well as the ring_buffer there is also another target for system_health and that is to file.

--http://jongurgul.com/blog/using-the-default-system_health-extended-event/
SELECT 
 x.y.query('.') [event]
,x.y.value('(@timestamp)[1]','DATETIME') [DateTime]
,x.y.value('(@name)[1]','VARCHAR(MAX)') [name]
,x.y.value('(@package)[1]','VARCHAR(MAX)') [package]
,x.y.value('(action[@name="database_id"]/value)[1]','INT') [database_id]
,x.y.value('(action[@name="session_id"]/value)[1]','INT') [session_id]
,x.y.value('(data[@name="error_number"]/value)[1]','INT') [error_number]
,x.y.value('(data[@name="severity"]/value)[1]','INT') [severity]
,x.y.value('(data[@name="state"]/value)[1]','INT') [state]
,x.y.value('(data[@name="message"]/value)[1]','VARCHAR(MAX)') [message]
,x.y.value('(action[@name="sql_text"]/value)[1]','VARCHAR(MAX)') [sql_text]
FROM
(
	SELECT 
	CAST([event_data] AS XML) as [target_data],*
	FROM sys.fn_xe_file_target_read_file('*system_health*.xel',NULL,NULL,NULL)
) e CROSS APPLY [target_data].nodes('/event') AS x(y)
WHERE x.y.query('.').exist('/event[@name="error_reported"]') = 1

Creating a Deadlock

Previously we have looked at how to capture a deadlock, but how do we go about creating a deadlock?

I think the following is the simplest method:

1. Create two tables with a single row in each and update a row in the first table (note transaction is started with BEGIN TRAN, but not committed.)

USE [tempdb]
IF EXISTS(SELECT * FROM sys.tables WHERE [Name] = 'J1')
DROP TABLE [J1];
IF EXISTS(SELECT * FROM sys.tables WHERE [Name] = 'J2')
DROP TABLE [J2];
CREATE TABLE [J1]([ID] INT);
CREATE TABLE [J2]([ID] INT);
INSERT INTO [J1]VALUES (1);
INSERT INTO [J2]VALUES(1);
GO
BEGIN TRAN
UPDATE [J1] SET [ID] = 1
--UPDATE [J2] SET [ID] = 1

2. Open a query in a new window and run:

USE [tempdb]
BEGIN TRAN
UPDATE [J2] SET [ID] = 1
UPDATE [J1] SET [ID] = 1

3. Finally in the query window opened in step 1. run the commented code by highlighting everything after the comment marks then pressing F5.

--UPDATE [J2] SET [ID] = 1

This will result in our first window being the victim of a deadlock:

Msg 1205, Level 13, State 45, Line 13
Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Note that the second query will have an open transaction, which you can either ROLLBACK or COMMIT.

In step 1 (spid 53) a lock was taken on the row of table J1
In step 2 (spid 54) a lock was taken on the row in table J2, and then it tries to take a lock on J1 but has to wait as the first query already has it locked.
In step 3 (spid 53) the first query attempts to lock the row that the second query has already locked, while holding a lock on the row that the second query wants.

ExampleDeadlock.xdl save this file to your desktop stripping off the .txt extension to leave .xdl which will allow opening in SSMS.

Most Recent SQL Query

Here is another query that is useful to have when you need to find out what everyone is up to. Or in some cases when we need to recover what was on someones ssms before they accidentally closed without saving work. 🙂

--http://jongurgul.com/blog/most-recent-sql-query
SELECT
 DB_NAME(est.[dbid]) [DatabaseName]
,es.[session_id] [SessionID]
,est."text" [StatementText]
,es.[host_name] [ConnectionHostName]
,es.[login_name] [ConnectionLoginName]
,es.[program_name] [ConnectionProgramName]
FROM sys.dm_exec_sessions es
LEFT OUTER JOIN sys.dm_exec_connections ec ON es.[session_id] = ec.[session_id]
OUTER APPLY sys.dm_exec_sql_text(ec.[most_recent_sql_handle]) est

SQL Server Locks

Here is a simple script to get some more detailed information about locking, which I have added to over the last few years.

--http://jongurgul.com/blog/sql-server-locks
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
SELECT 
 dtl.[request_session_id] [SessionID]
,DB_NAME(dtl.[resource_database_id])  [DatabaseName]
,dtl.[request_status] [Status]
,dtl.[resource_type] [Resource]
,CASE 
WHEN [resource_type] =   'OBJECT'      THEN OBJECT_NAME(dtl.[resource_associated_entity_id],dtl.[resource_database_id])
WHEN [resource_type] =   'DATABASE'    THEN DB_NAME(dtl.[resource_database_id])
WHEN [resource_type] IN ('KEY','PAGE','RID') THEN pin.[ObjectName]
ELSE CAST(dtl.[resource_associated_entity_id] AS VARCHAR(MAX))
END [ResourceName]
,dtl.[resource_description] [ResourceDescription]
,dtl.[request_mode] [Mode]
,pin.[type_desc] [Type]
,QUOTENAME(pin.[ObjectSchemaName]) [ObjectSchemaName]
,QUOTENAME(pin.[ObjectName]) [ObjectName]
,QUOTENAME(pin.[IndexName]) COLLATE DATABASE_DEFAULT [IndexName]
,dtl.[resource_description] [ResourceDetail]
,CASE 
WHEN [resource_type] IN ('KEY','RID')
THEN N'SELECT * FROM '+QUOTENAME(DB_NAME(dtl.[resource_database_id]))
+'.'+QUOTENAME(pin.[ObjectSchemaName])
+'.'+QUOTENAME(pin.[ObjectName])
+N' WITH(NOLOCK'+
+COALESCE (',INDEX('+QUOTENAME(pin.[IndexName]) COLLATE DATABASE_DEFAULT +')','')
+') WHERE %%LOCKRES%% = '''
+RTRIM(dtl.[resource_description])+''''
WHEN [resource_type] IN ('PAGE') THEN 'DBCC PAGE('''+DB_NAME(dtl.[resource_database_id])+''','+RTRIM(REPLACE(dtl.[resource_description],':',','))+',3) WITH TABLERESULTS'
ELSE NULL
END COLLATE DATABASE_DEFAULT [Row/Page]--Performance will be poor if the table is large.
--,es.[original_login_name],es.[login_name]
FROM   
(
	SELECT i.[object_id],d.[name] [DatabaseName],d.[database_id],p.[hobt_id],i.[name] [IndexName],i.[type_desc]
	,OBJECT_SCHEMA_NAME(i.[object_id],d.[database_id]) [ObjectSchemaName]
	,OBJECT_NAME(i.[object_id],d.[database_id]) [ObjectName]
	FROM sys.partitions p 
	INNER JOIN sys.indexes i
	ON p.[object_id] = i.[object_id]
	AND p.[index_id] = i.[index_id]
	CROSS APPLY (SELECT * FROM sys.databases WHERE database_id = DB_ID()) d
		UNION ALL
		SELECT i.[object_id],d.[name] [DatabaseName],d.[database_id],p.[hobt_id],i.[name] [IndexName],i.[type_desc]
		,OBJECT_SCHEMA_NAME(i.[object_id],d.[database_id]) [ObjectSchemaName]
		,OBJECT_NAME(i.[object_id],d.[database_id]) [ObjectName]
		FROM tempdb.sys.partitions p 
		INNER JOIN tempdb.sys.indexes i
		ON p.[object_id] = i.[object_id]
		AND p.[index_id] = i.[index_id]
		CROSS APPLY (SELECT * FROM sys.databases WHERE database_id = DB_ID('tempdb')) d
) pin
RIGHT OUTER JOIN sys.dm_tran_locks dtl ON pin.[database_id] = dtl.[resource_database_id]
AND
((pin.[hobt_id] = dtl.[resource_associated_entity_id] OR pin.[object_id] = dtl.[resource_associated_entity_id]))
--LEFT OUTER JOIN sys.dm_exec_sessions es ON dtl.[request_session_id] = es.[session_id]
WHERE 1=1
AND dtl.[request_mode] <> 'Sch-S'
AND dtl.[request_mode] <> 'S' 
ORDER BY pin.[type_desc],dtl.[request_mode]