Encrypt / Decrypt password in Sql Server Database

I know this solution is not a recommended solution by many DBAs but it is available to use. Sometimes you have to get things going and implementing each step with detail can take weeks to finish but as a quick solution, this method has helped me to store password in the database with encryption and decryption methods.

I will not suggest this implementation but it is a fast way to store passwords in database.

CREATE TABLE LoginDetails (
 UserName VARCHAR(50)
      , [Password] VARBINARY(100)
)

INSERT INTO LoginDetails VALUES ('John', ENCRYPTBYPASSPHRASE('secretkey', 'johnPassword'))
INSERT INTO LoginDetails VALUES ('Shaw', ENCRYPTBYPASSPHRASE('secretkey', 'shawPassword'))
INSERT INTO LoginDetails VALUES ('Mike', ENCRYPTBYPASSPHRASE('secretkey', 'mikePassword'))

When I select from table, I see binary data in Password column


Now to read password, you need to decrypt it with the same key along convert function to read binary data as readable text.

SELECT username, CONVERT(VARCHAR(100), DECRYPTBYPASSPHRASE('secretkey', [password])) AS Password FROM LoginDetails

Here is the result

How to find start date of month in SQL Server?

SELECT DATEADD(month, DATEDIFF(month, 0, @date), 0) AS StartOfMonth

0 in date functions means 1/1/1900.
The above function first find the difference of total months from 1/1/1900.
It then adds the difference in 0 (1/1/1900) again which will land to the beginning date of month.