You are currently browsing the category archive for the ‘MS SQL’ category.
PWDENCRYPT & PWDCOMPARE are two undocumented methods provided by MS SQL Server. PWDENCRYPT generates a hash given a string, and PWDCOMPARE is used to Compare a string with generated hash.
Example:
DECLARE @varPassword NVARCHAR(128)
SELECT @varPassword = ‘Test Encryption’
DECLARE @passwordHash VARBINARY(128)
SELECT @passwordHash = PWDENCRYPT(@varPassword)
PRINT ‘Password: ’ + @varPassword
PRINT ‘Password Hash: ’ + CAST(@passwordHash AS NVARCHAR(128))
DECLARE @chkPassword NVARCHAR(128)
SELECT @chkPassword = ‘Test Encryption’
IF (PWDCOMPARE(@chkPassword, @passwordHash) = 1)
BEGIN
PRINT ‘Match found’
END
ELSE
PRINT ‘Match not found’
These methods work great as far as you are not expecting too much out of them. Let me put down some pros and cons which will help you make your choice regarding whether you should use these methods or not.
Pros:
- Easy to use
- Encryption/Decryption handled directly at SQL level
- Easy to make bulk changes using queries
- In built methods
Cons:
- Case insensitive (now that’s a great problem if you are planning to use it for generating passwords)
- Altering the collation settings does not help make it work consistently
- Undocumented and hence not supported by Microsoft
Summary:
If you are in any way using PWDENCRYPT & PWDCOMPARE, you might want to go back and check for inconsistencies. I would personally recommend handling the encryption at Code level along with case sensitive collation on the SQL side for storing the encrypted values.
For people who are familiar with database triggers, you must be aware with INSERT, UPDATE & DELETE Triggers which get fired whenever the said action takes place. MS SQL provides a new type of trigger to the set called INSTEAD OF triggers. INSTEAD OF trigger help us override the action itself by our very own custom made SQL operations. For example, whenever a delete operation is performed on a table, I do not want the record to be deleted but instead just set a flag-‘Deleted’ as true in the record. This can be achieved using INSTEAD OF DELETE Trigger.
Below is the extract from msdn (http://msdn.microsoft.com/en-us/library/aa175158%28SQL.80%29.aspx) regarding workings of INSTEAD OF Triggers:
Designing INSTEAD OF Triggers
The primary advantage of INSTEAD OF triggers is that they allow views that would not be updatable support updates. A view comprising multiple base tables must use an INSTEAD OF trigger to support inserts, updates and deletes that reference data in the tables. Another advantage of INSTEAD OF triggers is that they allow you to code logic that can reject parts of a batch while allowing other parts of a batch succeed.
An INSTEAD OF trigger can take actions such as:
- Ignoring parts of a batch.
- Not processing a part of a batch and logging the problem rows.
- Taking an alternative action if an error condition is encountered.
Note INSTEAD OF DELETE and INSTEAD OF UPDATE triggers cannot be defined on a table that has a foreign key defined with a DELETE or UPDATE action.
Coding this logic as part of an INSTEAD OF trigger prevents all applications accessing the data from having to reimplement the logic.
In the following sequence of Transact-SQL statements, an INSTEAD OF trigger updates two base tables from a view. In addition, two approaches to handling errors are shown:
- Duplicate inserts to the Person table are ignored, and the information from the insert is logged in the PersonDuplicates table.
- Inserts of duplicates to the EmployeeTable are turned into an UPDATE statement that retrieves the current information into the EmployeeTable without generating a duplicate key violation.
The Transact-SQL statements create two base tables, a view, a table to record errors, and the INSTEAD OF trigger on the view. These tables separate personal and business data and are the base tables for the view:
CREATE TABLE Person
(
SSN char(11) PRIMARY KEY,
Name nvarchar(100),
Address nvarchar(100),
Birthdate datetime
)
CREATE TABLE EmployeeTable
(
EmployeeID int PRIMARY KEY,
SSN char(11) UNIQUE,
Department nvarchar(10),
Salary money,
CONSTRAINT FKEmpPer FOREIGN KEY (SSN)
REFERENCES Person (SSN)
)
This view reports all relevant data from the two tables for a person:
CREATE VIEW Employee AS
SELECT P.SSN as SSN, Name, Address,
Birthdate, EmployeeID, Department, Salary
FROM Person P, EmployeeTable E
WHERE P.SSN = E.SSN
You can record attempts to insert rows with duplicate social security numbers. The PersonDuplicates table logs the inserted values, the name of the user who attempted the insert, and the time of the insert:
CREATE TABLE PersonDuplicates
(
SSN char(11),
Name nvarchar(100),
Address nvarchar(100),
Birthdate datetime,
InsertSNAME nchar(100),
WhenInserted datetime
)
The INSTEAD OF trigger inserts rows into multiple base tables from a single view. Attempts to insert rows with duplicate social security numbers are recorded in the PersonDuplicates table. Duplicate rows in the EmployeeTable are changed to update statements.
CREATE TRIGGER IO_Trig_INS_Employee ON Employee
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
-- Check for duplicate Person. If no duplicate, do an insert.
IF (NOT EXISTS (SELECT P.SSN
FROM Person P, inserted I
WHERE P.SSN = I.SSN))
INSERT INTO Person
SELECT SSN,Name,Address,Birthdate
FROM inserted
ELSE
-- Log attempt to insert duplicate Person row in PersonDuplicates table.
INSERT INTO PersonDuplicates
SELECT SSN,Name,Address,Birthdate,SUSER_SNAME(),GETDATE()
FROM inserted
-- Check for duplicate Employee. If no duplicate, do an insert.
IF (NOT EXISTS (SELECT E.SSN
FROM EmployeeTable E, inserted
WHERE E.SSN = inserted.SSN))
INSERT INTO EmployeeTable
SELECT EmployeeID,SSN, Department, Salary
FROM inserted
ELSE
--If duplicate, change to UPDATE so that there will not
--be a duplicate key violation error.
UPDATE EmployeeTable
SET EmployeeID = I.EmployeeID,
Department = I.Department,
Salary = I.Salary
FROM EmployeeTable E, inserted I
WHERE E.SSN = I.SSN
END
Suraj Chavda's Blog 