Blog Archives
mssql aes function encrypt decrypt in microsoft sql
Simple symmetric encryption in MSSQL. Copied from MS references below:
USE AdventureWorks2012;
--If there is no master key, create one now.
IF NOT EXISTS
(SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = '23987hxJKL95QYV4369#ghf0%lekjg5k3fd117r$$#1946kcj$n44ncjhdlj'
GO
CREATE CERTIFICATE Sales09
WITH SUBJECT = 'Customer Credit Card Numbers';
GO
CREATE SYMMETRIC KEY CreditCards_Key11
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE Sales09;
GO
-- Create a column in which to store the encrypted data.
ALTER TABLE Sales.CreditCard
ADD CardNumber_Encrypted varbinary(128);
GO
-- Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY CreditCards_Key11
DECRYPTION BY CERTIFICATE Sales09;
-- Encrypt the value in column CardNumber using the
-- symmetric key CreditCards_Key11.
-- Save the result in column CardNumber_Encrypted.
UPDATE Sales.CreditCard
SET CardNumber_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11')
, CardNumber, 1, HashBytes('SHA1', CONVERT( varbinary
, CreditCardID)));
GO
-- Verify the encryption.
-- First, open the symmetric key with which to decrypt the data.
OPEN SYMMETRIC KEY CreditCards_Key11
DECRYPTION BY CERTIFICATE Sales09;
GO
-- Now list the original card number, the encrypted card number,
-- and the decrypted ciphertext. If the decryption worked,
-- the original number will match the decrypted number.
SELECT CardNumber, CardNumber_Encrypted
AS 'Encrypted card number', CONVERT(nvarchar,
DecryptByKey(CardNumber_Encrypted, 1 ,
HashBytes('SHA1', CONVERT(varbinary, CreditCardID))))
AS 'Decrypted card number' FROM Sales.CreditCard;
GO
References:
Encryption 101 and Security for the Paranoid
Asymmetric Cryptography
Modern day television, cinemas and news have created a big hype around security, especially computer security, usually without any good explanation.
I’m not going to tell you that hype is false, there is definitely a growing need to be careful in these areas, but in order to make informed decisions you need to become knowledgeable about the subject.
Unfortunately most literature and content that comes from these media outlets glances on these topics just enough to make a cautious viewer paranoid, but are not necessarily informative.
Fact:
Computer security issues are increasing as well as personal safety in general with regards to technology, such as card swiping, identity theft, etc. (Source: US GOVT).
Fiction:
By spending all your money on expensive antivirus software, home security systems, identity theft protection and specialized credit cards you will be completely safe and can rest soundly.
The reality is the best protection you can really offer yourself is mostly common sense and can be remembered with a simple timeless phrase…
Never put all your eggs in one basket.
24 Security Tips for the Paranoid
(that don’t require emptying your wallet)
(see glossary below for any terms you are unfamiliar with)
[1]
If there’s a little flashing icon in the bottom of your screen that says viruses have been found on your PC, or notifications offering to help you “fix” your PC, DON’T CLICK ON IT. 75% of all computer repairs I handle were victims of this circumstance. Well known vendors such as Norton, AVG and CA allow you to run timed and manual scans. If you’re not sure where this flashing little icon came from, Google it.. or email me. 🙂
[2]
If you’re concerned about online credit card theft, get a separate credit card just for online purchases with a very low spending limit.
[3]
Try to use common checkout methods you are familiar with such as Paypal and Google Checkout. Google and paypal have certain specifications for these methods that in many cases make them more secure than the standard method on a particular site.
[4]
Keep track of sites where you store your credit card numbers (if you choose to store them at all). In the event of compromise from online purchases this will help you identify the point of breach and you were likely not the only person affected.
[5]
Research pre-paid cards which aren’t necessarily tied to a long term account and already have many built in securities.
[6]
Memorize important numbers that do not change, such as your SS, Bank and Routing. Don’t write them down and especially don’t store them electronically.
[7]
It’s hard to memorize all your passwords, so write down hints instead. If your password is related to the date you bought your first $animal (<- dog), write down something obscure like the last name of your $animal veterinarian or something even harder to relate like just a number representing the age of your animal (in $animal years).
[8]
Visit only well known websites and be careful of links from blogs 😉 and places your friends may refer you to which could unknowingly be compromised. Social networks, much like school, are an easy place to pick up germs. Secure ecommerce sites should certify that they are PCI compliant.
[9]
Learn how basic encryption works. Many applications such as outlook contain plugins for popular encryption techniques such as GPG.
[10]
Be wary of public terminals, airports and coffee shops. Even if you’re on your smart phone and browsing the WiFi at your favorite Starbucks or even JFK, the entire location or an individual access point could have been compromised or an attacker could be snooping and that cool remote banking app on your phone could open up a can of worms.
[11]
Similar to the above, always use https and/or secure networks only (esp. if wireless) whenever possible. Learn how to add Mac address filtering on your local wireless network or call your favorite local IT guy (me!) and ask them what you could do to lock down.
[12]
If possible, keep a small safety net. While disputing fraud or identity theft, you may need funds temporarily to cover bills and other perishables until the issue is resolved.
[13]
If you’re loaning money to your son/daughter/family member or close friend, give them cash or a prepaid credit card which you can refill as needed or transfer money to their bank. Loaning credit cards can be very dangerous especially if the one you are helping doesn’t follow similar security tips as these.
[14]
If you’re traveling or visiting somewhere you don’t go very often, such as a business or personal family trip, or a not frequently visited restaurant – use cash. Most types of fraud occur overseas and on long-distance trips. (Source: US GOVT).
[15]
If you lose your cell phone or wallet, make sure to cancel any and all cards and identification contained within or have numbers re-issued. This will be a hassle, but it’s worth it.
[16]
Always lock or password protect your computer and electronic devices whenever possible. A lost cellphone or laptop could contain personal information and lead to compromise. In addition, many devices such as smartphones contain security countermeasures which allow you to remotely wipe the device if it is lost.
[17]
Own at least two forms of photo identification and only carry at most one on your person if possible. Whether it is military ID, state ID, drivers license or passport, if you happen to lose one it is often easier to re-obtain if you still have the other.
[18]
When you’re leaving the house, only bring the necessities. You should probably always carry your drivers license, especially if you’re pulled over for speeding ;). You or a relative’s social security card and other non-critical credit cards may not always need to be in your possession however. If you frequently use checks, keep a few in your wallet or purse, but don’t bring the whole checkbook.
[19]
Don’t share passwords or accounts! Your husband or spouse might be the exception, but make sure he/she is also familiar with these tips if you do.
[20]
If you’re concerned about home invasion, theft or burglary, purchase or make a sign which indicates the home is under surveillance and protected. Even if no such protection exists, this will often ward would-be attackers casing your home. If one or more of your neighbors has the same protection, they will likely avoid your neighborhood altogether permanently.
[21]
When traveling and away from home for a long period, a webcam can be setup as basic home surveillance. Keep in mind, this could also open up the possibility of your webcam becoming compromised so make sure they are setup in places such as the main room or doors and entry ways and do not make them accessible over the internet unless you first tunnel through a VPN.
[22]
Place anything important in a safe whenever possible. Jewelry or belongings which are rarely used fall into this category.
[23]
For home based businesses or small business owners – Beware of dumpster diving and make sure you have locked filing cabinets and shred any documents you don’t need. Old documents can be scanned and archived electronically and stored onto tape or other persistent media which can be encrypted. This can also be helpful in the event of a flood or fire.
[24]
If you’re extremely paranoid and worried that basic antivirus and a home alarm system or sign/neighborhood watch won’t be enough, purchase DIY home booby traps, watch every Home Alone movie in one sitting, and be prepared to lose all friend and family relations. Get ready for a long and lonely life. 😛
Glossary of Terms:
safety net – An alternative bank account, safe or separately managed funds to help you pay for expenses while recovering from fraud or any other event which could affect your existing assets.
SSL – A protocol which wraps your connection to a website inside a “secure socket layer” of encryption.
Dumpster Diving – Bad guys going through your trash looking for information.
VPN – Virtual Private Network. A secure way of accessing your home remotely. Call your local IT guy or do some Googling to set one up.
Casing – Bad guys driving through your neighborhood looking for targets. To prevent, talk to your neighbors, or put a sign and/or camera in front of the house.
skim / skimming / swiping – This is when bad guys posing as good guys, at your local restaurant or favorite retail store in the city, illicitly obtain your credit card number. Remember the tips regarding credit cards above as this crime is likely to increase over the next few years.
snoop / snooping / sniffing – In the context of computer security, this is usually when another user on the network is listening or capturing all information going to and from. Stick to SSL sites and secure wireless networks only.
encrypt / decrypt – Encryption is the process of transforming content from plaintext into ciphertext. decryption is the reverse; from ciphertext to plaintext.
plaintext / ciphertext – plaintext is human readable. like your email or the text messages on your phone. ciphertext is garbled and in many cases not even alphanumeric characters. writing in pig-latin or through a mirror is not making ciphertext. ciphertext requires someone to either know or guess the key, password, passphrase and/or vectors and apply a specific type of decryption to reverse.
key / password / passphrase / vector – These are all roughly synonymous with password and are sometimes stored in files instead of being typed in. Research encryption for more info on vectors.
TDES / AES / Rijndael / 128bit /block cipher – If you see or hear any words like this, they are talking about encryption and cryptography. These are different types. Read more on Wikipedia or my other pages.
bit (strength) – In the context of computer security or encryption this usually is in reference to the strength of the security, measured in bits. This can also apply to SSL strength since this utilizes encryption. Common values include 40bit, 64bit, 128bit, 256bit and 512bit.
asymmetric / symmetric – This identifies the process a particular encryption method uses, generally with regards to how information is communicated between two or more parties. It doesn’t necessarily govern HOW the data is encrypted, just the process flow of the data itself from beginning to end. See references below and research GPG for examples on how this might be usable in your everyday life.
pci compliance – Payment Card Industry standard on how personal data should be stored, processed and transmitted. Very important and might be better to understand for your general knowledge than you might think. See references for links.
Conclusion
For a technical illustration, take a look at my quick net encryption reference for a working example in Microsoft .Net which illustrates asymmetric key encryption.
See articles on encryption at Wikipedia and similarly linked articles for a more complete reference.
References:
Wikipedia, Encryption, http://en.wikipedia.org/wiki/Encryption
U.S. CERT, http://www.us-cert.gov/cas/tips/
GPG, http://www.gnupg.org/
PCI DSS, https://www.pcisecuritystandards.org/security_standards/index.php
Quick .Net Encryption Reference
The code below represents a very basic .NET encryption class which has been tested and should work in your application – simply plug and play. 🙂
Contains two static methods that can be called without needing to instantiate the class.
Keep in mind the initialization vector below (indicated by rgbIV) is generic, and you will need to come up with your own. Remember not to share this. Even if the password is compromised, the attacker would also need to know the initialization vector to crack your value.
Also note the code which has been commented out. This illustrates cases where passwords and/or IV can be statically set in the class and/or shared based on value passed in for password parameter.
Sharing IV and password or storing either statically is a security risk and could cause errors depending on byte differences of the values. If you statically store these values, you will still create secure cipher text, but it will be much easier to crack.
Enjoy. 😉
using System;
using System.IO;
using System.Text;
using System.Security.Cryptography;
namespace AIS.Common.Crypto
{
public static class Rijndael
{
public static string Encrypt(string ClearText,string password)
{
byte[] clearTextBytes = Encoding.UTF8.GetBytes(ClearText);
System.Security.Cryptography.SymmetricAlgorithm rijn = SymmetricAlgorithm.Create();
MemoryStream ms = new MemoryStream();
byte[] rgbIV = Encoding.ASCII.GetBytes("example");
//byte[] key = Encoding.ASCII.GetBytes("longerexample");
//byte[] rgbIV = Encoding.ASCII.GetBytes(password);
byte[] key = Encoding.ASCII.GetBytes(password);
CryptoStream cs = new CryptoStream(ms, rijn.CreateEncryptor(key, rgbIV),
CryptoStreamMode.Write);
cs.Write(clearTextBytes, 0, clearTextBytes.Length);
cs.Close();
return Convert.ToBase64String(ms.ToArray());
}
public static string Decrypt(string EncryptedText, string password)
{
byte[] encryptedTextBytes = Convert.FromBase64String(EncryptedText);
MemoryStream ms = new MemoryStream();
System.Security.Cryptography.SymmetricAlgorithm rijn = SymmetricAlgorithm.Create();
byte[] rgbIV = Encoding.ASCII.GetBytes("example");
//byte[] key = Encoding.ASCII.GetBytes("longerexample");
//byte[] rgbIV = Encoding.ASCII.GetBytes(password);
byte[] key = Encoding.ASCII.GetBytes(password);
CryptoStream cs = new CryptoStream(ms, rijn.CreateDecryptor(key, rgbIV),
CryptoStreamMode.Write);
cs.Write(encryptedTextBytes, 0, encryptedTextBytes.Length);
cs.Close();
return Encoding.UTF8.GetString(ms.ToArray());
}
}
}
References:
Wikipedia – Encryption, http://en.wikipedia.org/wiki/Encryption
Out of Office Reply 