Can I implement an input mask in SQL Server?

Access has a formatting feature that allows you to mask data (e.g. using ********) when users open a table in the Access GUI, instead of showing the sensitive data that's really stored in the table (be it a password, credit card number, social security number, etc). 
 

 
People often forget that Access is both a front-end and a back-end tool. SQL Server is only a back-end tool, designed for data management and NOT data presentation. So, there are several other approaches you will need to choose from. Some of these will assume that you really want to secure and protect the underlying data, not just make it look pretty (e.g. in case users use profanity in their passwords). 
 
Store an encrypted version 
 
Of course, if you need to later retrieve the data, encryption is only as strong as the place where you store your decryption algorithm. In fact, many will argue that encryption that can be reversed should not be called encryption at all, but rather "encoding" or "obfuscation." 
 
In the case of passwords, there really is no reason to store the password in plain text. You can easily store an encrypted version, and when verifying permissions as the user logs in, apply the same encryption to the password they submitted. Then, you compare the encrypted versions and make sure they're equal, instead of comparing the plain text versions. 
 
My recommendation is to use available APIs in your application to encrypt the data BEFORE you pass it to the database. This prevents the plain text data from going across the wire. For information about MD5 and other components available for ASP, see Article #2249 and Article #2397. Here is a quick example. 
 
First, I created this table and stored procedures: 
 

CREATE TABLE dbo.Users  (      email VARCHAR(255)          PRIMARY KEY CLUSTERED,      pass_word VARCHAR(255)  )  GO    CREATE PROCEDURE dbo.AddUser      @email VARCHAR(255),      @pass_word VARCHAR(255)  AS  BEGIN      SET NOCOUNT ON        INSERT dbo.Users      (          email,          pass_word      )      SELECT          @email,          @pass_word  END  GO    CREATE PROCEDURE dbo.GetUser      @email VARCHAR(255)  AS  BEGIN      SET NOCOUNT ON        SELECT email, pass_word          FROM dbo.Users          WHERE email = @email  END  GO

 
Next, I grabbed the MD5 code from ~linkid(131, '', 'http://userpages.umbc.edu/~mabzug1/cs/md5/md5.asp')~ and placed it into a file called MD5.inc (thanks, Betty and Wilma!). 
 

<!--#include file="MD5.inc"-->    <%      set conn = CreateObject("ADODB.Connection")      conn.open "<connection string>"        submittedEmail = "foo@bar.com"      submittedPassword = "foobar"    ' let's create a hashed password        sql = "EXEC dbo.AddUser 'foo@bar.com', '" & MD5("foobar") & "'"      conn.execute sql, , 129    ' let's get this user's details        sql = "EXEC dbo.GetUser 'foo@bar.com'"      set rs = conn.execute(sql)      storedPassword = rs(1)    ' let's make credentials fail:        checkAccess storedPassword, "splunge"    ' now this one will work:        checkAccess storedPassword, "foobar"    ' here is the function where you handle checking   ' and spit out the result, pass or fail.        sub checkAccess(storedPW, submittedPW)          if storedPW = MD5(submittedPW) then              Response.Write "Yay! It worked!"              ' set session variables, redirect, etc          else              Response.Write "Boo! No access!"          end if      end sub  %>

 
Clean-up: 
 

DROP TABLE dbo.Users  DROP PROCEDURE dbo.AddUser, dbo.GetUser

 
Obviously, your application wouldn't flow exactly like this. You would have a different page for adding users than for checking credentials (so you wouldn't be creating a hash of a password and then verifying that it worked four lines later), and you would likely have a lot more information in the users table than e-mail address and password. But the above is a starting point to demonstrate the methodology. As you are testing the above, you will want to stop between each refresh and do the following: 
 

TRUNCATE TABLE dbo.Users

 
A benefit of this approach is that you won't be using an undocumented function in SQL Server. PWDENCRYPT() and PWDCOMPARE() are very tempting to use, but they are undocumented for a reason: they are unsupported, and could change or disappear with a new version of SQL Server, or even a service pack or hotfix. So if you build an application around this, it may come crumbling down at the worst possible time. Also, I'm not going to speak for the strength of the algorithm PWDENCRYPT uses, or the ability of a determined user to crack it. 
 
Nonetheless, to be thorough, I will include a sample of how I would do this so that password comparison were handled inside the database, were these functions documented and supported (and weren't I so risk averse). 
 

CREATE TABLE dbo.Users  (      email VARCHAR(255)          PRIMARY KEY CLUSTERED,      pass_word VARBINARY(255)  )  GO    CREATE PROCEDURE dbo.AddUser      @email VARCHAR(255),      @pass_word VARCHAR(16)  AS  BEGIN      SET NOCOUNT ON        INSERT dbo.Users      (          email,          pass_word      )      SELECT          @email,          PWDENCRYPT(@pass_word)    END  GO    CREATE PROCEDURE dbo.GetUser      @email VARCHAR(255),      @pass_word VARCHAR(16)  AS  BEGIN      SET NOCOUNT ON        SELECT email, HasAccess = PWDCOMPARE(@pass_word, pass_word)          FROM dbo.Users          WHERE email = @email  END  GO

 
Now the ASP code to run a test (change out e-mail addresses to run multiple times, and don't forget to use a purposely false password to verify the failure "works"): 
 

<%      set conn = CreateObject("ADODB.Connection")      conn.open "<connection string>"        submittedEmail = "foo@bar.com"      submittedPassword = "foobar"    ' let's create a hashed password        sql = "EXEC dbo.AddUser '" & submittedEmail & "', '" & submittedPassword & "'"      conn.execute sql, , 129    ' let's get this user's details        sql = "EXEC dbo.GetUser '" & submittedEmail & "', '" & submittedPassword & "'"      set rs = conn.execute(sql)      HasAccess = rs(1)    ' let's check if he has access        if CLng(HasAccess) = 1 then          Response.Write "Yay! It worked!"          ' set session variables, redirect, etc      else          Response.Write "Boo! No access!"      end if  %>

 
Clean-up: 
 

DROP TABLE dbo.Users  DROP PROCEDURE dbo.AddUser, dbo.GetUser

 
You could do something similar using supported code by using CONVERT(VARBINARY), however this will only shield the password from the most casual users. You can convert to and from VARBINARY <-> VARCHAR without difficulty, so anyone with this knowledge would have free range over all the passwords in the table. At least in the above cases they will need to resort to a dictionary attack of some sort, and that can be prevented to some degree by both software and hardware (and can be pinpointed to a workstation if it is coming from within your company <G>). 
 
In SQL Server 2005, you will be able to create CLR code, using any encryption algorithm you like, and handling it all inside the database, without using unsupported or undocumented functions. 
 
Deny read access to the table 
 
You could easily create a view or stored procedure that simulated the input mask functionality that Access provides. For example: 
 

CREATE TABLE dbo.Users  (      email VARCHAR(255)          PRIMARY KEY CLUSTERED,      pass_word VARCHAR(16),      cc_number CHAR(16)  )  GO    INSERT dbo.Users(email, pass_word, cc_number)      SELECT 'foo@bar.com', 'mypassword', '4111111111111111'    INSERT dbo.Users(email, pass_word, cc_number)      SELECT 'foo@splunge.com', 'x', '4111111111111111'  GO    CREATE VIEW dbo.UsersPublicInfo  AS      SELECT          email,          pass_word = '********',          cc_number = 'xxxxxxxxxxxx'+RIGHT(cc_number, 4)      FROM dbo.Users  GO    SELECT * FROM dbo.Users  SELECT * FROM dbo.UsersPublicInfo  GO

 
In order to prevent users from viewing the data, you can simply issue a DENY: 
 

DENY ALL ON dbo.Users TO [username]

 
Now when this user tries to run the SELECT against dbo.Users listed above, they get the following error: 
 

Server: Msg 229, Level 14, State 5, Line 1  SELECT permission denied on object 'Users', database 'pubs', owner 'dbo'.

 
They get similar errors if they try to UPDATE, INSERT or DELETE. In other words, if they want to see the data in the table, add new rows, or modify existing data, they will need to interface through *your* stored procedures. Of course, if they are an elevated user (e.g. a sysadmin in SQL Server) or can access the database as a domain admin, they can easily thwart your object-level permissions settings. Users you need to be concerned about should only be in the public, db_datareader and db_datawriter roles. 
 
The benefit to this approach is that, because it is stored in plain text, the people who *should* have access to the data can easily view and update without having to use convoluted methods like 3rd party encryption or even data type conversion. Only you can determine how secure your data needs to be, relative to how easy you want your maintenance to be.