Tag Archives: Security

How to mimic a wildcard search on Always Encrypted columns with Entity Framework


The title of this post should have been “How to implement wildcard search functionality with Always Encrypted, make deterministic encryption safer, and load initial data using SqlBulkInsert”, but as you understand, that’s just too long for a title.

A number of years ago I built a web application with “Always Encrypted” as VARBINARY columns, before Microsoft offered this feature out of the SQL Server Box. So in case the database server would be compromised by hackers, no client details could be revealed.

Just like Microsoft my Data Access Layer, built in .NET did the encryption and decryption, transparent for other code that accessed it. And I had the same challenge how to search on encrypted columns.

So with (maybe more than average) interest I was looking forward to know how the Always Encrypted feature of SQL Server 2016 works.

The full article is posted on SQLShack.com, so you can read further there ..

Free download: Blog Yearbook 2016

I have made a compilation of all my blog posts of the past year, and it was more work than you would think.
Especially because it also contains a non-published new article.

After filling in the form you can download the e-book for free.

    I promise you the following:

    • I will not give or sell your email address to any third party.
    • I will not spam you.
    • If I start with a newsletter, you can easily unsubscribe or change the frequency.

    (c) 2016 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.

    Using sensitive parameters (SSIS Series)


    Change log

    Date Changes
    27 August, 2017 Alan brought to my attention that the sensitive value might get exposed in the log. And yes, he was right. I found out that the Raise Change Event (value True) caused this, so I put it to False. Then everything still worked and the sensitive value was not exposed via the log. So the changed advice is to leave Raise Change Event on False for the variable containing the plain value of the sensitive parameter.


    This time I want to tell something about the use of sensitive parameters. Passwords, connection strings, anything that is .. uhm .. sensitive.


    SSIS Package Parameters and Environment variables can hold sensitive values, like passwords, connection strings and so on. You do not want these to be seen by people who are not authorized to have access to this information.


    First I want to give a *short* intro on how (not) to save sensitive parameters in SSIS.
    There are several options which are explained enough on other internet sites:

    • DontSaveSensitive
    • EncryptSensitiveWithUserKey
    • EncryptSensitiveWithPassword
    • EncryptAllWithPassword
    • EncryptAllWithUserKey

    My opinion on those options:

    • When you use a *WithUserKey value, you might get into trouble when someone else from the development team wants to work on a SSIS Package.
    • When you use a *WithPassword value, you have to type (or paste) the password every time you open the package in Visual Studio. I personally really disklike that.
    • But more important, why would you care about saving the sensitive data at all? Thinking about professional SSIS package development, you would not want sensitive data like passwords and connection strings hard coded in the package anyway, while you can simply configure your packages from environment variables as explained in two of my earlier blog posts. So, as you might guess, DontSaveSensitive is my favorite option.

    So the rest of this article is based on using the DontSaveSensitive property.
    You have to set it in the SSIS package, but also in the project properties:
    ssis-e11-436Setting the package property “ProtectionLevel”
    ssis-e11-437Setting the Project Protection Level

    I have set up a very simple demo. It consists of the following steps:

    Set up a folder and environment in SSISDB if not done already

    See the script for this in my previous blog post.

    Create a sensitive environment variable “MyDBConnectionString”.

    In the demo I use my existing DEV folder with the DEV environment inside that folder.
    In my case I use Windows Integrated Security, therefore the connection string on itself is not sensitive. I’ll just use this example, because a connection string could also contain a username and password.

    ssis-e11-458The environment variable “MyDBConnectionString”. Check the checkbox to make it sensitive

    Create a SSIS Package with a sensitive parameter “MyDBConnectionString”

    Do I need to say more?

    In the SSIS Package create a (non-sensitive) variable “MyDBConnectionString”

    Not so exiting either, except that you should set Raise Change Event to True for the variable, so that any other properties set by an expression using the variable will certainly change when the variable changes.
    Before you can do that, you must make sure that the column is visible. The printscreens below explain how.
    ssis-e11-439Clicking on this icon shows the Variable Grid Options
    ssis-e11-440On the Variable Grid Options check “Raise event when variable value changes”
    ssis-e11-441For the variable set “Raise Change Event” to True

    Don’t set “Raise Change Event” to True!
    See change log at the top of the article for details.

    ssis-e11-455Here you can see that the Expression @[User::MyDBConnectionString] is used to set the ConnectionString property of the OLEDB Connection Manager OLE_SRC_MyDatabase.

    Copy the parameter value to the variable value using a Script Task

    The “problem” is that you cannot use the sensitive parameter in an expression that uses the value for a non-sensitive property, in my case the ConnectionString property of an OLEDB Connection Manager.
    This error is shown:

    This is why you have to copy the value of the sensitive parameter to a non-sensitive variable first using a Script Task.
    I personally think it is a pity that Microsoft did not create a GETSENSITIVEVALUE(« SensitiveParameterOrVariable ») expression, so you could use an Expression Task, but they didn’t, so a script task is the way to go.
    Add a Script Task to the control flow of your SSIS Package and adjust public void Main() as below:

    public void Main()
        // Copy sensitive value to normal (non-sensitive) variable, so that it can be used in an expression.
        string value = Dts.Variables["$Package::MyDBConnectionString"].GetSensitiveValue().ToString();
        if (!string.IsNullOrEmpty(value))
          // Only copy, if not empty. Else preserve design time value, for instance for debugging.
          Dts.Variables["User::MyDBConnectionString"].Value = value;
        Dts.TaskResult = (int)ScriptResults.Success;
      catch (Exception e)
        Dts.Log(e.Message, 0, null);
        Dts.TaskResult = (int)ScriptResults.Failure;
        throw (e);
      Dts.TaskResult = (int)ScriptResults.Success;

    The control flow of the demo package now looks as follows:
    The final control flow of the demo package

    To demonstrate how it works, the “design time” connection string, that is stored in the variable MyDBConnectionString, is different from the sensitive environment variable MyDBConnectionString.
    I have added an Execute SQL Task SQL_FooBar that uses the OLEDB Connection Manager OLE_SRC_MyDatabase.
    The connection string in the variable connects to a database named FF (which is Dutch SMS talk for “Just a moment”/temporary).
    The connection string in the sensitive environment variable connects to tempdb.
    To proof that the connection string from the sensitive environment variable (which passes it to the package parameter) is used during package execution, I take the FF database offline, after the OLEDB Connection Manager is configured.
    ssis-e11-443Taking database FF offline ..

    So when the connection manager would not be configured from the sensitive parameter during run time, the Execute SQL Task would certainly fail during execution.

    Now it’s time to deploy my project with the testpackage to my DEV folder in SSISDB. I do not have screenshots of that, because I assume that you will know how to do this. If not, check my previous blog posts.

    ssis-e11-450This time I do the configuration manually: in the project I add a reference to the DEV environment (not in the picture), then I connect the environment variable to the package parameter.

    After it is configured, I execute the package.


    Shortly after it has succeeded, proof for me that it was using the configured connectionstring with database tempdb, and not the design time value with database FF.

    Considerations for debugging

    If you want to debug the SSIS package in Visual Studio you have a problem: due to DontSaveSensitive the package parameter will have no value, and you cannot debug the package.
    My workaround for this is to put the debug value for the sensitive value in the SSIS variable. In the Script Task the variable will only get the value from the parameter when it is not empty. So during debug time, the variable value will be preserved.
    If your value is too sensitive for this, either use a less sensitive value referring to a test database/server etc., or remove the value from the variable after debugging the package. In the latter case, also delete copies of the package in the bin folder.

    Will my sensitive value be safe in an environment variable?

    Short answer: yes, I think pretty safe.
    Why? If a DBA unticks the “Sensitive” checkbox, the value is wiped out and cannot be read.
    Of course a DBA or hacker with sysadmin access to the SQL Instance and knowledge could deploy a SSIS Package, just to pull out the sensitive value. But there are little things a DBA cannot do..

    Conclusion / Wrap up

    In this article you could read some practical insights on using the ProtectionLevel DontSaveSensitive for a SSIS package containing a sensitive parameter configured from a sensitive environment variable during execution.
    Steps to take are:

    1. Create a folder in SSISDB. In that folder, create an environment. In my example both the folder and environment were called DEV.
    2. Create a sensitive environment variable.
    3. Create a SSIS package with a sensitive parameter and a (non-sensitive) variable with the same name.
    4. In the SSIS package create a script task to set the value of the variable to the value of the parameter.
    5. In the SSIS package use an expression to use the variable value where needed, for example in a OLEDB Connection Manager or a FTP Task.

    (c) 2016/2017 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.