Blog Page

SSIS Variable Expression Limits

sqlsafety 0 Comments

     I recently blogged about using SSIS variables in data flow or execute SQL tasks, the post can be found here.  I received a couple of questions about this technique specifically regarding the limit of 4,000 characters in a variable expression.  The first thing I want to clarify is that this character limitation is not a limitation for SSIS string variables, but only a limitation in regards to the variable expression.  Consider an outrageously large query such as this:

SELECT HumanResources.EmployeePayHistory.*, Person.Person.BusinessEntityID AS Expr1, Person.Person.PersonType, Person.Person.NameStyle, Person.Person.Title,
                  
Person.Person.Demographics, Person.Person.AdditionalContactInfo, Person.Person.EmailPromotion, Person.Person.Suffix, Person.Person.LastName,
                  
Person.Person.rowguid, Person.Person.ModifiedDate AS Expr2, Sales.PersonCreditCard.BusinessEntityID AS Expr3, Sales.PersonCreditCard.CreditCardID,
                  
Sales.PersonCreditCard.ModifiedDate AS Expr4, Person.PersonPhone.PhoneNumber, Person.PersonPhone.PhoneNumberTypeID, Employee_1.BusinessEntityID AS Expr5,
                  
Employee_1.NationalIDNumber, Employee_1.LoginID, Employee_1.OrganizationNode, Employee_1.OrganizationLevel, Employee_1.JobTitle, Employee_1.BirthDate,
                  
Employee_1.CurrentFlag, Employee_1.SickLeaveHours, Employee_1.VacationHours, Employee_1.SalariedFlag, Employee_1.HireDate, Employee_1.rowguid AS Expr6
FROM     Sales.SalesOrderHeader INNER JOIN
                  
Sales.SalesOrderDetail ON Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID AND
                  
Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID AND Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID AND
                  
Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID AND
                  
Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderDetail.SalesOrderID INNER JOIN
                  
Sales.SalesOrderHeaderSalesReason ON Sales.SalesOrderHeader.SalesOrderID = Sales.SalesOrderHeaderSalesReason.SalesOrderID INNER JOIN
                  
Sales.SalesPerson ON Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.BusinessEntityID AND
                  
Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.BusinessEntityID AND Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.BusinessEntityID AND
                  
Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.BusinessEntityID AND
                  
Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.BusinessEntityID INNER JOIN
                  
Person.Person INNER JOIN
                  
Sales.PersonCreditCard ON Person.Person.BusinessEntityID = Sales.PersonCreditCard.BusinessEntityID INNER JOIN
                  
Person.PersonPhone ON Person.Person.BusinessEntityID = Person.PersonPhone.BusinessEntityID INNER JOIN
                  
HumanResources.Employee AS Employee_1 ON Person.Person.BusinessEntityID = Employee_1.BusinessEntityID INNER JOIN
                  
HumanResources.EmployeeDepartmentHistory ON Employee_1.BusinessEntityID = HumanResources.EmployeeDepartmentHistory.BusinessEntityID AND
                  
Employee_1.BusinessEntityID = HumanResources.EmployeeDepartmentHistory.BusinessEntityID AND
                  
Employee_1.BusinessEntityID = HumanResources.EmployeeDepartmentHistory.BusinessEntityID INNER JOIN
                  
HumanResources.EmployeePayHistory ON Employee_1.BusinessEntityID = HumanResources.EmployeePayHistory.BusinessEntityID AND
                  
Employee_1.BusinessEntityID = HumanResources.EmployeePayHistory.BusinessEntityID AND
                  
Employee_1.BusinessEntityID = HumanResources.EmployeePayHistory.BusinessEntityID ON Sales.SalesPerson.BusinessEntityID = Employee_1.BusinessEntityID AND
                  
Sales.SalesPerson.BusinessEntityID = Employee_1.BusinessEntityID AND Sales.SalesPerson.BusinessEntityID = Employee_1.BusinessEntityID INNER JOIN
                  
Production.Product ON Sales.SalesOrderDetail.ProductID = Production.Product.ProductID INNER JOIN
                  
Production.ProductInventory ON Production.Product.ProductID = Production.ProductInventory.ProductID AND
                  
Production.Product.ProductID = Production.ProductInventory.ProductID AND Production.Product.ProductID = Production.ProductInventory.ProductID AND
                  
Production.Product.ProductID = Production.ProductInventory.ProductID AND Production.Product.ProductID = Production.ProductInventory.ProductID AND
                  
Production.Product.ProductID = Production.ProductInventory.ProductID AND Production.Product.ProductID = Production.ProductInventory.ProductID AND
                  
Production.Product.ProductID = Production.ProductInventory.ProductID AND Production.Product.ProductID = Production.ProductInventory.ProductID AND
                  
Production.Product.ProductID = Production.ProductInventory.ProductID CROSS JOIN
                  
Production.ProductCategory

The above query obviously exceeds the 4,000 character variable expression maximum, but it is still possible to use an expression to contain the entire query.

     To demonstrate this create three variables all of a data type of string.

 variables

Paste half of the query into the expression of variables Part1 and Part2 enclosing it within double quotes.  In the expression of the Whole variable use the expression:

@[User::Part1] +  @[User::Part2]

    You can validate the variable values by dragging a script task from the toolbox onto the control flow design tab and configure it to use all three variables at read only variables:

script

Use three different message boxes to display the values of each variable:
MessageBox.Show(Dts.Variables["Part1"].Value.ToString());
MessageBox.Show(Dts.Variables["Part2"].Value.ToString());
MessageBox.Show(Dts.Variables["Whole"].Value.ToString());

      Executing the package displays three different message boxes, Part1 and Part2 which contain half of the query and Whole, which contains the entire query:

Part1

 Part1

Part2

Part2

Whole

Whole

     It is obvious that the character length of the Whole variable exceeds 4,000, which proves that the limitation of character size is specific to the expression and not the variable value.  I know that this seems quite inconvenient if you are working with a long and complex expression for a variable value, but unfortuntaely, this is the only work around avaialable.  This drawback has been posted on Microsoft Connect, but unfortunately has been closed as “By design“. 

     The sample package displayed in this post can be downloaded here.

Encrypting Column Level Data in SQL Server

sqlsafety 2 Comments

     As promised this is a repost from SQLSafety.blogspot.com, but includes the scripts outlined here.

The ability to encrypt data natively using t-sql was provided in SQL Server 2005 with the introduction of SQL Server cryptographic services.  The magic behind this feature originates in the operating system with the data protection api, DPAPI.  The first time an instance of SQL Server is started the “service master key”, SMK, is created.  The SMK is a 128-bit 3DES key which is encrypted using the DPAPI and the credentials of the SQL Server service account.  Once created the SMK is used to encrypt all “database master keys”, DMK’s, and various server side resources, credentials, linked server logins, etc.  The ability to backup, restore, and regenerate the SMK is available through t-sql:

BACKUP SERVICE MASTER KEY
    TO FILE =
'C:\SMK\service_master_key'
   
ENCRYPTION BY PASSWORD = 'Pa$$w0rd';
GO

RESTORE SERVICE MASTER KEY
    FROM FILE =
‘C:\SMK\service_master_key’
   
DECRYPTION BY PASSWORD = ‘Pa$$w0rd’;
GO

ALTER SERVICE MASTER KEY
    WITH
NEW_ACCOUNT = ‘AdvWorks\sqlserver’,
   
NEW_PASSWORD = ‘P@ssw0rd’;
GO

One of the more interesting functions above is the ALTER statement which regenerates the SMK optionally using a new service account.  As the SMK is encrypted on first start up using the credentials of the service account the question arises, what if the service account is changed?  Stuart Padley provides an excellent post that outlines theloading and decryption process of the SMK in SQL Server 2008 and Laurentiu Cristofor outlines considerations to take when changing service accounts.

In order to encrypt column level data then SMK sits at the root, but a database master key, DMK, is required in each database where data will be encrypted.  The creation and maintenance of the SMK is outside of our control, for the most part, but the creation and maintenance of DMK’s falls directly on the developer/dba.  The first step is to create the DMK:

-- Use the AdventureWorks database
USE AdventureWorks;

-- Create a Database Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd';
GO

Once the key is created it is always considered best practice to back the key up and store it some place safe in case it needs to be recovered at a later time:

BACKUP MASTER KEY
    TO FILE =
'c:\ADWDMK\ADWKey'
   
ENCRYPTION BY PASSWORD = 'P@ssw0rd';
GO

There is only one DMK per database, it is not created by default, is used solely for data encryption, and is encrypted and decrypted using the SMK.   The DMK does not directly encrypt data, but provides the ability to create keys that are used for data encryption.  The easiest way to demonstrate encrypting data is to create a key that is encrypted with a password:

CREATE SYMMETRIC KEY TestSymKey
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY PASSWORD = 'Pa$$w0rd'

OPEN SYMMETRIC KEY TestSymKey
     DECRYPTION
BY PASSWORD = ‘Pa$$w0rd’;

DECLARE @Encryptvalsym VARBINARY(MAX)
SET @Encryptvalsym = ENCRYPTBYKEY(KEY_GUID(‘TestSymKey’),‘I TOLD YOU THIS WOULD WORK!!!’)

SELECT CONVERT(VARCHAR(MAX),DecryptByKey(@Encryptvalsym)),
     
@Encryptvalsym;
GO

CLOSE SYMMETRIC KEY TestSymKey;
GO

The results look something like this:

Col1: I TOLD YOU THIS WOULD WORK!!!

Col2: 0x003F3493D8D3604B994544AA3855E5BE0100000007927EE71039F01B3E4F5946E9583D498301F804A12DD55555EC4DE6FF027B53815E9121432DA316832BF2EA5581452D

The above example demonstrates how to create a symmetric key using the 3DES algorithm* and is encrypted by password.  This is simplistic, but has some glaring shortcomings.  The first item of concern is the requirement of passing the key’s password in clear text for every ENCRYPT or DECRYPT statement.  To mitigate this risk a key can be created using a certificate rather than a password:

*As I mentioned previously the “out of the box” algorithm’s available are dependant on the operating system unless utilizing enterprise edition and EKM.

-- Create a Test Certificate

CREATE CERTIFICATE TestCertificate
  
WITH SUBJECT = 'Adventureworks Test Certificate',
  
EXPIRY_DATE = '10/31/2012';
GO

--Backup the certificate with the private key

BACKUP CERTIFICATE TestCertificate TO FILE = 'c:\cert\TestSymmetricKey'
WITH PRIVATE KEY ( FILE = 'c:\cert\TestSymmetricKeykey' ,
   
ENCRYPTION BY PASSWORD = 'Pa$$w0rd' );
GO

Once a certificate is created than that certificate can be used for data encryption rather than a password.  The below example demonstrates creating a symmetric key using the newly created TestCertificate, creating a temporary table and populating it with encrypted data from the Person.Contact table:

-- Create a Symmetric Key

CREATE SYMMETRIC KEY TestSymmetricKey
  
WITH ALGORITHM = TRIPLE_DES
   
ENCRYPTION BY CERTIFICATE TestCertificate;
   
GO

    
--Create a temporary table to hold encrypted data

-- Create a Temp Table

CREATE TABLE Person.#Temp
(ContactID   INT PRIMARY KEY,
FirstName   NVARCHAR(200),
MiddleName  NVARCHAR(200),
LastName    NVARCHAR(200),
eFirstName  VARBINARY(200),
eMiddleName VARBINARY(200),
eLastName   VARBINARY(200));
GO

— EncryptByKey demonstration encrypts 100 names from the Person.Contact table

OPEN SYMMETRIC KEY TestSymmetricKey
   DECRYPTION
BY CERTIFICATE TestCertificate;
  
GO

INSERT
INTO
Person.#Temp (ContactID, eFirstName, eMiddleName, eLastName)
SELECT ContactID,
  
EncryptByKey(Key_GUID(‘TestSymmetricKey’), FirstName),
  
EncryptByKey(Key_GUID(‘TestSymmetricKey’), MiddleName),
  
EncryptByKey(Key_GUID(‘TestSymmetricKey’), LastName)
FROM Person.Contact
WHERE ContactID <= 100;

–View the Encrypted data

SELECT *
FROM Person.#Temp

The results are displayed here:image

The data can then be decrypted using the symmetric key and certificate:

-- DecryptByKey demonstration decrypts the previously encrypted data

UPDATE Person.#Temp
SET FirstName = DecryptByKey(eFirstName),
  
MiddleName = DecryptByKey(eMiddleName),
  
LastName = DecryptByKey(eLastName);
GO

— View the results

SELECT *
FROM Person.#Temp;
GO

The FirstName, MiddleName, and LastName columns now contain the decrypted values of hte eFirstName, eMiddleName, and eLastName.  The below query will clean up some of the items created in the previous examples.

 

— Clean up work:  drop temp table, symmetric key, test certificate and master key

DROP TABLE Person.#Temp;
GO

CLOSE SYMMETRIC KEY TestSymmetricKey;
GO

DROP SYMMETRIC KEY TestSymmetricKey;
GO

DROP CERTIFICATE TestCertificate;
GO

DROP SYMMETRIC KEY  TestSymKey;
GO

The ability to encrypt and decrypt data using t-sql is an awesome tool, but this feature tends to benefit the developer a bit more than the database administrator.  Being tasked with maintaining and managing databases that are used for a front end application limits where the dba can implement encryption without having to modify source code or stored procedures created by the developer.

To give an example of how encryption can be utilized to secure column level data in a more realistic demonstration look at the code below.  The t-sql code will create two logins, login1 and login2, who will be given insert and select permissions on the AdventureWorks database.  A table called employeessn is created that holds all employees names and social security numbers.  A symmetric key is first create with encryption by password and authorization is granted to login1.  Data is inserted into the employeessn table using the symmetric key to encrypt the ssn column.  The statements following the insert statement demonstrate how login1 is able to open the key and decrypt the data while login2 is unable to open the key.  In order to demonstrate how keys can be modified a certificate is created with authorization given to login1 and the symmetric key is altered to use the new certificate and then the encryption by password is dropped.  Using the WITH EXECUTE AS the decrypted data is selected by login1.  Finally a stored procedure is created that will execute as login1 and will utilize the certificate secured key to decrypt the data.  Initially login1 and login2 are granted execute permissions on the procedure and both are able to execute and see the decrypted social security numbers.  To demonstrate the versatility of the WITH EXECUTE AS in the stored procedure login2 is revoked execute permissions of the stored procedure so all attempts to execute result in failure.

USE MASTER;
GO

--Create logins login1 and login2

CREATE LOGIN Login1
WITH PASSWORD='Pa$$w0rd';
GO

CREATE LOGIN Login2
WITH PASSWORD = 'Pa$$w0rd';
GO

--Create database users in AdventureWorks login1 and login2 that map back to the sql server logins

USE AdventureWorks;
GO

CREATE USER login1
FOR LOGIN Login1;
GO

CREATE USER login2
FOR LOGIN Login2;
GO   
--Create a table to hold an employees social security #

CREATE TABLE employeessn
(employee  VARCHAR(50),
ssn        VARBINARY(100))
GO   
--Give access to this table to login1 and login2 so they can SELECT and INSERT data

GRANT SELECT,
    
INSERT
TO
login1;
GO

GRANT SELECT,
    
INSERT
TO
login2;
GO    
--Create a symmetric key exncrypted with a password and authorize the user ONLY login1

CREATE SYMMETRIC KEY sensitive_data
AUTHORIZATION login1
WITH ALGORITHM=TRIPLE_DES
ENCRYPTION BY PASSWORD='Pa$$w0rd';
GO   
--Use EXECUTE AS to change the security context of the current query

EXECUTE AS LOGIN='login1';
GO   
--Open the key with the password
OPEN SYMMETRIC KEY sensitive_data
DECRYPTION
BY PASSWORD='Pa$$w0rd';
GO    
--INSERT data into the table encrypting the ssn
INSERT INTO employeessn
VALUES ('David', ENCRYPTBYKEY(KEY_GUID('sensitive_data'), '123-45-7777')),
      (
'Brandon', ENCRYPTBYKEY(KEY_GUID('sensitive_data'), '123-45-6666')),
      (
'Chase', ENCRYPTBYKEY(KEY_GUID('sensitive_data'), '123-45-8888')),
      (
'Derek', ENCRYPTBYKEY(KEY_GUID('sensitive_data'), '123-45-9999'));
GO

CLOSE ALL SYMMETRIC KEYS;
GO

--View the encrypted ssn's
SELECT *
FROM employeessn;
GO

--Open the symmetric key to decrypt the employees ssn
OPEN SYMMETRIC KEY sensitive_data
DECRYPTION
BY PASSWORD='Pa$$w0rd'
GO   
SELECT Employee,
     
CONVERT(VARCHAR, DECRYPTBYKEY(ssn))  
FROM employeessn
GO

–Close the symmetric key
CLOSE ALL SYMMETRIC KEYS
GO

–Revert back to sysadmin
REVERT;
GO

–Use EXECUTE AS to change the security context of the current query
EXECUTE AS LOGIN=‘login2’;
GO

–Attempt to open the symmetric key as login2 and notice that login2 does not have permission to open
OPEN SYMMETRIC KEY sensitive_data
DECRYPTION
BY PASSWORD=‘Pa$$w0rd’
GO     

–Revert back to sysadmin in order to alter the symmetric key and create new certificate
REVERT;
GO

–Now create a new certificate to use for the symmetric key and remove the encryption by password
CREATE CERTIFICATE SSNCert
AUTHORIZATION login1
WITH SUBJECT=‘Certificate used to decrypt SSNs’;
GO

–The key must be open to make modifications
OPEN SYMMETRIC KEY sensitive_data
DECRYPTION
BY PASSWORD=‘Pa$$w0rd’;
GO

–Add the encryption by certificate first
ALTER SYMMETRIC KEY sensitive_data
ADD ENCRYPTION BY CERTIFICATE SSNCert
GO

–Remove the password encryption
ALTER SYMMETRIC KEY sensitive_data
DROP ENCRYPTION BY PASSWORD= ‘Pa$$w0rd’;  
GO

–Close the symmetic key
CLOSE ALL SYMMETRIC KEYS;

GO

–Use EXECUTE AS to change the security context of the current query
EXECUTE AS LOGIN=‘login1’;
GO

–Open the symmetric key using the certificate
OPEN SYMMETRIC KEY sensitive_data
DECRYPTION
BY CERTIFICATE SSNCert;
GO

–SELECT from the employeessn table and decrypt the ssn
SELECT employee,
     
CONVERT(VARCHAR, DECRYPTBYKEY(ssn))  
FROM employeessn;
GO

–Revert back to the sysadmin
REVERT;
GO

–Create a stored procedure that will execute as logn1 and decrypt the employee’s ssn
CREATE PROC decryptaslogin1
 
WITH EXECUTE AS ‘login1’
AS
OPEN
SYMMETRIC KEY sensitive_data
DECRYPTION
BY CERTIFICATE SSNCert;

SELECT Employee,
     
CONVERT(VARCHAR, DECRYPTBYKEY(ssn))  
FROM employeessn;

CLOSE ALL SYMMETRIC KEYS;
GO

–Grant execute permission to both login1 and login2
GRANT EXECUTE ON decryptaslogin1
TO login1;
GO

GRANT EXECUTE ON decryptaslogin1
TO login2;
GO

–Use EXECUTE AS to changge the security context of the current query
EXECUTE AS LOGIN=‘login1’;
GO

–Notice that login1 is able to execute the proc and see the decrypted ssn’s
EXEC decryptaslogin1;
GO


–Revert back to sysadmin
REVERT;
GO

–Use EXECUTE AS to change the security context of the current query
EXECUTE AS LOGIN=‘login2’;
GO

–Notice login2 is able to execute the procedure and see the decrypted ssn’s as the procedure is running as login1
EXEC decryptaslogin1;
GO

–Revert back to sysadmin
REVERT;
GO

–Revoke permissions of execute to login2 for the decryptaslogin1 procedure
REVOKE EXECUTE ON decryptaslogin1
TO login2;
GO

–Use EXECUTE AS to change the security context of the current query
EXECUTE AS LOGIN=‘login2’;
GO

–Notice that as execute permissions are revoked for login2 execution fails
EXEC decryptaslogin1;
GO

REVERT;
GO

–Assure that the symmetric key is closed before cleanup
CLOSE ALL SYMMETRIC KEYS;
GO

–Cleanup all keys, logins, certs, procs and tables
DROP PROC decryptaslogin1;
GO

DROP TABLE employeessn;
GO

DROP SYMMETRIC KEY sensitive_data;
GO

DROP CERTIFICATE SSNCert;
GO

DROP USER login1;
GO

DROP USER login2;
GO

USE MASTER;
GO

DROP LOGIN login1;
GO

DROP LOGIN login2;
GO

While preparing this post I came across some excellent reference material which I have tried to include in hyperlinks.  Once such article was written by Michael Coles on SQLServerCentral that provides some great insight and examples on encrypting data using SQL.

More To Come At The New SQL Safety!!

sqlsafety 0 Comments

As I have spent the past few days preparing to present and customize the new SQL Safety site I have not had time to prepare any new posts.  I appreciate the input that I have received over the past couple of months and have done my best fulfill all requests, first and foremost providing all available code, scripts, packages, etc.  I decided that it was best to provide all code for the most recent posts on my blogspot site, as these were quickly available, and then to provide the code for the most popular posts over the past several years.  After reviewing the hit counts I found that I had two posts, Encrypting Column Level Data and Formatting SSRS, that counted for a large percentage of over all site hits.  I will will be completing and posting, for download, a detailed PDF outlining my SSRS formatting post as well an updated T-SQL script for encrypting column level data for download  within the next week.

A big thanks again to everyone and please check back soon for new posts and sample scripts.

Hello world!

sqlsafety 0 Comments

After some years on Googles Blogspot I have decided to host my own site using WordPress.  Beginning next week my first official post will be published on this site.


Hit Counter provided by Sign Holders