Sending Passwords Via Emails

Request for a new login or reset the password for existing login is one of very frequent tickets that we regularly come across as DBAs. And I see lot DBA send passwords in the emails without setting the right information classification, Which we should avoid.  That is a bad practice, as an DBA learn/ask the company standards/protocols on how to send passwords to User or requester. 

I sometime break the protocol (because of the amount of work we do ;) ) and one day I was reminded by my lead to follow the Security Protocol very politely. He send an email to me as "Please do not send App IDs and Passwords in the same email." Then I realized and reset the password and send it to the recipient  as per the company standard. 

The standard that we follow is:

1. Login IDs and password should be in separate emails.
2. Emails for password should be marked Confidential.
3. Emails of Passwords should use “To” line only, not CC or BCC line.
4. Emails of Passwords should never use Group email Ids as recipients. 
5. Recipient should be individual email Ids only.

or alternately,

1. Put the Login Id and Password in a document (word, excel, pdf etc.).  

2. Encrypt the document with a password.

3. Send the document (marked confidential) to recipient only in an email. 

4. Send the password to the document via separate email (marked confidential) to recipient           only. and follow above steps 2 to 5. 

This will procedure will work in any company as best practice to share passwords to others, but anyhow try to find what is your company protocol/standard as well. 

Below are the sample Email classifications and Sensitivity type that are in use for different companies. 

Sample 1:

Sample 2:

Sample 3:

Thanks- I hope it will help. 

ACID Nature of SQL Server

We all know that every Relational Database Management System has to be ACID compliant, even SQL Server is one of those RDBMS. The four transaction Properties of ACID are Atomicity, Consistency, Isolation and Durability. Let's see how SQL Server will satisfy each of the property.

Atomicity: In a single transaction involving two or more distinct changes 

(Updates\Inserts\Deletes), either all of the changes are committed or none are. To make it simple let’s say you have a single transaction consists of a delete, update and insert in the same order. And your delete, update are successful but insert is failed (bad) because of some issue in this case SQL Server just roll backs your delete and update as well. 

SQL Server uses a Write Ahead Transaction Log. The log always gets written to first before the associated data changes. That way, if and when things go wrong, SQL Server will know how to roll back to a state where every transaction happened or didn’t happen. 

Example: 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

CREATE DATABASE Test GO --//Create test Table as Atomicity_table with-- --only column Name as primary key//-- USE Test CREATE TABLE Atomicity_table ( Name VARCHAR(50) NOT NULL CONSTRAINT pk_atomicit PRIMARY KEY (name) ) GO --/Inserting two values into the Atomicity_table/-- USE Test INSERT INTO Atomicity_table (Name) VALUES ('SQL') INSERT INTO Atomicity_table (Name) VALUES ('SQL1')

The result of the above two inserts looks like below.

Now Lets introduce a single transaction that do 3 things as below.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

--//Starting a single transaction (used Begin and Commit Tran)-- --with delete\update\insert//-- Use Test GO BEGIN TRAN DELETE FROM Atomicity_table WHERE name='SQL' GO UPDATE Atomicity_table SET Name = 'Oracle' WHERE name='SQL1' GO --//To make the transaction run longer I used WAITFOR DELAY 15 secs,-- -- this is where I will force SL Server to restart to make the Insert fail//-- WAITFOR DELAY '00:00:15' INSERT INTO Atomicity_table (Name) VALUES ('Oracle1') GO COMMIT TRAN

The result of the above Delete\Update\Insert query look like below after forced to fail query execution in the middle.

All the changes are rolled back though delete and Update are successful before insert failed. And the table is unmodified yields same results (SQL and SQL1) as above first screen shot.

Consistency: Most of the tables in SQL Server will bound by set of rules like Primary-Foreign key constraint relation ships, auto increment columns, Column sizes, column data types, columns with default binding values and few columns are set use specified values only and etc., and if the data has to reside on the tables with these rules data has to obey those rules. Let’s say a table has column called Gender which only takes Male or Female, SQL Server won’t allow the transaction to complete successfully if it is trying to insert something else into that Gender column which make the data inconsistent. These kind of transactions will fail and any changes made will be rolled back to bring the data to consistent state as it was before the start of the transaction. And any valid transaction which insert Male or Female will be processed successfully and it will take the data\database from one consistent state with all rules in place to another consistent state with all rules in place and obeying.

Isolation: This will ensure transactions running at same time on same tables will not be 

impacted by each other. No current transaction sees the intermediate results (Dirty Reads\Phantom reads) of the other current transaction. Let’s say we have two transactions both are performing the same function and running at the same time, the isolation will ensure that each transaction separate from other until both are finished. This is where Isolation Levels comes into picture. We all see blockings/lockings while running queries on SQL Server databases which result from Isolation compliance of SQL Server. 

Durability: Once a transaction is successfully executed and committed to the database, that transaction wont be lost. Those changes will be permanent and durable. SQL Server relays on continuous Checkpoints and tracking db changes with LSNs for point in time recovery purposes. The committed transaction can be recovered from any software\hardware \system failures.

Features to consider for durability:

• Recovery to the most recent successful commit point after a database software failure.
• Recovery to the most recent successful commit point after an application software failure.
• Recovery to the most recent successful commit point after a CPU failure.
• Recovery to the most recent successful backup after a disk failure. 

-Thanks