Finding weak passwords in SQL Server

This is just a quick security tip regarding weak passwords.  Identifying weak passwords in SQL Server is a good precautionary measure to prevent hacking and to ensure your SQL Servers are secure.  Newer versions of SQL Server are said to enforce standards regarding password complexity and enforcement, but that does not mean you cannot get around them...

Which passwords are weak?  That could be a combination of many things, but I always look for blank passwords, passwords matching their logins, and even just strings like 'password' or '1234'.  In this demo I am using PWDCOMPARE, which is one of MSFT's functions designed to identify weak passwords.

First, we'll create some logins:

USE [master]
GO
CREATE LOGIN SQLuser1 WITH PASSWORD='', DEFAULT_DATABASE=[master],

DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

CREATE LOGIN SQLuser2 WITH PASSWORD='SQLuser2', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

CREATE LOGIN SQLuser3 WITH PASSWORD='1234', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

CREATE LOGIN SQLuser4 WITH PASSWORD='abc', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

CREATE LOGIN SQLuser5 WITH PASSWORD='password', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

Use a table variable to hold a few of the more common offenders:

-- input the different passwords you're watching for

DECLARE @WeakPwdList TABLE(WeakPwd NVARCHAR(255))

INSERT INTO @WeakPwdList(WeakPwd)

SELECT ''

UNION ALL SELECT '1234'

UNION ALL SELECT 'abc'

UNION ALL SELECT 'password'

Now you can find them using PWDCOMPARE:

-- what about the others?

SELECT t1.name,t1.type_desc,t1.is_policy_checked,t1.is_expiration_checked,REPLACE(t2.WeakPwd,'@@Name',t1.name) As [Password]

FROM sys.sql_logins t1 INNER JOIN @WeakPwdList t2 ON (PWDCOMPARE(t2.WeakPwd, password_hash) = 1 OR PWDCOMPARE(REPLACE(t2.WeakPwd,'@@Name',t1.name),password_hash) = 1) WHERE t1.is_policy_checked = 0

ON (PWDCOMPARE(t2.WeakPwd, password_hash) = 1

OR PWDCOMPARE(REPLACE(t2.WeakPwd,'@@Name',t1.name),password_hash) = 1)

WHERE t1.is_policy_checked = 0

My results...

Or, you can use this to quickly find passwords matching their logins:

-- which pwds match login name

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

It's just a small example, but any strong DBA knows that security starts at the basics, and passwords are the front door. PWDCOMPARE provides an easy, very low-impact method for identifying weak credentials that could become easy entry points for the uninvited.  I think this should be a part of every DBA's security toolkit.

More details:  PWDCOMPARE

When SQL Server Replication Breaks After Windows Updates...

I received an email from a customer yesterday regarding their Replication, which began failing with this error after Windows updates were applied:

Message Replication-Replication Transaction-Log Reader Subsystem: agent servername-xxx2 failed. Executed as user: domainname\svcaccount. A required privilege is not held by the client. The step failed.

Slightly dummied, but the important content is in red.  What does that mean?  'A required privilege is not held by the client'... he didn't change anything, I didn't change anything - why is Replication suddenly failing with permissions problems?

The only known change was the Windows Update, but every SQL Server DBA really should know that Windows Updates are often known to trigger failures like this due to security changes.  In this case, Group Policy reapplications were performed, and silently overwrote the local security settings for the SQL Server Agent service account.

How did I know this?  

First, I checked the security polices for the SQL Server Agent service account in Run \ secpol.msc \ Local Policies \ User Rights Assignment.  Specifically, 'Log on as a batch job' and 'Log on as a service', which are needed by the Agent to run snapshot and log reader replication tasks.  Neither were enabled for the Agent's service account, DOMAIN\SQLAgentSvc:

So, I input the Agent service account in there, applied and restarted the Agent, but it made no impact.  The errors continued.  I even rebooted the server but still saw no impact from the change.  Then I ran this in powershell:

Get-ResultantSetOfPolicy -ReportType Html -Path C:\GPOReport.html

That cmd retrieves the Resultant Set of Policy (RSoP) information for the computer to html file for review, and it did NOT show 'Log on as a batch job' or 'Log on as a service' for the Agent's service account within Local Policies, User Rights Assignment:

This tells us that the domain-level GPO (Group Policy Object) reasserted itself after the Windows updates and overwrote the local security policy settings for the SQL Server service accounts. Without this policy, the Agent no longer has the permissions needed to run the snapshot and log reader replication tasks.  Hence, the error 'A required privilege is not held by the client'.

The fix?

Assign both of these policies at the Group Level within Group Policy Management Console (gpm.msc) for the SQL Server Agent service account:

  Log on as a batch job

  Log on as a service

Then restart the SQL Server Agent.

After doing so, the failing replication tasks immediately begin to recover.  I believe it took about 5-7 minutes for everything to recover fully.

In my Customer's words... "I just want to call out that you are amazing."

Estimating SQL Server backup sizes — with and without compression

How big will my backup file be?  Very good question.  And of course, it depends... especially if you're using compression.

I was working something today that required a backup beforehand for safety measures, and the server had limited storage.  So, I needed to estimate the backup file size beforehand to confirm whether I had enough space for the bak file.

This script will show you how to estimate the backup size, both uncompressed and compressed, using a 30% reduction, which is a realistic compression ratio.  Please understand, the actual backup sizes vary and depend on table contents, data types, how much empty space exists in the data files and more, but 30% is a safe rule-of-thumb that works well for general estimates.

USE master;

-- input your database name here

DECLARE @dbname SYSNAME = 'DBA';

-- estimate compressed size using 30% compression ratio

DECLARE @compratio DECIMAL(5,2) = 0.30;

-- estimate backup size

SELECT 

    DB.name [Database],

    CAST(SUM(MF.size) * 8.0 / 1024 AS DECIMAL(18,2)) [CurrentDB_SizeMB],

    CAST(SUM(MF.size) * 8.0 / 1024 AS DECIMAL(18,2)) [Estimated_BackupSizeMB_Uncompressed],

    CAST(SUM(MF.size) * 8.0 / 1024 * (1 - @compratio) AS DECIMAL(18,2)) [Estimated_BackupSizeMB_Compressed],

    @compratio [Assumed_Compression_Ratio]

FROM 

    sys.master_files mf INNER JOIN sys.databases db 

  ON mf.database_id = db.database_id

WHERE 

    DB.name = @dbname

    AND MF.type_desc IN ('ROWS', 'LOG')

GROUP BY 

    DB.name;

 

Example output:

 

Of course, you can also use the backup history to estimate backup size, but the above is just a quick method to estimate the backup size, compressed or not, using just the current database size and the compression ratio -- in this case, 30%.  

Provide access to SQL Server Activity Monitor without giving sysadmin perms

 

Is there any way to provide a user access to the Activity Monitor without enabling them as a sysadmin?  Yes.  Of course there is.  More easily managed if we do it with a server-level role than to multiple individual logins, like this:

     USE master;
     -- Step 1: Create the custom server role
     CREATE SERVER ROLE [ActivityMonitorRole];

     -- Step 2: Grant the necessary permission to the role
     GRANT VIEW SERVER STATE TO [ActivityMonitorRole];

     -- Step 3: Add specific logins to the role
     ALTER SERVER ROLE [ActivityMonitorRole] ADD MEMBER [YOURUSER];
     -- Repeat for additional logins as needed

Change your mind for some reason?  Just as easy to revert.

     -- Step 1: Remove login from the role

     ALTER SERVER ROLE [ActivityMonitorRole] DROP MEMBER [your_user];

     -- Step 2: Revoke VIEW SERVER STATE from the role

     REVOKE VIEW SERVER STATE FROM [ActivityMonitorRole];

     -- Step 3: Drop the role if no longer needed

     DROP SERVER ROLE [ActivityMonitorRole];

What SQL Server services are running on that server?

Good question. 😏 Of course, there are many ways to answer this question in Windows gui-land. A couple examples are the SQL Server Configuration Manager or services.msc, but both are more timely, and it's easy to miss something.  This script provides a faster way to gather all SQL Server service details from your server with just one call.

This is the output from one of my v2019 instances:

Here's the script...

/* query status details for all existing SQL Server services  */
SET NOCOUNT ON;
 
-- temp tables
IF (OBJECT_ID ('tempdb..#RegResult')) IS NOT NULL
DROP TABLE #RegResult;
CREATE TABLE #RegResult (
    ResultValue NVARCHAR(4)
       );
 
IF (OBJECT_ID ('tempdb..#ServicesServiceStatus')) IS NOT NULL
DROP TABLE #ServicesServiceStatus;
CREATE TABLE #ServicesServiceStatus(
    ID INT IDENTITY(1,1),
    SQLServerName NVARCHAR(128),
    ServiceName NVARCHAR(128),
    ServiceStatus VARCHAR(128),
    CheckDateTime DATETIME DEFAULT (GETDATE()),
    PhysicalSrvName NVARCHAR(128)
  );
 
IF (OBJECT_ID ('tempdb..#Services')) IS NOT NULL
DROP TABLE #Services;
CREATE TABLE #Services(
    ID INT IDENTITY(1,1),
    ServiceName NVARCHAR(128),
    DefaultInstance NVARCHAR(128),
    NamedInstance NVARCHAR(128)
  );
 
-- load service details
INSERT #Services
VALUES 

 ('MS SQL Server Service','MSSQLSERVER','MSSQL'),
 ('SQL Server Agent Service','SQLSERVERAGENT','SQLAgent'),
 ('Analysis Services','MSSQLServerOLAPService','MSOLAP'),
 ('Full Text Search Service','MSFTESQL','MSSQLFDLauncher'),
 ('Reporting Service','ReportServer','ReportServer'),
 ('SQL Browser Service - Instance Independent','SQLBrowser','SQLBrowser'),
 ('SSIS','MsDtsServer110','MsDtsServer110');
 
-- declarations
DECLARE
    @ChkInstanceName NVARCHAR(128),
    @ChkSrvName NVARCHAR(128),
    @REGKEY NVARCHAR(128),
    @i INT=1,
    @Service NVARCHAR(128);
 
SET @ChkSrvName = CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128))
 
-- SQL Server Services selection
WHILE (@i<=(SELECT MAX(ID) FROM #Services))
BEGIN
IF (@ChkSrvName IS NULL OR (
 SELECT COUNT(*) FROM #Services 

 WHERE ServiceName IN ('SQL Browser Service - Instance Independent','SSIS')
 AND ID = @i) > 0

)
SELECT @Service= DefaultInstance FROM #Services WHERE ID = @i
  ELSE
SELECT @Service= NamedInstance+'$'+CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128))
FROM #Services WHERE ID = @i
 
SET @REGKEY = 'System\CurrentControlSet\Services\' + @Service
INSERT #RegResult ( ResultValue )
EXEC MASTER.sys.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key= @REGKEY
 
-- check service status
IF (SELECT ResultValue FROM #RegResult) = 1
BEGIN
   INSERT #ServicesServiceStatus (ServiceStatus)
   EXEC xp_servicecontrol N'QUERYSTATE',@Service
END
ELSE
BEGIN
   INSERT #ServicesServiceStatus (ServiceStatus) VALUES ('NOT INSTALLED')
END
 
UPDATE #ServicesServiceStatus
SET
 ServiceName = (SELECT ServiceName FROM #Services WHERE ID = @i),
 SQLServerName = @@SERVERNAME,
 PhysicalSrvName = (

    SELECT CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS')AS VARCHAR(128)))
WHERE
  ID = @@IDENTITY
 
  TRUNCATE TABLE #RegResult
  SET @i = @i + 1;
 
END
 
-- return all details
SELECT * FROM #ServicesServiceStatus
 
SET NOCOUNT OFF;

Hope to have helped!

Where is the 1205 error for the SQL Server Deadlock ?!!

Just a short post, but I believe still helpful.  Yesterday I was playing around with different methods of capturing deadlock notifications.  I used XE's (Extended Event Sessions), Service Broker Queue / Event Notifications and SQL Server Alerts. While testing the Alerts, I simulated my own deadlock and produced the deadlock event myself, like this:

The expectation here was that the SQL Server Alert would send me a notification for the deadlock event... but it didn't.  Curious.  I verified the mail and the operator, I verified the last_occurrence_date of the alert... everything checked out good, but there still wasn't any 1205 recorded in the SQL Server Error log.  Then I just decided to be sure the 1205 was in sys.messages:

We can see it IS there, but do you see that "is_event_logged" = 0 ?  This means it is not going to be recorded in the SQL Server Error log.  Easily amended:

               EXEC master.sys.sp_altermessage
                  @message_id = 1205,
                  @parameter = 'WITH_LOG',
                  @parameter_value = 'true';

I generated the next deadlock and the 1205 is now in my Error Log:

All is well now, and I can move forward w/the deadlock capture.  I will post that here when it is complete.  See you soon!

More than one database transaction log file?

You can have more than one transaction log file for your database... but why would you?   

SQL Server will only write to one log file at a time, regardless of how many you give the database.  The transaction log file is written to sequentially, not serially, and there are ZERO performance benefits to having more than one log file for your database.  In fact, multiple log files can actually degrade your performance in some cases.  

But again, why would you have more than one log file?  Easy.  Your log file blows up due to a rogue transaction or some other unexpected reason, and your drive is filling up fast.  You cannot afford the downtime, so your only choice is to add a 2nd transaction log file temporarily.

Here's how:

USE master;
ALTER DATABASE Nautilus ADD LOG FILE (
        name = 'NewLogFilename',
        filename = 'D:\MSSQL\2017\Log\Nautilus_log_2.ldf',
        size = 1048MB,
        filegrowth = 5%
        )
GO

Now you can do whatever you need to do operationally until you reach a point where you can clean things up and remove that 2nd log file.  When it doesn't contain any transactions, the log file can be removed with this ALTER statement:

ALTER DATABASE Nautilus REMOVE FILE NewLogFilename;

If the log file is not completely empty, your statement may fail with this error:

The fast way to resolve that is to backup the log first:

BACKUP LOG Nautilus TO DISK = 'F:\Backup\Nautilus.bak'

Now run the same ALTER statement again and it should succeed:

We've got to remember that there is no reason to create more than one transaction log file for your database under normal circumstances.  The above method can be used in the abnormal or unexpected situations when you're running out of disk and need to do something fast to keep your database online.

See these for more details:   

Adding & removing data or transaction log files
Multiple transaction logs for SQL Server databases