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:

http://msdn.microsoft.com/en-us/library/bb669072.aspx

http://msdn.microsoft.com/en-us/library/ms179331.aspx

asp .net could not establish trust relationship for the SSL/TLS secure channel

A quick google search revealed multiple reported resolutions, however, after following the steps in the MSDN blog reference listed below, the issue was still unresolved in my situation.

Additional details in the stack trace will reveal another similar message: “The remote certificate is invalid according to the validation procedure.”

In this specific scenario, the site in question is either not configured with a wildcard certificate for a subdomain of the parent site or the operation system I am working on does not support SNI. In the meantime, a workaround is needed to continue testing and development.

Additional reading on google revealed another solution which was more suitable and utilized a code based approach, as opposed to a server configuration based solution.

To make it more dynamic, I added a key into the app/web config to control if SSL errors should be ignored. Please note that it is also possible to replace the code based approach solely with an app/web config entry listed in the west-wind blog referenced below, but I personally prefer to go with code whenever possible.

<?xml version="1.0"?>
<configuration>
  <configSections>
  </configSections>
  <connectionStrings>
    <add name="ConnectionString"
      connectionString="Data Source=servername;Initial Catalog=databasename;"
      providerName="System.Data.SqlClient" />
  </connectionStrings>
  <appSettings>
    <add key="ignoresslerrors" value="true"/>
  </appSettings>
<startup><supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/></startup></configuration>
 public class ConfigValues
    {
        public static string IgnoreSSLErrors { get { return getval("ignoresslerrors"); } }
}

public function main() {
connect("https://sitename.com",ConfigValues.IgnoreSSLErrors);
}

public function connect(string url, string ignoresslerrors) {
HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);

try
            {
                if (Convert.ToBoolean(ignoresslerrors))
                {
                    System.Net.ServicePointManager.ServerCertificateValidationCallback +=
            delegate(object sender, System.Security.Cryptography.X509Certificates.X509Certificate certificate,
                                    System.Security.Cryptography.X509Certificates.X509Chain chain,
                                    System.Net.Security.SslPolicyErrors sslPolicyErrors)
            {
                return true; //will always accept the cert and ignore errors. this is not good common practice unless you are sure of the destination you are connecting to. needed in this scenario to continue development until issue with cert is resolved.
            };
                }
            }
            catch (Exception ex)
            {
                Shared.HandleError(ex);
            }
}

References

http://www.west-wind.com/weblog/posts/2011/Feb/11/HttpWebRequest-and-Ignoring-SSL-Certificate-Errors

http://blogs.msdn.com/b/jpsanders/archive/2009/09/16/troubleshooting-asp-net-the-remote-certificate-is-invalid-according-to-the-validation-procedure.aspx

Create Custom and Self Signed SSL Certificate in IIS 6 (Windows Server 2003)

Single domain

Ex.
http://www.ronniediaz.com

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

Follow

Get every new post delivered to your Inbox.