Tag Archives: secure

How to Secure Your Azure SQL Database

Azure, Data, Database Administrator, SQL Server, , , , , , , , , , , , , , , ,
  1. Authentication and Authorization:
    • Use Azure Active Directory (Azure AD) authentication for better security.
    • Implement firewall rules to control access to your database.
    • Assign minimal permissions to users based on their roles (principle of least privilege).
  2. Encryption:
    • Enable Transparent Data Encryption (TDE) to protect data at rest.
    • Use Always Encrypted to secure sensitive data in transit.
    • Consider client-side encryption for additional protection.
  3. Auditing and Monitoring:
    • Enable Azure SQL Auditing to track database activity.
    • Set up Azure Monitor to receive alerts and insights.
    • Regularly review logs and audit trails.
  4. Network Security:
    • Isolate your database using Virtual Network Service Endpoints.
    • Restrict public access and use private endpoints.
  5. Patch Management:
    • Keep your database engine up to date with the latest security patches.
    • Regularly review vulnerability assessments.
  6. Backup and Recovery:
    • Implement automated backups and test recovery procedures (remember a backup is only theoretically there unless it has been tested and proven to work).
    • Store backups conforming to the 3-2-1 Backup Rule explained below (do not assume your backups are safe just because they are in the cloud).

The 3-2-1 Backup Rule: Ensuring Data Resilience

The 3-2-1 Rule is a robust strategy that emphasizes redundancy, resilience, and data availability. Here’s what it entails:

  1. Three Copies of Your Data:
    • Maintain the original data and create at least two additional copies.
  2. Two Different Types of Media for Storage:
    • Store your data on distinct forms of media (e.g., hard drives, tapes) to enhance redundancy.
  3. At Least One Copy Off-Site:
    • Safeguard one backup copy in an off-site location, separate from your primary data and on-site backups.

By adhering to this rule, you mitigate single points of failure, protect against corruption, and ensure data safety even in unexpected events or disasters

A picture with garbled characters and the word password in the middle

How to generate a random password with T-SQL

Database Administrator, dba, sql, SQL Server, T-SQL, , , , , , , , , , , , , , , , , , ,

The following script will generate a random 10 character password that meets the complexity requirement for Microsoft Windows. To generate a password just run the script in a new SQL Server Management Studio window. The logic can also be easily turned into a function.

The option of symbol characters is limited to what’s shown below as dealing with quotes and obscure characters in a password is often more trouble than it is worth. The password generated however should still be very secure as it will be 10 characters long with a guaranteed number, lowercase letter, uppercase letter and a symbol.

!
#
$
%
&
(
)
*
+

/*Declare Variables*/
DECLARE @i INT;
DECLARE @Pw VARCHAR(MAX);
DECLARE @Numbers TABLE (Characters CHAR(1));
DECLARE @LowerCase TABLE (Characters CHAR(1));
DECLARE @UpperCase TABLE (Characters CHAR(1));
DECLARE @Symbols TABLE (Characters CHAR(1));
DECLARE @BaseCharacters TABLE (Characters CHAR(1));
DECLARE @GuaranteedCharacters TABLE (Characters CHAR(1));
DECLARE @PwCharacters TABLE (Characters CHAR(1));

/*Generate Numbers*/
SET @i = 0;

WHILE @i <= 9
BEGIN
	INSERT INTO @Numbers
	SELECT @i

	SET @i = @i + 1
END;

/*Generate Lowercase Letters*/
SET @i = 97;

WHILE @i <= 122
BEGIN
	INSERT INTO @LowerCase
	SELECT CHAR(@i)

	SET @i = @i + 1
END;

/*Generate Uppercase Letters*/
SET @i = 65;

WHILE @i <= 90
BEGIN
	INSERT INTO @UpperCase
	SELECT CHAR(@i)

	SET @i = @i + 1
END;

/*Generate Symbols*/
SET @i = 33;

WHILE @i <= 43
BEGIN
	IF (
			@i = 34
			OR @i = 39
			)
	BEGIN
		SET @i = @i + 1
	END

	INSERT INTO @Symbols
	SELECT CHAR(@i)

	SET @i = @i + 1
END;

/*
Randomly Select A Number, Lowercase Letter,
Uppercase Letter And A Symbol So Four Character Types
Are Guaranteed To Be Present Somewhere In The Password
*/
INSERT INTO @GuaranteedCharacters (Characters)
SELECT TOP 1 Characters
FROM @Numbers
ORDER BY NEWID();

INSERT INTO @GuaranteedCharacters (Characters)
SELECT TOP 1 Characters
FROM @LowerCase
ORDER BY NEWID();

INSERT INTO @GuaranteedCharacters (Characters)
SELECT TOP 1 Characters
FROM @UpperCase
ORDER BY NEWID();

INSERT INTO @GuaranteedCharacters (Characters)
SELECT TOP 1 Characters
FROM @Symbols
ORDER BY NEWID();

/*
Randomly Select Another 6 Characters
*/
INSERT INTO @BaseCharacters
SELECT TOP 6 Characters
FROM (
	SELECT Characters
	FROM @Numbers
	
	UNION ALL
	
	SELECT Characters
	FROM @LowerCase
	
	UNION ALL
	
	SELECT Characters
	FROM @UpperCase
	
	UNION ALL
	
	SELECT Characters
	FROM @Symbols
	) AS Characters
ORDER BY NEWID()

/*Generate A 10 Character Password*/
INSERT INTO @PwCharacters (Characters)
SELECT Characters
FROM (
	SELECT Characters
	FROM @BaseCharacters
	
	UNION ALL
	
	SELECT Characters
	FROM @GuaranteedCharacters
	) AS Characters
ORDER BY NEWID()

/*Save The Password To A String*/
SELECT @Pw = COALESCE(@Pw + Characters, Characters)
FROM @PwCharacters

SELECT @Pw AS PW;

 

If you found this post helpful please like, comment and share.