Showing posts with label TSQL. Show all posts
Showing posts with label TSQL. Show all posts

Thursday, 1 October 2020

Sql Server features affecting security

A number of security benchmarks (e.g. CIS v1.0.0, FedRAMP, ..) those days we are recommending to disable Microsoft Sql Server features such as remote access, contained database authentication, cross db ownership chaining, allow updates, .. unless we actually have a real requirement for those features.

The rationale is that disabling those features, we would shrink the surface attack area.

A first step we can take is to get a report of which features are actually enabled in our database systems. The following query will do the deed (per instance):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
CREATE TABLE #Database
(
[Name] VARCHAR(255),
[Feature] VARCHAR(255)
)
 
EXEC sp_MSforeachdb N'
BEGIN
	INSERT INTO	#Database
	SELECT	''?'' AS [Name], NAME AS [Feature]
	FROM	sys.configurations
	WHERE	NAME IN ( ''allow updates'', ''cross db ownership chaining'',
                 ''contained database authentication'', ''remote access'')
			AND Cast(value AS INT) = 1
END
'
SELECT * FROM #Database
DROP TABLE #Database

What if we find out that some of those features affecting security is actually enabled?
Here is a query which will reconfigure all the databases in a given instance, disabling 
remote access, one of those features:. 

1
2
3
4
5
6
7
EXEC sp_MSforeachdb N'
BEGIN
	EXEC sp_configure ''show advanced options'', 1 RECONFIGURE WITH OVERRIDE
	EXEC sp_configure ''remote access'', 0 RECONFIGURE
	EXEC sp_configure ''show advanced options'', 0 RECONFIGURE
END
'

As usual:

Caveat: generally, don't use the above or similar scripts in Production, as long as you don't understand and accept the consequences. 

Caveat: always read the message log.

Caveat: sp_MSforeachdb is undocumented, and AFAIK unsupported.

Caveat: the code above is provided "as is", without warranty of any kind, express or implied, including but not limited to the warranties of merchantability, fitness for a particular purpose and noninfringement. in no event shall the author be liable for any claim, damages or other liability, whether in an action of contract, tort or otherwise, arising from, out of or in connection with the software or the use or other dealings in the code above.

Tuesday, 12 May 2015

Simplify and Shrink

The following scripts sets the non system databases to simple recovery model, and shrinks the related log files:

EXEC sp_MSforeachdb N'IF DatabasePropertyEx(''?'', ''Recovery'')=''FULL''
    and   DatabasePropertyEx(''?'', ''Status'')=''ONLINE''
    and ''?'' not in (''tempdb'') and ''?'' not in (''master'') and ''?'' not in (''model'') and ''?'' not in (''msdb'')
begin
  exec ('' print char(13) + char(10) + ''''Set recovery model to simple for '''' + ''''?'''';
 alter database [?] set recovery simple with NO_WAIT;'')
end'

EXEC sp_MSforeachdb 
N'IF DatabasePropertyEx(''?'', ''Status'')=''ONLINE''
    and ''?'' not in (''tempdb'') and ''?'' not in (''master'') and ''?'' not in (''model'') and ''?'' not in (''msdb'')
begin
 exec ('' use [?];
 declare @logFile varchar(128);
 select @logFile= mf.name from sys.master_files mf inner join sys.databases db on mf.database_id = db.database_id where type=1 and db.name = ''''?'''';
 print char(13) + char(10) + ''''Shrink ''''+ @logFile + '''' log file of ?'''';
 dbcc shrinkfile (@logFile , 0)
 '')
end'

Caveat: generally, don't use the above or similar scripts in Production, as long as you don't understand and accept the consequences. The Simple recovery model is usually fine in Test or Development environments, but again, it may not be appropriate in many scenarios.

Caveat: always read the message log.

Caveat: the second script assumption is that there is no more than 1 log file per database. If there are multiple log files per database, it would be more sensible to look at a solution which doesn't use sp_MSforeachdb.

Caveat: sp_MSforeachdb is undocumented, and AFAIK unsupported.

Caveat: the code above is provided "as is", without warranty of any kind, express or implied, including but not limited to the warranties of merchantability, fitness for a particular purpose and noninfringement. in no event shall the author be liable for any claim, damages or other liability, whether in an action of contract, tort or otherwise, arising from, out of or in connection with the software or the use or other dealings in the code above.

Wednesday, 4 April 2007

Transaction and Concurrency on SQL Server 2005

Ayende recently posted an article about Transaction and Concurrency, and yeah, I read his blog very often, and if you are a .Net developer you should do it as well.
He had noticed that using the ReadCommitted isolation level something was not working as he had expected. I have to confess that when I first read his post and some of the comments, I had superficially concluded that could be related to the emergence of the Phantom phenomenon.

The SQL standard (at last SQL-92 and SQL-99) is defining the Phantom phenomenon as such:

P3 (‘‘Phantom’’): SQL-transaction T1 reads the set of rows N that satisfy some <search>.
SQL-transaction T2 then executes SQL-statements that generate one or more rows that satisfy the <search> used by SQL-transaction T1. If SQL-transaction T1 then repeats the initial read with the same <search>, it obtains a different collection of rows.


Today I tried to test Ayende's code, and I finally understood the issue is far more interesting than what I was supposing (lecture 1: run the code you are trying to understand).

Under the hypothesis of Ayende (which are far from being exotic, but for a little point), the ReadCommitted isolation level doesn't work as expected on Microsoft SQL Server 2005! Kudos to Stuart Carnie, who had grokked this well before me, and pointed out a very interesting article from Tony Rogerson.

Rogerson's conclusions are that on Microsoft SQL Server 2005 the ReadCommitted isolation level (and its ReadCommittedSnapshot sibling) "does not give a point in time view of your data", so when the need arises, the safe way to go may be to use the Snapshot isolation level (which incidentally seems to have been a playtoy of Microsoft Research since 10 years).

Today I played a bit with Ayende's code, and I wrote a little console application I called TransactionsAndConcurrency.exe:
TransactionsAndConcurrency mode ilp ilc [iterations [records]]
The mode argument can be one of "aye", "aye-run" or "ale", ilp and ilc one of "ch", "rc", "rr", "ru", "se", "sn" (respectively Chaos, Read Committed, Repeatable Read, Read Uncommitted, Serializable and Snapshot) while iterations and records should be self-explanative ints.
In example you may call this little console application as such:
TransactionsAndConcurrency aye rc rc
or as such:
TransactionsAndConcurrency ale rc rc 20 500
You will notice that when called with aye, this application is probably working as Ayende's code, with aye-run with a slightly different behaviour (doesn't stop the first time the consumer fetch an "unexpected" amount of rows) instead with ale unexpectedly works (I tested it until with rc and ru until 5000 various times, not a single glitch).
What is the difference? The exotic point on Ayende's hypothesis, that is that its table doesn't have a primary key. If the same exercise is done with a table with a primary key (using a surrogate key through the T-SQL Identity column) everything goes fine.

I have to confess that I am still wondering if this is a bug of Microsoft SQL Server 2005, because those are obviously uncommitted phantom rows that are showing up because something in the range lock of the tables without primary keys is obviously not working as most of us would expect.

If you wish to play as well, here is the code:
using System;
using System.Data.SqlClient;
using System.Threading;
using System.Data;

namespace TransactionsAndConcurrency
{
public class Program
{
#region Vars and Consts
private const int DEFAULT_ITERATIONS = 20;
private const int DEFAULT_RECORDS = 500;

private static int records = DEFAULT_RECORDS;
private static int iterations = DEFAULT_ITERATIONS;
private static IsolationLevel isolationLevelProducer = IsolationLevel.Unspecified;
private static IsolationLevel isolationLevelConsumer = IsolationLevel.Unspecified;
private static string mode;

private static string connectionString = "Data Source=myDB;Initial Catalog=test;User=sa;Pwd=ICannotSay1!;";
#endregion

#region Setup
private static void Setup()
{
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
SqlTransaction sqlTransaction = connection.BeginTransaction();
for (int i = 0; i < records; i++)
{
SqlCommand sqlCommand = connection.CreateCommand();
sqlCommand.Transaction = sqlTransaction;
if (mode != "ale")
{
sqlCommand.CommandText = "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[t]') AND type in (N'U')) DROP TABLE [dbo].[t]";
sqlCommand.ExecuteNonQuery();
string create = "CREATE TABLE [dbo].[t]("
+ " [id] [int] NOT NULL"
+ " ) ON [PRIMARY]";
sqlCommand.CommandText = create;
sqlCommand.ExecuteNonQuery();
}
else
{
sqlCommand.CommandText = "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[s]') AND type in (N'U')) DROP TABLE [dbo].[s]";
sqlCommand.ExecuteNonQuery();
string create = "CREATE TABLE [dbo].[s]("
+ " [id] [int] IDENTITY(1,1) NOT NULL,"
+ " [field] [int] NULL,"
+ " CONSTRAINT [PK_s] PRIMARY KEY CLUSTERED"
+ " ([id] ASC) WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]"
+ ") ON [PRIMARY]";
sqlCommand.CommandText = create;
sqlCommand.ExecuteNonQuery();
}
sqlCommand.Dispose();
}

sqlTransaction.Commit();
Console.WriteLine("Create table");
connection.Close();
}
#endregion

#region TearDown
private static void TearDown()
{
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
SqlTransaction sqlTransaction = connection.BeginTransaction();
for (int i = 0; i < records; i++)
{
SqlCommand sqlCommand = connection.CreateCommand();
sqlCommand.Transaction = sqlTransaction;
if (mode != "ale")
{
sqlCommand.CommandText = "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[t]') AND type in (N'U')) DROP TABLE [dbo].[t]";
sqlCommand.ExecuteNonQuery();
}
else
{
sqlCommand.CommandText = "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[s]') AND type in (N'U')) DROP TABLE [dbo].[s]";
sqlCommand.ExecuteNonQuery();
}
sqlCommand.Dispose();
}

sqlTransaction.Commit();
Console.WriteLine("Drop table");
connection.Close();
}
#endregion

#region Producer
private static void Producer()
{

SqlConnection connection = new SqlConnection(connectionString);
int iteration = 0;
while (true)
{
connection.Open();
SqlTransaction sqlTransaction = connection.BeginTransaction(isolationLevelProducer);
for (int i = 0; i < records; i++)
{
SqlCommand sqlCommand = connection.CreateCommand();
sqlCommand.Transaction = sqlTransaction;
if (mode != "ale")
sqlCommand.CommandText = "INSERT INTO t (Id) VALUES(@p1)";
else
sqlCommand.CommandText = "INSERT INTO s (field) VALUES(@p1)";
sqlCommand.Parameters.AddWithValue("@p1", iteration);
sqlCommand.ExecuteNonQuery();
sqlCommand.Dispose();
}

sqlTransaction.Commit();
Console.WriteLine("Wrote {0} records in iteration {1}", records, iteration+1);
iteration += 1;
connection.Close();
if (iteration == iterations)
return;
}
}
#endregion

#region Consumer
private static void Consumer()
{

SqlConnection connection = new SqlConnection(connectionString);
int iteration = 0;
while (true)
{
connection.Open();
SqlTransaction sqlTransaction = connection.BeginTransaction(isolationLevelConsumer);
SqlCommand sqlCommand = connection.CreateCommand();
sqlCommand.Transaction = sqlTransaction;
if (mode != "ale")
sqlCommand.CommandText = "SELECT COUNT(*) FROM t GROUP BY id ORDER BY id ASC";
else
sqlCommand.CommandText = "SELECT COUNT(*) FROM s GROUP BY field";
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
if (sqlDataReader.RecordsAffected != -1)
Console.WriteLine("Read: {0}", sqlDataReader.RecordsAffected);
while (sqlDataReader.Read())
{
int count = sqlDataReader.GetInt32(0);
if (mode != "ale")
Console.WriteLine("Count = {0} in {1} iteration", count, iteration+1);
if (count != records)
{
if (mode == "ale")
Console.WriteLine("Count = {0} in {1} iteration", count, iteration+1);
if (!(mode == "aye-run"))
Environment.Exit(1);
}
}

sqlDataReader.Dispose();
sqlCommand.Dispose();
sqlTransaction.Commit();
iteration += 1;
connection.Close();
if (iteration == iterations)
return;
}
}
#endregion

#region Delete
private static void Delete()
{
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
SqlTransaction sqlTransaction = connection.BeginTransaction();
for (int i = 0; i < records; i++)
{
SqlCommand sqlCommand = connection.CreateCommand();
sqlCommand.Transaction = sqlTransaction;
if (mode != "ale")
{
sqlCommand.CommandText = "DELETE FROM t";
sqlCommand.ExecuteNonQuery();
}
else
{
sqlCommand.CommandText = "DELETE FROM s";
sqlCommand.ExecuteNonQuery();
}
sqlCommand.Dispose();
}

sqlTransaction.Commit();
Console.WriteLine("Delete data from table");
connection.Close();
}
#endregion

#region Main
private static void Main(string[] args)
{
// string describing the isolation level of the producer
string ilp = string.Empty;
// string describing the isolation level of the consumer
string ilc = string.Empty;

if ((args.Length > 2) && (args.Length < 6))
{
mode = args[0];
if ((mode != "aye") &&amp;amp;amp;amp; (mode != "aye-run") && (mode != "ale"))
Environment.Exit(2);
ilp = args[1];
ilc = args[2];
}
else
Environment.Exit(3);
if (args.Length > 3)
int.TryParse(args[3], out iterations);
if (args.Length == 5)
int.TryParse(args[4], out records);

isolationLevelProducer = getIsolationLevel(ilp);
isolationLevelConsumer = getIsolationLevel(ilc);

try
{
Setup();
Delete();
Thread p = new Thread(Producer);
Thread c = new Thread(Consumer);
p.Start();
c.Start();
while ((p.IsAlive) || c.IsAlive)
{ }
}
finally
{
TearDown();
}
}
#endregion

#region Utils
private static IsolationLevel getIsolationLevel(string isolationLevel)
{
IsolationLevel il = IsolationLevel.Unspecified;

switch (isolationLevel)
{
case "ch":
il = IsolationLevel.Chaos;
break;
case "rc":
il = IsolationLevel.ReadCommitted;
break;
case "ru":
il = IsolationLevel.ReadUncommitted;
break;
case "rr":
il = IsolationLevel.RepeatableRead;
break;
case "se":
il = IsolationLevel.Serializable;
break;
case "sn":
il = IsolationLevel.Snapshot;
break;
default:
il = IsolationLevel.Unspecified;
break;
}
return il;
}
#endregion
}
}



Friday, 26 January 2007

TSQL Script to get the space used by every table in a SQL Server 2005 database

Here is a TSQL script (developed, tested and used only on Microsoft SQL Server 2005) to get the space used by the tables of a database.
declare @sql nvarchar(MAX)
create table #usedSpace (
name nvarchar
(128),
rows varchar(11),
reserved varchar(18),
data varchar(18),
index_size varchar(18),
unused varchar(18))

declare @table nvarchar(MAX)
declare tabcur cursor for
select s.[name] +'.'+ t.[name] as [name]
from
sys.tables t inner join sys.schemas s on t.schema_id = s.schema_id
order by
s.[name], t.[name];

open tabcur;
fetch next from tabcur into @table;
while @@FETCH_STATUS = 0
begin
fetch
next from tabcur into @table;

select @sql = 'sp_executesql N''insert #usedSpace exec sp_spaceused ''''' + @table + ''''''''
-- print @sql -- uncomment if you wish to read the sql statements
exec (@sql)
end;
close tabcur;
deallocate tabcur;
-- select * from #usedSpace order by cast((left(Data,len(Data)-3)) as int) desc -- uncomment if you wish to get a recordset
drop table #usedSpace


Use it at your own risk, uncomment the select (or substitute that with your more appropriate log strategy) and read about sp_spaceused and above all dbcc updateusage before bitching about the quality of the results.