Monday, March 30, 2020

Renamed Active Directory user (member of a group) lost its permissions on SQL Server

Strange behaviour reported by a user today.
The user DOMAIN\THE_USER.EXT had access to our SQL Server 2016 database using a group, let say DOMAIN\THE_GROUP.
In our database, he had access to tables & views using a row-level security, meaning using a SUSER_NAME() function.
It worked fine.

But the user was renamed in Active Directory, from DOMAIN\THE_USER.EXT to DOMAIN\THE_USER
Starting this point, he did not have access to any object anymore, even after rebooting its computer.
And very strange, when admin users of the database do emulate his credentials using EXECUTE AS LOGIN='DOMAIN\THE_USER', the row-level security was working as expected.

Part of the solution is here: sys.dm_exec_sessions, the user has inconsistent logins.

Just running the following query is enough to free the credential cache and fix the inconsistencies.

DBCC FREESYSTEMCACHE('TokenAndPermUserStore');
Another way of achieving this is to restart the SQL instance, but it is more aggressive on a production environment...

Thursday, September 27, 2018

Currently running queries

Here is a piece of code that shows information about SQL queries running on the instance. I removed system queries from that selection.
Usually, I put that in a view... This is very usefull to understand what is running on your SQL Server instance when you see slowness, out-of-memory, etc...
SELECT
 r.start_time AS StartDate,
 DATEDIFF(SECOND, r.start_time, GETDATE()) AS DurationInSecond,
 r.session_ID AS [SPID],
 USER_NAME(r.user_id) AS UserName,
 DB_NAME(r.database_id) AS [Database],
 SUBSTRING(t.text,
  ( r.statement_start_offset / 2 ) + 1,
  CASE WHEN statement_end_offset = -1 OR statement_end_offset = 0
  THEN ( DATALENGTH(t.Text) - r.statement_start_offset / 2) + 1
  ELSE ( r.statement_end_offset - r.statement_start_offset ) / 2 + 1
  END) AS SqlQuery, 
 t.text AS SqlFullQuery,
 r.Status AS QueryStatus,
 r.command AS SqlCommand,
 r.wait_type AS WaitType,
 r.wait_time AS WaitTime,
 wait_resource AS WaitResource,
 r.blocking_session_id AS BlockingSessionId,
 r.cpu_time / 1000 AS CpuInSecond,
 r.logical_reads AS ReadLogical,
 (r.logical_reads * 8 / 1024) AS ReadLogicalInMB,
 r.reads AS ReadPhysical,
 (r.reads * 8 / 1024) AS ReadPhysicalInMB,
 r.writes AS WritePhysical,
 (r.writes * 8 / 1024) AS WritePhysicalInMB,
 ISNULL(NULLIF(CASE
  WHEN s.nt_domain IS NULL THEN ''
  ELSE s.nt_domain + '\' END + s.nt_user_name,
  ''), s.login_name) AS NtUserName,
 s.memory_usage AS SessionMemoryUsage,
 q.query_plan AS QueryPlan,
 s.program_name AS ProgramName,
 s.host_name AS HostName,
 s.client_interface_name AS ClientInterfaceName,
 r.sql_handle AS SqlHandle,
 r.session_id AS SessionId
FROM master.sys.dm_exec_requests r WITH (NOLOCK)
OUTER APPLY master.sys.dm_exec_sql_text(r.sql_handle) t
OUTER APPLY master.sys.dm_exec_query_plan(r.plan_handle) q
LEFT JOIN master.sys.dm_exec_sessions s WITH (NOLOCK) ON s.session_id = r.session_id
WHERE s.login_name <> 'sa'
AND r.session_id > 50

Wednesday, September 26, 2018

Check whether user running Sql Server engine has enough privileges

SQL Server installation automatically grants privileges to the user that runs MSSQL engine.
But, if you change the user that runs the engine AFTER the installation is complete, these permissions are not applied automatically. So it is important to remember to set these privileges.
Here is a script that helps you to determine which privileges you need to give. Depending of your SQL Server configuration, some may not be needed.
DECLARE @Res TABLE ( [output] NVARCHAR(255) );

INSERT INTO @Res
EXEC xp_cmdshell 'WHOAMI';
SELECT 'MSSQL engine runs with user ' + output
FROM @Res
WHERE output IS NOT NULL

DELETE FROM @Res
INSERT INTO @Res
EXEC xp_cmdshell 'WHOAMI /PRIV';

SELECT
 rp.PrivilegeName,
 RTRIM(LTRIM(REPLACE(r.output, rp.PrivilegeName, ''))) AS PrivilegeStatus
FROM @Res r
INNER JOIN
(
 VALUES
 ( 'SeAssignPrimaryTokenPrivilege' ),
 ( 'SeChangeNotifyPrivilege' ),
 ( 'SeIncreaseQuotaPrivilege' ),
 ( 'SeLockMemoryPrivilege' )
) rp ( PrivilegeName ) ON r.output LIKE '%' + rp.PrivilegeName + '%'
To add these privileges, go to Server Manager / Tools / Local Security Policy (or run secpol.msc).
Under Security Settings / Local Policies / User Rights Assignment, you can find the privileges mentioned above. Open each one that is disabled, and add the MSSQL engine user.
A reboot of server may be required for changes to take effect.

Resources:

Wednesday, November 22, 2017

SQL Server disk usage

Since DBA are very busy people, it is helpful to have queries (and reports) that gives info about disk usage on SQL Server instance.

It is quite easy to have insight about disk usage for one database. Run this query on a specific database :

WITH FileByFile AS
(
 SELECT
  v.volume_mount_point AS Volume_MountPoint,
  MAX(v.total_bytes) AS Volume_TotalBytes,
  MAX(v.available_bytes) AS Volume_AvailableBytes,
  f.type_desc AS FileType,
  SUM(ISNULL(f.size, 0) -
   CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS BIGINT))
    * 8 * 1024 AS DB_FreeSpaceBytes,
  SUM(CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS BIGINT))
   * 8 * 1024 AS DB_UsedBytes
 FROM sys.database_files f WITH (NOLOCK)
 CROSS APPLY sys.dm_os_volume_stats(DB_ID(), file_id) AS v
 GROUP BY v.volume_mount_point, f.type_desc
)
SELECT
 f.Volume_MountPoint,
 f.FileType,
 MAX(f.Volume_TotalBytes) AS Volume_TotalBytes,
 MAX(f.Volume_AvailableBytes) AS Volume_AvailableBytes,
 SUM(f.DB_FreeSpaceBytes) AS DB_FreeSpaceBytes,
 SUM(f.DB_UsedBytes) AS DB_UsedBytes
FROM FileByFile f
GROUP BY f.Volume_MountPoint, f.FileType


OK, it says for each volume used by my database :

  • The drive letter of the volume
  • The file type (is it used by LOGS or ROWS)
  • The total size of the volume
  • The free space on the volume
  • The free space of the selected database
  • The space actually used by the selected database
It is interesting, but I would prefer to know the space used by EVERY database of the SQL instance, instead of only one database.
So let's create a cursor, iterate over all databases, and do some dynamic SQL to achieve this.

DECLARE @Stmt NVARCHAR(MAX);
DECLARE @DatabaseID AS INT;
DECLARE @DatabaseName AS NVARCHAR(50);
 
DECLARE @DatabaseCursor AS CURSOR;
 
SET @DatabaseCursor = CURSOR FOR
    SELECT name, database_id FROM sys.databases WHERE HAS_DBACCESS(name) = 1 ;

IF OBJECT_ID('tempdb..##VolumeInfo') IS NOT NULL DROP TABLE ##VolumeInfo ;

CREATE TABLE ##VolumeInfo
(
 DatabaseName SYSNAME NOT NULL,
 Volume_MountPoint SYSNAME NOT NULL,
 FileType SYSNAME NOT NULL,
 Volume_TotalBytes BIGINT NOT NULL,
 Volume_AvailableBytes BIGINT NOT NULL,
 DB_FreeSpaceBytes BIGINT NOT NULL,
 DB_UsedBytes BIGINT NOT NULL
)
 
OPEN @DatabaseCursor;
FETCH NEXT FROM @DatabaseCursor INTO @DatabaseName, @DatabaseID;

WHILE @@FETCH_STATUS = 0
BEGIN
 SET @Stmt = '
 USE [' + @DatabaseName + '];
 WITH FileByFile AS
 (
  SELECT
   CAST(''' + @DatabaseName + ''' AS SYSNAME)
    COLLATE Latin1_General_CI_AI AS DatabaseName,
   v.volume_mount_point AS Volume_MountPoint,
   MAX(v.total_bytes) AS Volume_TotalBytes,
   MAX(v.available_bytes) AS Volume_AvailableBytes,
   f.type_desc AS FileType,
   SUM(ISNULL(f.size, 0) -
    CAST(FILEPROPERTY(f.name, ''SpaceUsed'') AS BIGINT))
    * 8 * 1024 AS DB_FreeSpaceBytes,
   SUM(CAST(FILEPROPERTY(f.name, ''SpaceUsed'') AS BIGINT))
    * 8 * 1024 AS DB_UsedBytes
  FROM sys.database_files f WITH (NOLOCK)
  CROSS APPLY sys.dm_os_volume_stats(DB_ID(''' + @DatabaseName + '''), file_id)
    AS v
  GROUP BY v.volume_mount_point, f.type_desc
 )
 INSERT INTO ##VolumeInfo
 SELECT
  f.DatabaseName,
  f.Volume_MountPoint,
  f.FileType,
  MAX(f.Volume_TotalBytes) AS Volume_TotalBytes,
  MAX(f.Volume_AvailableBytes) AS Volume_AvailableBytes,
  SUM(f.DB_FreeSpaceBytes) AS DB_FreeSpaceBytes,
  SUM(f.DB_UsedBytes) AS DB_UsedBytes
 FROM FileByFile f
 GROUP BY DatabaseName, f.Volume_MountPoint, f.FileType'

 EXEC sp_sqlexec @Stmt ;

 FETCH NEXT FROM @DatabaseCursor INTO @DatabaseName, @DatabaseID;
END
 
CLOSE @DatabaseCursor;
DEALLOCATE @DatabaseCursor;

SELECT
 f.DatabaseName, f.Volume_MountPoint, f.FileType,
 'File Used' AS Usage,
 f.DB_UsedBytes AS ValueInByte,
 f.DB_UsedBytes / (1024.0 * 1024 * 1024) AS ValueInGB
 FROM ##VolumeInfo f
 WHERE f.DB_UsedBytes <> 0
UNION ALL
SELECT f.DatabaseName, f.Volume_MountPoint, f.FileType,
 'File Unused' AS Usage,
 f.DB_FreeSpaceBytes AS ValueInByte,
 f.DB_FreeSpaceBytes / (1024.0 * 1024 * 1024) AS ValueInGB
 FROM ##VolumeInfo f
 WHERE f.DB_FreeSpaceBytes <> 0
UNION ALL
SELECT DISTINCT 'Available' AS DatabaseName, f.Volume_MountPoint,
 'Available' AS FileType,
 'Available' AS Usage,
 f.Volume_AvailableBytes AS ValueInByte,
 f.Volume_AvailableBytes / (1024.0 * 1024 * 1024) AS ValueInGB
 FROM ##VolumeInfo f
UNION ALL
SELECT
 'Unknown' AS DatabaseName,
 f.Volume_MountPoint,
 'Unknown' AS FileType,
 'Unknown' AS Usage,
 MAX(f.Volume_TotalBytes)
  - MAX(f.Volume_AvailableBytes)
  - SUM(f.DB_UsedBytes)
  - SUM(f.DB_FreeSpaceBytes) AS ValueInByte,
 (MAX(f.Volume_TotalBytes)
  - MAX(f.Volume_AvailableBytes)
  - SUM(f.DB_UsedBytes)
  - SUM(f.DB_FreeSpaceBytes)) / (1024.0 * 1024 * 1024) AS ValueInGB
FROM ##VolumeInfo f
GROUP BY f.Volume_MountPoint

DROP TABLE ##VolumeInfo


We get, for each drive and database, how many space is used / unused.
In addition, for every drive, we get the available space, and the unknown space... ie the space that is used on the volume but not by a database. This may be system files, backups, etc.

Based on this query, we can easily build some charts (on Reporting Services or Excel, for instance) to display important information. Here is waht I usually report :

Very helpful to know which filegroups to shrink !

Friday, January 23, 2015

How to execute sub packages in SSIS (and pass through configuration file)

Every time I need to execute a subpackage in SSIS, I am upset with the way SSIS handles the configuration between parent package and child package. Let's precise I like to use the old-fashion dtsConfig file, that allows an easy configuration for connection managers (and variables, and anything actually).

So, today I decided not to use the default "Execute Package Task" provided by SSIS.


Instead, I used a "Execute Process Task" that calls dtexec utility.


The idea is to make a generic version of this component, thanks to the powerful SSIS expressions, so that we can copy/paste the same component every-time we need to execute another subpackage.
The name of the component will be used to specify the package to be executed.

Let's do it.
First, we will prepare some variables. Create the following:
  • Param_ConfigurationFile : it points to the dtsConfig file you will want to use for running your subpackages.
  • Param_DtExec : the path to your dtexec utility. Generally, there is no need to give the absolute path.
  • Param_PackageDirectory : the directory where the SSIS subpackages will be found.

These variables will be configured with a dtsConfig file in server environments.

Create a new "Execute Process Task".
Open its properties.
In the "General > Name" textbox, write the name of the subpackage you want to execute.
Here, the name of the subpackage will be TheSubPackageIWantToRun.

In the Expressions tab, create 2 expressions :
  1. Arguments : "/FILE \"" + @[User::Param_PackageDirectory]  + "\\" + @[System::TaskName]  + ".dtsx\" /CONFIGFILE \"" + @[User::Param_PackageDirectory]  + "\\" + @[User::Param_ConfigurationFile] + " /CHECKPOINTING OFF  /REPORTING EWCDI "
    • The /FILE modifier tells to DtExec which package to run (directory\package.dtsx). Note that the Task Name is used to store the package you want to run, this is really user-friendly in SSIS : at first glance, you can see in SSIS Control Flow the subpackages that will be ran.
    • The /CONFIGFILE tells DtExec which configuration file will be used for subpackage (old fashion, not MSDB). In this example, I assume the configuration file is stored in the same directory that the packages. It can be changed easily.
    • The last 2 options are standard and generated by DtExecUI. Not subject to this post :)
  2. Executable : @[User::Param_DtExec]


So, if you want to run 3 subpackages in a row, you just have to copy/paste this generic component 2 times. In the following example, it will execute the package sub_package_1.dtsx, then sub_package_2.dtsx and finally sub_package_3.dtsx, and pass to them the same configuration file, in which you could define connection managers and variables.


Hope it may help !

Saturday, August 24, 2013

Data cloaking inside of BMP pictures

Tonight, I decided to create a small program that:
  • encodes any file into a picture
  • decodes this file from the same picture
A schema being better than any long sentence...


How does it work?

So easy... you know a bitmap file is nothing more than a succession of pixels, and a pixel is made of 3 bytes Red Green Blue, right? (let's ignore the file header, the reverse order of rows, etc etc... that's not important here)
The big picture is to hide one byte of the message per pixel. Red is coded on 8 bits, right? Let's use the 2 least significant bits (LSB) to encode 2 bits of this message byte. Green is coded on 8 bits too, so let's use the 3 LSB to encode 3 bits of the message byte. Same thing for Blue, let's use the 3 LSB to encode 3 bits of the message byte. You got it: 2 bits + 3 bits + 3 bits = 1 byte. This is how we will hide each byte from the message in one pixel.
The good news is that we always modified the LSB of the pixel components... when you will look at the modified picture (containing the message), you will not be able to detect the color changes from the original one.

Now, have a closer look at the two interesting parts in this program: encoding of data, decoding of data.

Encoding

The following method inserts a message (a memory Stream, it can be anything) into the original picture. Of course, we have to ensure the picture file is big enough to contain the message.
Note the the first 4 pixels will be used to encode the size of the message. We will need it to decode the message.
The source code is a bit messy, because it contains a homemade BMP writer. But the data cloaking itself is quite straightforward, provided you are familiar with bitmasks.

private void ProcessBmp(string InputFilename, System.IO.MemoryStream Message, string OutputFilename)
{
    using (System.IO.FileStream fs = new System.IO.FileStream(InputFilename, System.IO.FileMode.Open, System.IO.FileAccess.Read, System.IO.FileShare.Read, 1024 * 1024))
    {
        using (System.Drawing.Bitmap b = new System.Drawing.Bitmap(fs))
        {
            if (b.Width * b.Height < Message.Length + 4)
                throw new Exception(string.Format("BMP file is too small, it must be at least {0} bytes to encode this message", (Message.Length + 4) * 3));
            using (System.IO.BinaryWriter bw = new System.IO.BinaryWriter(new System.IO.FileStream(OutputFilename, System.IO.FileMode.Create, System.IO.FileAccess.Write)))
            {
                int ComplementToAdd = (4 - ((b.Width * 3) % 4)) % 4;
                byte[] BlackPixel = new byte[3] { 0, 0, 0 };
                bw.Write('B');
                bw.Write('M');

                int x, y;

                bw.Write((uint)(54 + b.Width * b.Height * 3 + b.Height * ComplementToAdd));
                bw.Write((uint)0);
                bw.Write((uint)54);
                bw.Write((uint)40);
                bw.Write((uint)b.Width);
                bw.Write((uint)b.Height);
                bw.Write((uint)24 * 0x10000 + 1);   // planes & bitcount
                bw.Write((uint)0);   // compression
                bw.Write((uint)b.Width * b.Height * 3 + ComplementToAdd * b.Height);    // size of image
                bw.Write((uint)0);   // XPPM
                bw.Write((uint)0);   // YPPM
                bw.Write((uint)0);   // color used

                int Idx = 0;
                for (y = b.Height - 1; y >= 0; y--)
                {
                    for (x = 0; x < b.Width; x++)
                    {
                        Color c = b.GetPixel(x, y);
                        byte[] RGB = new byte[3] { c.B , c.G, c.R };

                        byte ByteToEncode;
                        switch (Idx)
                        { // The 4 first bytes wil be used to encode the size of the message
                            case 0: ByteToEncode = (byte)(Message.Length & 0x000000FF); break;
                            case 1: ByteToEncode = (byte)((Message.Length & 0x0000FF00) >> 8); break;
                            case 2: ByteToEncode = (byte)((Message.Length & 0x00FF0000) >> 16); break;
                            case 3: ByteToEncode = (byte)((Message.Length & 0xFF000000) >> 32); break;
                            default: ByteToEncode = Idx - 4 < Message.Length
                                 ? (byte)Message.ReadByte()
                                 : (byte)0; break;
                        }

                        byte EncodeB = (byte)((ByteToEncode & (32 + 64 + 128)) >> 5);
                                       // Find the biggest 3 bits in the byte to encode
                        byte EncodeG = (byte)((ByteToEncode & (4 + 8 + 16)) >> 2);
                                       // Find the middle 3 bits in the byte to encode
                        byte EncodeR = (byte)(ByteToEncode & (1 + 2));
                                       // Find the smallest 2 bits in the byte to encode
                        RGB[0] = (byte)((RGB[0] & 0xF8) + EncodeB);
                                 // In Blue, 3 bits are used to encode (0xF8 = 1111 1000)
                        RGB[1] = (byte)((RGB[1] & 0xF8) + EncodeG);
                                 // In Green, 3 bits are used to encode (0xF8 = 1111 1000)
                        RGB[2] = (byte)((RGB[2] & 0xFC) + EncodeR);
                                 // In Red, 2 bits are used to encode (0xFC = 1111 1100)

                        bw.Write(RGB, 0, 3);
                        Idx++;
                    }
                    // Rows in bmp must be a multiple of 4, so let's fill with black pixels if necessary
                    if (ComplementToAdd != 0)
                        bw.Write(BlackPixel, 0, ComplementToAdd);
                }
            }
        }
    }
}

Decoding

This one is much easier than the encoding source code. So straightforward that is does not need any comment...

private System.IO.MemoryStream UnprocessBmp(string InputFilename)
{
    System.IO.MemoryStream Result = new System.IO.MemoryStream();
    using (System.IO.FileStream fs = new System.IO.FileStream(InputFilename, System.IO.FileMode.Open, System.IO.FileAccess.Read, System.IO.FileShare.Read, 1024 * 1024))
    {
        using (System.Drawing.Bitmap b = new System.Drawing.Bitmap(fs))
        {
            int x, y, MessageSize = 0, Idx = 0;
            for (y = b.Height - 1; y >= 0; y--)
            {
                for (x = 0; x < b.Width; x++)
                {
                    Color c = b.GetPixel(x, y);
                    byte EncodedB = (byte)(c.B & 0x07);
                                    // Find the last 3 bits of the blue component
                    byte EncodedG = (byte)(c.G & 0x07);
                                    // Find the last 3 bits of the green component
                    byte EncodedR = (byte)(c.R & 0x03);
                                    // Find the last 2 bits of the red component
                    byte EncodedByte = (byte)((EncodedB << 5) + (EncodedG << 2) + EncodedR);
                                    // combine the 3 + 3 + 2 bits to make a byte

                    switch (Idx)
                    { // The 4 first bytes wil be used to encode the size of the message
                        case 0: MessageSize = EncodedByte; break;
                        case 1: MessageSize = MessageSize + (EncodedByte << 8); break;
                        case 2: MessageSize = MessageSize + (EncodedByte << 16); break;
                        case 3: MessageSize = MessageSize + (EncodedByte << 32); break;
                        default: if (Idx - 4 < MessageSize) Result.WriteByte(EncodedByte); break;
                    }
                    Idx++;
                }
            }
        }
    }
    Result.Seek(0, System.IO.SeekOrigin.Begin);
    return Result;
}

You can download the solution here. Please, pay special attention to my high-level skills in GUI design... \m/

Thursday, August 22, 2013

Get ASCII codes from a VARCHAR SQL string

Today I faced a strange issue in SQL Server. I tried to select a specific row in SCCM table about OS Windows 7 Entreprise. So I typed the name of the OS in management studio, and ran a select on the proper table using a where clause on the OS name. Unexpectedly, no rows returned.
So I copied the name of the OS (from the SCCM table), and tested it against my own OS name. It can be summarized as below:

SELECT
  CASE WHEN 'Microsoft Windows 7 Entreprise' -- coming from SCCM
          = 'Microsoft Windows 7 Entreprise' -- what I typed
    THEN 'Equal'
    ELSE 'Not equal'
  END AS IsEqual
 

Wow... everyone who ever fought against special characters already knows the solution: a non-breakable space (or whatever) is present in one of the strings.

So I wrote a small query to inspect the ASCII characters of a string, and wished to share it. SO here it is:
 
DECLARE @Str VARCHAR(255) = 'Microsoft Windows 7 Entreprise' -- Coming from SCCM
;

WITH MyCounter AS
(
 SELECT 1 AS Idx
 UNION ALL
 SELECT a.Idx + 1 AS Idx
 FROM MyCounter a
 WHERE a.Idx < LEN(@Str)
)
SELECT Idx AS [Index], SUBSTRING(@Str, Idx, 1) AS [Character], ASCII(SUBSTRING(@Str, Idx, 1)) AS [AsciiCode]
FROM MyCounter
ORDER BY Idx asc
 

As expected, a non breakable space (ascii code 160) was present in SCCM table content, instead of standard whitespace (ascii code 32).

This small code can easily be turned into scalar valued function, if needed...
Hope it helps!