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:
Create Custom and Self Signed SSL Certificate in IIS 6 (Windows Server 2003)
Single domain
Note: Foremost, in order to access your site over HTTPS, the HTTP SSL Service will need to be running. This service binds to port 443 and filters HTTP traffic to IIS.
Relatively easy to do in never versions of IIS , but a few more steps in previous.
First, download SSL Diagnostics from MS:
http://www.microsoft.com/download/en/details.aspx?amp;displaylang=en&id=674
Make sure if you are on 32bit you install the x86 version above or the x64 version if you are on 64 bit.
Once installed, open a cmd prompt.
cd C:\Program Files\IIS Resources\SSLDiag ssldiag /selfssl /N:CN=(certname.com) /V:365 /S:(ID)
This will create a self signed cert with (certname.com) which is good for 365 days. See link below for additional information on other parameters you may need to pass in such as site identifier and key length (default 1024).
If you try to test it right now, you will likely get a “Page Cannot Be Displayed”, “Cannot Find Server”, “Server Cannot be Found” or something to this effect. This is because the port 443 bindings are not yet in place.
To add the port 443 bindings to your site, run adsutil:
cscript.exe "C:\inetpub\AdminScripts\adsutil.vbs" set w3svc/(site id in iis)/SecureBindings ":443:sub.host.com"
To confirm the certificate was added, navigate to your site in IIS and go to:
(right click domain)->Properties->Directory Security->Secure Communications->View Certificate
Multi domain wildcard
Ex.
http://www.ronniediaz.com
test.ronniediaz.com
blog.ronniediaz.com
Configuring the wildcard certificate, is essentially the same as above, however simply use * for sub domain in your cert creation and binding, and export and add this cert to your other sub domains which will be using it.
Multi domain SAN or UCC
Ex.
http://www.ronniediaz.com
test.ronniediaz.com
http://www.wordpress.com
ronniediaz.wordpress.com
Subject alternative name certificates a.k.a unified communications certificates can get very expensive when issued by third parties.. To create your own, there are three approaches to consider:
– Certificate Enrollment wizard with an enterprise CA (Using Advanced->Custom Request or New Request->Template)
– Certificate Enrollment wizard with a standalone CA
– Certreq.exe
For more information on configuring your own SAN cert, reference MS Kbase article http://technet.microsoft.com/en-us/library/ff625722(WS.10).aspx
OpenSSL/Linux
On Linux, the process is IMO a bit easier and simply requires openssl. Since this utility is also available for windows, this method can be used on IIS as well.
http://lanestechblog.blogspot.com/2009/04/creating-ssl-certificates-with-multiple.html
References
ServiceFirstSupport, http://www.servicefirstsupport.com/KB/a175/how-do-i-create-a-self-signed-ssl-certificate-in-iis-6.aspx
SAN Cert/Custom Request, http://techontip.wordpress.com/2011/06/06/how-to-create-a-san-certificate-signing-request-for-iis-web-server/
Page Cannot be Display, http://support.microsoft.com/?id=290391
Debug SSL Connectivity on Intermediary Device, http://support.microsoft.com/?id=290051
Cannot Find Server, http://support.microsoft.com/?id=292296
IIS forums, http://forums.iis.net/t/1147942.aspx
adsutil, http://support.microsoft.com/?id=290391
Server 03 pre SP1 HTTP 1.1 host headers bug with SSL, http://support.microsoft.com/kb/187504/EN-US
Configuring Host Headers (IIS6.0), http://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/596b9108-b1a7-494d-885d-f8941b07554c.mspx?mfr=true
Configuring Server Bindings for SSL Host Headers, http://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/8d9f2a8f-cd23-448c-b2c7-f4e87b9e2d2c.mspx?mfr=true
Obtaining/Installing Wildcard certificate (IIS6), http://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/5d0fb4c2-3333-4fec-82fc-6e15d3733937.mspx?mfr=true
SSL Host Headers in IIS 6, http://www.sslshopper.com/article-how-to-configure-ssl-host-headers-in-iis-6.html
UCC Certificate Partners, http://support.microsoft.com/kb/929395
How to Create SAN Certificate for Web Server, http://techontip.wordpress.com/2011/06/06/how-to-create-a-san-certificate-signing-request-for-iis-web-server/
OpenSSL SAN Cert, http://lanestechblog.blogspot.com/2009/04/creating-ssl-certificates-with-multiple.html
How to Request a Certificate With a Custom Subject Alternative Name, http://technet.microsoft.com/en-us/library/ff625722(WS.10).aspx
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
IIS Application Pool
A great many documents, blogs and references exist on IIS which cover it comprehensively. This article simply serves to discuss one aspect – the application pool.
When configuring IIS in an environment that will contain many websites, there are a few important considerations:
– Will each site potentially run different versions of .NET? Sites with different frameworks should not share the same app pool. This will cause a conflict and they will not be functional.
– Is it a large web application that requires many resources? If yes, you may also want to consider creating it’s own app pool, which allows you to manage detailed information regarding how the server prioritizes the site.
Recycling
The first tab for the app pool properties displays information regarding memory resource usage. If you have multiple sites on a server which are hit very often and would like to force the amount of memory each one uses, you can create an app pool for each and manage it here.
Or if it will be split evenly among various sites, you can create “groups” of app pools with assigned memory and IIS will allocate among children accordingly.
You can also force the worker process to automatically free up memory after specified interval and/or at different times of the day.
Recycling
Performance
The performance tab allows for CPU resource management. Certain sites may have functions that, left unchecked or still under development, can cause excessive CPU load and affect other processes running on the same server.
Max CPU usage can be assigned here setup in conjunction with events to trigger when these scenarios occur.
A remote exploit or overflow attempt of your server can also be secured here by limiting the maximum number of kernel requests.
Idle timeout can help free up CPU for a site that is not very active. Legacy sites or informational areas which are rarely visited are still using a fraction of the CPU usage, and setting an idle timeout can help release some of those resources.
Performance
Health
The health tab can help you diagnose potential issues with your site or application. By forcing certain restrictions such as maximum failures and/or maximum failures within a specific time period, as well as enabling pinging, you can check for periods of down time or help identify timeout issues a specific web service may be experiencing.
This tab works well in conjunction with third party testing tools used to benchmark and stress test your site, application or service.
Health
Identity
Depending on the size and nature of your infrastructure, you may want to configure certain application pools to run under different credentials. For highly security conscious people this can theoretically reduce the likelihood of certain privilege escalation techniques that may be executed in the event of a remote application pool exploit.
Identity
Hope the above information was helpful. This was a condensed version based on a similar article I read on Windows Networking. See reference below for full article.
References
WindowsNetworking, http://www.windowsnetworking.com/articles_tutorials/Working-Application-Pools-Internet-Information-Server.html
Aldi Stores Credit Card Fraud
A recent article I came across from my Security Focus newsletter inspired my latest post regarding the recent Aldi Credit Card fraud that occurred across 31 states. (See entire article below)
To quote the article’s author:
“It looks like this was the work of a network of criminals who went into stores and somehow distracted store personnel long enough to take out PIN pads and swap them out with retrofitted devices…rogue PIN pads allowed the attackers to capture payment card data wirelessly from within the store itself or from a nearby location…tampering likely occurred over a period of several months…driving the trend is the easy and growing availability of sophisticated counterfeit payment terminal kits…rings of fraudsters, largely from Eastern Europe…same types of fraudsters are organized to attack multiple stores in multiple states simultaneously…”
These kind of high level crimes set a dangerous precedence if they increase in popularity. Our current civil authorities simply put do not have the level of sophistication required to stop these types of criminals.
A common police protocol in this type of situation might be to see the video types for individual stores for the last 6 months, however, most stores do not carry footage for this long and unless the devices themselves can be traced back to original manufacturing, not many leads could be extracted without the resources of federal investigative units.
This emphasizes the importance of security corporations and organizations to help and assist these government agencies in any way possible. Federal units are currently highly diversified, and with increased focus on countering violent terrorists, these other acts that are non-violent may get overlooked.
Even with federal and state-wide corporate assistance, their may still be needed political resources. Groups which organized crimes such as the Aldi Stores Fraud may have established rings outside of the country, and fly in trained professionals, either consultants or direct employees, most likely with fake identities, to commit the crimes and then fly back out, etc.
To stop this kind of international crime will require treaties and help of many government and corporate agencies, as this clearly indicates the need for international policy to combat all forms of terrorism, even these non-violent actions.
The defense and security of individual nations can only be a realized with international cooperation.
I’m sure these realizations and concerns are not mine alone, and can only hope this same thought is shared across the right people to truly make a difference.
References:
Jaikumar Vijayan. “Aldi data breach shows payment terminal holes”. Computerworld. http://www.computerworld.com/s/article/9189982
October Newsletter. Securityfocus. http://www.securityfocus.com/
Out of Office Reply 