How do I install xp_encrypt?

How do I install xp_encrypt?

Pharos Systems supplies an extended stored procedure that can be called using T-SQL to encrypt passwords into the form used to store them in the database. The extended stored procedure, XP_Encrypt.dll, can be obtained from Pharos Systems on request. It must be installed before it can be used in scripts.

Note: There are two versions of XP_Encrypt.dll: one for SQL Server 7 only and one for SQL Server 2000 (which also supports SQL Server 7). Make sure you have the correct version.

Installing XP_Encrypt.dll

To install the extended stored procedure, copy XP_Encrypt.dll to your SQL Server Binn directory (depending on which version of SQL Server you use, this will be MSDE Binn, MSSQL7\Binn, or Microsoft SQL Server\MSSQL\Binn).

Add your new extended stored procedure by using the SQL Server Enterprise Manager, or by using the SQL Server Query Analyzer to execute the following SQL command against the master database:

sp_addextendedproc 'xp_encrypt', 'XP_Encrypt.dll'

You may drop the extended stored procedure by using the SQL command:

sp_dropextendedproc 'xp_encrypt'

You may release the DLL from the server (to delete or replace the file), by using the SQL command:

DBCC xp_encrypt(FREE)

In order for a script to use the xp_encrypt stored procedure, the "pharos" account in SQL Server must be assigned EXECUTE permissions against the master database in the SQL Server Enterprise Manager.

To use the extended stored procedure, call it from a SQL Server SQL script. When called from a script, it expects two arguments:

• The first argument is the password to be encrypted.

• The second argument must be OUTPUT.

The parameters can be passed either by name or by position (unnamed). If the remote stored procedure call is made with some parameters passed by name and some passed by position, an error occurs.

Sample Script

Below is a sample SQL script that uses xp_encrypt to update a user’s password:
create procedure change_users_password
( @user_id varchar(16),
@new_password varchar(16)
)
as
begin
declare @crypttext varchar(64)
-- encrypt the password and put it into @crypttext
exec master..xp_encrypt @new_password, @crypttext output
-- update entry in database
update people
set password = @crypttext
where id = @user_id
end